This question often comes to developer/DBA's mind that how many rows a table had in past. These type of information is quite handy when explain plan change happens. As we know change in data volume can also change the explain plan.
So keeping the history and knowing table growth trend one can know when a particular plan can change.
Whenever stats are gathered on table, its old stats like rowcount,numbers of blocks, average row len etc are flushed to SYS.WRI$_OPTSTAT_TAB_HISTORY table.
So its bit easy to get the history of rowcount for any table.
Like
SQL> select n.object_name as T,o.ROWCNT,o.BLKCNT,to_char(o.SAVTIME,'hh/dd/mm HH:
MI') as time from dba_objects n,WRI$_OPTSTAT_TAB_HISTORY o where o.obj#=n.object
_id and n.object_name='T1';
Table ROWCNT BLKCNT TIME
---------- ---------- --------------
T1
4000 155 12/23/03 12:13
T1
4000 155 12/23/03 12:15
T1
10/29/02 10:01
Needless to say this view can have zero rows if stats where on gathered at all on table.
Enjoy!
So keeping the history and knowing table growth trend one can know when a particular plan can change.
Whenever stats are gathered on table, its old stats like rowcount,numbers of blocks, average row len etc are flushed to SYS.WRI$_OPTSTAT_TAB_HISTORY table.
So its bit easy to get the history of rowcount for any table.
Like
SQL> select n.object_name as T,o.ROWCNT,o.BLKCNT,to_char(o.SAVTIME,'hh/dd/mm HH:
MI') as time from dba_objects n,WRI$_OPTSTAT_TAB_HISTORY o where o.obj#=n.object
_id and n.object_name='T1';
Table ROWCNT BLKCNT TIME
---------- ---------- --------------
T1
4000 155 12/23/03 12:13
T1
4000 155 12/23/03 12:15
T1
10/29/02 10:01
Needless to say this view can have zero rows if stats where on gathered at all on table.
Enjoy!
This comment has been removed by the author.
ReplyDeleteI am using this statement but its now working.
ReplyDeletePlease guide how to use this i am new to SQL and oracle.
This comment has been removed by the author.
ReplyDelete