Statement consuming high CPU

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Fri, 7 Nov 2014 20:27:57 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED0E0F6A49_at_USA7109MB012.na.xerox.net>



Hi,
We have a statement, which is part of a standard Oracle E-Business Suite program, that is taking a long time to complete. It used to take ~1-2 minutes for the program to finish which executes this statement multiple times but now it is taking 20-25 minutes to complete. When I traced the program to see what it was actually doing, the statistics are showing that it is pretty much all CPU. So, there are a lot of DECODE and some ROUND functions in the statement but I am not sure if they would really require this much CPU. Overall, it is not a very complicated statement. What should I look for to see why this statement might be taking this much CPU time and what might be contributing to it? Thanks,
Amir

insert into MTL_TRANSACTION_ACCOUNTS(TRANSACTION_ID,REFERENCE_ACCOUNT,   LAST_UPDATE_DATE,LAST_UPDATED_BY,CREATION_DATE,CREATED_BY,LAST_UPDATE_LOGIN,   INVENTORY_ITEM_ID,ORGANIZATION_ID,TRANSACTION_DATE,TRANSACTION_VALUE,   GL_BATCH_ID,ACCOUNTING_LINE_TYPE,BASE_TRANSACTION_VALUE,BASIS_TYPE,   CONTRA_SET_ID,COST_ELEMENT_ID,CURRENCY_CODE,CURRENCY_CONVERSION_DATE,   CURRENCY_CONVERSION_RATE,CURRENCY_CONVERSION_TYPE,PRIMARY_QUANTITY,   RATE_OR_AMOUNT,TRANSACTION_SOURCE_TYPE_ID,TRANSACTION_SOURCE_ID,

  ENCUMBRANCE_TYPE_ID,REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,
  PROGRAM_UPDATE_DATE,USSGL_TRANSACTION_CODE)select :b0 ,:b1 ,sysdate  ,:b2 ,
  sysdate  ,:b2 ,:b4 ,:b5 ,:b6 ,TO_DATE(:b7,'YYYY/MM/DD') ,decode(:b8,0,null ,
  decode(:b9:b10,:b11,null ,null ,null ,decode(c2.minimum_accountable_unit,
  null ,ROUND((:b12/:b13),c2.precision),(ROUND(((:b12/:b13)   /c2.minimum_accountable_unit))* c2.minimum_accountable_unit)))) ,(-1) ,:b16   ,decode(c1.minimum_accountable_unit,null ,ROUND(:b12,c1.precision),   (ROUND((:b12/c1.minimum_accountable_unit))* c1.minimum_accountable_unit)) ,   1 ,1 ,decode(:b19,0,null ,:b19) ,decode(:b8,0,null ,decode(:b9:b23,:b11,   null ,:b9:b26)) ,decode(:b8,0,null ,decode(:b9:b29,:b11,null ,null ,null ,   TO_DATE(:b31,'YYYY/MM/DD'))) ,decode(:b8,0,null ,decode(:b9:b34,:b11,null ,   null ,null ,:b13)) ,decode(:b8,0,null ,decode(:b9:b39,:b11,null ,null ,null   ,:b41)) ,(abs(:b42)* decode(sign(:b12),0,sign(:b42),sign((:b12/:b46)))) ,   decode(:b42,0,0,(abs((:b12/:b42))* sign(:b46))) ,:b51 ,:b52 ,decode(:b8,1,   null ,0,decode(:b51,1,:b55:b56,7,:b57:b58,8,:b57:b60,null ),null ) ,:b61 ,
:b62 ,:b63 ,sysdate ,decode(:b64,1,decode(:b65,18,decode(:b16,1,:b67,3,
:b67,6,:b67,15,:b67,null ),36,decode(:b16,1,:b67,3,:b67,6,:b67,15,:b67,null
  ),71,decode(:b16,1,:b67,3,:b67,6,:b67,15,:b67,null ),null ),null ) from   fnd_currencies c1 ,fnd_currencies c2 where (c1.currency_code=:b11 and   c2.currency_code=decode(:b9:b83,null ,:b11,:b9:b86)) ;

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.01       0.00          0          0          0           0
Execute    594   1138.61    1142.20        263       2869      17663         594
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      595   1138.62    1142.20        263       2869      17663         594

Rows (1st) Rows (avg) Rows (max) Row Source Operation

---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  (cr=835 pr=94 pw=0 time=3685213 us)
         1          1          1   NESTED LOOPS  (cr=4 pr=0 pw=0 time=37 us cost=2 size=42 card=1)
         1          1          1    TABLE ACCESS BY INDEX ROWID FND_CURRENCIES (cr=2 pr=0 pw=0 time=18 us cost=1 size=21 card=1)
         1          1          1     INDEX UNIQUE SCAN FND_CURRENCIES_U1 (cr=1 pr=0 pw=0 time=11 us cost=0 size=0 card=1)(object id 3569)
         1          1          1    TABLE ACCESS BY INDEX ROWID FND_CURRENCIES (cr=2 pr=0 pw=0 time=9 us cost=1 size=21 card=1)
         1          1          1     INDEX UNIQUE SCAN FND_CURRENCIES_U1 (cr=1 pr=0 pw=0 time=5 us cost=0 size=0 card=1)(object id 3569)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net more data from client                   1        0.00          0.00
  utl_file I/O                                 2358        0.00          0.01
  Disk file operations I/O                       33        0.00          0.00
  db file sequential read                       263        0.03          2.15
  SQL*Net message to client                     594        0.00          0.00
  SQL*Net message from client                   594        1.06          3.85
********************************************************************************


--
http://www.freelists.org/webpage/oracle-l
Received on Fri Nov 07 2014 - 21:27:57 CET

Original text of this message