Re: Question about TKPROF output.

It's a good question. We've long said (e.g., p80 of *Optimizing Oracle Performance*) that the "disk" figure (which maps to the *p* statistic in the raw trace data) represents the number of Oracle database blocks obtained by the database call (in your case, a fetch) via operating system disk read calls.

But it's possible that it includes "direct path writes," too. I don't know the answer. A simple test is to run your query again, and this time in addition to using Oracle's extended SQL trace, use your operating system's strace tool, as well, upon your session's Oracle kernel process. In the strace output, you'll be able to see exactly how many blocks are being manipulated by OS calls, and you'll be able to see the mapping of that information to your tkprof output.

Cary Millsap
http://method-r.com
http://carymillsap.blogspot.com

On Tue, May 20, 2008 at 7:21 AM, Ronnie Doggart <ronnie_doggart_at_lagan.com> wrote:

> All,
>
> I would just like some clarification on a point:
>
> 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 20 1.01 10.85 6171 7395 0
> 283
> ------- ------ -------- ---------- ---------- ---------- ----------
> ----------
> total 22 1.01 10.85 6171 7395 0
> 283
>
> When the tkprof output shows disk activity of 6171 does this include blocks
> used in one/multi pass hash joins. Are the direct path reads/writes included
> ?
>
> Elapsed times include waiting on following events:
> Event waited on Times Max. Wait Total
> Waited
> ---------------------------------------- Waited ----------
> ------------
> SQL*Net message to client 20 0.00
> 0.00
> db file sequential read 851 0.03
> 2.32
> db file scattered read 341 0.03
> 1.89
> direct path write 312 0.06
> 3.99
> direct path read 312 0.03
> 1.60
> SQL*Net message from client 20 2.27
> 35.33
>
> ********************************************************************************
>
> Ronnie Doggart
> Database Architect
> Lagan
> 209 Airport Road West
> Belfast
> BT3 9EZ
> Connecting Governments and People
> T: +44 (0) 28 9078 8300
> F: +44 (0) 28 9078 8339
> W: www.lagan.com
>
> The information in this message is confidential and may be legally
> privileged. It is intended solely for the addressee. Access to this message
> by anyone else is unauthorised. If you are not the intended recipient, any
> disclosure, copying, or distribution of the message, or any action or
> omission taken by you in reliance on it, is prohibited and may be unlawful.
> Please immediately contact the sender if you have received this message in
> error.
>
> The views and opinions expressed in this email may not reflect the views
> and opinions of any member of Lagan Technologies Limited, or any of its
> subsidiaries.
>
> Lagan Technologies Limited is a company registered in Northern Ireland with
> registration number NI 28773. The registered office of Lagan Technologies
> Limited is 209 Airport Road West, Belfast, Co. Antrim, BT3 9EZ.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l