Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: VERY HIGH PARSE ELAPSED TIME DURING INSERT OVER A DBLINK

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US