一文搞懂 Oracle 统计信息

前 言

以下四个地址是我个人博客地址，一般情况下文章首发于墨天轮或者微信公众号，然后同步至 CSDN 和 腾讯云，其他网站均属于盗版链接，感兴趣的朋友可关注我的公众号或者墨天轮地址，第一时间获取最新文章信息。

————————————————————————————
微信公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————

对象统计信息，是优化器引擎生成执行计划的必要输入，过时或错误统计信息很可能导致一个错误的执行计划。为所有缺失或过时统计信息的对象自动收集，默认表的数据修改达到 10% 就是候选对象。保持最新的统计信息非常重要，当统计信息已经过时，需要重新收集。DBA_TAB_STATISTICS 视图 STALE_STATS 列代表统计信息是否陈旧，值为 YES 表示统计信息是陈旧的，需要及时收集，值为 NO 表示统计信息是正常的，不需要重新收集。从 12.2 开始您不再需要使用 DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO 确保视图元数据是当前的。DBA_TAB_STATISTICS、DBA_IND_STATISTICS 和 DBA_TAB_MODIFICATIONS 视图中显示的统计信息是从磁盘和内存中获得的。Oracle 对于表的 DML 变化，记录在视图DBA_TAB_MODIFICATIONS 中，当 DML 累计达到表格行数的 10% 时，就认为统计信息是陈旧的，需要及时收集。

select table_owner, table_name, inserts, updates, deletes, timestamp
  from dba_tab_modifications
 where table_owner = 'JIEKEXU'
   and table_name = 'JIEKE';

select owner, table_name,object_type,stale_stats,last_analyzed 
from dba_tab_statistics
where owner = 'SCOTT' and table_name = 'T_STATS';

下图是近期汇总网上资料整理的 Oracle 统计信息脑图笔记，图片可能不太清晰，感兴趣的可在公众号后台回复关键字【Oracle统计信息】下载查看。

DBA_TAB_MODIFICATIONS 视图：默认监控表上自前一次分析之后发生的 DML 及 TRUNCATE 操作，并将这些操作数量记录到数据字典基表 MON_MODS$ 中，我们平时查询的 DBA_TAB_MODIFICATIONS 视图数据实际上来源于 MON_MODS_ALL$ 基表，SMON 定期会将 MON_MODS$ 中符合要求的数据融合（MERGE）到 MON_MODS_ALL$ 中。以上操作中，SMON 会定期触发刷新数据，用户也可以自行发起。同样，Oracle 也提供了 DBMS_STATS 中的FLUSH_DATABASE_MONITORING_INFO 存储过程包，以实现手动触发的效果。

exec dbms_stats.flush_database_monitoring_info;

1、为什么需要统计信息

统计信息对于优化器生成一个好的执行计划来说是必要的。
• 优化器为了产生最佳的执行计划，依赖于对象统计信息
• 保持最新的统计信息非常重要
• 当统计信息已经过时，需要刷新

对象统计信息，是优化器引擎生成执行计划的必要输入，过时或错误统计信息很可能导致一个错误的执行计划。

统计信息包含哪些信息？

1.1、统计信息自动收集窗口

周一到周五是晚上 22 点开始到第二日凌晨 2 点结束，共计 4 小时。
周六周日早上 6 点开始，到第二日凌晨 2 点结束，持续 20 个小时。

set line 234
set pagesize 120
col WINDOW_NEXT_TIME for a34 
col WINDOW_NAME for a16 
select WINDOW_NAME,WINDOW_NEXT_TIME,WINDOW_ACTIVE,OPTIMIZER_STATS from DBA_AUTOTASK_WINDOW_CLIENTS order by WINDOW_NEXT_TIME;

WINDOW_NAME      WINDOW_NEXT_TIME                   WINDO OPTIMIZE
---------------- ---------------------------------- ----- --------
THURSDAY_WINDOW  25-JAN-24 10.00.00.000000 PM PRC   FALSE ENABLED
FRIDAY_WINDOW    26-JAN-24 10.00.00.000000 PM PRC   FALSE ENABLED
SATURDAY_WINDOW  27-JAN-24 06.00.00.000000 AM PRC   FALSE ENABLED
SUNDAY_WINDOW    28-JAN-24 06.00.00.000000 AM PRC   FALSE ENABLED
MONDAY_WINDOW    29-JAN-24 10.00.00.000000 PM PRC   FALSE ENABLED
TUESDAY_WINDOW   30-JAN-24 10.00.00.000000 PM PRC   FALSE ENABLED
WEDNESDAY_WINDOW 31-JAN-24 10.00.00.000000 PM PRC   FALSE ENABLED

7 rows selected.

--如下示例，将周五时间窗口时间到晚间 23 点 30 分
BEGIN
    DBMS_SCHEDULER.SET_ATTRIBUTE (
        name        =>'"SYS"."SATURDAY_WINDOW"',
        attribute   => 'REPEAT_INTERVAL',
		VALUE       =>'FREQ=WEEKLY;BYDAY=FRI;BYHOUR=23;BYMINUTE=30;BYSECOND=0');
    END;
/

2、 手动收集统计信息

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_STATS.html#GUID-CA6A56B9-0540-45E9-B1D7-D78769B7714C

调整默认采集参数
可以使用 SET 命令进行调整：
DBMS_STATS.SET_GLOBAL_PREFS/SET_TABLE_PREFS/SET_SCHEMA_PREFS/SET_DATABASE_PREFS

设置完成后，下次采集生效。

2.1 常用采集命令

使用 dbms_stats 包
• gather_table_stats 收集单表统计信息
• gather_schema_stats 收集用户统计信息

例子：exec dbms_stat.gather_table_stats(user, ‘TABLE_NAME’, degree=>16);

采样比例通过参数 ESTIMATE_PRECENT 来控制，模式设置是 AUTO_SAMPLE_SIZE。建议收集统计信息使用默认 AUTO_SAMPLE_SIZE，对于 Oracle 12c 如果要生成 HYBIRD 或 TOP Frequency，必须使用 AUTO_SAMPLE_SIZE 进行采样。

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                tabname          => 'T_STATS',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size auto',
                                no_invalidate    => FALSE,
                                degree           => 8,
                                cascade          => TRUE);
END;
/

exec dbms_stats.gather_table_stats(user, 'TABLE_NAME', degree=>16);
exec dbms_stats.gather_index_stats(user, 'INDEX_NAME', degree=>8);
exec dbms_stats.gather_schema_stats('SCOTT');

no_invalidate 表示共享池中涉及到该表的游标是否立即失效，默认值为 DBMS_STATS.AUTO_INVALIDATE，表示让 Oracle 自己决定是否立即失效。建议将 no_invalidate 参数设置为 FALSE，立即失效。因为发现有时候 SQL 执行缓慢是因为统计信息过期导致，重新收集了统计信息之后执行计划还是没有更改，原因就在于没有将这个参数设置为 false。

degree 表示收集统计信息的并行度，默认为 NULL。如果表没有设置 degree，收集统计信息的时候后就不开并行；如果表设置了degree，收集统计信息的时候就按照表的 degree 来开并行。可以查询 DBA_TABLES.degree 来查看表的 degree，一般情况下，表的 degree 都为 1。我们建议可以根据当时系统的负载、系统中 CPU 的个数以及表大小来综合判断设置并行度。

cascade表示在收集表的统计信息的时候，是否级联收集索引的统计信息，默认值为DBMS_STATS.AUTO_CASCADE，表示让Oracle自己判断是否级联收集索引的统计信息。我们一般将其设置为TRUE，在收集表的统计信息的时候，级联收集索引的统计信息。

2.2 METHOD_OPT 详解

默认是 FOR ALL COLUMNS SIZE AUTO
 FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
 FOR COLUMNS [column_clause] [size_clause]
size_clause 定义为size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}
column_clause定义为column_clause := column_name | extension name | extension
• - integer：直方图桶的数量。必须在 [1，2048] 范围内。
• - REPEAT：仅在已有直方图的列上收集直方图
• - AUTO：Oracle 根据数据分布和列的工作负载确定要在其上收集直方图的列。
• - SKEWONLY：Oracle 根据列的数据分布确定要在其上收集直方图的列。
• - column_name：列的名称
• - extension : 可以是 （，[， …]） 格式的列组或表达式column_name Colume_name
默认值为 FOR ALL COLUMNS SIZE AUTO。可以使用 SET_DATABASE_PREFS 过程、SET_GLOBAL_PREFS 过程 SET_SCHEMA_PREFS 过程 和SET_TABLE_PREFS 过程更改默认值。

• 列上面是否创建直方图由列的使用情况决定(COL_USAGE$)

• 全局禁用直方图

dbms_stats.set_global_prefs( method_opt => 'FOR ALL COLUMNS SIZE 1’ );

• 给指定的某些列创建直方图

 method_opt => 'FOR ALL COLUMNS SIZE 1, FOR COLUMNS SIZE 254 CUST_ID PROD_ID');

• 给指定的某些列禁用直方图

 dbms_stats.set_table_prefs('SH','SALES','METHOD_OPT','FOR ALL COLUMNS SIZE AUTO,FOR COLUMNS SIZE 1 PROD_ID');

• 更新直方图统计信息
METHOD_OPT=>’FOR ALL COLUMNS REPEAT’
• 备注: 不会修改桶的数量

使用非默认的 METHOD_OPT…

• 不要使用 ‘FOR ALL INDEX COLUMNS SIZE 254’
  • 收集统计信息时间变长
  • 统计信息占用空间更多
  • 不收集非索引列直方图
• 不要使用 ‘FOR ALL COLUMNS SIZE 254’
  • 收集统计信息时间变长
  • 统计信息占用空间更多

两种类型的扩展统计信息

扩展统计信息 – Column Group

• Column groups 统计
  • 当 SQL 语句中单个表的多个列被 where 条件同时引用时候，Column groups 统计非常有用

列之间的相关性会导致错误的估计 Cardinality

使用 column group 统计提升 Cardinality 估算的准确度.

eg:
dbms_stats.create_extend_stats(NULL, ‘TAB1’,‘(COl1,COL2)’);
dbms.gather_table_stats(NULL, ‘TAB1’);

dbms_stats.create_extend_stats(NULL, ‘CALENDAR’,‘(MONTH_NAME,STAR_SIGN)’);
dbms.gather_tabe_stats(NULL, ‘CALENDAR’);

• Expression 统计信息
  • Expression statistics 当表列在 where 条件中以复杂表达式出现时候，表达式统计信息非常有用

其他 dbms_stats 详细介绍：

GATHER_DATABASE_STATS

此过程收集数据库中所有对象的统计信息。要运行此过程，您需要 SYSDBA 角色，或者同时具有 ANALYZE ANY DICTIONARY 和 ANALYZE ANY 系统权限。

DBMS_STATS.GATHER_DATABASE_STATS (
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                (get_param('ESTIMATE_PERCENT')), 
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   options          VARCHAR2 DEFAULT 'GATHER',
   statown          VARCHAR2 DEFAULT NULL,
   gather_sys       BOOLEAN  DEFAULT TRUE,
   no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type (
                                     get_param('NO_INVALIDATE')),
   obj_filter_list ObjectTab DEFAULT NULL);

GATHER_DICTIONARY_STATS

这个过程收集字典模式 SYS、SYSTEM 和 RDBMS 组件模式的统计信息。
要运行此过程，您需要 SYSDBA 角色，或者同时具有 ANALYZE ANY DICTIONARY 和 ANALYZE ANY 系统权限

GATHER_FIXED_OBJECTS_STATS

此过程收集所有固定对象(动态性能表)的统计信息。要运行此过程，您必须具有 SYSDBA 或 ANALYZE ANY DICTIONARY 系统权限才能执行此过程。

GATHER_INDEX_STATS

这个过程收集索引统计信息。它试图并行化尽可能多的工作。
限制在各个参数中描述。该操作不会与某些类型的索引并行，包括集群索引、域索引和位图连接索引。granularity 和 no_invalidate 参数与这些类型的索引无关。

Granularity：要收集的统计信息的粒度(仅在表已分区时相关)。
no_invalidate：如果设置为 TRUE，则不会使依赖游标无效。如果设置为 FALSE，该过程将立即使依赖游标失效。使用 DBMS_STATS.AUTO_INVALIDATE。让 Oracle 决定何时使依赖游标失效，这是默认设置。

GATHER_PROCESSING_RATE

此过程开始收集处理速率的工作，处理速率在以分钟为单位定义的间隔后结束。

GATHER_SCHEMA_STATS

这个过程收集 schema 中所有对象的统计信息。

要调用此过程，您必须是表的所有者，或者需要 ANALYZE ANY 特权。对于 SYS 拥有的对象，您必须是表的所有者，或者您需要 ANALYZE ANY DICTIONARY 特权或 SYSDBA 特权。

下面的示例指定如果表 SH.SALES 和 SH.cost 过时，将收集它们的统计信息。

DECLARE
   filter_lst  DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
BEGIN
   filter_lst.extend(2);
   filter_lst(1).ownname := 'SH';
   filter_lst(1).objname := 'SALES';
   filter_lst(2).ownname := 'SH';
   filter_lst(2).objname := 'COSTS';
   DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'SH',obj_filter_list=>filter_lst);
 END;

GATHER_SYSTEM_STATS

此过程收集系统统计信息。要运行此过程，您必须拥有 GATHER_SYSTEM_STATISTICS 角色。

假设您希望在白天执行数据库应用程序处理 OLTP 事务，在晚上运行报表。
如果需要收集系统白天的统计信息，则需要收集 720 分钟的统计信息。将统计信息存储在 MYSTATS 表中。

BEGIN
   DBMS_STATS.GATHER_SYSTEM_STATS (
      interval => 720,
      stattab  => 'mystats',
      statid   => 'OLTP');
END;

如果需要收集系统夜间统计信息，则需要收集 720 分钟的统计信息。将统计信息存储在 MYSTATS 表中。

BEGIN
   DBMS_STATS.GATHER_SYSTEM_STATS (
      interval => 720,
      stattab  => 'mystats',
      statid   => 'OLAP');
END;

GATHER_TABLE_STATS

此过程收集表、列和索引统计信息。它试图并行化尽可能多的工作，但是有一些限制，这些限制在单个参数中描述。

要调用此过程，您必须是表的所有者，或者需要 ANALYZE ANY 特权。对于 SYS 拥有的对象，您要么需要是表的所有者，要么需要 ANALYZE ANY DICTIONARY 特权或 SYSDBA 特权。除集群索引、域索引和连接索引外，索引统计信息收集可以并行化。

DBMS_STATS.GATHER_TABLE_STATS (
   ownname          VARCHAR2, 
   tabname          VARCHAR2, 
   partname         VARCHAR2 DEFAULT NULL,
   estimate_percent NUMBER   DEFAULT to_estimate_percent_type 
                                                  (get_param('ESTIMATE_PERCENT')),
   block_sample     BOOLEAN  DEFAULT FALSE,
   method_opt       VARCHAR2 DEFAULT get_param('METHOD_OPT'),
   degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
   granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'), 
   cascade          BOOLEAN  DEFAULT to_cascade_type(get_param('CASCADE')),
   stattab          VARCHAR2 DEFAULT NULL, 
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL,
   no_invalidate    BOOLEAN  DEFAULT  to_no_invalidate_type (
                                                      get_param('NO_INVALIDATE')),
   stattype         VARCHAR2 DEFAULT 'DATA',
   force            BOOLEAN  DEFAULT FALSE,
   context          DBMS_STATS.CCONTEXT DEFAULT NULL, -- non operative
   options          VARCHAR2 DEFAULT get_param('OPTIONS'));

3、锁定统计信息

(1).锁定统计信息，禁止搜集表的统计信息;
(2).删除统计信息后锁定，使之每次都用动态采样:
(3)改变统计信息对表数据的估算。

Oracle锁定统计信息:

(1)锁定统计信息

exec dbms_stats.lock_table_stats('ownname',tabname'cascade=>TRUE);

(2).验证统计信息锁定情况

select table_name,stattype_locked from dba_tab_statistics where stattype_locked is not null;

(3).解锁统计信息

exec dbms_stats.unlock_table_stats('ownname','tabname');

(4).删除统计信息

exec dbms_stats.delete_table_stats('ownname',tabname'cascade=>TRUE);

(5)设置统计信息

exec dbms_stats.set_table_stats('ownname',tabname'numrows=>10):

Oracle锁定统计信息存在的问题

(1).统计信息处于锁定状态，新建索引不会自动收集统计信息，存在性能风险，建议新建索引前解锁，创建完成后锁定。

4、 查看直方图信息

4.1 直方图

• 用来更进一步描述某列的数据分布情况
• 基于SQL使用情况来创建

• sys.col_usage$保存了谓词条件列和join条件列的使用情况
• 当采集统计信息时候，这些列就是创建直方图的候选列
  • 直方图类型
  • Frequency ---- 列的NDV<=254
  • Top-frequency----列的NDV>254 但是高频的254个列值占总行数比例大于100-100/n
  • Hybrid Histogram —列的NDV>254 但是高频的254个列值占总行数比例小于100-100/n
  • Height-balanced—如果使用了AUTO_SAMPLE_SIZE将不再创建这个类型的直方图
  • 被Hybrid直方图替换
  • 列的NDV > 254
  • 如果使用estimate_percent依然会创建
  • 升级后保留

 select a.column_name,
         b.num_rows,
         a.num_nulls,
         a.num_distinct Cardinality,
         round(a.num_distinct / b.num_rows * 100, 2) selectivity,
         a.histogram,
         a.num_buckets
    from dba_tab_col_statistics a, dba_tables b
   where a.owner = b.owner
     and a.table_name = b.table_name
     and a.owner = 'PROD'
     and a.table_name = 'T_FILE';

4.2 查看表的统计信息

统计信息收集如下数据:
(1)表自身的分析: 包括表中的行数，数据块数，行长等信息。
(2)列的分析:包括列值的重复数，列上的空值，数据在列上的分布情况。
(3)索引的分析: 包括索引叶块的数量，索引的深度，索引的聚合因子等。这些统计信息存放在数据字典里，如:

DBA_TABLES
DBA_OBJECT_TABLES
DBA_TAB_STATISTICS
DBA_TAB_COL_STATISTICS
DBA_TAB_HISTOGRAMS
DBA_INDEXES
DBA_IND_STATISTICS
DBA_CLUSTERS
DBA_TAB_PARTITIONS
DBA_TAB_SUBPARTITIONS
DBA_IND_PARTITIONS
DBA_IND_SUBPARTITIONS
DBA_PART_COL_STATISTICS
DBA_PART_HISTOGRAMS
DBA_SUBPART_COL_STATISTICS
DBA_SUBPART_HISTOGRAMS

例如:查看普通表 T_STATS 统计信息。

 select owner, table_name , object_type, stale_stats, last_analyzed
   from dba_tab_statistics
  where owner = 'SCOTT'
and table_name = 'T_STATS';

--查看表的 DML 变化量
select table_owner, table_name, inserts, updates, deletes, timestamp
    from all_tab_modifications
   where table_owner = 'SCOTT'
     and table_name = 'T_STATS';

查询 DBA_TAB_STATISTICS 视图，STALE_STATS 列代表统计信息是否陈旧，值为 YES 表示统计信息是陈旧的，需要及时收集，值为 NO 表示统计信息是正常的，不需要重新收集。Oracle 对于表的 DML 变化，记录在视图 DBA_TAB_MODIFICATIONS 中，当 DML 累计达到表行数的 10% 时，就认为统计信息是陈旧的，需要及时收集。所以，Oracle 的定时任务也是对 STALE_STATS 进行筛选、统计和收集。

5、收集数据字典统计信息

5.1 查询以查找最近的统计信息收集

set linesize 200
select max(end_time) LATEST, operation from DBA_OPTSTAT_OPERATIONS
where operation in ('gather_dictionary_stats', 'gather_fixed_objects_stats')
group by operation; 

--命令如下
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM');
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

5.2 收集几个聚簇索引的统计信息

--Bug 25286819 : CLUSTER INDEX STATS NOT GATHERED WHEN STALE TABLE OR DICTIONARY STATS ARE GATHER 

exec dbms_stats.gather_schema_stats('SCOTT');
exec dbms_stats.gather_index_stats('SYS','I_OBJ#');
exec dbms_stats.gather_index_stats('SYS','I_FILE#_BLOCK#');
exec dbms_stats.gather_index_stats('SYS','I_TS#');
exec dbms_stats.gather_index_stats('SYS','I_USER#');
exec dbms_stats.gather_index_stats('SYS','I_TOID_VERSION#');
exec dbms_stats.gather_index_stats('SYS','I_MLOG#');
exec dbms_stats.gather_index_stats('SYS','I_RG#'); 

5.3 不需要收集统计信息的情况

• 数据量变化非常频繁的表
  • 随时间表的数据量变化非常快的表，当表中具有代表性数据量时收集统计信息
  锁定统计信息以确保统计信息收集作业不会覆盖代表性统计信息
  • 比如排队信息表，开始表为空，然后数据入表, 处理数据，处理完成表为空
• 11g版本中的全局临时表
  • 用来临时存放中间结果
  • 一些会话有很多记录，另外一些可能只有少数记录
• 12c中全局临时表默认包含 session 级别统计信息
  • DBMS_STATS 设置 GLOBAL_TEMP_TABLE_STATS 为 SHARED 或者 SESSION
• 中间表
  • 一次写入，一次读取，然后被 Truncate 或 deleted
  • 常见于 ETL 过程中的中间表
  一次写入，一次读取，然后被 Truncate 或 deleted，这样的表无需收集统计信息，否则会增加 ETL 的时间，建议使用高级别的动态采样
  • 通过 对中间表使用 dynamic_sampling hint 或修改 session 参数

select /*+ dynamic_sampling(t 2)*/ * from t_jiekexu t where address_change='Y';

参考链接

【Oracle 公益课堂 Oracle 统计信息管理】 https://www.bilibili.com/video/BV1nc411V7zh/?share_source=copy_web&vd_source=9f857c2d20b44dcbf6ea70def4b2f83d
官方文档：https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_STATS.html#GUID-01FAB8ED-E4A3-4C3E-8FE2-88717DCDDA06
中亦云课堂第 40 期- Oracle 统计信息使用专题分享