Re: Zero rowcount ops in tkprof (only some queries!)
- From: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>
- To: kadmon@xxxxxxxxx
- Date: Sun, 29 Jun 2008 23:55:26 +0200
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
#<nn> --
<nn> being the cursor number. Then search forward to the next STAT #<nn>
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 <kadmon@xxxxxxxxx> 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 <kadmon@xxxxxxxxx> 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@xxxxxxxxxxxx
http://www.trivadis.com
OCP 9i/10g SCSA SCNA
=========================
Other related posts:
