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

小麦苗DBA宝典

发布于 2019-09-29 16:23:04

6590

发布于 2019-09-29 16:23:04

文章被收录于专栏：小麦苗的DB宝专栏小麦苗的DB宝专栏

♣

题目部分

在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/