| TKPROF analysis [message #19150] |
Sun, 03 March 2002 20:49  |
aks
Messages: 8
Registered: February 2002
|
Junior Member |
|
|
when a TKPROF was done on a query the output of the tkprof was as follows.
SELECT NVL(MAX(SRL_NUM),'NotExists')
FROM
ALH WHERE SRL_NUM < :b1 AND B2K_ID = :b2 AND ACID = ACID
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1259 0.20 0.21 0 0 0 0
Fetch 1258 3482.81 3537.57 11682672 40328964 15096 1258
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2518 3483.01 3537.78 11682672 40328964 15096 1258
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: RULE
Parsing user id: 23 (CUSTOM) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: RULE
0 SORT (AGGREGATE)
0 TABLE ACCESS (FULL) OF 'ACCOUNT_LIEN_HISTORY_TABLE'
********************************************************************************
Subsequently a index was created on srl_num in that table.
And the query was run again. This time the output of the TKPROF was
SELECT NVL(MAX(SRL_NUM),'NotExists')
FROM
ALH WHERE SRL_NUM < :b1 AND B2K_ID = :b2 AND ACID = ACID
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1760 0.24 0.23 0 0 0 0
Fetch 1760 14964.08 15112.79 8673 819117032 0 1760
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3521 14964.32 15113.02 8673 819117032 0 1760
Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 23 (CUSTOM) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1760 SORT AGGREGATE
72 TABLE ACCESS BY INDEX ROWID ACCOUNT_LIEN_HISTORY_TABLE
1406194699 INDEX RANGE SCAN (object id 20665)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: RULE
1760 SORT (AGGREGATE)
72 TABLE ACCESS (BY INDEX ROWID) OF 'ACCOUNT_LIEN_HISTORY_TABLE'
1406194699 INDEX (RANGE SCAN) OF 'IDX_ALH_SRL_NUM' (NON-UNIQUE)
********************************************************************************
DOUBTS :
1) was i right on creating the index on srl_num. Please note than on the same table another index exists on srl_num as second column.
2) after creating the index, why has the system taken more time. From the trace file, it is evident that the index has been used.
3) how to tune the query furthur.
thanks and regards
Arun.S
|
|
|
|
| Re: TKPROF analysis [message #19156 is a reply to message #19150] |
Sun, 03 March 2002 23:12  |
Geoffrey
Messages: 32
Registered: February 2002
|
Member |
|
|
a few questions,
if there is an index on srl_num, is srl_num the first column in that index ?
why didn't you make an index on srl_num and b2k_id (I think you will get the best result if you do that)?
it looks like you get more rows the second time than the first time, how can that be ?
|
|
|
|
| Re: TKPROF analysis [message #19188 is a reply to message #19150] |
Mon, 04 March 2002 15:14 |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Using an index is not always the fastest way to retrieve data from a table. If the ROWIDS returned from the index read results in you you having to read most of the table data blocks anyway, then your overall number of IO's is higher than it would have been by just doing a full table scan.
Query 1 returns 1258 rows, the second returns 1760. Make sure you run the statements on the same data. Only do your sql_trace after running the statment at least once to ensure that the data is cached. If you created the index and then immediately traced the statement you would not get a "steady state" or "repeatable" result.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
