Re: Statement consuming high CPU
- From: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
- To: Amir.Hameed@xxxxxxxxx
- Date: Sat, 8 Nov 2014 00:43:57 +0400
Do you have triggers? What's you doing with utl_file?
Or maybe do you have views with pl/sql functions?
On Sat, Nov 8, 2014 at 12:27 AM, Hameed, Amir <Amir.Hameed@xxxxxxxxx> wrote:
> 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
>
>
> ********************************************************************************
>
>
>
--
Best regards,
Sayan Malakshinov
Senior performance tuning engineer
PSBank
http://orasql.org
Other related posts:
