| please help me interpret this tkprof [message #65919] |
Mon, 31 January 2005 12:00  |
Andrea Correale
Messages: 9
Registered: January 2005
|
Junior Member |
|
|
I have run a tkprof for a query and a different version of the same query and am having a hard time understanding the results. The first one has a much higher consistent gets but no disk. The second one has disk but less cpu time.
SELECT userid, COUNT (*) cts
FROM assignments a
WHERE version = (SELECT MAX (version)
FROM assignments
WHERE claimno = a.claimno)
AND claim_date IS NULL
GROUP BY userid
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 19 5.18 5.17 0 345562 4 259
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 5.18 5.17 0 345562 4 259
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 48
Rows Row Source Operation
------- --------------------------------
259 SORT GROUP BY
90920 FILTER
289431 INDEX FAST FULL SCAN (object id 79711)
341308 SORT AGGREGATE
170654 FIRST ROW
170654 INDEX RANGE SCAN (MIN/MAX) (object id 79705)
********************************************************************************
select userid, count(*) cts
from (select userid,
rank() over (partition by claimno order by version desc) rn
from assignments
where claim_date is null)
where rn = 1
group by userid
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 19 4.60 4.63 491 6987 7 259
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 4.60 4.63 491 6987 7 259
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 48
Rows Row Source Operation
------- ---------------------------------
259 SORT GROUP BY
90920 VIEW
111881 WINDOW SORT PUSHED RANK
289430 TABLE ACCESS FULL assignments
|
|
|
|
| Re: please help me interpret this tkprof [message #65924 is a reply to message #65919] |
Tue, 01 February 2005 05:06  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
Andrea,
What does each tkprof report look like on the second run of each query, after the shared pool has been "warmed up"?
I'm looking for two tkprof reports where the "Misses in library cache during parse" are both zero, as they would be on a second run.
|
|
|
|
| Re: please help me interpret this tkprof [message #65925 is a reply to message #65924] |
Tue, 01 February 2005 05:36 |
Andrea Correale
Messages: 9
Registered: January 2005
|
Junior Member |
|
|
Thanks Art. Here it is. I ran the first one, then the second, then repeated, so you'll see the "accumulated" 2-run info in tkprof.
SELECT aca_user_id, COUNT (*) cts
FROM adjuster_claim_assignment a
WHERE aca_claim_rev_id = (SELECT MAX (aca_claim_rev_id)
FROM adjuster_claim_assignment
WHERE aca_claim_id = a.aca_claim_id)
AND aca_date_revoked IS NULL
GROUP BY aca_user_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.01 0.01 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 38 10.28 10.30 0 691124 8 518
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 42 10.29 10.31 0 691124 8 518
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 48
Rows Row Source Operation
------- ---------------------------------------------------
259 SORT GROUP BY
90920 FILTER
289431 INDEX FAST FULL SCAN (object id 79711)
341308 SORT AGGREGATE
170654 FIRST ROW
170654 INDEX RANGE SCAN (MIN/MAX) (object id 79705)
********************************************************************************
select aca_user_id, count(*) cts
from (select aca_user_id,
rank() over (partition by aca_claim_id
order by aca_claim_rev_id desc) rn
from adjuster_claim_assignment
where aca_date_revoked is null)
where rn = 1
group by aca_user_id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 38 9.17 9.20 1655 13974 14 518
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 42 9.17 9.20 1655 13974 14 518
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 48
Rows Row Source Operation
------- ---------------------------------------------------
259 SORT GROUP BY
90920 VIEW
111881 WINDOW SORT PUSHED RANK
289430 TABLE ACCESS FULL ADJUSTER_CLAIM_ASSIGNMENT
********************************************************************************
|
|
|
|
| Re: please help me interpret this tkprof [message #65926 is a reply to message #65925] |
Tue, 01 February 2005 08:14 |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
If the choice were up to me, I would go with the second query. Granted, in your tests, the second query takes longer, but the difference is only a half a second, and to me, this marginal difference in runtime is far outweighed by the LIO (logical I/O) improvement. The first query may run slightly faster, but the second query will scale better.
For more about reducing LIO's and the reasons for doing so, click here.
I'm guessing that the reason your second query goes to disk is that the sorting required exceeds the memory allocated, and to complete the sort, Oracle has to write to, and then read back out from, temporary tablespace. What is your sort_area_size (8i)/pga_aggregate_target (9i+)?
Out of curiosity, does this SQL produce the same results, and, if so, does it perform any better?SELECT x.aca_user_id
, COUNT(*) cts
FROM (SELECT aca.aca_user_id
, aca.aca_claim_rev_id
, MAX(aca.aca_claim_rev_id)
OVER (PARTITION BY aca.aca_claim_id) max_claim_rev_id
FROM adjuster_claim_assigment aca
WHERE aca.aca_date_revoked IS NULL) x
WHERE x.aca_claim_rev_id = x.max_claim_rev_id
GROUP BY x.aca_user_id
/ |
|
|
|
| Re: please help me interpret this tkprof [message #65927 is a reply to message #65926] |
Tue, 01 February 2005 09:35 |
Andrea Correale
Messages: 9
Registered: January 2005
|
Junior Member |
|
|
Actually, the second query is the one that uses less CPU. Our database is 8i on Sun Solaris, we have sort_area_size = 56553600 and sort_area_retained_size = 6553600.
Your query, which does indeed produce the same output, pretty much matches my second query in tkprof, at the expense of about double the disk.
Thank you for the help.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
