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: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 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 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 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: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: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 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: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(复制到浏览器或者点击“阅读原文”立即查看)

关于作者
马栋栋,云和恩墨技术顾问,Oracle 11g OCM,中国DBA联盟成员。拥有OCM、OGCA证书,长期服务于金融行业。现负责某银行的数据库优化工作,热衷于故障处理、性能优化等的学习与分享。
END
(0)

相关推荐