12.2统计信息之管理统计信息

除了收集合适的统计信息，提供一个管理这些统计信息的综合架构同样重要。oracle提供了多个方法去做这些工作，包括恢复统计信息到以前的版本，提供从一个系统迁移统计信息到另外一个的选项，甚至你可以手动设置统计信息。这些选项在某些特定情形下都非常有用，但是仍然建议都通过DBMS_STATS包来收集统计信息。

恢复统计信息

当你用DBMS_STATS包来收集统计信息时，原来的统计信息会自动备份存储在字典表里，如果新生成的统计信息有问题则能很容易的通过DBMS_STATS.RESTORE_TABLE_STATS恢复之前的统计信息。视图DBA_TAB_STATS_HISTORY包含多个时间点保存的统计信息。

下面这个例子恢复了表t的统计信息至昨天，并自动使shared pool里所有涉及到T表的游标都失效。因为恢复了昨天的统计信息，所以想立刻想使用昨天的统计信息来影响新的游标。参数NO_INVALIDATE的值决定了表T相关的游标是否要失效。

BEGIN 
    DBMS_STATS.RESTORE_TABLE_STATS(ownname => 'SYS', 
                                   tabname => 'T', 
                                   as_of_timestamp => SYSTIMESTAMP-1,
                                   force => FALSE, 
                                   no_invalidate => FALSE); 
END; 
/

挂起统计信息

默认情况下，当统计信息收集以后，它们将会立刻被写入到数据字典表然后立刻被优化器所使用。从11g开始，oracle支持收集完统计信息以后不立即写入字典表，而是先存储在挂起表中，这样就可以在正式使用之前先进行测试。这些挂起统计信息可以对单个会话进行启用，以一种可控的方式让你在应用这些统计信息之前先进行验证。为了激活挂起统计信息的收集，需要使用DBMS_STATS.SET_*_PREFS其中的过程来改变PUBLISH参数的值从TRUE（默认值）变成FALSE。

1	exec DBMS_STATS.SET_TABLE_PREFS('SYS', 'T', 'PUBLISH', 'FALSE');

跟之前一样正常收集统计信息

1	EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T');

这时收集的统计信息都会储存在USER_*_PENDING_STATS数据字典中，可以指定优化器去使用这些挂起统计信息，通过ALTER SESSION命令去设置初始化参数OPTIMIZER_USE_PENDING_STATS为TRUE然后运行sql。对于sql中那些没有挂起统计信息的表来说，优化器会自动选择它们当前存储在字典表的信息。当你启用了这些挂起统计信息，也可以通过DBMS_STATS.PUBLISH_PENDING_STATS去发布它们，将它们写入正式的数据字典当中。

sys@ORA12C> INSERT INTO T select * from t;                       >>>>==== 手动插入一些数据

55 rows created.

sys@ORA12C> commit;

Commit complete.

sys@ORA12C> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T');

PL/SQL procedure successfully completed.

sys@ORA12C> select table_name,NUM_ROWS ,BLOCKS ,LAST_ANALYZED from user_tables  where table_name='T';

TABLE_NAME             NUM_ROWS     BLOCKS LAST_ANALYZED
-------------------- ---------- ---------- -------------------
T                            55          1 2018-12-13 16:58:16                      >>>>====旧的统计信息

sys@ORA12C> select table_name,NUM_ROWS ,BLOCKS ,LAST_ANALYZED from user_tab_pending_stats  where table_name='T';

TABLE_NAME             NUM_ROWS     BLOCKS LAST_ANALYZED
-------------------- ---------- ---------- -------------------
T                           110          1 2018-12-18 15:13:14                      >>>>===挂起统计信息里显示正确的110行

更新挂起统计信息至正式表

sys@ORA12C> Exec dbms_stats.publish_pending_stats('SYS', 'T');

PL/SQL procedure successfully completed.

sys@ORA12C> select table_name,NUM_ROWS ,BLOCKS ,LAST_ANALYZED from user_tables  where table_name='T';

TABLE_NAME             NUM_ROWS     BLOCKS LAST_ANALYZED
-------------------- ---------- ---------- -------------------
T                           110          1 2018-12-18 15:13:14              >>>>====数据正确

sys@ORA12C> select table_name,NUM_ROWS ,BLOCKS ,LAST_ANALYZED from user_tab_pending_stats  where table_name='T';

no rows selected

如果不想使用新的统计信息，可以直接删掉

1	Exec dbms_stats.delete_pending_stats('SYS', 'T');

导入导出统计信息

统计信息可以从一个库复制到另一个，比如在准生产环境通过从别的库复制过来的统计信息进行性能测试是很有用的。复制的时候是通过DBMS_STATS.EXPORT_*_STATS和DBMS_STATS.IMPORT_*_STATS过程来实现。

在导出统计信息之前，你需要先通过DBMS_STATS.CREATE_STAT_TABLE创建一张表来储存这些信息。当表创建完毕以后，你就可以通过DBMS_STATS.EXPORT_*_STATS过程来将统计信息导出，当这些统计信息打包到表里以后，你就可以通过数据泵的方式将表的数据从生产环境导入到测试环境。当表导入测试数据库以后，就可以通过DBMS_STATS.IMPORT_*_STATS将统计信息导入字典当中。

exec DBMS_STATS.CREATE_STAT_TABLE('SYS','T','USERS');

exec dbms_stats.EXPORT_DATABASE_STATS('T','ST_TEST','SYS');

exec dbms_stats.IMPORT_DATABASE_STATS('T','ST_TEST','SYS');

复制分区统计信息

当处理分区表时，优化器需要同时依赖整表和单独分区的统计信息用于得到一个更优的执行计划。如果sql只需要访问单独的分区，优化器则只采用单独分区的统计信息。如果需要访问多个分区，则优化器会采用全局统计信息。

经常会有这样的场景，分区表增加分区，数据只会插入这个新分区。如果用户在这个分区收集统计信息之前去查询这些数据，那很可能就会得到一个比较差的执行计划。又一个很常见的场景就是传入谓词条件的值超过了列统计信息的最大值和最小值之间的范围，这就是被熟知的超出范围错误。在这种情况下，优化器根据谓词与最大值之间的距离来分配选择性（假设传参超过了最大值），谓词值与最大值或最小值越远，那么值的选择性就越低。

超出范围情况能通过DBMS_STATS.COPY_TABLE_STATS过程来避免，这个过程能将源分区的统计信息复制到新建的统计信息为空的分区中去。同时复制了依赖对象的统计信息：字段、本地索引等。分区字段的最小值和最大值按照如下进行调整：

如果分区类型是HASH则目标分区的最大值和最小值和源分区一致
如果分区类型是LIST并且目标分区不是一个默认分区，则目标分区的最小值设成用来定义分区的LIST值中的最小值，最大值就是LIST值中的最大值
如果分区类型是LIST并且目标分区是一个默认分区，则目标分区的最小值设成源分区的最小值，目标分区的最大值设成源分区的最大值
如果分区类型是RANGE，则目标分区的最小值设成前一个分区的上限值，目标分区的最大值设成定义目标分区的RANGE最大值，而如果RANGE最大值是MAXVALUE时，目标分区的最大值则设成前一个分区的上限值

可以根据给定的scale_factor参数来缩放统计信息（比如块的数量、行数等）。统计信息中像行的平均长度和唯一值数量并未进行调整，而是认为在目标分区中是相同的。

将SALES_Q3_2011范围分区当中的统计信息复制到SALES_Q4_2011，设置缩放因子为2来缩放基础统计信息

1	EXEC DBMS_STATS.COPY_TABLE_STATS('SH','SALES','SALES_Q3_2002','SALES_Q4_2002', 2);

只有在索引分区名称与表分区名称一样时才会复制索引信息（默认值），全局统计信息默认情况下并不会更新。只有在全局统计信息不存在并且通过聚合生成了全局统计信息时，才能通过DBMS_STATS.COPY_TABLE_STATS过程影响全局统计信息。

统计信息比较

一个系统的执行计划于另一个系统不一样的一个重要原因就是优化器统计信息不一样。比如如果数据没有同步，测试环境的统计信息很可能就与生产环境的不一样。为了确定统计信息之间的差异，可以通过DBMS_STATS.DIFF_TABLE_STATS_*函数来比较两个源。A下面的表可以和B下面的表进行比较，同样可以对一个表的不同时间点的统计信息比较，或者当前统计信息与挂起统计信息比较。比如比较当前时间和昨天

sys@ORA12C> select report, maxdiffpct from dbms_stats.diff_table_stats_in_history(user, 'T', SYSDATE-1, SYSDATE, 2);

REPORT
---------------------------------------------------------------------------------------------
MAXDIFFPCT
----------
###############################################################################

STATISTICS DIFFERENCE REPORT FOR:
.................................

TABLE         : T
OWNER         : SYS
SOURCE A      : Statistics as of 18-DEC-18 11.03.39.000000 AM +08:00
SOURCE B      : Statistics as of 19-DEC-18 11.03.39.000000 AM +08:00
PCTTHRESHOLD  : 2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE / (SUB)PARTITION STATISTICS DIFFERENCE:
.............................................

OBJECTNAME                  TYP SRC ROWS       BLOCKS     ROWLEN     SAMPSIZE
...............................................................................

T                           T   A   55         1          3          55
                                B   110        1          3          110
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

COLUMN STATISTICS DIFFERENCE:
.............................

COLUMN_NAME     SRC NDV     DENSITY    HIST NULLS   LEN  MIN   MAX   SAMPSIZ
...............................................................................

ID              A   10      .009090909 YES  0       3    80    C10A  55
                B   10      .004545454 YES  0       3    80    C10A  110
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


NO DIFFERENCE IN INDEX / (SUB)PARTITION STATISTICS
###############################################################################

比较不同库的两张表统计信息其实跟导入导出统计信息类似，也是将其中一个系统的表统计信息导出表然后导入第二个系统进行比较

最后通过dbms_stats.diff_table_stats_in_stattab完成

1	select report from dbms_stat.diff_table_stats_in_stattab( 'SCOTT', 'EMP', 'STAT_TAB_OLD', 'STAT_TAB_NEW');

DIFF函数同时比较依赖对象（索引、分区、列）的统计信息，如果统计信息之间的差异超过了阀值则会列出源端目标端对象的所有的统计信息。这个阀值可以作为入参传入，默认值是10%。计算的时候以源端统计信息作为基数。

锁定统计信息

有些情况下，你希望通过锁定表或者方案的统计信息来避免重新收集时产生影响。当统计信息被锁定后，任何操作都无法修改这些信息除非你重新解锁或则收集统计信息时设置FORCE参数为TRUE。

sys@ORA12C> exec DBMS_STATS.LOCK_TABLE_STATS('SYS', 'T');

PL/SQL procedure successfully completed.

sys@ORA12C> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T');
BEGIN DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 36873
ORA-06512: at "SYS.DBMS_STATS", line 36507
ORA-06512: at "SYS.DBMS_STATS", line 8582
ORA-06512: at "SYS.DBMS_STATS", line 9461
ORA-06512: at "SYS.DBMS_STATS", line 35836
ORA-06512: at "SYS.DBMS_STATS", line 36716
ORA-06512: at line 1


sys@ORA12C> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS', 'T',force=>TRUE);

PL/SQL procedure successfully completed.

也可以锁定分区级别的统计信息

1	EXEC DBMS_STATS.LOCK_PARTITION_STATS('SH', 'SALES', 'SALES_Q3_2000');

需要注意锁定统计信息的层次问题，比如如果锁定了一个分区表统计信息，然后为了单独搜集一个分区的统计信息而将这个分区统计信息进行解锁，则会报错ORA-20005。因为即使分区锁被解锁了，表级锁仍然存在。所以为了统计单独分区只能通过设置FORCE为TRUE。

手动设置统计信息

在罕见的情况下，手动设置数据字典里的统计信息可能会很有帮助。比如全局临时表的统计信息。可以手动通过DBMS_STATS.SET_*_STATS来收集。

Tuning

#12c #statistics

12.2统计信息之管理统计信息

https://www.xbdba.com/2018/12/19/12cr2-optimizer-statistics-manage-statistics/

作者

xbdba

发布于

2018年12月19日

许可协议

MMON进程未自动重启导致awr未生成上一篇

12.2统计信息之分区表下一篇