Home » RDBMS Server » Performance Tuning » please help me interpret this tkprof
please help me interpret this tkprof [message #65919] Mon, 31 January 2005 12:00 Go to next message
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 Go to previous messageGo to next message
Art Metzer
Messages: 2478
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Art Metzer
Messages: 2478
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 Go to previous message
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.
Previous Topic: oracle10g
Next Topic: Detrimental effect of many extents
Goto Forum:
  


Current Time: Tue Sep 29 07:01:11 CDT 2020