NetComrade wrote:
>
> 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
> ************************************************************************
> ~
> ~
> .......
> 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_at_yahoo.com
"Semper in excremento, sole profundum qui variat."
------------------------------------------------------------
Received on Tue Aug 02 2005 - 08:08:09 CDT
