Extreme slowdown of update statement

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: