一文搞懂 oracle 统计信息 -m6米乐安卓版下载

前 言

以下四个地址是我个人博客地址，一般情况下文章首发于墨天轮或者微信公众号，然后同步至 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 统计信息使用专题分享