| 10G统计信息的自动保存 | DBA之路：OoNiceDream |

10G统计信息的自动保存

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2008/11/10g-automatic-save-stats.html

从10G开始，若采用dbms_stats进行schema或table的分析，则会自动保存统计信息于dba_tab_stats_history。默认保存31天。且可用DBMS_STATS.RESTORE….相关的过程来恢复以前的统计信息。

sys@ORCL>select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

查看、修改统计信息保存时间：

sys@ORCL>select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
 
GET_STATS_HISTORY_RETENTION
---------------------------
                         31
sys@ORCL>execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (10);
 
PL/SQL 过程已成功完成。
 
sys@ORCL>select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;
 
GET_STATS_HISTORY_RETENTION
---------------------------
                         10

简单测试下统计信息的保存与恢复：
一、Table:
恢复采用：execute dbms_stats.restore_table_stats (‘OWNER’,'TABLE_NAME’,'date’);

sys@ORCL>create table test(id number);
 
表已创建。
 
sys@ORCL>begin   
  2  for i in 1..500 loop
  3  insert into test values(i);
  4  commit;
  5  end loop;
  6  end;
  7  /
 
PL/SQL 过程已成功完成
 
sys@ORCL>select count(*) from test;
 
  COUNT(*)
----------
       500
 
sys@ORCL>
sys@ORCL>select OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED from dba_tables
  2  where table_name='TEST';
 
OWNER      TABLE_NAME                       NUM_ROWS LAST_ANALYZED
---------- ------------------------------ ---------- --------------
SYS        TEST
 
已选择 1 行。
 
sys@ORCL>exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYS',tabname=>'TEST');
 
PL/SQL 过程已成功完成。
 
sys@ORCL>alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
 
会话已更改。
 
sys@ORCL>select OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED from dba_tables
  2  where table_name='TEST';
 
OWNER      TABLE_NAME                       NUM_ROWS LAST_ANALYZED
---------- ------------------------------ ---------- -------------------
SYS        TEST                                  500 2008-11-10 21:00:52
 
已选择 1 行。
 
sys@ORCL>select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history 
  2  where table_name='TEST';
 
TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ -----------------------------------------
TEST                           10-11月-08 09.00.52.822018 下午 +08:00
 
已选择 1 行。
 
sys@ORCL>begin   
  2  for i in 1..50 loop
  3  insert into test values(i);
  4  commit;
  5  end loop;
  6  end;
  7  /
 
PL/SQL 过程已成功完成。
 
sys@ORCL>select count(*) from test;
 
  COUNT(*)
----------
       550
 
已选择 1 行。
 
sys@ORCL>select OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED from dba_tables
  2  where table_name='TEST';
 
OWNER      TABLE_NAME                       NUM_ROWS LAST_ANALYZED
---------- ------------------------------ ---------- -------------------
SYS        TEST                                  500 2008-11-10 21:00:52
 
已选择 1 行。
 
sys@ORCL>exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SYS',tabname=>'TEST');
 
PL/SQL 过程已成功完成。
 
sys@ORCL>select OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED from dba_tables
  2  where table_name='TEST';
 
OWNER      TABLE_NAME                       NUM_ROWS LAST_ANALYZED
---------- ------------------------------ ---------- -------------------
SYS        TEST                                  550 2008-11-10 21:08:20
 
已选择 1 行。
 
sys@ORCL>select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history                                                 
  2  where table_name='TEST';
 
TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ -----------------------------------------
TEST                           10-11月-08 09.00.52.822018 下午 +08:00
TEST                           10-11月-08 09.08.20.993000 下午 +08:00
 
已选择2行。
 
sys@ORCL>
 
-----以下是恢复
sys@ORCL>execute dbms_stats.restore_table_stats ('SYS','TEST','10-11月-08 09.00.52.822018 下午 +08:00'); 
 
PL/SQL 过程已成功完成。
 
sys@ORCL>select OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED from dba_tables
  2  where table_name='TEST';
 
OWNER      TABLE_NAME                       NUM_ROWS LAST_ANALYZED
---------- ------------------------------ ---------- -------------------
SYS        TEST                                  500 2008-11-10 21:00:52
 
已选择 1 行。
 
sys@ORCL>select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history
  2  where table_name='TEST';
 
TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ ------------------------------------------
TEST                           10-11月-08 09.00.52.822018 下午 +08:00
TEST                           10-11月-08 09.08.20.993000 下午 +08:00
TEST                           10-11月-08 09.11.42.346643 下午 +08:00
 
已选择3行。

二、Schema:
恢复采用：execute DBMS_STATS.RESTORE_SCHEMA_STATS(‘OWNER’,'date’);

sys@ORCL>select OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED from dba_tables where table_name='TAB1';
 
OWNER      TABLE_NAME                       NUM_ROWS LAST_ANALYZED
---------- ------------------------------ ---------- -------------------
TEST       TAB1                                  550 2008-11-10 21:18:14
 
已选择 1 行。
 
sys@ORCL>exec dbms_stats.gather_schema_stats(ownname =>'TEST',options=>'gather');
 
PL/SQL 过程已成功完成。
 
sys@ORCL>select OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED from dba_tables 
  2  where table_name='TAB1';
 
OWNER      TABLE_NAME                       NUM_ROWS LAST_ANALYZED
---------- ------------------------------ ---------- -------------------
TEST       TAB1                                  600 2008-11-10 21:20:36
 
已选择 1 行。
 
sys@ORCL>select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history
  2  where table_name='TAB1';
 
TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ -----------------------------------------
TAB1                           10-11月-08 09.18.14.733592 下午 +08:00
TAB1                           10-11月-08 09.20.36.925042 下午 +08:00
 
已选择2行。
 
sys@ORCL>execute DBMS_STATS.RESTORE_SCHEMA_STATS('TEST','10-11月-08 09.18.14.733592 下午 +08:00');
 
PL/SQL 过程已成功完成。
 
sys@ORCL>select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history
  2  where table_name='TAB1';
 
TABLE_NAME                     STATS_UPDATE_TIME
------------------------------ -----------------------------------------
TAB1                           10-11月-08 09.18.14.733592 下午 +08:00
TAB1                           10-11月-08 09.20.36.925042 下午 +08:00
TAB1                           10-11月-08 09.22.07.272512 下午 +08:00
 
已选择3行。
 
sys@ORCL>select OWNER,TABLE_NAME,NUM_ROWS,LAST_ANALYZED from dba_tables 
  2  where table_name='TAB1';
 
OWNER      TABLE_NAME                       NUM_ROWS LAST_ANALYZED
---------- ------------------------------ ---------- -------------------
TEST       TAB1                                  550 2008-11-10 21:18:14
 
已选择 1 行。

注意：经测试发现，如果是恢复整个schema的统计信息，则时间点选取需要注意。
由于每张表的分析时间不同，恢复是以时间点为准，而不是以整个schema为准。
举个例子来说，如果选取了当中的时间点，恢复后会发现，一部份表的统计信息是新的，一部份是旧的。
当然，统计信息还是以自己备份的好，特别是整个schema的分析。

— The End —

关键字: 基础知识