Re: Question about TKPROF output.

Ronnie,

Upon further consideration, it should be even easier than that to figure out the answer. I believe that the 'direct path %' events have a p1, p2, or p3 value that gives you the number of Oracle blocks manipulated (like 'db file % read' does in the formerly-known-as-p3 value; check in v$event_name to be sure). You should be able to skip the complication of the strace step by using that information from the raw Oracle trace file.

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

On Tue, May 20, 2008 at 2:01 PM, Cary Millsap <cary.millsap_at_method-r.com> wrote:

> 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