Re: Unexpected Tkprof Row Count

Here's an observation - which doesn't
explain anything, but is an extreme oddity which suggests that even the row source operation output may be suspect.

Look at the three lines above the big SORT line:

> 6220 TABLE ACCESS FULL PS_TM_PEFF_TWTCAL
> 1176 TABLE ACCESS FULL PS_TM_PEFF_COEF
> 2352 VIEW PS_TM_PEFF_V_CCEH
> 7314720 SORT GROUP BY
Notice that 6220 * 1176 = 7314720

I can't tell you if it means anything, but it looks very suspicious. I would go back to the raw trace file and check to see if the stat lines are being output in the ID order. Possibly a few lines have been re-arranged in the plan - I have seen it happen (but only a couple of times) in the past.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated July 27th




"Matt" <mccmx_at_hotmail.com> wrote in message
news:cfee5bcf.0408310314.2f3cde08_at_posting.google.com...


> > Do you

> > have an example where the 'Row source statistics' show

> > the same anomaly ?

> >

> > -- 

> > Regards

> >

> > Jonathan Lewis

> >

>

> Yes,  the same anomaly exists in the run time stats.....  See below...

>

> The query takes over 20 seconds to complete, and I strongly suspect

> that it is the Group By operation which accounts for most of that

> time.

>

>

> Rows     Row Source Operation

> -------  ---------------------------------------------------

>      61  SORT ORDER BY

>      61   VIEW

>    1464    WINDOW SORT

>    1464     SORT GROUP BY

>    1464      HASH JOIN OUTER

>    1464       HASH JOIN

>    1464        VIEW PS_TM_PEFF_V_SHDAY

>    1464         SORT UNIQUE

>    1464          UNION-ALL

>    1176           FILTER

>    1177            NESTED LOOPS

>    1177             NESTED LOOPS OUTER

>    1177              HASH JOIN

>      42               MERGE JOIN CARTESIAN

>       2                NESTED LOOPS

>       2                 INDEX UNIQUE SCAN (object id 14471)

>       2                 TABLE ACCESS FULL PS_TM_PEFF_TCJR

>      42                SORT JOIN

>      42                 TABLE ACCESS FULL PS_TM_PEFF_T_CONTR

>    6220               TABLE ACCESS FULL PS_TM_PEFF_TWTCAL

>    1176              TABLE ACCESS FULL PS_TM_PEFF_COEF

>    2352             VIEW PS_TM_PEFF_V_CCEH

> 7314720              SORT GROUP BY

>  110312               FILTER

>  145807                NESTED LOOPS OUTER

>  110465                 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_GPQCAL

>  114449                  INDEX RANGE SCAN (object id 22901)

>  136124                 TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_BNCHMRK

>  246588                  INDEX RANGE SCAN (object id 22920)

>     288           NESTED LOOPS

>      43            MERGE JOIN CARTESIAN

>       2             TABLE ACCESS FULL PS_TM_PEFF_TCJR

>      43             SORT JOIN

>      42              TABLE ACCESS FULL PS_TM_PEFF_T_CONTR

>     288            TABLE ACCESS BY INDEX ROWID PS_TM_PEFF_TWTCAL

>    1650             INDEX RANGE SCAN (object id 22903)

>      24        VIEW PS_TM_PEFF_V_MFCAL

>    1095         WINDOW SORT

>    1095          TABLE ACCESS FULL PS_TM_PEFF_MFCAL

>    2248       VIEW PS_TM_PEFF_V_TGTCC

>    2248        HASH JOIN OUTER

>     476         TABLE ACCESS FULL PS_CMS_DPTDPTS

>    7248         INDEX FAST FULL SCAN (object id 14483)

>     110              SORT AGGREGATE

>      55               TABLE ACCESS FULL PS_TM_PEFF_TCJR

>    1536              SORT AGGREGATE

>    1824               INDEX RANGE SCAN (object id 14443)

>      96            SORT AGGREGATE

>      48             FIRST ROW

>      48              INDEX RANGE SCAN (MIN/MAX) (object id 14441)

>    1556            SORT AGGREGATE

>     868             INDEX FAST FULL SCAN (object id 22953)

>     138            SORT AGGREGATE

>      69             TABLE ACCESS FULL PS_TM_PEFF_TCJR