FAQ
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.

Search Discussions

  • Cary Millsap at May 20, 2008 at 7:01 pm
    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
    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

    Execute 1 0.00 0.00 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
  • Cary Millsap at May 20, 2008 at 7:25 pm
    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
    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
    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

    Execute 1 0.00 0.00 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
  • Yong Huang at May 21, 2008 at 7:36 pm
    I thought it should include disk blocks accessed through direct path. But I
    just did a test and the numbers don't quite add up. The database is 10.2.0.4
    64-bit Linux. disk_asynch_io is default true and filesystemio_options is
    default none if they matter.

    alter session set workarea_size_policy=manual;
    alter session set sort_area_size=1;
    alter session set hash_area_size = 1;
    --explain plan for select /*+ use_hash(a) */ count(*) from ow a, big b where
    a.i = b.line;
    --Make sure TempSpc column is shown: @?/rdbms/admin/utlxpls
    alter session set tracefile_identifier = hashtest;
    alter session set events '10046 trace name context forever, level 8';
    select /*+ use_hash(a) */ count(*) from ow a, big b where a.i = b.line;
    alter session set events '10046 trace name context off';

    The tkprof'ed trace file shows:

    select /*+ use_hash(a) */ count(*)
    from
    ow a, big b where a.i = b.line

    call count cpu elapsed disk query current
    rows
    ------- ------ -------- ---------- ---------- ---------- ----------

    Parse 1 0.00 0.00 0 1 0

    Execute 1 0.00 0.00 0 0 0

    Fetch 2 9.00 27.49 43967 26638 0
    1
    ------- ------ -------- ---------- ---------- ---------- ----------

    total 4 9.00 27.50 43967 26639 0
    1

    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 599

    Rows Row Source Operation

    ------- ---------------------------------------------------
    1 SORT AGGREGATE (cr=26638 pr=43967 pw=2684 time=27491836 us)

    2029417 HASH JOIN (cr=26638 pr=43967 pw=2684 time=26385795 us)

    8734 INDEX FAST FULL SCAN OW_PK (cr=23 pr=20 pw=0 time=18608 us)(object
    id 240097)
    2031611 TABLE ACCESS FULL BIG (cr=26615 pr=26555 pw=0 time=6102931 us)

    Elapsed times include waiting on following events:

    Event waited on Times Max. Wait Total Waited
    ---------------------------------------- Waited ---------- ------------
    SQL*Net message to client 2 0.00 0.00
    db file sequential read 2 0.00 0.00
    db file scattered read 1702 0.09 4.52
    direct path write temp 2684 0.00 0.07
    direct path read temp 17392 0.05 10.28
    SQL*Net message from client 2 3.04 3.04

    Sum the blocks accessed by direct path read and write:
    $ awk '/direct path/{print $14}' ristss1t_ora_20738_HASHTEST.trc | awk -F=
    '{a+=$2}END{print a}'
    20081

    Sum the blocks accessed by just direct path read:
    $ awk '/direct path read/{print $14}' ristss1t_ora_20738_HASHTEST.trc | awk -F=
    '{a+=$2}END{print a}'
    17395

    Sum the blocks accessed by db file scattered and sequential read:
    $ awk '/db file s/{print $11}' ristss1t_ora_20738_HASHTEST.trc | awk -F=
    '{a+=$2}END{print a}'
    26641

    Sum the number of times of direct path reads or writes:
    $ grep -c 'direct path ' ristss1t_ora_20738_HASHTEST.trc
    20081

    Note that 43967 is not a sum of any of the numbers from the awk commands. The
    closest is 17395+26641=44036, which is 69 higher.

    By the way, I looked at this before but without direct path reads and writes,
    and the numbers add up perfectly. See
    http://yong321.freeshell.org/computer/diskreadunit.txt

    Yong Huang
    From: Ronnie Doggart
    Date: Tue, 20 May 2008 13:21:23 +0100
    Subject: Question about TKPROF output. >
    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

    Execute 1 0.00 0.00 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


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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMay 20, '08 at 12:21p
activeMay 21, '08 at 7:36p
posts4
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase