【DB笔试面试643】在Oracle中,如何查询表和索引的历史统计信息?

题目部分

在Oracle中,如何查询表和索引的历史统计信息?

答案部分

从Oracle 10g开始,当收集表的统计信息的时候,旧的统计数据被保留,如果因为新的统计信息而出现性能问题,旧的统计信息就可以被恢复。历史统计信息保存在以下几张表中:

l WRI$_OPTSTAT_TAB_HISTORY 表的统计信息

l WRI$_OPTSTAT_IND_HISTORY 索引的统计信息

l WRI$_OPTSTAT_HISTHEAD_HISTORY 列的统计信息

l WRI$_OPTSTAT_HISTGRM_HISTORY 直方图的信息

从视图DBA_TAB_STATS_HISTORY可以查询历史收集统计信息的时间,但是不能查询到行数,所以需要结合基表来查询,查询的SQL语句如下:

SELECT B.OWNER,
       B.OBJECT_NAME TABLE_NAME, 
       TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,
       TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,
       D.ROWCNT
  FROM SYS.WRI$_OPTSTAT_TAB_HISTORY D, DBA_OBJECTS B
 WHERE D.OBJ# = B.OBJECT_ID
   AND B.OBJECT_NAME IN
       ('TEST_STAT', 'TPCCBOKBAL_TMP', 'TPCCBOKBALJN', 'PK_TPCCBOKBAL')
 ORDER BY D.OBJ#, D.SAVTIME;

查询索引的历史统计信息的SQL语句如下:

SELECT B.OWNER,
       B.OBJECT_NAME INDEX_NAME,
       TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME,
       TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME,
       D.ROWCNT,
       D.BLEVEL,
       D.LEAFCNT,
       D.DISTKEY,
       D.CLUFAC
  FROM SYS.WRI$_OPTSTAT_IND_HISTORY D, DBA_OBJECTS B
 WHERE D.OBJ# = B.OBJECT_ID
   AND B.OBJECT_NAME IN ('IND_TEST')
 ORDER BY D.OBJ#, D.SAVTIME; 

默认情况下统计信息将被保留31天,可以使用下面的命令修改:

EXECUTE DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (XX);  --xx是保留的天数

注意:这些统计信息在SYSAUX表空间中占有额外的存储开销,所以应该注意并防止统计信息将表空间填满。

SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;--查询统计信息当前保留的天数。

下面的查询返回统计信息已经被删除到的日期(所以只有在这日期之后的统计信息才可能被恢复)。任何恢复到比这日期旧的统计信息的请求都会失败:“ORA-20006: Unable to restore statistics , statistics history not available”:

SELECT TO_CHAR(DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

查询到可以恢复统计信息到某一个比较好的时间之后,可以执行下面的命令进行恢复:

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);--恢复SYSTEM的统计信息

可以通过如下的命令返回2次统计信息的比较结果:

SELECT *
  FROM TABLE(DBMS_STATS.DIFF_TABLE_STATS_IN_HISTORY(OWNNAME => 'TPLHR',
                                                    TABNAME => 'TPLHR_AB',
                                                    TIME1   => TO_TIMESTAMP('2016-09-07 10:24:45','YYYY-MM-DD HH24:MI:SS'),--SYSTIMESTAMP
                                                    TIME2   => TO_TIMESTAMP('2016-09-07 10:29:22','YYYY-MM-DD HH24:MI:SS')));

结果如下:

###############################################################################
STATISTICS DIFFERENCE REPORT FOR:
.................................
TABLE         : TPLHR_AB
OWNER         : TPLHR
SOURCE A      : Statistics as of 07-SEP-16 10.24.45.000000 AM +08:00
SOURCE B      : Statistics as of 07-SEP-16 10.29.22.000000 AM +08:00
PCTTHRESHOLD  : 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NO DIFFERENCE IN TABLE / (SUB)PARTITION STATISTICS
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
COLUMN STATISTICS DIFFERENCE:
.............................
COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................
CREDITAMT       A   1143    .000874890 NO   0       3    80    C12A2 3.9E+07
                B   1278    .000782472 NO   0       3    80    C50D3 3.9E+07
CURRBALANCE     A   2478080 .000424268 YES  0       5    3D475 C60F5 5557   
                B   2477312 .000589622 YES  0       5    3D475 C60F5 5508   
DEBITAMT        A   114     .008771929 NO   0       3    80    C1021 3.9E+07
                B   116     .008620689 NO   0       3    80    C1022 3.9E+07
LASTBALANCE     A   2476288 .000424268 YES  0       5    3D475 C60F5 5557   
                B   2476288 .000589622 YES  0       5    3D475 C60F5 5508   
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS
###############################################################################

本文选自《Oracle程序员面试笔试宝典》,作者:小麦苗

本文分享自微信公众号 - DB宝(xiaomaimiaolhr)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-09-18

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券