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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 3 Sep 2004 07:03:21 +0000 (UTC)
Message-ID: <ch94vp$oqs$1@sparta.btinternet.com>

I wonder if the library caches misses are part of a general mix-up in the reporting.

A previous post showed that the statement was sent to the remote site for execution, and that the SELECT portion was then
sent back by the remote site to acquire
the data needed by the insert.

Perhaps the library misses are actually
related to this returning statement, rather than the original statement.

Do you have the part of the output that relates to the SELECT that got sent back - perhaps this would give some clues... possibly some indication of unexpected double-counting.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 2nd





"Matt" <mccmx_at_hotmail.com> wrote in message
news:cfee5bcf.0409020450.4c2a366c_at_posting.google.com...


> > INSERT INTO RECEIPTDETAIL_at_FORTE SELECT * FROM FORTE.RECEIPTDETAIL WHERE

> >

> >   RECEIPTKEY = :1

> >

> >

> >

> > call     count       cpu    elapsed       disk      query    current

> > rows

> >

>

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


> > ----

> >

> > Parse      478      9.84    1505.08          2          5        960

> > 0

> >

> > Execute    478      1.54     551.65          3          3          0

> > 0

> >

> > Fetch        0      0.00       0.00          0          0          0

> > 0

> >

>

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


> > ----

> >

> > total      956     11.38    2056.73          5          8        960

> > 0

> >

> >

> >

> > Misses in library cache during parse: 478

> >

>

> These statistics seem a little strange to me..  It looks like you are

> repeatedly running the statement (478 TIMES) with a different

> "RECEIPTKEY" value.

>

> The process is using a bind variable (:1) but you have a miss in the

> library cache every time you parsed the statement (i.e. a hard parse

> every time).  How big is your library cache/shared pool...?  (SQL>

> show parameter shared_pool).

>

Received on Fri Sep 03 2004 - 02:03:21 CDT