Monday, August 3, 2009

IPC vs TCP

I did some quick tests to compare performance of TCP database links vs IPC database links to see if it would make sense to change the communication between databases on the same server. Both tests indicated IPC performance was better than TCP, but only by 6-7%.

Details on Oracle IPC can be found in Metalink Note 29232.1.

I ran two tests and recorded transaction runtimes :-

Test 1. insert into local table select 301049 rows

LOCAL source table :- 00:00:01.93
IPC database link :- 00:00:02.16 (11% slower than local table)
TCP database link :- 00:00:02.32 (20% slower than local table, 7.5% slower than IPC database link)

Test 2. a plsql loop doing 301049 inserts into a local table

LOCAL source table :- 00:02:01.79
IPC database link :- 00:02:11.74 (5% slower than local table)
TCP database link :- 00:02:24.18 (10% slower than local table, 6% slower than IPC database link)

# Test 1
# tnsnames.ora
NFR_CFD_TCP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=test-vip-10)(PORT=1587))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NFR_CFD)
)
)


NFR_CFD_IPC =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = NFR_CFD))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = NFR_CFD)
)
)

# listener.ora

LISTENER_TEST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = NFR_CFD))
)
)

SID_LIST_LISTENER_TEST =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = nfr_cfd)
(SID_NAME = tl01cfd1)
(ORACLE_HOME = /opt/oracle/product/10.2.0/db_2)
)
)

create database link NFR_CFD_TCP connect to testuser identified by xxx using 'NFR_CFD_TCP';
create database link NFR_CFD_IPC connect to testuser identified by xxx using 'NFR_CFD_IPC';

create table local as select * from dba_source where rownum < 2;
create table ipc as select * from dba_source where rownum < 2;
create table tcp as select * from dba_source where rownum < 2;

create or replace procedure local_proc as
cursor source is select * from sys.dba_source;
begin
for i in source loop
insert into local values (i.owner, i.name, i.type, i.line,i.text);
end loop;
end;
/
show errors;

create or replace procedure tcp_proc as
cursor source is select * from sys.dba_source@NFR_CFD_TCP;
begin
for i in source loop
insert into tcp values (i.owner, i.name, i.type, i.line,i.text);
end loop;
end;
/
show errors;

create or replace procedure ipc_proc as
cursor source is select * from sys.dba_source@NFR_CFD_IPC;
begin
for i in source loop
insert into ipc values (i.owner, i.name, i.type, i.line,i.text);
end loop;
end;
/
show errors;

ORADBA @tl01cfd1> insert into LOCAL select * from sys.dba_source;

301049 rows created.

Elapsed: 00:00:01.93

Statistics
----------------------------------------------------------
2927 recursive calls
55101 db block gets
31185 consistent gets
3 physical reads
43444304 redo size
836 bytes sent via SQL*Net to client
739 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
301049 rows processed

ORADBA @tl01cfd1> insert into IPC select * from sys.dba_source@NFR_CFD_IPC;

301049 rows created.

Elapsed: 00:00:02.16

Statistics
----------------------------------------------------------
2922 recursive calls
64791 db block gets
11236 consistent gets
2 physical reads
43444528 redo size
836 bytes sent via SQL*Net to client
749 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
301049 rows processed

ORADBA @tl01cfd1> insert into TCP select * from sys.dba_source@NFR_CFD_TCP;

301049 rows created.

Elapsed: 00:00:02.32

Statistics
----------------------------------------------------------
2912 recursive calls
64776 db block gets
11241 consistent gets
2 physical reads
43448712 redo size
836 bytes sent via SQL*Net to client
749 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
301049 rows processed

truncate table local;
truncate table tcp;
truncate table ipc;

# Test 2

ORADBA @tl01cfd1> exec local_proc

PL/SQL procedure successfully completed.

Elapsed: 00:02:01.79
ORADBA @tl01cfd1> exec ipc_proc

PL/SQL procedure successfully completed.

Elapsed: 00:02:11.74
ORADBA @tl01cfd1> exec tcp_proc

PL/SQL procedure successfully completed.

Elapsed: 00:02:24.18