Re: Unexpected Tkprof Row Count

mccmx_at_hotmail.com (Matt) wrote in message news:<cfee5bcf.0408310324.59b75cf2_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.
>

May we see the query in question?

>
> 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
Received on Tue Aug 31 2004 - 16:53:16 CDT