Oracle统计信息（一）—— 各版本新特性，统计信息收集及查看方法

一、 Oracle 10g以前

没有自动收集统计信息的机制，需要由DBA手工写shell脚本来实现。

二、 Oracle 10g

10g开始引入了自动统计信息收集作业，当 statistics_level 设置为typical（默认）或all时，oracle将每天自动收集统计信息。

自动统计信息收集作业会自动判断以下内容：

• 需要对哪些对象收集统计信息
• 统计信息是否已过期，需要重新收集
• 以估算模式收集统计信息时的采样比例
• 需要对哪些列收集直方图信息，bucket数应该为多少
• 是否使用并行收集以及并行度

10g中自动统计信息收集作业名为GATHER_STATS_JOB，核心是调用gather_database_stats_job_proc存储过程。dba_scheduler_job_run_details可以看到GATHER_STATS_JOB实际执行情况。

10g中自动统计信息收集作业特点

该Job在两种情况下会收集对象统计信息：

• Missing statistics（统计信息缺失）：对象的统计信息之前没有收集过。
• Stale statistics（统计信息陈旧）：自上次自动收集统计信息以来，对象有超过10%的rows 变化（包括增删改）或者表被truncate过

收集作业的维护窗口有两个：

select t1.window_name,t1.repeat_interval,t1.duration from dba_scheduler_windows t1,dba_scheduler_wingroup_members t2
where t1.window_name=t2.window_name and t2.window_group_name in ('MAINTENANCE_WINDOW_GROUP','BSLN_MAINTAIN_STATS_SCHED');

• weeknight_window：周一至周五晚10点开始运行，最长运行8小时
• weekend_window：周六早上0点开始，最长运行2天

10g中未对维护窗口添加资源限制，作业运行时可以无限制地消耗系统资源

select * from dba_scheduler_windows;

其中第二和第三点也正是它的缺点：可配置维护窗口太少，不够灵活；未对维护窗口添加资源限制，作业运行时可以无限消耗系统资源。这两点在11g中得到了改进。

三、 Oracle 11g

1. 自动统计信息收集作业改进

• 取消了GATHER_STATS_JOB作业，引入了GATHER_STATS_PROG自动运行任务专用于自动统计信息收集。每次运行时会先生成名为ORA$AT_OS_OPT_XXX的作业，再执行这个作业收集统计信息（实际也是调用gather_database_stats_job_proc存储过程实现），对应的客户端名称为 ’auto optimizer stats collection’

select * from dba_autotask_task;

• 可配置收集作业的维护窗口增加到了7个

• 维护窗口运行时间改变

• 对维护窗口添加了资源控制

从$ORACLE_HOME/rdms/admin/catmwin.sql中，可以清晰地看到自动统计信息收集作业搭建流程。另外通过unwrap存储过程gather_database_stats_job_proc可以看到oracle是通过调用dbms_stats_internal.update_target_list来决定应该哪些收集哪些对象的统计信息，及统计信息过旧的判断条件，有兴趣可以看看。

2. 支持多列统计信息

参考 Oracle统计信息（四）—— 动态采样（动态统计信息）与 多列统计信息

四、 Oracle 12c

1. 支持同时在多个表上收集统计信息

　　在之前的版本中，当执行DBMS_STATS收集统计信息时，Oracle习惯于一次一个表进行收集统计数据，如果表很大，那么推荐采用并行方式。在12c R1中，oracle可以同时在多个表、分区以及子分区上收集。

-- 使用之前必须对数据库进行以下设置以开启此功能：
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='DEFAULT_MAIN';
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=4;
-- 开始收集
EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT', 'ALL');
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');

2. dbms_stats report_gather_auto_stats统计信息报告

12c中引入了一系列dbms_stats的report_* 函数，在收集统计信息前向用户汇报预备收集的统计对象和统计情况。

例如report_gather_auto_stats

function report_gather_auto_stats (
detail_level varchar2 default ‘TYPICAL’,
format varchar2 default ‘TEXT’)
return clob;
 
— This procedure runs auto stats gathering job in reporting mode. That is,
— stats are not actually collected, but all the objects that will be
— affected when auto stats gathering is invoked are reported.
— The detail level for the report is defined by the detail_level
— input parameter. Please see the comments for report_single_stats_operation
— on possible values for detail_level and format.

使用方法

 
SQL> variable c clob;
SQL> exec :c:=dbms_stats.report_gather_auto_stats();
PL/SQL procedure successfully completed.
 
SQL> set long 999
SQL> print :c;

3. 检测有用的多列统计信息

前面提到，11g的新特性中有“多列统计信息”，但有可能你并不清楚需要为哪些列创建Column Group。

为此，12c中引入了“自动检测有用列组信息”新特性作为补充。这个新特性可以针对表基于特定的工作负载，通过使用DBMS_STATS.SEED_COL_USAGE和REPORT_COL_USAGE来确定我们需要哪些Column Group。当不清楚需要为哪些列创建多列统计信息时，这个技术是非常有用的。需要注意的是，它不适用于包含表达式的列。

使用案例参考：Oracle 12c新特性之检测有用的多列统计信息_ITPUB博客

五、 Oracle 19c

注意以下两个新特性目前都仅支持Exadata平台。

1. 高频自动优化器统计信息收集

High-Frequency Automatic Optimizer Statistics Collection

• 引入目的

如果数据经常更改，过时的统计信息可能会导致性能问题。高频自动优化器统计信息收集是一个轻量级任务，用于补充标准统计信息收集任务，默认每15分钟收集一次，这意味着统计信息失效的时间更短。

• 工作原理

之所以说是“轻量级”，是因为它只收集过时的统计信息数据，不执行诸如清除不存在的对象的统计信息或调用Optimizer Statistics Advisor之类的操作，这些操作依然由标准统计信息收集作业执行。因此这个新特性只是一个补充，并不能替代标准作业。

在维护窗口中运行的标准作业不受高频作业的影响，维护窗口期间不会启动高频任务，可以通过查询DBA_AUTO_STAT_EXECUTIONS来监视任务运行情况。

• 启用和设置

要启用和禁用该任务、设置最长运行时间、执行间隔，请使用DBMS_STATS.SET_GLOBAL_PREFS存储过程（要有管理员权限）。

• 要启用高频任务，将AUTO_TASK_STATUS选项设置为ON

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON');

• 要设置最长运行时间，将AUTO_TASK_MAX_RUN_TIME选项设置为所需的秒数

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_MAX_RUN_TIME','600');

• 要设置间隔，将AUTO_TASK_INTERVAL选项设置为所需的秒数

EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','480');

2. 实时统计信息收集 Real Time Statistics

从19c开始，对于常规的DML操作，数据库也会自动ONLINE收集统计信息，从而避免统计信息陈旧，确保最优的执行计划。Real-Time Statistics特性默认启用，无需任何配置。当DML操作修改表时，Oracle数据库会动态计算基本的统计信息的值。比如你往一张业务表里插入了1000多行记录，Real-Time Statistics会跟踪插入的行数，如果这时一个新的需要硬解析的查询过来，优化器就可以借助这些实时统计信息来获得更准确的成本估计。

需要说明，虽然有了创建/重建索引自动收集统计信息、批量加载自动收集统计信息以及Real-Time Statistics这些特性，但它们都只是用于补充而非替代标准统计信息收集任务。Real-Time Statistics只是收集了最基本的统计信息，自动维护作业还需要继续使用DBMS_STATS定期收集。

在19.2版本中，相关数据字典也包含了Real-Time Statistics的说明

• DBA_TAB_COL_STATISTICS
• DBA_TAB_STATISTICS

如果某个session不想收集Real-Time Statistics，也可以通过NO_GATHER_OPTIMIZER_STATISTICS  hint来指定。

如果想禁用该特性，可以通过如下2个隐藏参数控制：

• _Optimizer_gather_stats_on_conventional_dml
• _Optimizer_use_stats_on_conventional_dml

下面简单看个示例

--创建测试表
create table  rts_test(object_id number, object_name varchar2(128));
insert /*+ append  */ into rts_test select object_id, object_name from dba_objects;
commit;
 
--针对Bulk-Load的自动收集的统计信息
select COLUMN_NAME,LOW_VALUE,HIGH_VALUE,SAMPLE_SIZE,NOTES from DBA_TAB_COL_STATISTICS where table_name='RTS_TEST';
select TABLE_NAME,NUM_ROWS,BLOCKS,NOTES from DBA_TAB_STATISTICS where table_name='RTS_TEST';
 
--下面进行一些常规的Insert操作，插入999行记录
insert into  rts_test select 72443+rownum, 'Test'||rownum from dual connect by rownum<1000;
commit;
 
select COLUMN_NAME,LOW_VALUE,HIGH_VALUE,SAMPLE_SIZE,NOTES from DBA_TAB_COL_STATISTICS where table_name='RTS_TEST';

DBA_TAB_STATISTICS可以看到后2行新增加的Real-Time Statistics信息

select TABLE_NAME,NUM_ROWS,BLOCKS,NOTES from DBA_TAB_STATISTICS where table_name='RTS_TEST';

不过也可能数据缓存在SGA中，还没有更新到数据字典，缺省每隔15分钟会写入数据字典，如果想立刻写入，可以通过如下存储过程刷新:

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
 
select TABLE_NAME,NUM_ROWS,BLOCKS,NOTES from DBA_TAB_STATISTICS where table_name='RTS_TEST';
 
-- 可以看到已经有了，下面简单看下优化器使用Real-Time Statistics
select count(*) from rts_test where object_id>2000;
 
    COUNT(*)
  ----------
        71447
 
select * from TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format=>'TYPICAL'));

六、 收集方法

1. dbms_stats包（推荐）

从oracle 8开始，dbms_stats包是官方推荐的统计信息收集方法，可以理解为analyze的增强版。

其中最常用的是以下4个存储过程：

• gather_table_stats：收集目标表、列、及表上索引的统计信息
• gather_index_stats：收集指定索引的统计信息
• gather_schema_stats：收集指定schema下所有表的统计信息
• gather_database_stats：收集全库所有对象的统计信息

例子

以估算模式只收集表统计信息，采样比例为15%

exec dbms_stats.gather_table_stats(ownname => 'USER01',tabname => 'TAB01',estimate_percent => 15,method_opt=> 'for table',cascade=>false); 

以计算模式（采样比例为100%，将estimate_percent设为100%或null）只收集表统计信息

exec dbms_stats.gather_table_stats(ownname => 'USER01',tabname => 'TAB01',estimate_percent => 100,method_opt=> 'for table',cascade=>false); 
-- 或者
exec dbms_stats.gather_table_stats(ownname => 'USER01',tabname => 'TAB01',estimate_percent => NULL,method_opt=> 'for table',cascade=>false); 

指定并行度为4，并行收集统计信息

exec dbms_stats.gather_table_stats(ownname => 'USER01',tabname => 'TAB01',estimate_percent => 100,cascade=>true,degree=>4);

收集统计信息后强制重新解析sql  no_invalidate=>false

有些DBA在收集统计信息时，没有使用no_invalidate=>false选项，即使收集了统计信息，执行计划却没有立即改变。因为该参数的默认值是AUTO_INVALIDATE，优化器会选择5个小时内的某个时间点来对SQL重新做硬解析。因为不了解这个参数，有人还会在收集完统计信息后flush shared_pool来强制对所有SQL做硬解析。

exec dbms_stats.gather_table_stats(ownname => 'USER01',tabname => 'TAB01',estimate_percent => 100,cascade=>true,degree=>4,no_invalidate=>false);

收集object_name,object_id列（和对应表）的统计信息，并设置不收集直方图。dbms_stats包无法设置只收集列的信息，会同时收集表的统计信息

exec dbms_stats.gather_table_stats(ownname => 'USER01',tabname => 'TAB01',estimate_percent => 100,method_opt=> 'for column size 1 object_name object_id',cascade=>false);

收集索引统计信息

exec dbms_stats.gather_index_stats(ownname => 'USER01',indname => 'IDX_T2',estimate_percent => 100);

一次收集表及所有列所有索引的统计信息（级联收集）

exec dbms_stats.gather_table_stats(ownname => 'USER01',tabname => 'TAB01',estimate_percent => 100,cascade=>true); 

删除表（及所有列所有索引）的统计信息

exec dbms_stats.delete_table_stats(ownname => 'USER01',tabname => 'TAB01');

2. dbms_stats包较analyze的增强

• analyze命令不能正确收集分区表的统计信息
• analyze命令不能并行收集统计信息

七、 查看方法

可以直接使用以下脚本，指定表名即可。会显示表级、分区级、子分区级统计信息

set echo off
set scan on
set lines 150
set pages 66
set verify off
set feedback off
set termout off
column uservar new_value Table_Owner noprint
select user uservar from dual;
set termout on
column TABLE_NAME heading "Tables owned by &Table_Owner" format a30
select table_name from dba_tables where owner=upper('&Table_Owner') order by 1
/
undefine table_name
undefine owner
prompt
accept owner prompt 'Please enter Name of Table Owner (Null = &Table_Owner): '
accept table_name  prompt 'Please enter Table Name to show Statistics for: '
column TABLE_NAME heading "Table|Name" format a15
column PARTITION_NAME heading "Partition|Name" format a15
column SUBPARTITION_NAME heading "SubPartition|Name" format a15
column NUM_ROWS heading "Number|of Rows" format 9,999,999,990
column BLOCKS heading "Blocks" format 999,990
column EMPTY_BLOCKS heading "Empty|Blocks" format 999,999,990
 
column AVG_SPACE heading "Average|Space" format 9,990
column CHAIN_CNT heading "Chain|Count" format 999,990
column AVG_ROW_LEN heading "Average|Row Len" format 990
column COLUMN_NAME  heading "Column|Name" format a25
column NULLABLE heading Null|able format a4
column NUM_DISTINCT heading "Distinct|Values" format 999,999,990
column NUM_NULLS heading "Number|Nulls" format 9,999,990
column NUM_BUCKETS heading "Number|Buckets" format 990
column DENSITY heading "Density" format 990
column INDEX_NAME heading "Index|Name" format a15
column UNIQUENESS heading "Unique" format a9
column BLEV heading "B|Tree|Level" format 90
column LEAF_BLOCKS heading "Leaf|Blks" format 990
column DISTINCT_KEYS heading "Distinct|Keys" format 9,999,999,990
column AVG_LEAF_BLOCKS_PER_KEY heading "Average|Leaf Blocks|Per Key" format 99,990
column AVG_DATA_BLOCKS_PER_KEY heading "Average|Data Blocks|Per Key" format 99,990
column CLUSTERING_FACTOR heading "Cluster|Factor" format 999,999,990
column COLUMN_POSITION heading "Col|Pos" format 990
column col heading "Column|Details" format a24
column COLUMN_LENGTH heading "Col|Len" format 9,990
column GLOBAL_STATS heading "Global|Stats" format a6
column USER_STATS heading "User|Stats" format a6
column SAMPLE_SIZE heading "Sample|Size" format 9,999,999,990
column to_char(t.last_analyzed,'MM-DD-YYYY') heading "Date|MM-DD-YYYY" format a10
 
prompt
prompt ***********
prompt Table Level
prompt ***********
prompt
select 
    TABLE_NAME,
    NUM_ROWS,
    BLOCKS,
    EMPTY_BLOCKS,
    AVG_SPACE,
    CHAIN_CNT,
    AVG_ROW_LEN,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    to_char(t.last_analyzed,'MM-DD-YYYY')
from dba_tables t
where 
    owner = upper(nvl('&&Owner',user))
and table_name = upper('&&Table_name')
/
select
    COLUMN_NAME,
    decode(t.DATA_TYPE,
           'NUMBER',t.DATA_TYPE||'('||
           decode(t.DATA_PRECISION,
                  null,t.DATA_LENGTH||')',
                  t.DATA_PRECISION||','||t.DATA_SCALE||')'),
                  'DATE',t.DATA_TYPE,
                  'LONG',t.DATA_TYPE,
                  'LONG RAW',t.DATA_TYPE,
                  'ROWID',t.DATA_TYPE,
                  'MLSLABEL',t.DATA_TYPE,
                  t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
    decode(t.nullable,
              'N','NOT NULL',
              'n','NOT NULL',
              NULL) col,
    NUM_DISTINCT,
    DENSITY,
    NUM_BUCKETS,
    NUM_NULLS,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    to_char(t.last_analyzed,'MM-DD-YYYY')
from dba_tab_columns t
where 
    table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user))
/
 
select 
    INDEX_NAME,
    UNIQUENESS,
    BLEVEL BLev,
    LEAF_BLOCKS,
    DISTINCT_KEYS,
    NUM_ROWS,
    AVG_LEAF_BLOCKS_PER_KEY,
    AVG_DATA_BLOCKS_PER_KEY,
    CLUSTERING_FACTOR,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    to_char(t.last_analyzed,'MM-DD-YYYY')
from 
    dba_indexes t
where 
    table_name = upper('&Table_name')
and table_owner = upper(nvl('&Owner',user))
/
break on index_name
select
    i.INDEX_NAME,
    i.COLUMN_NAME,
    i.COLUMN_POSITION,
    decode(t.DATA_TYPE,
           'NUMBER',t.DATA_TYPE||'('||
           decode(t.DATA_PRECISION,
                  null,t.DATA_LENGTH||')',
                  t.DATA_PRECISION||','||t.DATA_SCALE||')'),
                  'DATE',t.DATA_TYPE,
                  'LONG',t.DATA_TYPE,
                  'LONG RAW',t.DATA_TYPE,
                  'ROWID',t.DATA_TYPE,
                  'MLSLABEL',t.DATA_TYPE,
                  t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '||
           decode(t.nullable,
                  'N','NOT NULL',
                  'n','NOT NULL',
                  NULL) col
from 
    dba_ind_columns i,
    dba_tab_columns t
where 
    i.table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user))
and i.table_name = t.table_name
and i.column_name = t.column_name
order by index_name,column_position
/
 
prompt
prompt ***************
prompt Partition Level
prompt ***************
 
select
    PARTITION_NAME,
    NUM_ROWS,
    BLOCKS,
    EMPTY_BLOCKS,
    AVG_SPACE,
    CHAIN_CNT,
    AVG_ROW_LEN,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    to_char(t.last_analyzed,'MM-DD-YYYY')
from 
    dba_tab_partitions t
where 
    table_owner = upper(nvl('&&Owner',user))
and table_name = upper('&&Table_name')
order by partition_position
/
 
 
break on partition_name
select
    PARTITION_NAME,
    COLUMN_NAME,
    NUM_DISTINCT,
    DENSITY,
    NUM_BUCKETS,
    NUM_NULLS,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    to_char(t.last_analyzed,'MM-DD-YYYY')
from 
    dba_PART_COL_STATISTICS t
where 
    table_name = upper('&Table_name')
and owner = upper(nvl('&Owner',user))
/
 
break on partition_name
select 
    t.INDEX_NAME,
    t.PARTITION_NAME,
    t.BLEVEL BLev,
    t.LEAF_BLOCKS,
    t.DISTINCT_KEYS,
    t.NUM_ROWS,
    t.AVG_LEAF_BLOCKS_PER_KEY,
    t.AVG_DATA_BLOCKS_PER_KEY,
    t.CLUSTERING_FACTOR,
    t.GLOBAL_STATS,
    t.USER_STATS,
    t.SAMPLE_SIZE,
    to_char(t.last_analyzed,'MM-DD-YYYY')
from 
    dba_ind_partitions t, 
    dba_indexes i
where 
    i.table_name = upper('&Table_name')
and i.table_owner = upper(nvl('&Owner',user))
and i.owner = t.index_owner
and i.index_name=t.index_name
/
 
 
prompt
prompt ***************
prompt SubPartition Level
prompt ***************
 
select 
    PARTITION_NAME,
    SUBPARTITION_NAME,
    NUM_ROWS,
    BLOCKS,
    EMPTY_BLOCKS,
    AVG_SPACE,
    CHAIN_CNT,
    AVG_ROW_LEN,
    GLOBAL_STATS,
    USER_STATS,
    SAMPLE_SIZE,
    to_char(t.last_analyzed,'MM-DD-YYYY')
from 
    dba_tab_subpartitions t
where 
    table_owner = upper(nvl('&&Owner',user))
and table_name = upper('&&Table_name')
order by SUBPARTITION_POSITION
/
break on partition_name
select 
    p.PARTITION_NAME,
    t.SUBPARTITION_NAME,
    t.COLUMN_NAME,
    t.NUM_DISTINCT,
    t.DENSITY,
    t.NUM_BUCKETS,
    t.NUM_NULLS,
    t.GLOBAL_STATS,
    t.USER_STATS,
    t.SAMPLE_SIZE,
    to_char(t.last_analyzed,'MM-DD-YYYY')
from 
    dba_SUBPART_COL_STATISTICS t, 
    dba_tab_subpartitions p
where 
    t.table_name = upper('&Table_name')
and t.owner = upper(nvl('&Owner',user))
and t.subpartition_name = p.subpartition_name
and t.owner = p.table_owner
and t.table_name=p.table_name
/
 
break on partition_name
select 
    t.INDEX_NAME,
    t.PARTITION_NAME,
    t.SUBPARTITION_NAME,
    t.BLEVEL BLev,
    t.LEAF_BLOCKS,
    t.DISTINCT_KEYS,
    t.NUM_ROWS,
    t.AVG_LEAF_BLOCKS_PER_KEY,
    t.AVG_DATA_BLOCKS_PER_KEY,
    t.CLUSTERING_FACTOR,
    t.GLOBAL_STATS,
    t.USER_STATS,
    t.SAMPLE_SIZE,
    to_char(t.last_analyzed,'MM-DD-YYYY')
from 
    dba_ind_subpartitions t, 
    dba_indexes i
where 
    i.table_name = upper('&Table_name')
and i.table_owner = upper(nvl('&Owner',user))
and i.owner = t.index_owner
and i.index_name=t.index_name
/
 
clear breaks
set echo on

参考

《基于oracle的sql优化》

手把手教你19c新特性:实时统计信息收集 - 墨天轮

19c-高频自动优化器统计收集_冰山一角_新浪博客

Gathering Optimizer Statistics

Best Practices for  Gathering Optimizer Statistics with Oracle Database 19c(https://www.oracle.com/technetwork/database/bi-datawarehousing/twp-bp-for-stats-gather-19c-5324205.pdf)