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

> 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).

Of the 1505 seconds spent parsing this statement, only 9 seconds were spent actually executing on CPU... the rest will be due to waiting for library cache latches or dblink network latencies. Thats why a 10046 trace is required.

THe best way to improve the speed of this process would be to eliminate the hard parses.... Something is ageing yout statement out of the library cache repeatedly.. This could be a DDL statement on one of the tables in the SQL, or a very small library cache.

Matt Received on Thu Sep 02 2004 - 07:50:56 CDT