I saw many cases Oracle will parse each query if the query access the object
through db_link. From your trace, your query looks like go through db_link.
George
"Alex Daman" <alexd_at_apis.com.au> wrote in message
news:ch6e7b$2e8h$1_at_otis.netspace.net.au...
> Hi John / Ganesh
> Thanks for your reply.
>
> I turned on the session event suggested . These are the results I captured
> from the "Source" database ( The DB running the package ) :
>
> OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
>
> call count cpu elapsed disk query current
> rows
> ------- ------ -------- ---------- ---------- ---------- ---------- ----
> Parse 2407 40.78 664.06 1 4759 20
> 0
> Execute 1151 13.04 45.34 17 198 2074
> 103
> Fetch 3986 0.21 1.09 250 5938 25
> 3195
> ------- ------ -------- ---------- ---------- ---------- ---------- ----
> total 7544 54.03 710.49 268 10895 2119
> 3298
>
> Misses in library cache during parse: 120
> Misses in library cache during execute: 66
>
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total
> Waited
> ----------------------------------------
> Waited ---------- ------------
> latch free 3234 0.02
> 0.17
> file open 3 0.05
> 0.05
> db file scattered read 33 0.14
> 0.23
> SQL*Net message to dblink 22926 0.07
> 0.49
> SQL*Net message from dblink 22925 0.34
> 390.81
> SQL*Net more data to dblink 2069 0.04
> 0.32
> SQL*Net more data from dblink 13 0.00
> 0.00
> db file sequential read 20 0.03
> 0.27
> library cache pin 7696 3.08
> 297.17
>
> It appears as though the time is being spent here:
> SQL*Net message from dblink 22925 0.34
390.81
> library cache pin 7696 3.08
> 297.17
>
> The statistics in the destination database were all fine. What is the
cause
> of such wait times with these 2 events.
>
> Thanks again
>
> Alex
>
>
>
> "John" <nytimesjdarrah_at_hotmail.com> wrote in message
> news:289173ea.0409010735.193ff28c_at_posting.google.com...
> > What wait events are you seeing on source and target? Rerun your
> > trace at level 8 or level 12 (alter session set events '10046 trace
> > name context forever, level 8')
>
>
Received on Sun Sep 05 2004 - 16:18:01 CDT
