Re: VERY HIGH PARSE ELAPSED TIME DURING INSERT OVER A DBLINK

From: Alex Daman <alexd_at_apis.com.au>
Date: Thu, 2 Sep 2004 16:22:30 +1000
Message-ID: <ch6e7b$2e8h$1@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 Thu Sep 02 2004 - 01:22:30 CDT