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

Alex Daman wrote:

> Hello All,
>
> Platform / Oracle Server Version - Oracle Ent Edition 8.1.7.4 on AIX 4.3.3
>
>
> We have a PL/SQL package that purges data from one database and inserts it
> into another database (Archive Database) Using a dblink.
>
> This package has been running everyday for the past 2 months. Recently we
> incurred a performance issue with the package and noticed that the INSERT
> statement was taking a long time to complete.
>
> After running a trace I got these alarming figures below:
>
> >From the trace file:
>
> 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
>
> Optimizer goal: CHOOSE
>
> Parsing user id: 20 (FORTE) (recursive depth: 1)
>
>
>
> Rows Execution Plan
>
> ------- ---------------------------------------------------
>
> 0 INSERT STATEMENT GOAL: CHOOSE (REMOTE)
>
> 0 REMOTE [!]
>
> SELECT "GENNUMBER","RECEIPTKEY","RECEIPTLINENUMBER",
>
> "EXTERNRECEIPTKEY","EXTERNLINENO","STORERKEY","POKEY",
>
> "TARIFFKEY","SKU","ALTSKU","ID","STATUS","DATERECEIVED",
>
> "QTYEXPECTED","QTYADJUSTED","QTYRECEIVED","UOM","PACKKEY",
>
> "VESSELKEY","VOYAGEKEY","XDOCKKEY","CONTAINERKEY","TOLOC",
>
> "TOLOT","TOID","CONDITIONCODE","LOTTABLE01","LOTTABLE02",
>
> "LOTTABLE03","LOTTABLE04","LOTTABLE05","LOTTABLE06",
>
> "LOTTABLE07","LOTTABLE08","LOTTABLE09","LOTTABLE10","CASECNT",
>
> "INNERPACK","PALLET","CUBE","GROSSWGT","NETWGT","OTHERUNIT1",
>
> "OTHERUNIT2","UNITPRICE","EXTENDEDPRICE","EFFECTIVEDATE",
>
> "ADDDATE","ADDWHO","EDITDATE","EDITWHO","FORTE_FLAG",
>
> "TRAFFICCOP","ARCHIVECOP","SUSR1","SUSR2","SUSR3","SUSR4",
>
> "SUSR5","NOTES","VENDORKEY","CANCELLED","CSADDED","CSUPDATED",
>
> "CSADDWHO","CSADDDATE","CSEDITWHO","CSEDITDATE","POREMARKS"
>
> FROM "FORTE"."RECEIPTDETAIL" "A2" WHERE "RECEIPTKEY"=:1
>
>
>
>
>
> I don't know how to debug this issue and I am hoping somebody out there in
> DBA land can help.
>
> Thanks in advance.
>
>
>
> Alex
>
>
>

Enable a 10046 Trace on the Remote DB and see what is happening.

Or Query the V$session_wait view to know what is happening in the db when the insert is happening.

HTH

-- 

Cheers
Ganesh Raja
ganesh.raja_at_nospam.mantas.com
<Remove nospam to Mail>