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
: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)) ;
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-lReceived on Fri Nov 07 2014 - 21:27:57 CET