| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: reduce dblink (database link) chatter
On Mon, 01 Aug 2005 22:15:00 GMT, netcomradeNSPAM_at_bookexchange.net
(NetComrade) wrote:
>I have a query that requires a join over the link.
>It doesn't return a lot of info from the link (5-20 rows), however,
>the trace file shows plenty of waits on db link.
>
>Is there are any way to reduce the # of roundtrips?
>Upping the arraysize (at least through sql*plus) didn't seem to modify
>the behavior.
>
>Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total Waited
> -------------------------------- Waited ---------- ------------
> SQL*Net message to client 117 0.00 0.00
> SQL*Net message from client 117 0.04 0.55
> SQL*Net message to dblink 14218 0.01 0.02
> SQL*Net message from dblink 14218 0.14 26.81
> file open 8 0.00 0.00
> direct path write 17 0.02 0.18
> direct path read 18 0.02 0.08
> db file sequential read 72 0.03 0.87
>************************************************************************
>
>
>call count cpu elapsed disk query current rows
>------- ----- ------- -------- ------ ------- -------- --------
>Parse 16 0.09 0.38 0 0 0 0
>Execute 16 0.27 1.00 0 0 0 0
>Fetch 101 5.00 30.05 90 1491 29 158
>------- ----- ------- -------- ------ ------- -------- --------
>total 133 5.36 31.43 90 1491 29 158
Actually, i just noticed that the same sql coming from client vs. sql coming from sql*plus have different explain plans.. i'll investigate more tomorrow.. the one from sql*plus has a much 'smaller' chatter (as in waits per execution or waits per row). The explain plan coming from client (customer pro*c) app contains multiple calls to 'remote' whereas sql*plus contains only one (3 remote queries vs. 1)
This is weird.. the only difference is usage of bind vars in the app (we'll test more later.)
call count cpu elapsed disk query current rows ------- ------ -------- ------ ------ ------- ---------- ----------
Parse 12 0.09 0.31 0 2 6 0 Execute 12 0.03 0.01 0 0 0 0 Fetch 24 0.06 0.95 0 780 0 84 ------- ------ -------- ------ ------ ------- ---------- ---------- total 48 0.18 1.27 0 782 6 84 Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited -------------------------------- Waited ---------- ------------ SQL*Net message to dblink 196 0.00 0.00 SQL*Net message from dblink 196 0.05 0.91 SQL*Net message to client 24 0.00 0.00 SQL*Net more data from dblink 48 0.03 0.30 SQL*Net message from client 24 14.11 26.88 ************************************************************************~
 |
 |