Re: Extreme slowdown of update statement

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.
> ______________________________________________________________________
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

Other related posts: