【DB笔试面试633】在Oracle中，什么是待定的统计信息（Pending Statistic）？

• 2019 年 10 月 10 日
• 筆記

题目部分

在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和表级别是否自动发布（默认情况下都是自动发布）：

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选项:

l 全局：EXEC DBMS_STATS.SET_GLOBAL_PREFS(PNAME=>'PUBLISH',PVALUE=>'FALSE');

l SCHEMA：EXEC DBMS_STATS.SET_SCHEMA_PREFS(OWNNAME=>USER,PNAME=>'PUBLISH',PVALUE=>'TRUE');

l 表：EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'T_LHR','PUBLISH','FALSE');

缺省情况下，优化器使用数据字典视图中已发布的统计信息。如果希望优化器使用新收集的待定统计信息，那么可以设置初始化参数OPTIMIZER_USE_PENDING_STATISTICS的值为TRUE（缺省值为FALSE）。可以使用下面的SQL语句为一个特定的数据对象发布待定统计信息：

EXEC DBMS_STATS.PUBLISH_PENDING_STATS('SH','CUSTOMERS');  

如果不想发布待定的统计信息，那么可以执行下面的语句删除这些待定的统计信息：

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秒：

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的完整示例：

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') ;  

查询一下历史统计信息：

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  

进行简单查询：

LHR@orclasm > SET AUTOT ON  LHR@orclasm > SELECT P.ID,P.NAME FROM T_PS_20170605_LHR P WHERE ID=1 ;            ID NAME  ---------- --------------------------------------------           1 name1      Execution Plan  ----------------------------------------------------------  Plan hash value: 2892875560    --------------------------------------------------------------------------------------------------------  | Id  | Operation                   | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |  --------------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |                          |     1 |    13 |     2   (0)| 00:00:01 |  |   1 |  TABLE ACCESS BY INDEX ROWID| T_PS_20170605_LHR        |     1 |    13 |     2   (0)| 00:00:01 |  |*  2 |   INDEX RANGE SCAN          | IDX_T_PS_20170605_LHR_ID |     1 |       |     1   (0)| 00:00:01 |  --------------------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("ID"=1)      Statistics  ----------------------------------------------------------            1  recursive calls            0  db block gets            4  consistent gets            0  physical reads            0  redo size          596  bytes sent via SQL*Net to client          519  bytes received via SQL*Net from client            2  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)            1  rows processed    LHR@orclasm >  

设定表的PUBLISH选项为FALSE：

LHR@orclasm > EXEC DBMS_STATS.SET_TABLE_PREFS(USER,'T_PS_20170605_LHR', 'PUBLISH', 'FALSE');    PL/SQL procedure successfully completed.    LHR@orclasm > SELECT DBMS_STATS.GET_PREFS('PUBLISH',USER,'T_PS_20170605_LHR') FROM DUAL ;    DBMS_STATS.GET_PREFS('PUBLISH',USER,'T_PS_20170605_LHR')  ---------------------------------------------------------------  FALSE  

再次向表中插入2W行ID列都为1的数据：

INSERT INTO T_PS_20170605_LHR(ID,NAME) SELECT 1, 'name' || LEVEL FROM DUAL CONNECT BY LEVEL<= 20000 ;  COMMIT ;  

再次收集一下统计信息，这个时候收集的统计信息不会立刻被优化器使用：

LHR@orclasm > EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T_PS_20170605_LHR') ;    PL/SQL procedure successfully completed.    LHR@orclasm > SET AUTOT TRACEONLY    LHR@orclasm > SELECT P.ID,P.NAME FROM T_PS_20170605_LHR P WHERE ID=1 ;      20001 rows selected.      Execution Plan  ----------------------------------------------------------  Plan hash value: 2892875560    --------------------------------------------------------------------------------------------------------  | Id  | Operation                   | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |  --------------------------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT            |                          |     1 |    13 |     2   (0)| 00:00:01 |  |   1 |  TABLE ACCESS BY INDEX ROWID| T_PS_20170605_LHR        |     1 |    13 |     2   (0)| 00:00:01 |  |*  2 |   INDEX RANGE SCAN          | IDX_T_PS_20170605_LHR_ID |     1 |       |     1   (0)| 00:00:01 |  --------------------------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       2 - access("ID"=1)      Statistics  ----------------------------------------------------------            0  recursive calls            0  db block gets         2778  consistent gets            0  physical reads            0  redo size       597478  bytes sent via SQL*Net to client        15182  bytes received via SQL*Net from client         1335  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)        20001  rows processed  

如所料，这里还是使用旧的统计信息，依旧使用INDEX RANGE SCAN代价比较高。看一下统计信息的情况，已经发布的统计信息还是比较老的，而如下所示PENDING表里面的统计信息表示新收集的待定的统计信息：

LHR@orclasm > SELECT 'publish' AS STAT,T.NUM_ROWS,T.BLOCKS,TO_CHAR(T.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS')  FROM USER_TAB_STATISTICS T  WHERE TABLE_NAME='T_PS_20170605_LHR'    2  UNION  ALL    3  SELECT 'pending' AS STAT,S.NUM_ROWS,S.BLOCKS,TO_CHAR(S.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') FROM USER_TAB_PENDING_STATS S WHERE TABLE_NAME='T_PS_20170605_LHR';    STAT      NUM_ROWS     BLOCKS TO_CHAR(T.LAST_ANAL  ------- ---------- ---------- -------------------  publish      10000         29 2017-06-05 15:54:16  pending      30000         84 2017-06-05 16:07:39  

下面来验证一下新的统计信息是否有助于改善SQL语句的执行：

LHR@orclasm > ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = TRUE;    Session altered.    LHR@orclasm > SET AUTOT TRACEONLY  LHR@orclasm > SELECT P.ID,P.NAME FROM T_PS_20170605_LHR P WHERE ID=1 ;    20001 rows selected.      Execution Plan  ----------------------------------------------------------  Plan hash value: 4079616360    ---------------------------------------------------------------------------------------  | Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |  ---------------------------------------------------------------------------------------  |   0 | SELECT STATEMENT  |                   | 19488 |   228K|    25   (0)| 00:00:01 |  |*  1 |  TABLE ACCESS FULL| T_PS_20170605_LHR | 19488 |   228K|    25   (0)| 00:00:01 |  ---------------------------------------------------------------------------------------    Predicate Information (identified by operation id):  ---------------------------------------------------       1 - filter("ID"=1)      Statistics  ----------------------------------------------------------            0  recursive calls            0  db block gets         1414  consistent gets            0  physical reads            0  redo size       533474  bytes sent via SQL*Net to client        15182  bytes received via SQL*Net from client         1335  SQL*Net roundtrips to/from client            0  sorts (memory)            0  sorts (disk)        20001  rows processed  

可以看到，使用优化器使用待定的统计信息生成的查询计划使用的是全表扫描，更加有效率验证结束，无误，可以发布新的统计信息了：

LHR@orclasm > EXEC DBMS_STATS.PUBLISH_PENDING_STATS(USER,'T_PS_20170605_LHR');    PL/SQL procedure successfully completed.    LHR@orclasm > ALTER SESSION SET OPTIMIZER_USE_PENDING_STATISTICS = FALSE;    Session altered.    LHR@orclasm >  LHR@orclasm > set autot off  LHR@orclasm > SELECT 'publish' AS STAT,T.NUM_ROWS,T.BLOCKS,TO_CHAR(T.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS')  FROM USER_TAB_STATISTICS T  WHERE TABLE_NAME='T_PS_20170605_LHR'    2  UNION  ALL    3  SELECT 'pending' AS STAT,S.NUM_ROWS,S.BLOCKS,TO_CHAR(S.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') FROM USER_TAB_PENDING_STATS S WHERE TABLE_NAME='T_PS_20170605_LHR';    STAT      NUM_ROWS     BLOCKS TO_CHAR(T.LAST_ANAL  ------- ---------- ---------- -------------------  publish      30000         84 2017-06-05 16:07:39    LHR@orclasm >  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  

可以看到PENDING的统计信息已经发布并且从USER_TAB_PENDING_STATS中删除，USER_TAB_STATISTICS表中的LAST_ANALYZED时间显示的是统计信息收集的时间。如果已经发布了统计信息，想要恢复从前的统计信息，可以根据USER_TAB_STATS_HISTORY中的STATS_UPDATE_TIME来确定TIMESTAMP，执行下面的操作，最后一个参数AS_OF_TIMESTAMP指的是恢复在这个时间点生效的统计信息吗，所以可以多1秒：

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.    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  T_PS_20170605_LHR              2017-06-05 16:22:20    LHR@orclasm > SELECT 'publish' AS STAT,T.NUM_ROWS,T.BLOCKS,TO_CHAR(T.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS')  FROM USER_TAB_STATISTICS T  WHERE TABLE_NAME='T_PS_20170605_LHR'    2  UNION  ALL    3  SELECT 'pending' AS STAT,S.NUM_ROWS,S.BLOCKS,TO_CHAR(S.LAST_ANALYZED,'YYYY-MM-DD HH24:MI:SS') FROM USER_TAB_PENDING_STATS S WHERE TABLE_NAME='T_PS_20170605_LHR';    STAT      NUM_ROWS     BLOCKS TO_CHAR(T.LAST_ANAL  ------- ---------- ---------- -------------------  publish      10000         29 2017-06-05 15:54:16  

& 说明：

有关待定的统计信息的更多内容可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140300/

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