Extreme slowdown of update statement
From: Vladimir Barac <vbarac_at_Alghanim.com>
Date: Sun, 28 Dec 2008 22:30:04 +0300
Message-ID: <C85D70C785B07043A485C8AE85E8F671043AAED558@ALGMB.Alghanim.com>
UPDATE /*+ no_parallel(x) */ ELE_PRODUCT X SET X.FIRST_FOB_PRICE =(SELECT MAX(D.UNIT_PRICE) FROM ELEC_PO_ORDER_DETAILS D,ELEC_PO E,(SELECT MIN(B.DATE_CREATED) MIN_DATE,MAX(B.DATE_CREATED) MAX_DATE,A.ITEM_NO FROM ELEC_PO_ORDER_DETAILS A,ELEC_PO B
WHERE
A.COMP_CODE=B.COMP_CODE AND A.PO_NO=B.PO_NO GROUP BY ITEM_NO) C WHERE D.COMP_CODE=E.COMP_CODE AND D.PO_NO=E.PO_NO AND E.DATE_CREATED = C.MIN_DATE AND D.ITEM_NO = X.ITEM_CODE AND C.ITEM_NO = X.ITEM_CODE) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Optimizer mode: ALL_ROWS
Parsing user id: 24 (DM_ELEC_MANAGER) (recursive depth: 2) Rows Execution Plan
WHERE
A.COMP_CODE=B.COMP_CODE AND A.PO_NO=B.PO_NO GROUP BY ITEM_NO) C WHERE D.COMP_CODE=E.COMP_CODE AND D.PO_NO=E.PO_NO AND E.DATE_CREATED = C.MIN_DATE AND D.ITEM_NO = X.ITEM_CODE AND C.ITEM_NO = X.ITEM_CODE) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Optimizer mode: ALL_ROWS
Parsing user id: 24 (DM_ELEC_MANAGER) (recursive depth: 1) Rows Execution Plan
Date: Sun, 28 Dec 2008 22:30:04 +0300
Message-ID: <C85D70C785B07043A485C8AE85E8F671043AAED558@ALGMB.Alghanim.com>
Hi, listers
Oracle EE 10.2.0.4 64bit, Linux
We have some update statement (part of PL/SQL procedure) that may run for less than a minute if executed from SQL*Plus prompt or may take ~30 minutes if executed as a DBMS_SCHEDULER scheduled job. TKPROF output is pasted below.
What could be the reason for this behaviour? Is there some "slowdown" imposed by DBMS_SCHEDULER?
All insights are welcomed,
Vladimir Barac
UPDATE /*+ no_parallel(x) */ ELE_PRODUCT X SET X.FIRST_FOB_PRICE =(SELECT MAX(D.UNIT_PRICE) FROM ELEC_PO_ORDER_DETAILS D,ELEC_PO E,(SELECT MIN(B.DATE_CREATED) MIN_DATE,MAX(B.DATE_CREATED) MAX_DATE,A.ITEM_NO FROM ELEC_PO_ORDER_DETAILS A,ELEC_PO B
WHERE
A.COMP_CODE=B.COMP_CODE AND A.PO_NO=B.PO_NO GROUP BY ITEM_NO) C WHERE D.COMP_CODE=E.COMP_CODE AND D.PO_NO=E.PO_NO AND E.DATE_CREATED = C.MIN_DATE AND D.ITEM_NO = X.ITEM_CODE AND C.ITEM_NO = X.ITEM_CODE) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.06 0.07 1 432 0 0 Execute 1 1747.92 1732.64 209 753991 120144 54039 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 1747.98 1732.72 210 754423 120144 54039Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 24 (DM_ELEC_MANAGER) (recursive depth: 2) Rows Execution Plan
------- --------------------------------------------------- 0 UPDATE STATEMENT MODE: ALL_ROWS 0 UPDATE OF 'ELE_PRODUCT' 0 TABLE ACCESS MODE: ANALYZED (FULL) OF 'ELE_PRODUCT' (TABLE) 0 SORT (AGGREGATE) 0 HASH JOIN 0 NESTED LOOPS 0 VIEW 0 SORT (GROUP BY) 0 NESTED LOOPS 0 INDEX MODE: ANALYZED (RANGE SCAN) OFUPDATE /*+ no_parallel(x) */ ELE_PRODUCT X SET X.FIRST_FOB_PRICE =(SELECT MAX(D.UNIT_PRICE) FROM ELEC_PO_ORDER_DETAILS D,ELEC_PO E,(SELECT MIN(B.DATE_CREATED) MIN_DATE,MAX(B.DATE_CREATED) MAX_DATE,A.ITEM_NO FROM ELEC_PO_ORDER_DETAILS A,ELEC_PO B
'ELEC_PO_ORDER_DETAILS1' (INDEX)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'CUST_ELEC_PO01' (INDEX)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'ELEC_PO2' (INDEX) 0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'ELEC_PO_ORDER_DETAILS1' (INDEX) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ db file sequential read 209 0.10 1.45 log file switch completion 4 0.09 0.18 log buffer space 5 0.25 0.74 ******************************************************************************** ********************************************************************************
WHERE
A.COMP_CODE=B.COMP_CODE AND A.PO_NO=B.PO_NO GROUP BY ITEM_NO) C WHERE D.COMP_CODE=E.COMP_CODE AND D.PO_NO=E.PO_NO AND E.DATE_CREATED = C.MIN_DATE AND D.ITEM_NO = X.ITEM_CODE AND C.ITEM_NO = X.ITEM_CODE) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.05 0.22 2 432 0 0 Execute 1 28.68 28.62 0 743780 119906 54039 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 28.73 28.84 2 744212 119906 54039Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 24 (DM_ELEC_MANAGER) (recursive depth: 1) Rows Execution Plan
------- --------------------------------------------------- 0 UPDATE STATEMENT MODE: ALL_ROWS 0 UPDATE OF 'ELE_PRODUCT' 0 TABLE ACCESS MODE: ANALYZED (FULL) OF 'ELE_PRODUCT' (TABLE) 0 SORT (AGGREGATE) 0 HASH JOIN 0 NESTED LOOPS 0 VIEW 0 SORT (GROUP BY) 0 NESTED LOOPS 0 INDEX MODE: ANALYZED (RANGE SCAN) OFThis e-mail message and any attachments to it are for the sole use of the intended recipients and may contain confidential and privileged information. This e-mail message and any attachments are the property of Yusuf A. Alghanim & Sons w.l.l. or any of its subsidiaries or affiliates (“Alghanim Industries”). Any unauthorized review, use, disclosure, or distribution of this e-mail message or its attachments is prohibited. Any opinions expressed in this message are those of the author and do not necessarily reflect the opinion of Alghanim Industries. If you are not an intended recipient, please notify the sender by reply e-mail and destroy all copies of the original message and any attachments.
'ELEC_PO_ORDER_DETAILS1' (INDEX)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'CUST_ELEC_PO01' (INDEX)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'ELEC_PO2' (INDEX) 0 INDEX MODE: ANALYZED (RANGE SCAN) OF 'ELEC_PO_ORDER_DETAILS1' (INDEX) ******************************************************************************** ______________________________________________________________________
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Dec 28 2008 - 13:30:04 CST