Oracle统计信息中的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:37IND_OBJECT_ID 2021-07-12 11:14:37SQL> 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 processed4.模拟表中数据变化，统计信息变得不准、陈旧。
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 gets12915 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 processed6.在再次收集统计信息之前，先使用Pengding Statistics做验证。使用set_table_prefs过程将t1表的publish选项设置为false，并查看验证：
SQL> exec dbms_stats.set_table_prefs('mdd','t1','publish','false');
PL/SQL 过程已成功完成。
SQL> set autot offSQL> select dbms_stats.get_prefs('publish','mdd','t1') from dual;DBMS_STATS.GET_PREFS('PUBLISH','MDD','T1')-----------------------------------------------FALSESQL> 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:37IND_OBJECT_ID 2021-07-12 11:14:37SQL> 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:04IND_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 processed9.可以使用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:04IND_OBJECT_ID 2021-07-12 13:43:04SQL> 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过程时的时间。
墨天轮原文链接：https://www.modb.pro/db/81306?sjhy（复制到浏览器或者点击“阅读原文”立即查看）