Help with interpreting TKPROF output

From: Tim Kearsley <tim.kearsley_at_milton-keynes.gov.uk>
Date: 29 Aug 2003 01:09:28 -0700
Message-ID: <725736ef.0308290009.1f736509@posting.google.com>

Hi all,

Environment is:

Oracle 8.1.7.0.0
AIX 4.3.3
RS6000 SP node

I'm trying to improve my understanding of tuning SQL and am using TKPROF and EXPLAIN PLAN quite a bit. A few things are puzzling me at the moment and searching the archives from this group for a particular answer has resulted in conflicting views!

This was the SQL I was analysing:

select os_obj_id, os_sub_id, vw.amt
from obj_sub, (select osd_obj_id, osd_sub_id, osd_year, sum(osd_jeamt) amt
from obj_sub_detail group by osd_obj_id, osd_sub_id, osd_year) vw where os_obj_id = vw.osd_obj_id
and os_sub_id = vw.osd_sub_id
and os_year = vw.osd_year

This is the execution plan:

| Operation | Name | Rows | Bytes| Cost |

---

| SELECT STATEMENT | | 13K| 687K| 30531 |
| NESTED LOOPS | | 13K| 687K| 30531 |
| VIEW | | 643K| 22M| 30531 |
| SORT GROUP BY | | 643K| 14M| 30531 |
| TABLE ACCESS FULL |OBJ_SUB_D | 643K| 14M| 4911 |
| INDEX UNIQUE SCAN |OS1 | 1M| 18M| |

---

Optimizer mode was CHOOSE.

Below is a sample of some TKPROF output:

select os_obj_id, os_sub_id, vw.amt
from obj_sub, (select osd_obj_id, osd_sub_id, osd_year, sum(osd_jeamt) amt
from obj_sub_detail group by osd_obj_id, osd_sub_id, osd_year) vw where os_obj_id = vw.osd_obj_id
and os_sub_id = vw.osd_sub_id
and os_year = vw.osd_year

call     count       cpu    elapsed       disk      query    current  
     rows

------- ------ -------- ---------- ---------- ---------- ----------

---

Parse        1      0.01       0.04          0          0          0  
        0
Execute      2      0.00       0.02          0          0          0  
        0
Fetch     3639     15.68     109.66      40408     153537        329  

    54557
------- ------ -------- ---------- ---------- ---------- ----------

---

total 3642 15.69 109.72 40408 153537 329

    54557

Misses in library cache during parse: 1
Optimizer goal: CHOOSE

I'm OK with most of this, but the exact meaning of the "disk", "query" and "current" columns puzzles me. My understanding is that the "disk" column refers to the number of blocks physically read from disk, the "query" column refers to the number of buffers retrieved in consistent mode, and the "current" column refers to the number of buffers read in curent mode. The bit I don't understand is why the query, which does no inserts, updates etc., should get buffers in anything but consistent mode.

Could anyone help me out here? I apologise in advance if I'm missing something particularly obvious, but this is an area of work in which I don't have a lot of experience.

Many thanks.

Tim Kearsley
Milton Keynes Council Received on Fri Aug 29 2003 - 03:09:28 CDT