从Oracle10g开始，当对一个表收集统计信息时，旧的统计信息会被保留了，因此当出现新收集的统计信息引起性能问题时，可以还原旧统计信息。缺省情况下统计信息会被保留31天，但可以执行execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (days)来进行修改，days为你所指定的天数。
SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
SQL> execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION(60);
PL/SQL procedure successfully completed
SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
60
在修改统计信息保留天数时要确保sysaux表空间有足够的表空间大小来存储统计信息，防止统计信息写满sysaux表空间。
可以执行以下语句来查询统计信息的保留天数：
SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
GET_STATS_HISTORY_RETENTION
---------------------------
31
执行以下语句可以查询被保留时间最久的统计信息，早于这个时间点的统计信息已经被删除。任何要求还原这个时间点及之前的统计信息都会报错”ORA-20006: Unable to restore statistics , statistics history not available”
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;
GET_STATS_HISTORY_AVAILABILITY
--------------------------------------------------------------------------------
10-APR-16 10.50.36.929152000 PM +08:00
SQL> execute dbms_stats.restore_table_stats ('SCOTT','EMP','10-APR-16 10.50.36.929152000 PM +08:00');
begin dbms_stats.restore_table_stats ('SCOTT','EMP','10-APR-16 10.50.36.929152000 PM +08:00'); end;
ORA-20006: Unable to restore statistics , statistics history not available
ORA-06512: at "SYS.DBMS_STATS", line 17063
ORA-06512: at "SYS.DBMS_STATS", line 17080
ORA-06512: at line 2
要想找到表的统计信息收集历史信息可以执行以下查询
SQL> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='PM_BILL'; TABLE_NAME STATS_UPDATE_TIME ------------------------------ -------------------------------------------------------------------------------- PM_BILL 12-APR-16 10.06.00.454894 PM +08:00 PM_BILL 29-APR-16 10.11.24.030930 PM +08:00 PM_BILL 01-MAY-16 04.44.42.324357 AM +08:00 PM_BILL 02-MAY-16 07.03.38.762862 AM +08:00 PM_BILL 03-MAY-16 09.33.53.942353 AM +08:00
还原统计信息可以根据需要选择以下几种方式:
execute DBMS_STATS.RESTORE_TABLE_STATS (‘owner’,’table’,date)
execute DBMS_STATS.RESTORE_DATABASE_STATS(date)
execute DBMS_STATS.RESTORE_DICTIONARY_STATS(date)
execute DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(date)
execute DBMS_STATS.RESTORE_SCHEMA_STATS(‘owner’,date)
execute DBMS_STATS.RESTORE_SYSTEM_STATS(date)
下面介绍一个还原指定表统计信息的操作:
1.查询表pm_bill的统计信息，显示有232277行记录。
SQL> select table_name,num_rows,blocks,empty_blocks,sample_size from user_tables where table_name='PM_BILL'; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS SAMPLE_SIZE ------------------------------ ---------- ---------- ------------ ----------- PM_BILL 232277 5164 0 69683
2.查询表pm_bill收集的统计信息的次数
SQL> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='PM_BILL'; TABLE_NAME STATS_UPDATE_TIME ------------------------------ -------------------------------------------------------------------------------- PM_BILL 12-APR-16 10.06.00.454894 PM +08:00 PM_BILL 29-APR-16 10.11.24.030930 PM +08:00 PM_BILL 01-MAY-16 04.44.42.324357 AM +08:00 PM_BILL 02-MAY-16 07.03.38.762862 AM +08:00 PM_BILL 03-MAY-16 09.33.53.942353 AM +08:00
3.查询表pm_bill中真实的记录数为235032与统计信息中记录的232277有差异
SQL> select count(*) from pm_bill;
COUNT(*)
----------
235032
4.查询统计信息保留的最早时间
SQL> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual; GET_STATS_HISTORY_AVAILABILITY -------------------------------------------------------------------------------- 10-APR-16 10.50.36.929152000 PM +08:00
5.对表pm_bill收集统计信息，采样比例使用100%
SQL> exec dbms_stats.gather_table_stats(ownname => 'insur_changde',tabname => 'pm_bill',estimate_percent => 100,method_opt => 'for all columns size repeat'); PL/SQL procedure successfully completed
6.查询表pm_bill的统计信息，可以看到重新收集统计信息后表的记录数与真实记数一样为235032
SQL> select table_name,num_rows,blocks,empty_blocks,sample_size from user_tables where table_name='PM_BILL'; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS SAMPLE_SIZE ------------------------------ ---------- ---------- ------------ ----------- PM_BILL 235032 5290 0 235032 SQL> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='PM_BILL'; TABLE_NAME STATS_UPDATE_TIME ------------------------------ -------------------------------------------------------------------------------- PM_BILL 12-APR-16 10.06.00.454894 PM +08:00 PM_BILL 29-APR-16 10.11.24.030930 PM +08:00 PM_BILL 01-MAY-16 04.44.42.324357 AM +08:00 PM_BILL 02-MAY-16 07.03.38.762862 AM +08:00 PM_BILL 03-MAY-16 09.33.53.942353 AM +08:00 PM_BILL 12-MAY-16 03.06.43.688976 PM +08:00
7.将表pm_bill的统计信息还原到03-MAY-16 09.33.53.942353 AM +08:00这个时间点
SQL> exec dbms_stats.restore_table_stats ('insur_changde','pm_bill','03-MAY-16 09.33.53.942353 AM +08:00');
PL/SQL procedure successfully completed
SQL> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history where table_name='PM_BILL';
TABLE_NAME STATS_UPDATE_TIME
------------------------------ --------------------------------------------------------------------------------
PM_BILL 12-APR-16 10.06.00.454894 PM +08:00
PM_BILL 29-APR-16 10.11.24.030930 PM +08:00
PM_BILL 01-MAY-16 04.44.42.324357 AM +08:00
PM_BILL 02-MAY-16 07.03.38.762862 AM +08:00
PM_BILL 03-MAY-16 09.33.53.942353 AM +08:00
PM_BILL 12-MAY-16 03.06.43.688976 PM +08:00
PM_BILL 12-MAY-16 03.12.55.388126 PM +08:00
7 rows selected
从上面的查询结果可以看到，表pm_bill的统计信息收集的历史信息多了一条时间为12-MAY-16 03.12.55.388126 PM +08:00
的记录，这是我们还原统计信息所产生的。
查询表pm_bill的统计信息，可以看到统计信息中记录的表的记录数恢复成了232277行
SQL> select table_name,num_rows,blocks,empty_blocks,sample_size from user_tables where table_name='PM_BILL'; TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS SAMPLE_SIZE ------------------------------ ---------- ---------- ------------ ----------- PM_BILL 232277 5164 0 69683
这个功能当新收集统计信息后，如果引起了性能问题可以做为一个临时手段还解决性能问题。