SEO

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
........
We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes
remove NSPAM to email

On Mon, 01 Aug 2005 22:15:00 GMT, netcomradeNSPAM@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
************************************************** **********************
~
~
........
We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes
remove NSPAM to email

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
> .......
> We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes
> remove NSPAM to email

One thought ... try TCP.NODELAY=yes in SQLNET.ORA but I suspect it won't
help.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

ProC might set optimizer_mode=RULE in a beginning of session, and Plus
might set optimizer_mode=ALL_ROWS. RULE will cause nested loops -
frequent roundtrips.

NetComrade wrote:
> On Mon, 01 Aug 2005 22:15:00 GMT, netcomradeNSPAM@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
> ************************************************** **********************
> ~
> ~
> .......
> We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes
> remove NSPAM to email

Hello,

i think there is a compoleir flag in Pro *C that controls the cursor
size, so you can reduce the no.of trips for each fetch from the
database defined by the dblink.

also, the driving table makes a HUGE difference when it comes to
distributed queries. I think there is a very good section in the Oracle
9i Performance Tuning Manual about Distributed Queries and tuning. Also
look in Metalink for some very good notes.

you may want to check the network utilisation on the network card that
is used for db link, see if you can use a private link across instead
of using a public network link. this will surely help.

ORA600

NetComrade wrote:
>
> On Mon, 01 Aug 2005 22:15:00 GMT, netcomradeNSPAM@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
> ************************************************** **********************
> ~
> ~
> .......
> We use Oracle 8.1.7.4 and 9.2.0.5 on Solaris 2.7 boxes
> remove NSPAM to email

Could be arraysize ? row-by-row fetching isn't a good way to do things
(and sqlplus uses 10 or 15 by default)
--
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald@yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------