Historical Row Counts via Stats History

Ever had a customer asking about the growth of a table? Or suspect an execution plan change might be because of an influx of data into a table?

Well you are in luck (maybe!).

Oracle 10g and 11g have a months worth of historical statistics stored by default. Before stats are gathered for a table their current values are written off into SYS.WRI$OPTSTAT_TAB_HISTORY table.

If your table changes a lot and stats were gathered every evening you may have 30 records in that table. If the table is static and doesn't change much it may have zero records in that table. But I am usually trying to corroborate a 'hunch' for changing plans due to large changes in data volume so this can usually be shown with a few data points.

Here is a query which will give you all the rowcounts recorded in history as well as the current value from dba_tables.num_rows . :

-- Historical RowCounts From Stats History
WITH TableList AS
  (SELECT object_id, 
          OWNER,
          object_name
     FROM dba_objects
    WHERE object_type='TABLE'
      AND object_name  = UPPER(:TNAME)  ) ,
  HistTableInfo AS
  (SELECT TableList.OWNER,
          OBJECT_NAME                         AS TABLE_NAME,
          TO_CHAR(ANALYZETIME,'YYYY-MM-DD HH24:MI') AS AnalyzeTime,
          OTH.ROWCNT
     FROM SYS.WRI$_OPTSTAT_TAB_HISTORY OTH,
          TableList
    WHERE OTH.OBJ# = TableList.OBJECT_ID  ),
  CurrTableInfo AS
  (SELECT TableList.OWNER, 
          TABLE_NAME,
          TO_CHAR(LAST_ANALYZED,'YYYY-MM-DD HH24:MI') AS AnalyzeTime,
          num_rows                                    AS ROWCNT
     FROM DBA_TABLES,
          TableList
    WHERE DBA_TABLES.TABLE_NAME = TableList.object_name
      AND DBA_TABLES.OWNER = TableList.owner)
SELECT * FROM HistTableInfo
UNION
SELECT * FROM CurrTableInfo 
ORDER BY 1,2,3;