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


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

Other related posts: