Re: Unexpected Tkprof Row Count

> 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