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

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Sun, 29 Jun 2008 23:55:26 +0200
Message-ID: <486b2b610806291455l4785d43n32e273c6cb9e4b89@mail.gmail.com>


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_at_gmail.com> 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_at_gmail.com> 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_at_trivadis.com
http://www.trivadis.com

OCP 9i/10g SCSA SCNA
=========================

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Jun 29 2008 - 16:55:26 CDT

Original text of this message