Hi,
I've done some test on AIX machine 9.2.0.8 EE DB .
Was expecting performance improvement but observed almost no difference :
IPC
*** SESSION ID:(11.28) 2010-03-05 11:30:05.385
********************************************************************************
alter session set events '10046 trace name context forever, level 12'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 2568
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 10.27 10.27
SQL*Net message to dblink 3 0.00 0.00
SQL*Net message from dblink 3 0.00 0.00
********************************************************************************
create table test1 as select * from adresy@WORLD
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 1 0
Execute 1 169.78 169.43 0 3038 3499 17835543
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 169.78 169.44 0 3038 3500 17835543
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 2568
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 4 0.00 0.00
SQL*Net message to dblink 547 0.00 0.00
SQL*Net message from dblink 547 0.00 0.11
SQL*Net more data from dblink 529041 0.02 6.96
direct path write 2 0.00 0.00
log file sync 2 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 137.19 137.19
********************************************************************************
TCP
alter session set events '10046 trace name context forever, level 12'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 2568
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 8.03 8.03
SQL*Net message to dblink 3 0.00 0.00
SQL*Net message from dblink 3 0.00 0.00
********************************************************************************
create table test1 as select * from adresy@WORLD
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 1 0
Execute 1 171.05 170.39 0 3046 3499 17835543
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 171.06 170.40 0 3046 3500 17835543
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 2568
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 4 0.00 0.00
SQL*Net message to dblink 547 0.00 0.00
SQL*Net message from dblink 547 0.00 0.13
SQL*Net more data from dblink 529041 0.01 5.79
direct path write 2 0.00 0.00
log file sync 2 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 166.90 166.90
********************************************************************************
Any ideas what can be improved ?
I've obserwer little less CPU utilization when IPC was used and no l0 (aix loopback interface) usage.
What thing which bothers me is LOCAL=NO when using dblink based on such tnsnames.ora entry:
PROD1L =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(key=XXXTEST))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD1)
)
)