一、 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优化》
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)
693
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
