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

  • From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
  • To: kadmon@xxxxxxxxx
  • Date: Mon, 30 Jun 2008 22:02:55 +0800

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, from my_order_test o, my_source_tab s
and > 57000
order by

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.

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   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,

Hemant K Chitale

"A 'No' uttered from the deepest conviction is better than a 'Yes' merely uttered to please, or worse, to avoid trouble." Mohandas Gandhi Quotes :


Other related posts: