Re: Extreme slowdown of update statement
- From: "LS Cheng" <exriscer@xxxxxxxxx>
- To: vbarac@xxxxxxxxxxxx
- Date: Mon, 29 Dec 2008 17:11:12 +0100
Hi
Your tkprof looks a bit strange, what were the options you used?
In the excution plan we should see c and p reads and elapsed time.
Thanks
--
LSC
On Sun, Dec 28, 2008 at 8:30 PM, Vladimir Barac <vbarac@xxxxxxxxxxxx> wrote:
> 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
> 54039
> Misses 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) OF
> '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
>
> ********************************************************************************
>
>
>
> ********************************************************************************
> 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.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
> 54039
> Misses 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) OF
> '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)
>
> ********************************************************************************
>
> ______________________________________________________________________
> This 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.
> ______________________________________________________________________
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
