Re: Help with interpreting TKPROF output

tim.kearsley_at_milton-keynes.gov.uk (Tim Kearsley) wrote in message news:<725736ef.0308290009.1f736509_at_posting.google.com>...
>
> 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.

Hi, Tim,

The disk column gives the number of blocks physically read (physical as far as Oracle sees it; could be cached by the filesystem), not the number of disk reads, although the difference is only meaningful in case of db block multiblock reading (see http://www.stormloader.com/yonghuang/computer/diskreadunit.txt). I won't conclude but I think the buffer reads ("query" and "current" columns) use the unit of number of buffers, not number of times of reading buffers.

To answer your second question, even a query needs current mode gets, because the recursive SQLs read the data dictionary.

Yong Huang Received on Fri Aug 29 2003 - 14:53:17 CDT