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_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
Received on Mon Aug 01 2005 - 20:12:29 CDT
