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;