VERY HIGH PARSE ELAPSED TIME DURING INSERT OVER A DBLINK

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 Received on Wed Sep 01 2004 - 03:05:16 CDT