合 在Oracle中，什么是待定的统计信息（Pending Statistic）
在数据库系统运维中，DBA常常希望维持SQL执行计划的稳定。很多DBA和开发人员对于Hint的依赖，很大程度上也是源于在CBO情况下，执行计划对于统计量过于依赖，容易形成不稳定执行计划。所以，SQL语句执行计划的稳定性，就变成统计信息的稳定性问题。更进一步，就是新的统计信息更新，无论是手动收集还是自动收集，能否促进SQL语句生成更高效的执行计划。所以，一种思路是：在新的统计信息收集生成时，暂时不要生效投入执行计划生成。等待最后确认统计信息正确之后，再投入生产环境。
在Oracle 11g中，推出了统计信息管理的一种新技术——待定的统计信息（Pending Statistic）技术。简单的说，DBA可以对一系列的数据表设置PENDING属性。设置PENDING属性之后，数据的统计信息在数据字典中相当于已经锁定。当新的统计信息生成之后，不是直接替换原有的数据，而是存放在PENDING数据字典中。在PENDING字典中的统计信息在默认情况下是不会参与SQL执行计划的生成的。只有在进行SQL测试通过的时候，经过用户手工的确定，才会将其PUBLISH出来，替换原有的统计信息。这样，就给运维DBA提供了一种维持执行计划稳定的思路。通过固定统计信息，将新统计信息以PENDING的方式将原有的统计信息固定，从而稳定执行计划。
可以使用如下的SQL语句查询统计信息在全局、SCHEMA和表级别是否自动发布（默认情况下都是自动发布）：
1 2 | SELECT DBMS_STATS.GET_PREFS('PUBLISH') GLOBAL,DBMS_STATS.GET_PREFS('PUBLISH','LHR') SCHEMA,DBMS_STATS.GET_PREFS('PUBLISH','LHR','T') TB_LEVEL FROM DUAL; |
以上SQL语句的查询结果返回TRUE或FALSE。TRUE表明收集统计信息完成后自动发布，而FALSE表明收集统计信息完成后将待定。可以使用下面的包来改变各个级别的默认PUBLISH选项:
- 全局：
EXEC DBMS_STATS.SET_GLOBAL_PREFS(PNAME=>'PUBLISH',PVALUE=>'FALSE'); - SCHEMA：
EXEC DBMS_STATS.SET_SCHEMA_PREFS(OWNNAME=>USER,PNAME=>'PUBLISH',PVALUE=>'TRUE'); - 表：
EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'T_LHR','PUBLISH','FALSE');
缺省情况下，优化器使用数据字典视图中已发布的统计信息。如果希望优化器使用新收集的待定统计信息，那么可以设置初始化参数OPTIMIZER_USE_PENDING_STATISTICS的值为TRUE（缺省值为FALSE）。可以使用下面的SQL语句为一个特定的数据对象发布待定统计信息：
1 | EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SH','CUSTOMERS'); |
如果不想发布待定的统计信息，那么可以执行下面的语句删除这些待定的统计信息：
1 | EXEC DBMS_STATS.DELETE_PENDING_STATS('SH','CUSTOMERS'); |
可以通过视图DBA_TAB_STATISTICS和DBA_IND_STATISTICS查询发布的统计信息，通过视图DBA_TAB_PENDING_STATS和DBA_IND_PENDING_STATS查询待定的统计信息。可以使用存储过程DBMS_STATS.EXPORT_PENDING_STATS导出待定的统计信息。如果已经发布了统计信息，想要恢复以前的统计信息，那么可以根据DBA_TAB_STATS_HISTORY中的STATS_UPDATE_TIME来确定TIMESTAMP，执行下面的操作，最后一个参数AS_OF_TIMESTAMP指的是恢复在这个时间点生效的统计信息，所以可以多1秒：
1 2 3 4 5 6 7 8 | LHR@orclasm > SELECT H.TABLE_NAME, TO_CHAR(H.STATS_UPDATE_TIME, 'YYYY-MM-DD HH24:MI:SS') STATS_UPDATE_TIME FROM USER_TAB_STATS_HISTORY H WHERE H.TABLE_NAME = 'T_PS_20170605_LHR'; TABLE_NAME STATS_UPDATE_TIME ------------------------------ ------------------- T_PS_20170605_LHR 2017-06-05 15:54:16 T_PS_20170605_LHR 2017-06-05 16:17:29 LHR@orclasm > EXEC DBMS_STATS.RESTORE_TABLE_STATS(OWNNAME => USER,TABNAME =>'T_PS_20170605_LHR',AS_OF_TIMESTAMP => TO_DATE('2017-06-05 15:54:17','YYYY-MM-DD HH24:MI:SS'));--恢复以前的统计信息 PL/SQL procedure successfully completed. |
示例
下面给出一个使用Pending Statistic的完整示例：
1 2 3 | CREATE TABLE T_PS_20170605_LHR AS SELECT LEVEL ID, 'name' || LEVEL NAME FROM DUAL CONNECT BY LEVEL<= 10000 ; CREATE INDEX IDX_T_PS_20170605_LHR_ID ON T_PS_20170605_LHR(ID) ; EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_PS_20170605_LHR') ; |
查询一下历史统计信息：
1 2 3 4 5 | LHR@orclasm > SELECT H.TABLE_NAME, TO_CHAR(H.STATS_UPDATE_TIME, 'YYYY-MM-DD HH24:MI:SS') STATS_UPDATE_TIME FROM USER_TAB_STATS_HISTORY H WHERE H.TABLE_NAME = 'T_PS_20170605_LHR'; TABLE_NAME STATS_UPDATE_TIME ------------------------------ ------------------- T_PS_20170605_LHR 2017-06-05 15:54:16 |
进行简单查询：
