rajesh@10GR2> create table emp
2 nologging
3 as
4 select rownum as empno,
5 object_name as ename,
6 mod(rownum,100) as deptno,
7 created as hiredate,
8 abs(dbms_random.value) as salary,
9 mod(rownum,100)*.001 as comm
10 from all_objects;
Table created.
Elapsed: 00:00:06.88
rajesh@10GR2>
rajesh@10GR2> alter table emp add constraint emp_pk primary key(empno);
Table altered.
Elapsed: 00:00:00.45
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(ownname=>user,tabname=>'EMP',cascade=>true);
3 end;
4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.56
rajesh@10GR2>
rajesh@10GR2> select table_name,column_name,num_distinct
2 from user_tab_col_statistics
3 where table_name ='EMP';
TABLE_NAME COLUMN_NAME NUM_DISTINCT
------------------------------ ------------------------------ ------------
EMP EMPNO 56169
EMP ENAME 31883
EMP DEPTNO 101
EMP HIREDATE 2040
EMP SALARY 56287
EMP COMM 101
6 rows selected.
Elapsed: 00:00:00.01
rajesh@10GR2> select index_name,table_name,leaf_blocks,distinct_keys,num_rows
2 from user_ind_statistics
3 where table_name ='EMP';
INDEX_NAME TABLE_NAME LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS
------------------------------ ------------------------------ ----------- ------------- ----------
EMP_PK EMP 117 56287 56287
Elapsed: 00:00:00.09
rajesh@10GR2>
Now the statistics collected on the EMP table data is available in user_tab_col_statistics and Index information available in user_ind_statistics data dictionary. Now let us wipe out all data from EMP table and regather statistics.
rajesh@10GR2> truncate table emp;
Table truncated.
Elapsed: 00:00:00.67
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(ownname=>user,tabname=>'EMP',cascade=>true);
3 end;
4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.12
rajesh@10GR2>
rajesh@10GR2> select table_name,column_name,num_distinct
2 from user_tab_col_statistics
3 where table_name ='EMP';
TABLE_NAME COLUMN_NAME NUM_DISTINCT
------------------------------ ------------------------------ ------------
EMP EMPNO 0
EMP ENAME 0
EMP DEPTNO 0
EMP HIREDATE 0
EMP SALARY 0
EMP COMM 0
6 rows selected.
Elapsed: 00:00:00.06
rajesh@10GR2>
rajesh@10GR2> select index_name,table_name,leaf_blocks,distinct_keys,num_rows
2 from user_ind_statistics
3 where table_name ='EMP';
INDEX_NAME TABLE_NAME LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS
------------------------------ ------------------------------ ----------- ------------- ----------
EMP_PK EMP 0 0 0
Elapsed: 00:00:00.10
rajesh@10GR2>
Oracle database 10gR2 introduced a new view USER_TAB_STATS_HISTORY provides a history of table statistics modifications for all tables owned by the current user.
So looking at the USER_TAB_STATS_HISTORY for the table 'EMP' now contains two entries.
- First timestamp represents, statistics gathered for first time after table creation and data loading.
- Second timestamp represents, statistics gathered after table is Truncated.
rajesh@10GR2> SELECT table_name, stats_update_time
2 FROM user_tab_stats_history
3 WHERE table_name ='EMP'
4 order by 2
5 /
TABLE_NAME STATS_UPDATE_TIME
------------------------------ --------------------------------------------------
EMP 02-JAN-11 10.24.00.880000 PM +05:30
EMP 02-JAN-11 10.24.21.958000 PM +05:30
Elapsed: 00:00:00.03
rajesh@10GR2>
If you wish to restore old version statistics back (statistics version prior to table Truncation) to data-dictionary, then it can be restored using RESTORE procedures of DBMS_STATS package. These procedures use a time stamp as an argument and restore statistics as of that time stamp. This is useful in case newly collected statistics leads to some sub-optimal execution plans and the administrator wants to revert to the previous set of statistics.
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.restore_table_stats(
3 ownname=>user,
4 tabname=>'EMP',
5 as_of_timestamp=>to_timestamp('02-JAN-2011 10.24.00.880000 PM','dd-mon-yyyy hh.mi.ss.ff am')
6 );
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.20
rajesh@10GR2>
rajesh@10GR2> select table_name,column_name,num_distinct
2 from user_tab_col_statistics
3 where table_name ='EMP';
TABLE_NAME COLUMN_NAME NUM_DISTINCT
------------------------------ ------------------------------ ------------
EMP EMPNO 56169
EMP ENAME 31883
EMP DEPTNO 101
EMP HIREDATE 2040
EMP SALARY 56287
EMP COMM 101
6 rows selected.
Elapsed: 00:00:00.04
rajesh@10GR2> select index_name,table_name,leaf_blocks,distinct_keys,num_rows
2 from user_ind_statistics
3 where table_name ='EMP';
INDEX_NAME TABLE_NAME LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS
------------------------------ ------------------------------ ----------- ------------- ----------
EMP_PK EMP 117 56287 56287
Elapsed: 00:00:00.09
rajesh@10GR2>
The old statistics are purged automatically at regular intervals based on the statistics history retention setting. The default value is 31 days, which means that you would be able to restore the optimizer statistics to any time in last 31 days.
rajesh@10GR2>
rajesh@10GR2> select dbms_stats.get_stats_history_retention from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2>
Its is important to note that if ANALYZE command has been used for collecting statistics, then those versions will not be maintained in USER_TAB_STATS_HISTORY and hence those old version statistics cannot be restored.
rajesh@10GR2> create table t
2 nologging
3 as
4 select * from all_objects
5 /
Table created.
Elapsed: 00:00:02.59
rajesh@10GR2>
rajesh@10GR2> analyze table T compute statistics;
Table analyzed.
Elapsed: 00:00:01.81
rajesh@10GR2>
rajesh@10GR2> select table_name, column_name, num_distinct, last_analyzed
2 from user_tab_col_statistics
3 where table_name ='T'
4 /
TABLE_NAME COLUMN_NAME NUM_DISTINCT LAST_ANALYZED
------------------------------ ------------------------------ ------------ -----------------------
T OWNER 30 03-jan-2011 12:08:02 pm
T OBJECT_NAME 31883 03-jan-2011 12:08:02 pm
T SUBOBJECT_NAME 1072 03-jan-2011 12:08:02 pm
T OBJECT_ID 56325 03-jan-2011 12:08:02 pm
T DATA_OBJECT_ID 8922 03-jan-2011 12:08:02 pm
T OBJECT_TYPE 38 03-jan-2011 12:08:02 pm
T CREATED 5604 03-jan-2011 12:08:02 pm
T LAST_DDL_TIME 5786 03-jan-2011 12:08:02 pm
T TIMESTAMP 5886 03-jan-2011 12:08:02 pm
T STATUS 2 03-jan-2011 12:08:02 pm
T TEMPORARY 2 03-jan-2011 12:08:02 pm
T GENERATED 2 03-jan-2011 12:08:02 pm
T SECONDARY 1 03-jan-2011 12:08:02 pm
13 rows selected.
Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2>
Now, the history of table statistics modifications will not be available in USER_TAB_STATS_HISTORY since we used ANALYZE command.
rajesh@10GR2> select *
2 from user_tab_stats_history
3 where table_name ='T'
4 /
no rows selected
Elapsed: 00:00:00.03
rajesh@10GR2>
If we use dbms_stats to gather statistics, then statistics history will be available in USER_TAB_STATS_HISTORY .
rajesh@10GR2>
rajesh@10GR2> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.51
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> select *
2 from user_tab_stats_history
3 where table_name ='T'
4 /
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME STATS_UPDATE_TIME
------------------------------ ------------------------------ ------------------------------ -----------------------------------------------
T 03-JAN-11 12.09.24.566000 PM +05:30
Elapsed: 00:00:00.04
rajesh@10GR2>
So, having looked about statistics at Column level, how about Histograms? will that be restored as part of RESTORE_TABLE_STATS procedure available in DBMS_STATS API? YES. For example,
rajesh@10GR2> create table emp
2 nologging
3 as
4 select rownum as empno,
5 object_name as ename,
6 mod(rownum,100) as deptno,
7 created as hiredate,
8 abs(dbms_random.value) as salary,
9 mod(rownum,100)*.001 as comm
10 from all_objects;
Table created.
Elapsed: 00:00:07.46
rajesh@10GR2>
rajesh@10GR2> alter table emp add constraint emp_pk primary key(empno);
Table altered.
Elapsed: 00:00:00.37
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(ownname=>user,tabname=>'EMP',cascade=>true,method_opt=>'for all columns size 254');
3 end;
4
5 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.82
rajesh@10GR2>
rajesh@10GR2> select count(*)
2 from user_tab_histograms
3 where table_name ='EMP';
COUNT(*)
----------
1217
Elapsed: 00:00:00.04
rajesh@10GR2>
rajesh@10GR2> select table_name, stats_update_time
2 from user_tab_stats_history
3 where table_name ='EMP'
4 order by 2;
TABLE_NAME STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
EMP 03-JAN-11 05.54.06.355000 PM +05:30
Elapsed: 00:00:00.01
rajesh@10GR2>
rajesh@10GR2> truncate table emp ;
Table truncated.
Elapsed: 00:00:01.07
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.gather_table_stats(ownname=>user,tabname=>'EMP',cascade=>true,method_opt=>'for all columns size 254');
3 end;
4 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.81
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> select count(*)
2 from user_tab_histograms
3 where table_name ='EMP';
COUNT(*)
----------
0
Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> select table_name, stats_update_time
2 from user_tab_stats_history
3 where table_name ='EMP'
4 order by 2;
TABLE_NAME STATS_UPDATE_TIME
------------------------------ ---------------------------------------------------------------------------
EMP 03-JAN-11 05.54.06.355000 PM +05:30
EMP 03-JAN-11 05.54.21.355000 PM +05:30
Elapsed: 00:00:00.03
rajesh@10GR2>
rajesh@10GR2> begin
2 dbms_stats.restore_table_stats(
3 ownname=>user,
4 tabname=>'EMP',
5 as_of_timestamp=>to_timestamp('03-JAN-2011 05.54.06.355000 PM','dd-mon-yyyy hh.mi.ss.ff am')
6 );
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.50
rajesh@10GR2>
rajesh@10GR2>
rajesh@10GR2> select count(*)
2 from user_tab_histograms
3 where table_name ='EMP';
COUNT(*)
----------
1217
Elapsed: 00:00:00.04
rajesh@10GR2>
No comments:
Post a Comment