【DB笔试面试643】在Oracle中,如何查询表和索引的历史统计信息?
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了【DB笔试面试643】在Oracle中,如何查询表和索引的历史统计信息?,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4770字,纯文字阅读大概需要7分钟。
内容图文
在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语句如下:
1SELECT B.OWNER, 2 B.OBJECT_NAME TABLE_NAME, 3 TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME, 4 TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME, 5 D.ROWCNT 6 FROM SYS.WRI$_OPTSTAT_TAB_HISTORY D, DBA_OBJECTS B 7 WHERE D.OBJ# = B.OBJECT_ID 8 AND B.OBJECT_NAME IN 9 ('TEST_STAT', 'TPCCBOKBAL_TMP', 'TPCCBOKBALJN', 'PK_TPCCBOKBAL') 10 ORDER BY D.OBJ#, D.SAVTIME;? ? ?
查询索引的历史统计信息的SQL语句如下:
1SELECT B.OWNER, 2 B.OBJECT_NAME INDEX_NAME, 3 TO_CHAR(D.ANALYZETIME, 'YYYY-MM-DD HH24:MI:SS') LAST_ANALYZETIME, 4 TO_CHAR(D.SAVTIME, 'YYYY-MM-DD HH24:MI:SS') CURR_ANALYZETIME, 5 D.ROWCNT, 6 D.BLEVEL, 7 D.LEAFCNT, 8 D.DISTKEY, 9 D.CLUFAC 10 FROM SYS.WRI$_OPTSTAT_IND_HISTORY D, DBA_OBJECTS B 11 WHERE D.OBJ# = B.OBJECT_ID 12 AND B.OBJECT_NAME IN ('IND_TEST') 13 ORDER BY D.OBJ#, D.SAVTIME;? ? ?
默认情况下统计信息将被保留31天,可以使用下面的命令修改:
1EXECUTE DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (XX); --xx是保留的天数? ? ?
注意:这些统计信息在SYSAUX表空间中占有额外的存储开销,所以应该注意并防止统计信息将表空间填满。
1SELECT DBMS_STATS.GET_STATS_HISTORY_RETENTION FROM DUAL;--查询统计信息当前保留的天数。? ? ?
下面的查询返回统计信息已经被删除到的日期(所以只有在这日期之后的统计信息才可能被恢复)。任何恢复到比这日期旧的统计信息的请求都会失败:“ORA-20006: Unable to restore statistics , statistics history not available”:
1SELECT TO_CHAR(DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;? ? ?
查询到可以恢复统计信息到某一个比较好的时间之后,可以执行下面的命令进行恢复:
1EXECUTE DBMS_STATS.RESTORE_TABLE_STATS ('OWNER','TABLE',DATE);--恢复表的统计信息 2EXECUTE DBMS_STATS.RESTORE_DATABASE_STATS(DATE);--恢复数据库的统计信息 3EXECUTE DBMS_STATS.RESTORE_DICTIONARY_STATS(DATE);--恢复数据字典的统计信息 4EXECUTE DBMS_STATS.RESTORE_FIXED_OBJECTS_STATS(DATE);--恢复固定表的统计信息 5EXECUTE DBMS_STATS.RESTORE_SCHEMA_STATS('OWNER',DATE);--恢复某个用户的统计信息 6EXECUTE DBMS_STATS.RESTORE_SYSTEM_STATS(DATE);--恢复SYSTEM的统计信息? ? ?
可以通过如下的命令返回2次统计信息的比较结果:
1SELECT * 2 FROM TABLE(DBMS_STATS.DIFF_TABLE_STATS_IN_HISTORY(OWNNAME => 'TPLHR', 3 TABNAME => 'TPLHR_AB', 4 TIME1 => TO_TIMESTAMP('2016-09-07 10:24:45','YYYY-MM-DD HH24:MI:SS'),--SYSTIMESTAMP 5 TIME2 => TO_TIMESTAMP('2016-09-07 10:29:22','YYYY-MM-DD HH24:MI:SS')));? ? ?
结果如下:
1############################################################################### 2STATISTICS DIFFERENCE REPORT FOR: 3................................. 4TABLE : TPLHR_AB 5OWNER : TPLHR 6SOURCE A : Statistics as of 07-SEP-16 10.24.45.000000 AM +08:00 7SOURCE B : Statistics as of 07-SEP-16 10.29.22.000000 AM +08:00 8PCTTHRESHOLD : 10 9~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 10NO DIFFERENCE IN TABLE / (SUB)PARTITION STATISTICS 11~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 12COLUMN STATISTICS DIFFERENCE: 13............................. 14COLUMN_NAME SRC NDV DENSITY HIST NULLS LEN MIN MAX SAMPSIZ 15............................................................................... 16CREDITAMT A 1143 .000874890 NO 0 3 80 C12A2 3.9E+07 17 B 1278 .000782472 NO 0 3 80 C50D3 3.9E+07 18CURRBALANCE A 2478080 .000424268 YES 0 5 3D475 C60F5 5557 19 B 2477312 .000589622 YES 0 5 3D475 C60F5 5508 20DEBITAMT A 114 .008771929 NO 0 3 80 C1021 3.9E+07 21 B 116 .008620689 NO 0 3 80 C1022 3.9E+07 22LASTBALANCE A 2476288 .000424268 YES 0 5 3D475 C60F5 5557 23 B 2476288 .000589622 YES 0 5 3D475 C60F5 5508 24~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 25NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS 26###############################################################################? ? ? ??
内容总结
以上是互联网集市为您收集整理的【DB笔试面试643】在Oracle中,如何查询表和索引的历史统计信息?全部内容,希望文章能够帮你解决【DB笔试面试643】在Oracle中,如何查询表和索引的历史统计信息?所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。