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 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
