Oracle 统计信息的备份恢复机制

最新推荐文章于 2023-08-26 17:39:30 发布

DBhanG

最新推荐文章于 2023-08-26 17:39:30 发布

阅读量628

点赞数

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/baoyuhang0/article/details/113064128

版权

ORACLE 专栏收录该内容

20 篇文章 2 订阅

订阅专栏

统计信息的备份恢复机制:

今天说一下统计信息的备份恢复机制.从Oracle10g开始,当收集统计信息的时候,旧的统计信息会被保留,如果因为新的统计信息而出现性能问题,旧的统计信息旧可以被恢复。

Oracle的历史统计信息保存在以下几张表中：

WRI$_OPTSTAT_TAB_HISTORY 表的统计信息
WRI$_OPTSTAT_IND_HISTORY 索引的统计信息
WRI$_OPTSTAT_HISTHEAD_HISTORY 列的统计信息
WRI$_OPTSTAT_HISTGRM_HISTORY 直方图的信息

默认情况下,统计信息将被保留31天.我们可以手动进行修改

SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;
EXECUTE DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (XX);

既然旧的统计信息会被保留,那么我们如何才可以查询到历史的统计信息.

为此,Oracle提供了dba_tab_stats_history视图,我们虽然可以从视图dba_tab_stats_history可以查询表历史收集统计信息的时间，但是该视图无法显示表的总行数，我们很难去区分,由于DBA_TAB_STATS_HISTORY来源于WRI$_OPTSTAT_TAB_HISTORY,所以我们需要结合基表WRI $_OPTSTAT_TAB_HISTORY来获取更多关于表的信息.具体查询方式如下:

历史统计信息查询语句如下(表)：

  SELECT b.OWNER,
        b.OBJECT_NAME TABLE_NAME, 
        TO_CHAR(a.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,
        TO_CHAR(a.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,
        a.ROWCNT
  FROM SYS.WRI$_OPTSTAT_TAB_HISTORY a, DBA_OBJECTS b
  WHERE a.OBJ# = b.OBJECT_ID
    AND b.OBJECT_NAME ='TEST'
  ORDER BY a.OBJ#, a.SAVTIME;

既然我们可以查询出来旧的统计信息,那么我们如何恢复旧的统计信息呢?
Oracle提供了DBMS_STATS包供我们使用:

EXECUTE DBMS_STATS.RESTORE_TABLE_STATS ('HR','TEST',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);       --恢复SYSTEM的统计信息

从oracle 11g开始,Oracle又增加了系统信息的备份和恢复功能,我们可以将统计信息导出存放至一张表中.并可以随时进行导入恢复.
dbms_stats.export_table_stats
dbms_stats.import_table_stats

下面通过测试,来详细说明一下两种方式恢复统计信息操作的整个流程！

1.Oracle自动保留历史统计信息机制:

模拟表的统计信息恢复流程:
创建测试表:
SQL> create table hr.hang as select * from dba_objects;
Table created.

查询该表的统计信息:
SQL>select owner, table_name, num_rows, blocks, avg_row_len
from    dba_tables
where  owner='HR'
and  table_name='HANG';
OWNER            TABLE_NAME       NUM_ROWS     BLOCKS     AVG_ROW_LEN
--------------- --------------   ----------   ----------  -----------
HR                 HANG


收集统计信息:
SQL>exec dbms_stats.gather_table_stats(ownname => 'HR',tabname => 'HANG')
PL/SQL procedure successfully completed.

SQL>exec dbms_stats.gather_table_stats(ownname => 'HR',tabname => 'HANG')
PL/SQL procedure successfully completed.
(测试明显一些,收集两次)

再次查询:
SQL>select owner, table_name, num_rows, blocks, avg_row_len
from    dba_tables
where  owner='HR'
and  table_name='HANG';
OWNER              TABLE_NAME         NUM_ROWS     BLOCKS AVG_ROW_LEN
-------------- --------------------- ---------- ---------- -----------
HR                HANG                 86954       1270          98

查询统计信息历史记录:
  SELECT b.OWNER,
        b.OBJECT_NAME TABLE_NAME, 
        TO_CHAR(a.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,
        TO_CHAR(a.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,
        a.ROWCNT
  FROM SYS.WRI$_OPTSTAT_TAB_HISTORY a, DBA_OBJECTS b
  WHERE a.OBJ# = b.OBJECT_ID
    AND b.OBJECT_NAME ='HANG'
  ORDER BY a.OBJ#, a.SAVTIME;

OWNER      TABLE_NAME LAST_ANALYZETIME    CURR_ANALYZETIME        ROWCNT
---------- ---------- ------------------- ------------------- ----------
HR         HANG                           2020-12-17 21:41:16
HR         HANG       2020-12-17 21:41:16 2020-12-17 21:42:28      86954



执行插入:
SQL>insert into hr.hang select * from hr.hang；
SQL>commit；

收集统计信息:
SQL>exec dbms_stats.gather_table_stats(ownname => 'HR',tabname => 'HANG')
PL/SQL procedure successfully completed.

再次查询:
SQL>select owner, table_name, num_rows, blocks, avg_row_len
from    dba_tables
where  owner='HR'
and  table_name='HANG';
OWNER      TABLE_NAME   NUM_ROWS     BLOCKS AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
HR         HANG           173908       2540          98


查询统计信息历史记录:
  SELECT b.OWNER,
        b.OBJECT_NAME TABLE_NAME, 
        TO_CHAR(a.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,
        TO_CHAR(a.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,
        a.ROWCNT
  FROM SYS.WRI$_OPTSTAT_TAB_HISTORY a, DBA_OBJECTS b
  WHERE a.OBJ# = b.OBJECT_ID
    AND b.OBJECT_NAME ='HANG'
  ORDER BY a.OBJ#, a.SAVTIME;
  
OWNER      TABLE_NAME LAST_ANALYZETIME    CURR_ANALYZETIME        ROWCNT
---------- ---------- ------------------- ------------------- ----------
HR         HANG                           2020-12-17 21:41:16
HR         HANG       2020-12-17 21:41:16 2020-12-17 21:42:28      86954
HR         HANG       2020-12-17 21:42:28 2020-12-17 21:43:30      86954




恢复表的统计信息至2020-12-17 21:32:31
EXECUTE 
DBMS_STATS.RESTORE_TABLE_STATS 
('HR','HANG',TO_TIMESTAMP('2020-12-17 21:42:28','YYYY-MM-DD HH24:MI:SS')); 

恢复完成后查询表的统计信息:
SQL> select owner, table_name, num_rows, blocks, avg_row_len
    from    dba_tables
    where  owner='HR'
    and  table_name='HANG';

OWNER      TABLE_NAME   NUM_ROWS     BLOCKS AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
HR         HANG            86954       1270          98

再次查询历史信息:
  SELECT b.OWNER,
        b.OBJECT_NAME TABLE_NAME, 
        TO_CHAR(a.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,
        TO_CHAR(a.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,
        a.ROWCNT
  FROM SYS.WRI$_OPTSTAT_TAB_HISTORY a, DBA_OBJECTS b
  WHERE a.OBJ# = b.OBJECT_ID
    AND b.OBJECT_NAME ='HANG'
  ORDER BY a.OBJ#, a.SAVTIME;
OWNER      TABLE_NAME LAST_ANALYZETIME    CURR_ANALYZETIME        ROWCNT
---------- ---------- ------------------- ------------------- ----------
HR         HANG                           2020-12-17 21:47:10
HR         HANG       2020-12-17 21:47:10 2020-12-17 21:48:12      86954
HR         HANG       2020-12-17 21:48:12 2020-12-17 21:48:53      86954
HR         HANG       2020-12-17 21:48:53 2020-12-17 21:50:24     173908

恢复成功.

2.导出导入方式:

我们首先需要创建一个存放统计信息的表,之后将表的统计信息导出到存放统计信息的表中.最后可以采取导入恢复.

采用导出方式来备份恢复统计信息流程测试:
SQL> create table hr.hang as select * from dba_objects;
Table created.
收集统计信息:
SQL>exec dbms_stats.gather_table_stats(ownname => 'HR',tabname => 'HANG')
PL/SQL procedure successfully completed.

查看统计信息:
select owner, table_name, num_rows, blocks, avg_row_len
from    dba_tables
where  owner='HR'
and  table_name='HANG';
OWNER      TABLE_NAME          NUM_ROWS     BLOCKS AVG_ROW_LEN
--------- ------------------- ---------- ---------- -----------
HR            HANG               86954       1270          98

备份统计信息:
进入HR用户中,创建一个存放统计信息的表:
SQL>conn hr/hr
SQL> exec dbms_stats.create_stat_table(user,'STAT_TIMESTAMP');

将表的统计信息导出传输到STAT_TIMESTAMP表中.
SQL> exec dbms_stats.export_table_stats(user,'HANG',NULL,'STAT_TIMESTAMP');
PL/SQL procedure successfully completed.

执行插入数据并收集统计信息:
SQL> insert into hr.hang select * from hr.hang;
86958 rows created.
SQL> commit;
Commit complete.

SQL> exec dbms_stats.gather_table_stats(ownname => 'HR',tabname => 'HANG')
PL/SQL procedure successfully completed.



查看当前统计信息:
SQL> select owner, table_name, num_rows, blocks, avg_row_len
   from    dba_tables
   where  owner='HR'
   and  table_name='HANG';
OWNER      TABLE_NAME   NUM_ROWS     BLOCKS AVG_ROW_LEN
---------- ---------- ---------- ---------- -----------
HR         HANG           173908       2540          98

恢复统计信息:
exec dbms_stats.import_table_stats(NULL,'HANG', NULL,'STAT_TIMESTAMP');

再次查看:
select owner, table_name, num_rows, blocks, avg_row_len
from    dba_tables
where  owner='HR'
and  table_name='HANG';
OWNER        TABLE_NAME          NUM_ROWS     BLOCKS     AVG_ROW_LEN
--------- -------------------     ------      ---------- -----------
HR            HANG                86954       1270          98

恢复完成.

参考:
How to View Table Statistics History (Doc ID 761554.1)
Statistics Best Practices: How to Backup and Restore Statistics (Doc ID 464939.1)
How to Gather Optimizer Statistics on 11g (Doc ID 749227.1)
How to Restore Oracle Optimizer Statistics (Doc ID 452011.1)

DBhanG

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
Oracle 统计信息的备份恢复机制

统计信息的备份恢复机制:今天说一下统计信息的备份恢复机制.从Oracle10g开始,当收集统计信息的时候,旧的统计信息会被保留,如果因为新的统计信息而出现性能问题,旧的统计信息旧可以被恢复。Oracle的历史统计信息保存在以下几张表中：WRI$_OPTSTAT_TAB_HISTORY 表的统计信息WRI$_OPTSTAT_IND_HISTORY 索引的统计信息WRI$_OPTSTAT_HISTHEAD_HISTORY 列的统计信息WRI$_OPTSTAT_HISTGRM_HISTORY 直方图的
复制链接

扫一扫