FAQ
Hello all,

Can't quite understand this - I have just run a 26-hour level-12 10046 trace
on a slow process producing a 750M trc file. Imagine my disappointment to
find that the key query I am interested in has no rowsource data - or only
reports zeroes. Other queries have row totals - e.g.:

Rows Row Source Operation
------- ---------------------------------------------------

3 SORT ORDER BY (cr=3 pr=0 pw=0 time=57 us)
3 TABLE ACCESS CLUSTER HISTGRM$ (cr=3 pr=0 pw=0 time=37 us)
1 INDEX UNIQUE SCAN I_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=23

us)(object id 252)

but for my huge query I just get an execution plan with zeroes in the Rows
column:

Rows Execution Plan
------- ---------------------------------------------------

0 INSERT STATEMENT MODE: ALL_ROWS
0 VIEW
0 SORT (ORDER BY)
0 HASH JOIN (ANTI)
0 NESTED LOOPS
0 NESTED LOOPS
0 HASH JOIN (OUTER)
0 HASH JOIN (RIGHT ANTI)

.. etc.. etc

I'd be interested in any ideas as to why this is. Also, am I correct in my
assumption that the rowsource is arguably more valuable than the explain
plan in that it shows what *actually* happened rather than an explain plan
generated by tkprof 24 hours later - albeit likely a valid explain plan?

Cheers for any insight,
cam

Search Discussions

  • Cam at Jun 29, 2008 at 9:10 pm
    Ooops - 10.2.0.4 on HP-UX 11.23, trace started with:

    EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context
    forever,level 12''';

    in a logon trigger. tkprof generated with defaults and explain credentials.

    c
    On Sun, Jun 29, 2008 at 10:07 PM, cam wrote:

    Hello all,

    Can't quite understand this - I have just run a 26-hour level-12 10046
    trace on a slow process producing a 750M trc file. Imagine my disappointment
    to find that the key query I am interested in has no rowsource data - or
    only reports zeroes. Other queries have row totals - e.g.:

    Rows Row Source Operation
    ------- ---------------------------------------------------
    3 SORT ORDER BY (cr=3 pr=0 pw=0 time=57 us)
    3 TABLE ACCESS CLUSTER HISTGRM$ (cr=3 pr=0 pw=0 time=37 us)
    1 INDEX UNIQUE SCAN I_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=23
    us)(object id 252)

    but for my huge query I just get an execution plan with zeroes in the Rows
    column:

    Rows Execution Plan
    ------- ---------------------------------------------------
    0 INSERT STATEMENT MODE: ALL_ROWS
    0 VIEW
    0 SORT (ORDER BY)
    0 HASH JOIN (ANTI)
    0 NESTED LOOPS
    0 NESTED LOOPS
    0 HASH JOIN (OUTER)
    0 HASH JOIN (RIGHT ANTI)
    .. etc.. etc

    I'd be interested in any ideas as to why this is. Also, am I correct in my
    assumption that the rowsource is arguably more valuable than the explain
    plan in that it shows what *actually* happened rather than an explain plan
    generated by tkprof 24 hours later - albeit likely a valid explain plan?

    Cheers for any insight,
    cam
    --
    http://www.freelists.org/webpage/oracle-l
  • Stefan Knecht at Jun 29, 2008 at 9:55 pm
    Could be a bug in tkprof. Did you look at your query's STAT lines in the raw
    trace file ?

    Just search for your query in the trc file, and mark the PARSING IN CURSOR
    # --
    being the cursor number. Then search forward to the next STAT #
    lines. That'll be your execution plan. Do you see the same results like
    tkprof reported ?

    Stefan
    On Sun, Jun 29, 2008 at 11:10 PM, cam wrote:

    Ooops - 10.2.0.4 on HP-UX 11.23, trace started with:

    EXECUTE IMMEDIATE 'alter session set events ''10046 trace name context
    forever,level 12''';

    in a logon trigger. tkprof generated with defaults and explain credentials.

    c

    On Sun, Jun 29, 2008 at 10:07 PM, cam wrote:

    Hello all,

    Can't quite understand this - I have just run a 26-hour level-12 10046
    trace on a slow process producing a 750M trc file. Imagine my disappointment
    to find that the key query I am interested in has no rowsource data - or
    only reports zeroes. Other queries have row totals - e.g.:

    Rows Row Source Operation
    ------- ---------------------------------------------------
    3 SORT ORDER BY (cr=3 pr=0 pw=0 time=57 us)
    3 TABLE ACCESS CLUSTER HISTGRM$ (cr=3 pr=0 pw=0 time=37 us)
    1 INDEX UNIQUE SCAN I_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=23
    us)(object id 252)

    but for my huge query I just get an execution plan with zeroes in the Rows
    column:

    Rows Execution Plan
    ------- ---------------------------------------------------
    0 INSERT STATEMENT MODE: ALL_ROWS
    0 VIEW
    0 SORT (ORDER BY)
    0 HASH JOIN (ANTI)
    0 NESTED LOOPS
    0 NESTED LOOPS
    0 HASH JOIN (OUTER)
    0 HASH JOIN (RIGHT ANTI)
    .. etc.. etc

    I'd be interested in any ideas as to why this is. Also, am I correct in my
    assumption that the rowsource is arguably more valuable than the explain
    plan in that it shows what *actually* happened rather than an explain plan
    generated by tkprof 24 hours later - albeit likely a valid explain plan?

    Cheers for any insight,
    cam
    --
    =========================

    Stefan P Knecht
    Senior Consultant
    Infrastructure Managed Services

    Trivadis AG
    Europa-Strasse 5
    CH-8152 Glattbrugg

    Phone +41-44-808 70 20
    Fax +41-808 70 12
    Mobile +41-79-571 36 27
    stefan.knecht_at_trivadis.com
    http://www.trivadis.com

    OCP 9i/10g SCSA SCNA
    =========================

    --
    http://www.freelists.org/webpage/oracle-l
  • Hemant K Chitale at Jun 30, 2008 at 2:02 pm
    Does tkprof show the time and number of block gets for each step
    (even if row source operations shows "0" rows).

    For example, in one of my tests, I get
    select o.id, o.name from my_order_test o, my_source_tab s
    where o.name=s.object_name
    and o.id > 57000
    order by o.id

    call count cpu elapsed disk query current
    rows

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

    Parse 1 0.07 0.07 0 0 0

    Execute 1 0.00 0.00 0 0 0

    Fetch 1 0.03 0.04 0 3421 0

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

    total 3 0.10 0.12 0 3421 0

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

    Rows Row Source Operation

    ------- ---------------------------------------------------
    0 SORT ORDER BY (cr=3421 pr=0 pw=0 time=42304 us)
    0 HASH JOIN (cr=3421 pr=0 pw=0 time=42278 us)
    0 TABLE ACCESS FULL MY_ORDER_TEST (cr=3421 pr=0 pw=0 time=41969 us)
    0 TABLE ACCESS FULL MY_SOURCE_TAB (cr=0 pr=0 pw=0 time=0 us)

    The "Rows" are 0 (and the query really did return 0 rows), but I can
    see that the query went through 3,421 blocks
    in MY_ORDER_TEST to return 0 rows. Since this was 0 rows, the Hash
    Join didn't even have to read any
    blocks from MY_SOURCE_TAB -- but MY_SOURCE_TAB appears because that
    IS the execution plan.

    Also, if there have been *multiple* executions of the same SQL
    statement, tkprof would show you the
    RowSourceOperations of only the last execution.
    See
    http://hemantoracledba.blogspot.com/2008/03/example-sliced-trace-files-and-tkprof.html
    At 05:07 AM Monday, you wrote:
    Hello all,

    Can't quite understand this - I have just run a 26-hour level-12
    10046 trace on a slow process producing a 750M trc file. Imagine my
    disappointment to find that the key query I am interested in has no
    rowsource data - or only reports zeroes. Other queries have row totals - e.g.:



    but for my huge query I just get an execution plan with zeroes in
    the Rows column:

    Rows Execution Plan
    ------- ---------------------------------------------------
    0 INSERT STATEMENT MODE: ALL_ROWS
    0 VIEW
    0 SORT (ORDER BY)
    0 HASH JOIN (ANTI)
    0 NESTED LOOPS
    0 NESTED LOOPS
    0 HASH JOIN (OUTER)
    0 HASH JOIN (RIGHT ANTI)
    .. etc.. etc

    I'd be interested in any ideas as to why this is. Also, am I correct
    in my assumption that the rowsource is arguably more valuable than
    the explain plan in that it shows what *actually* happened rather
    than an explain plan generated by tkprof 24 hours later - albeit
    likely a valid explain plan?

    Cheers for any insight,
    cam
    Hemant K Chitale
    http://hemantoracledba.blogspot.com

    "A 'No' uttered from the deepest conviction is better than a 'Yes'
    merely uttered to please, or worse, to avoid trouble."
    Mohandas Gandhi Quotes
    : http://www.brainyquote.com/quotes/authors/m/mohandas_gandhi.html

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 29, '08 at 9:07p
activeJun 30, '08 at 2:02p
posts4
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase