Re: Zero rowcount ops in tkprof (only some queries!)

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Mon, 30 Jun 2008 22:02:55 +0800
Message-Id: <200806301403.m5UE38Fq032118@smtp41.singnet.com.sg>

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 
          0
Execute      1      0.00       0.00          0          0          0 
          0
Fetch        1      0.03       0.04          0       3421          0 
          0

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

total        3      0.10       0.12          0       3421          0 
          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.:
>
><deleted>
>
>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

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

Received on Mon Jun 30 2008 - 09:02:55 CDT