Re: Unexpected Tkprof Row Count

Jaap W. van Dijk <j.w.vandijk.removethis_at_hetnet.nl> wrote in message news:<0o5vi0tr629ci44pq0a6j3i8i8j0p966j1_at_4ax.com>...
> On 27 Aug 2004 01:15:14 -0700, mccmx_at_hotmail.com (Matt) wrote:
>
> >Oracle 8.1.7.2.1 EE on HP-UX 11
> >
> >Does anyone know of a reason why a SORT (GROUP BY) row source
> >operation would increase the number of rows returned from a query...
> >
> >I thought that a GROUP BY would reduce the number of rows, not
> >increase them...
> >
> >Here is the TKPROF output which shows that a JOIN between 2 tables
> >('PS_TM_PEFF_GPQCAL' and 'PS_TM_PEFF_BNCHMRK') returns about 110,000
> >rows but when this is passed up to the GROUP BY operation, the row
> >count jumps up to 7.3 million.
> >
> >Rows Execution Plan
> >------- ---------------------------------------------------
...
> >7314720 SORT (GROUP BY)
> > 110312 FILTER
> > 145807 NESTED LOOPS (OUTER)
...
>
> I have the same experience with this. Inexplicable and seemingly
> unreconcilable row counts.
> Couldn't find anything on this matter on Metalink. The docs also leave
> you wondering what this number actually should show: the number of
> processed records by a step or the number of resulting records from a
> step.

I checked 8i and 9i documentation. In the chapter "Using SQL Trace and TKPROF" of the performance tuning book (title varies), I find

Execution Plan
If you specify the EXPLAIN parameter on the TKPROF statement line, then TKPROF uses the EXPLAIN PLAN statement to generate the execution plan of each SQL statement traced. TKPROF also displays the number of rows processed by each step of the execution plan.

The last sentence seems to answer your question.

Guy Harrison's "Oracle SQL High-Performance Tuning" says the same:

A significant enhancement to the tkprof execution plan is the presence of both the step (m) and also the number of rows processed by each step (1).

I'm reading his 1997 first ed. pp.103-4. But the tkprof sample with the confusing superscripts is on p.102. The book is about Oracle8.

However, Tom Kyte's "Expert One-On-One" p.461 (2001 ed.) says of the numbers under ROWS "These counts are the row counts of the rows flowing out of that step of the the execution plan" for 8i, and row count for the rows flowing INTO that step before 8i. Please read his book for a better description. To me, it sounds like that number counts the resulting records, not the processing records, in Oracle8i, contrary to what the documentation says.

Yong Huang Received on Sun Aug 29 2004 - 11:57:01 CDT