OraFAQ Forum: Performance Tuning » tkprof trace analyze for recommendataion

Home » RDBMS Server » Performance Tuning » tkprof trace analyze for recommendataion (10.2.0.2.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

tkprof trace analyze for recommendataion [message #504214]

Fri, 22 April 2011 05:38

sudityakumargmailcom
Messages: 3
Registered: April 2011
Location: Bangalore

Junior Member

hi

Please find and review the attached trace file. we have traced one of long running report . Please analyze the trace file and get back to us with your recommendations.

Attachment: India_creditors_new.txt
(Size: 150.53KB, Downloaded 2228 times)

Report message to a moderator

Re: tkprof trace analyze for recommendataion [message #504217 is a reply to message #504214]

Fri, 22 April 2011 05:46

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please send me money for the time I will spend to make your job and I will put my recommandations.

Regards
Michel

Report message to a moderator

Re: tkprof trace analyze for recommendataion [message #504224 is a reply to message #504217]

Fri, 22 April 2011 07:22

sudityakumargmailcom
Messages: 3
Registered: April 2011
Location: Bangalore

Junior Member

Hi,

Please give me some recommendation I'm a junior dba ,could u at lease let me know what steps will be required to make the query run fast.

Report message to a moderator

Re: tkprof trace analyze for recommendataion [message #504225 is a reply to message #504224]

Fri, 22 April 2011 07:55

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

There are 8159 user statements in that file! You will have to identify the statement(s) that is (are) causing a problem, then perhaps it can be looked at. For example, the very first statment took nearly 2 hours of elapsed time, but as that was close to a million executions, it probably isn't a problem - particularly as it looks like internal code, not user code. The second statement was 912 executions in 1033 seconds, and is definitely user code. But is it a problem? Only you can say.

Report message to a moderator

Re: tkprof trace analyze for recommendataion [message #504307 is a reply to message #504225]

Sat, 23 April 2011 04:38

sudityakumargmailcom
Messages: 3
Registered: April 2011
Location: Bangalore

Junior Member

Thanks! a lot

I am mentioning few those are mainly causing the problem.

SELECT CLOSING_BALANCE
FROM
JAI_CMN_RG_OTHERS WHERE SOURCE_REGISTER = :b1 AND TAX_TYPE = :b2 AND
SOURCE_REGISTER_ID = (SELECT MAX (A.REGISTER_ID) FROM JAI_CMN_RG_PLA_TRXS
A , JAI_CMN_RG_OTHERS B WHERE A.REGISTER_ID = B.SOURCE_REGISTER_ID AND
B.SOURCE_REGISTER = :b1 AND B.TAX_TYPE = :b2 AND A.CREATION_DATE =
(SELECT MAX (C.CREATION_DATE) FROM JAI_CMN_RG_PLA_TRXS C ,
JAI_CMN_RG_OTHERS D WHERE C.REGISTER_ID = D.SOURCE_REGISTER_ID AND
D.SOURCE_REGISTER = :b1 AND D.TAX_TYPE = :b2 AND TO_DATE
(C.CREATION_DATE ) < TO_DATE (:b3 ) AND C.ORGANIZATION_ID = :b4 AND
C.LOCATION_ID = :b5 ) )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 9774 42749.87 42675.73 16509839 826323358 0 0
Fetch 9774 4.87 4.57 0 97744 0 9774
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19549 42754.75 42680.31 16509839 826421102 0 9774

Misses in library cache during parse: 1
Misses in library cache during execute: 3
Optimizer mode: ALL_ROWS
Parsing user id: 44 (APPS)

Rows Row Source Operation
------- ---------------------------------------------------
979 TABLE ACCESS BY INDEX ROWID JAI_CMN_RG_OTHERS (cr=82777467 pr=5026508 pw=0 time=4665284438 us)
979 INDEX SKIP SCAN JAI_CMN_RG_OTHERS_UK1 (cr=82776488 pr=5026508 pw=0 time=4665256660 us)(object id 290858)
979 SORT AGGREGATE (cr=82767673 pr=5026508 pw=0 time=4664840493 us)
979 HASH JOIN (cr=82767673 pr=5026508 pw=0 time=4664807953 us)
979 TABLE ACCESS FULL JAI_CMN_RG_PLA_TRXS (cr=53113738 pr=2970620 pw=0 time=3235179198 us)
979 SORT AGGREGATE (cr=47795790 pr=2970620 pw=0 time=2954269225 us)
13140138 HASH JOIN (cr=47795790 pr=2970620 pw=0 time=3209536627 us)
15783438 TABLE ACCESS BY INDEX ROWID JAI_CMN_RG_PLA_TRXS (cr=18141849 pr=2 pw=0 time=844634083 us)
25352184 INDEX RANGE SCAN JAI_CMN_RG_PLA_TRXS_N2 (cr=105732 pr=2 pw=0 time=52035989 us)(object id 290827)
191906591 TABLE ACCESS FULL JAI_CMN_RG_OTHERS (cr=29653941 pr=2970618 pw=0 time=1291231860 us)
191906591 TABLE ACCESS FULL JAI_CMN_RG_OTHERS (cr=29653935 pr=2055888 pw=0 time=1202986775 us)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
979 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'JAI_CMN_RG_OTHERS' (TABLE)
979 INDEX MODE: ANALYZED (SKIP SCAN) OF 'JAI_CMN_RG_OTHERS_UK1'
(INDEX (UNIQUE))
979 SORT (AGGREGATE)
979 HASH JOIN
979 TABLE ACCESS MODE: ANALYZED (FULL) OF
'JAI_CMN_RG_PLA_TRXS' (TABLE)
979 SORT (AGGREGATE)
13140138 HASH JOIN
15783438 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'JAI_CMN_RG_PLA_TRXS' (TABLE)
25352184 INDEX MODE: ANALYZED (RANGE SCAN) OF
'JAI_CMN_RG_PLA_TRXS_N2' (INDEX)
191906591 TABLE ACCESS MODE: ANALYZED (FULL) OF
'JAI_CMN_RG_OTHERS' (TABLE)
191906591 TABLE ACCESS MODE: ANALYZED (FULL) OF
'JAI_CMN_RG_OTHERS' (TABLE)

********************************************************************************

SELECT CLOSING_BALANCE
FROM
JAI_CMN_RG_OTHERS WHERE SOURCE_REGISTER = :b1 AND TAX_TYPE = :b2 AND
SOURCE_REGISTER_ID = (SELECT MAX (A.REGISTER_ID) FROM JAI_CMN_RG_PLA_TRXS
A , JAI_CMN_RG_OTHERS B WHERE A.REGISTER_ID = B.SOURCE_REGISTER_ID AND
B.SOURCE_REGISTER = :b1 AND B.TAX_TYPE = :b2 AND A.CREATION_DATE =
(SELECT MAX (C.CREATION_DATE) FROM JAI_CMN_RG_PLA_TRXS C ,
JAI_CMN_RG_OTHERS D WHERE C.REGISTER_ID = D.SOURCE_REGISTER_ID AND
D.SOURCE_REGISTER = :b1 AND D.TAX_TYPE = :b2 AND TRUNC (C.CREATION_DATE
) < TRUNC (:b3 ) AND C.ORGANIZATION_ID = :b4 AND C.LOCATION_ID = :b5
) )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 9774 19264.29 19403.92 10470892 530272424 0 0
Fetch 9774 0.37 0.28 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19549 19264.66 19404.21 10470892 530272424 0 0

Misses in library cache during parse: 1
Misses in library cache during execute: 10
Optimizer mode: ALL_ROWS
Parsing user id: 44 (APPS)

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID JAI_CMN_RG_OTHERS (cr=26692500 pr=1576750 pw=0 time=1089018133 us)
0 INDEX SKIP SCAN JAI_CMN_RG_OTHERS_UK1 (cr=26692500 pr=1576750 pw=0 time=1089013735 us)(object id 290858)
492 SORT AGGREGATE (cr=26692500 pr=1576750 pw=0 time=1089003977 us)
0 HASH JOIN (cr=26692500 pr=1576750 pw=0 time=1088983704 us)
0 TABLE ACCESS FULL JAI_CMN_RG_PLA_TRXS (cr=26692500 pr=1576750 pw=0 time=1088796965 us)
492 SORT AGGREGATE (cr=24019944 pr=1576750 pw=0 time=952255003 us)
0 HASH JOIN (cr=24019944 pr=1576750 pw=0 time=952237635 us)
7932024 TABLE ACCESS BY INDEX ROWID JAI_CMN_RG_PLA_TRXS (cr=9117252 pr=0 pw=0 time=190713063 us)
12740832 INDEX RANGE SCAN JAI_CMN_RG_PLA_TRXS_N2 (cr=53136 pr=0 pw=0 time=25584917 us)(object id 290827)
492 TABLE ACCESS FULL JAI_CMN_RG_OTHERS (cr=14902692 pr=1576750 pw=0 time=638423761 us)
0 TABLE ACCESS FULL JAI_CMN_RG_OTHERS (cr=0 pr=0 pw=0 time=0 us)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'JAI_CMN_RG_OTHERS' (TABLE)
0 INDEX MODE: ANALYZED (SKIP SCAN) OF 'JAI_CMN_RG_OTHERS_UK1'
(INDEX (UNIQUE))
492 SORT (AGGREGATE)
0 HASH JOIN
0 TABLE ACCESS MODE: ANALYZED (FULL) OF
'JAI_CMN_RG_PLA_TRXS' (TABLE)
492 SORT (AGGREGATE)
0 HASH JOIN
7932024 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'JAI_CMN_RG_PLA_TRXS' (TABLE)
12740832 INDEX MODE: ANALYZED (RANGE SCAN) OF
'JAI_CMN_RG_PLA_TRXS_N2' (INDEX)
492 TABLE ACCESS MODE: ANALYZED (FULL) OF
'JAI_CMN_RG_OTHERS' (TABLE)
0 TABLE ACCESS MODE: ANALYZED (FULL) OF
'JAI_CMN_RG_OTHERS' (TABLE)

********************************************************************************

SELECT OPENING_BALANCE
FROM
JAI_CMN_RG_PLA_TRXS WHERE TRUNC (CREATION_DATE ) >= TRUNC (:b1 ) AND
REGISTER_ID = (SELECT MIN (REGISTER_ID) FROM JAI_CMN_RG_PLA_TRXS WHERE
TRUNC (CREATION_DATE ) >= TRUNC (:b1 ) )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 9774 14102.63 13772.09 3 53092389 0 0
Fetch 9774 1.19 1.18 2 29322 0 9774
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19549 14103.82 13773.27 5 53121711 0 9774

Misses in library cache during parse: 1
Misses in library cache during execute: 10
Optimizer mode: ALL_ROWS
Parsing user id: 44 (APPS)

Rows Row Source Operation
------- ---------------------------------------------------
517 TABLE ACCESS BY INDEX ROWID JAI_CMN_RG_PLA_TRXS (cr=2809907 pr=0 pw=0 time=727587754 us)
517 INDEX UNIQUE SCAN JAI_CMN_RG_PLA_TRXS_PK (cr=2809390 pr=0 pw=0 time=727562739 us)(object id 290832)
517 SORT AGGREGATE (cr=2808356 pr=0 pw=0 time=727534922 us)
41175832 TABLE ACCESS FULL JAI_CMN_RG_PLA_TRXS (cr=2808356 pr=0 pw=0 time=714651452 us)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
517 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'JAI_CMN_RG_PLA_TRXS' (TABLE)
517 INDEX MODE: ANALYZED (UNIQUE SCAN) OF
'JAI_CMN_RG_PLA_TRXS_PK' (INDEX (UNIQUE))
517 SORT (AGGREGATE)
41175832 TABLE ACCESS MODE: ANALYZED (FULL) OF
'JAI_CMN_RG_PLA_TRXS' (TABLE)

********************************************************************************

SELECT NVL(SUM (CR_BASIC_ED) , 0 ) - NVL(SUM (DR_BASIC_ED) , 0 )
FROM
JAI_CMN_RG_PLA_TRXS WHERE TRUNC (CREATION_DATE ) < TRUNC (:b1 ) AND
ORGANIZATION_ID = :b2 AND LOCATION_ID = :b3

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 9775 9.05 9.07 0 0 0 0
Fetch 9775 6105.56 5962.27 0 181143855 0 9775
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19552 6114.61 5971.34 0 181143855 0 9775

Misses in library cache during parse: 1
Misses in library cache during execute: 13
Optimizer mode: ALL_ROWS
Parsing user id: 44 (APPS)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=18531 pr=0 pw=0 time=669024 us)
16122 TABLE ACCESS BY INDEX ROWID JAI_CMN_RG_PLA_TRXS (cr=18531 pr=0 pw=0 time=387198 us)
25896 INDEX RANGE SCAN JAI_CMN_RG_PLA_TRXS_N2 (cr=108 pr=0 pw=0 time=25955 us)(object id 290827)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 SORT (AGGREGATE)
16122 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'JAI_CMN_RG_PLA_TRXS' (TABLE)
25896 INDEX MODE: ANALYZED (RANGE SCAN) OF
'JAI_CMN_RG_PLA_TRXS_N2' (INDEX)

********************************************************************************

SELECT NVL(SUM (CR_OTHER_ED) , 0 ) - NVL(SUM (DR_OTHER_ED) , 0 )
FROM
JAI_CMN_RG_PLA_TRXS WHERE TRUNC (CREATION_DATE ) < TRUNC (:b1 ) AND
ORGANIZATION_ID = :b2 AND LOCATION_ID = :b3

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 9775 8.94 8.79 0 0 0 0
Fetch 9775 6055.78 5914.82 89 181140525 0 9775
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 19552 6064.72 5923.62 89 181140525 0 9775

Misses in library cache during parse: 1
Misses in library cache during execute: 6
Optimizer mode: ALL_ROWS
Parsing user id: 44 (APPS)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=18531 pr=0 pw=0 time=632692 us)
16122 TABLE ACCESS BY INDEX ROWID JAI_CMN_RG_PLA_TRXS (cr=18531 pr=0 pw=0 time=370952 us)
25896 INDEX RANGE SCAN JAI_CMN_RG_PLA_TRXS_N2 (cr=108 pr=0 pw=0 time=25958 us)(object id 290827)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 SORT (AGGREGATE)
16122 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'JAI_CMN_RG_PLA_TRXS' (TABLE)
25896 INDEX MODE: ANALYZED (RANGE SCAN) OF
'JAI_CMN_RG_PLA_TRXS_N2' (INDEX)

********************************************************************************

Thanks in advance!

Report message to a moderator

Re: tkprof trace analyze for recommendataion [message #504647 is a reply to message #504307]

Wed, 27 April 2011 01:04

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

1. Your new statements does NOT belong to the trace you posted.
2. Look at TKPROF:

SELECT OPENING_BALANCE
  FROM JAI_CMN_RG_PLA_TRXS
 WHERE TRUNC(CREATION_DATE) >= TRUNC(:b1)
   AND REGISTER_ID =
       (SELECT MIN(REGISTER_ID)
          FROM JAI_CMN_RG_PLA_TRXS
         WHERE TRUNC(CREATION_DATE) >= TRUNC(:b1))


call    count  cpu      elapsed    disk       query      current    rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1     0.00     0.00            0          0          0          0
Execute   9774 14102.63 13772.09            3   53092389          0          0
Fetch     9774     1.19     1.18            2      29322          0       9774
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total    19549 14103.82 13773.27            5   53121711          0       9774

Misses in library cache during parse: 1
Misses in library cache during execute: 10
Optimizer mode: ALL_ROWS
Parsing user id: 44 (APPS)

Rows     Row Source Operation
-------- ---------------------------------------------------
     517 TABLE ACCESS BY INDEX ROWID JAI_CMN_RG_PLA_TRXS (cr=2809907 pr=0 pw=0 time=727587754 us)
     517   INDEX UNIQUE SCAN JAI_CMN_RG_PLA_TRXS_PK (cr=2809390 pr=0 pw=0 time=727562739 us)(object id 290832)
     517    SORT AGGREGATE (cr=2808356 pr=0 pw=0 time=727534922 us)
41175832      TABLE ACCESS FULL JAI_CMN_RG_PLA_TRXS (cr=2808356 pr=0 pw=0 time=714651452 us)

You perform FULL table scan on JAI_CMN_RG_PLA_TRXS.

Why do you need WHERE TRUNC(CREATION_DATE) >= TRUNC(:b1)?
IMHO WHERE CREATION_DATE >= TRUNC(:b1) will work as well and will enable index usage (if such index exists).

I would create an index on (CREATION_DATE, REGISTER_ID) columns for that table (if no such index already exists).

HTH.

Report message to a moderator

Re: tkprof trace analyze for recommendataion [message #504658 is a reply to message #504647]

Wed, 27 April 2011 02:47

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Before attempting to tune the code or define indexes, are you certain you have the correct statements? For example, is the statement detailed above actually a problem? Each execution was (on average) less than 1.5 seconds. If this is an interactive query, that is probably OK. If it is part of a batch job, possibly not.
By the way, Is JAI an EBS financials localization? I'm not familiar with it.

Report message to a moderator

Previous Topic:	Sql tuning
Next Topic:	What is causing the lock despite such an decent update state

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 21:04:14 CST 2024