RE: Statement consuming high CPU
- From: "Hameed, Amir" <Amir.Hameed@xxxxxxxxx>
- To: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
- Date: Fri, 7 Nov 2014 21:01:35 +0000
There are six triggers on the table but they have been there for at least a few
years now.
From: Sayan Malakshinov [mailto:xt.and.r@xxxxxxxxx]
Sent: Friday, November 07, 2014 3:44 PM
To: Hameed, Amir
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Statement consuming high CPU
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<mailto: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:
