统计信息中的Pending Statistics

Oracle中的统计信息相信大家都不陌生，统计信息中还有Pending Statistics的概念。统计信息的准确性对于cbo评估sql的各种可能的执行路径的cost非常重要，当统计信息不准时，很可能cbo选择了不佳的执行计划，此时需要收集统计信息。或者当进行sql优化时，怀疑是统计信息不准导致的问题时，需要收集统计信息。但生产环境下统计信息的收集也是有风险的，其中，就有可能当收集了统计信息后执行计划反而变的更差，这也是有可能的，这时，就可以利用Pending Statistics。

默认的，当完成收集统计信息后，统计信息会存储到数据字典表中。可以使用SET_TABLE_PREFS过程对表将PUBLISH选项设置为false，新收集的统计信息就会存储到系统的一块私有区域，这样的统计信息称为pending statistics，当参数optimizer_use_pending_statistics为true时cbo才会使用私有区域中的统计信息，默认为false即不使用，此参数可以在会话级或系统级设置。因此，可以在会话级别使用pending statistics来验证新收集的统计信息对sql执行计划的影响，还不会使数据库系统受到影响。

实验步骤如下：
1.复制dba_objects创建t1表，同时创建索引并收集统计信息

SQL> conn mdd/mdd
已连接。
SQL> create table t1 as select * from dba_objects;
表已创建。
SQL> create index ind_object_id on t1(object_id);
索引已创建。
SQL> exec dbms_stats.gather_table_stats('mdd','t1',cascade=>true);
PL/SQL 过程已成功完成。
复制

2.查看统计信息

SQL> select table_name object_name,last_analyzed from dba_tab_statistics where owner='MDD' and table_name='T1' union all
     select index_name object_name,last_analyzed from dba_ind_statistics where owner='MDD' and table_name='T1';  

OBJECT_NAME		       LAST_ANALYZED
------------------------------ -------------------
T1			       2021-07-12 11:14:37
IND_OBJECT_ID		       2021-07-12 11:14:37

SQL> select table_name object_name,last_analyzed from dba_tab_pending_stats where owner='MDD' and table_name='T1' union all
     select index_name object_name,last_analyzed from dba_ind_pending_stats where owner='MDD' and table_name='T1';

未选定行
复制

3.查看sql的执行计划，执行计划没有问题

SQL> select * from t1 where object_id=5;

执行计划
----------------------------------------------------------
Plan hash value: 1662447412

---------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		    |	  1 |	 98 |	  2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1	    |	  1 |	 98 |	  2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	    | IND_OBJECT_ID |	  1 |	    |	  1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=5)

统计信息
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	  4  consistent gets
	  0  physical reads
	  0  redo size
       1615  bytes sent via SQL*Net to client
	520  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed
复制

4.模拟表中数据变化，统计信息变得不准、陈旧

SQL> update t1 set object_id=5 where rownum<=86200;

已更新86200行。

SQL> commit;

提交完成。
复制

5.再次查看sql的执行计划，consistent gets为12915

SQL> select * from t1 where object_id=5;

已选择86200行。

执行计划
----------------------------------------------------------
Plan hash value: 1662447412

---------------------------------------------------------------------------------------------
| Id  | Operation		    | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	    |		    |	  1 |	 98 |	  2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1	    |	  1 |	 98 |	  2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN	    | IND_OBJECT_ID |	  1 |	    |	  1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=5)

统计信息
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
      12915  consistent gets
	  0  physical reads
	  0  redo size
    9747648  bytes sent via SQL*Net to client
      63726  bytes received via SQL*Net from client
       5748  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
      86200  rows processed
复制

6.在再次收集统计信息之前，先使用pengding statistics做验证。使用set_table_prefs过程将t1表的publish选项设置为false，并查看验证

SQL> exec dbms_stats.set_table_prefs('mdd','t1','publish','false');

PL/SQL 过程已成功完成。

SQL> set autot off
SQL> select dbms_stats.get_prefs('publish','mdd','t1') from dual;

DBMS_STATS.GET_PREFS('PUBLISH','MDD','T1')
-----------------------------------------------
FALSE
SQL> select * from dba_tab_stat_prefs where table_name='T1';

OWNER		TABLE_NAME	PREFERENCE_NAME      PREFERENCE_VALUE
--------------- --------------- -------------------- --------------------
MDD		T1		PUBLISH 	     FALSE
复制

7.收集统计信息，发现原有的统计信息没有受影响，此次收集的统计信息为pending statistics

SQL> exec dbms_stats.gather_table_stats('mdd','t1',cascade=>true);

PL/SQL 过程已成功完成。
SQL> select table_name object_name,last_analyzed from dba_tab_statistics where owner='MDD' and table_name='T1' union all
     select index_name object_name,last_analyzed from dba_ind_statistics where owner='MDD' and table_name='T1';   

OBJECT_NAME		       LAST_ANALYZED
------------------------------ -------------------
T1			       2021-07-12 11:14:37
IND_OBJECT_ID		       2021-07-12 11:14:37

SQL> select table_name object_name,last_analyzed from dba_tab_pending_stats where owner='MDD' and table_name='T1' union all
     select index_name object_name,last_analyzed from dba_ind_pending_stats where owner='MDD' and table_name='T1';   

OBJECT_NAME		       LAST_ANALYZED
------------------------------ -------------------
T1			       2021-07-12 13:43:04
IND_OBJECT_ID		       2021-07-12 13:43:04
复制

8.会话级别设置参数为true，发现sql使用了全表扫描的方式，consistent gets由12915下降到6899，收集统计信息是有效的

SQL> alter session set optimizer_use_pending_statistics=true;
会话已更改。
SQL> select * from t1 where object_id=5;

已选择86200行。

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 | 86172 |  8078K|   344   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T1	 | 86172 |  8078K|   344   (1)| 00:00:05 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=5)

统计信息
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
       6899  consistent gets
	  0  physical reads
	  0  redo size
    4040027  bytes sent via SQL*Net to client
      63726  bytes received via SQL*Net from client
       5748  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
      86200  rows processed
复制

9.可以使用PUBLISH_PENDING_STATS过程，将pending statistics转化为正常的统计信息

SQL> exec DBMS_STATS.PUBLISH_PENDING_STATS('mdd','t1',no_invalidate=>false);

PL/SQL 过程已成功完成。

SQL> select table_name object_name,last_analyzed from dba_tab_statistics where owner='MDD' and table_name='T1' union all
     select index_name object_name,last_analyzed from dba_ind_statistics where owner='MDD' and table_name='T1';  

OBJECT_NAME		       LAST_ANALYZED
------------------------------ -------------------
T1			       2021-07-12 13:43:04
IND_OBJECT_ID		       2021-07-12 13:43:04

SQL> select table_name object_name,last_analyzed from dba_tab_pending_stats where owner='MDD' and table_name='T1' union all
     select index_name object_name,last_analyzed from dba_ind_pending_stats where owner='MDD' and table_name='T1';  

未选定行
复制

10.若发现收集了pending statistics后效果不好，可以使用DELETE_PENDING_STATS过程删除pending statistics

SQL> exec DBMS_STATS.DELETE_PENDING_STATS ('mdd','t1');

PL/SQL 过程已成功完成。
复制

要点总结：
1.使用set_table_prefs过程将表的publish选项设置为false，收集统计信息时表和索引的统计信息都会被收集为pending statistics，可以查看视图dba_tab_pending_stats和dba_ind_pending_stats

2.可以使用dbms_stats.get_prefs或查询dba_tab_stat_prefs来查看相关选项设置，当要查看多个选项或多张表时，查询视图dba_tab_stat_prefs更方便

3.使用PUBLISH_PENDING_STATS过程，将pending statistics转化为正常的统计信息，其LAST_ANALYZED显示的是pending statistics收集的时间，而不是执行PUBLISH_PENDING_STATS过程时的时间