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

HI Guys,

Thanks for your help. I have resolved the issue.

Problem : There were many SYS packages that had become INVALID ( I dont know how this happened ) Once I recompiled them, our Package ran normally and we nolonger experienced performance problems in the library cache.

Thanks again to all who tried to help.

Regards

Alex

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:ch94vp$oqs$1_at_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 Sun Sep 05 2004 - 23:51:08 CDT