共计 9130 个字符，预计需要花费 23 分钟才能阅读完成。
这篇文章主要介绍“怎么理解 Oracle 统计信息”，在日常操作中，相信很多人在怎么理解 Oracle 统计信息问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”怎么理解 Oracle 统计信息”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！
统计信息的常用的脚本
DBMS_STATS.GATHER_TABLE_STATS 参数
dbms_stats.gather_table_stats(
owner VARCHAR2,
tablename VARCHAR2,
partname VARCHAR2,
estimate_percent NUMBER,
block_sample BOOLEAN,
method_opt VARCHAR2,
degree NUMBER,
granularity VARCHAR2,
cascade BOOLEAN,
stattab VARCHAR2,
statid VARCHAR2,
statown VARCHAR2,
no_invalidate BOOLEAN,
force BOOLEAN
)
参数说明
1.owner: 要分析表的所有者
2.tablename：要分析的表的表名
3.partname：分区名
4.estimate_percent: 采样行的百分比，从 0.000001-100，null 为全部分析，不采样。常量 DBMS_STATS.AUTO_SAMPLE_SIZE 是默认值，由 Oracle 决定最佳采样率。
5.block_sample: 是否用块采样代替行采样。
6.method_opt：决定 histograms 信息是怎样被统计的，method_opt 的取值如下：
for all columns：统计所有的 histograms
for all indexed columns：统计所有 index 列的 histograms
for all hidden coloumns：统计 hidden 列的 histograms
for columns SIZE | REPEAT | AUTO | SKEWONLY 统计指定列的 histograms，N 的取值范围是 0 -254
7.degree: 设置统计信息收集的并行度，默认值为 null。
8.cascade：收集索引的统计信息，默认为 false
9.stattab：指定存储统计信息的表。
10.statid：如果多个表的统计信息存储在一个 stattab 中时，statid 用作分区条件。
11.statown：存储统计信息表的所有着。
如果不指定上述三个参数，则统计信息会被更新到数据字典。
12.force: 即使表锁住了也收集统计信息。
非分区表：
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname = SCOTT ,
tabname = DEPT ,
estimate_percent = 30,
method_opt = for all columns size repeat ,
no_invalidate = FALSE,
degree = 8,
cascade = TRUE);
END;
/
对分区表收集统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname = ROBINSON ,
tabname = P_TEST ,
estimate_percent = 30,
method_opt = for all columns size repeat ,
no_invalidate = FALSE,
degree = 8,
granularity = ALL ,
cascade = TRUE);
END;
/
注意：分区的统计信息合并 到 DBA_TABLES
脚本中的参数讲解
estimate_percent
表示采样率，采样率设置太大，也没必要，使用 dbms_stats.auto_sample_size 选项允许 Oracle 自动估算要采样的一个 segment 的最佳百分比。
如果表非常大，采样率过高会导致收集统计信息跑很长，增加了系统压力。
采样率设置过小，统计的信息就不能很完整的体现表中数据的分布，这样 CBO 在进行执行计划的选择上，很可能选择错误的执行计划。
根据工作经验：
表小于 1GB 采样率可以设置 50%-100%
表大于 1GB 小于 5GB 可以设置 30%
表大于 5GB 这类表都应该进行分区，采样率可以设置为 30%
method_opt 有两部分构成
表示收集的方法，参数分为两部分
这一部分 for all [indexed | hidden] columns
控制着哪些列将会收集列的基本统计信息 (目标列上的最小值, 最大值, 列上不同值的数量, 空值的数量等等). 系统默认值为 for all columns, 它将收集表上所有列(包括隐藏列) 的基本的统计信息. 此外, 它的其他可选值如下所示：
FOR ALL INDEXED COLUMNS
指定只有含有索引的字段才能收集列的基本统计信息. 一般不推荐使用这个选项值, 因为在数据库环境中的所有 sql 语句所使用的字段, 比如 select 后面的字段, where 后面字段, group by 中的字段, 并不只是会引用含有索引的字段.
FOR ALL HIDDEN COLUMNS
指定表中所有不可见的字段才能收集列的基本统计信息, 也就是说不会去收集表上实际可见的列的统计信息. 同样的一般也不推荐使用这个选项值. 这个选项值通常只用于这种情况, 在一个所有列的统计信息都是准确的表中新增了一个或几个不可见或者说是虚拟的列, 只需要收集这个或者这几个不可见列的统计信息, 而不再重复去其他列的统计信息, 那么就使用 for all hidden columns 这个选项.
第二部分 Size [size_clause]
控制收集直方图的方式, size 后面可以有以下选项
AUTO Oracle 自己决定根据列的统计信息 (sys.col_usage$) 以及列的数据倾斜程度 (均匀分布程度) 决定哪些列需要收集直方图
Integer 指定收集直方图的桶数, 桶数最小为 1 最大为 254 (针对 11g 及以前的版本, 12c 后没有这个限制). 注意如果桶数为 1, 即 size 1 意味着不建立直方图, 如果已经有直方图的列则会删除该列的直方图.
REPEAT 只在已经有直方图的列上重新收集直方图. repeat 会确保在全局级别上对已经存在直方图的列重新收集直方图. 一般不推荐使用这个选项, 因为新的直方图使用的桶数将不能超过旧的直方图中的桶数. 假设当前直方图中桶数为 5, 当使用 size repeat 重新收集直方图时, 新的直方图使用的桶数将不能超过 5 , 这钟方式可能不会取得好的效果.
SKEWONLY 只在数据不均匀分布的列上收集直方图. 让 ORACLE 自己判断列是否收集直方图 只要是列倾斜了 ORACLE 就会收集直方图 OLTP 系统用这个 非常坑爹 基本上所有列都要收集直方图
如果 method_opt 的默认参数 for all columns size auto 在你的数据环境不适用, 可能你遇到的情况属于下面两种情况：
1. 除了指定的列, 在其它列上创建直方图
2. 只在指定的列上创建直方图
一个稳定的系统收集统计信息的时候推荐使用 method_opt= for all columns size repeat，repeat 表示以前收集过直方图，现在收集统计信息的时候就收集直方图，如果以前没收集过直方图，现在收集统计信息的时候就不收集。
有时候收集统计信息的时候，用 method_opt = for all columns size auto , 很有可能把当前的 sql 搞定了，但是把其他的 sql 搞悲剧了，这是因为 auto 表示 Oracle 根据谓词过滤信息(前文讲解直方图的时候提到过的 where 条件过滤)，自动判断该列是否收集直方图。一个稳定的系统，不应该让 Oracle 去自动判断，自动判断很可能就会出事，比如某列不该收集直方图，设置 auto 过后它自己去收集直方图了，从而导致系统不稳定。
options
控制 Oracle 统计信息的刷新方式:
gather: 重新分析整个架构
gather empty: 只分析目前还没有统计的表
gather stale: 只重新分析修改量超过 10% 的表（包括插入、更新和删除）
gather auto: 重新分析当前没有统计的对象，以及统计数据过期（变脏）的对象。使用 gather auto 类似于组合使用 gather stale 和 gather empty
degree
表示收集统计信息的时候并行度，并行度根据你系统配置以及当前系统可用资源自行设置。 一般 degree 设置 4 –8。一个 CPU 一般可以开 2 个线程
DEGREE 就等于 show parameter cpu
你开并行 8 去收集统计信息，很有可能开 17 个进程，1 个进程作为主进程来协调其他 16 个并行进程，8 个进程 进行 读取数据 另外 8 个进程 来进行 CPU 运算 进行分析
cascade
表示收集表的统计信息时候同时收集索引的统计信息。其实收集索引的统计信息非常坑爹，因为索引收集统计信息 是单块读。
no_invalidate
表示收集统计信息之后在共享池中引用了相关表的 SQL 游标是否失效。这个一定要设置为 FALSE，默认是 TRUE，不然你可能在做 SQL 优化的时候，你发现明明更新了统计信息，但是执行计划还是没改变。
granularity
统计数据的收集，ALL – 收集所有（子分区，分区和全局）统计信息
① ALL：采集 Global、partition、subpartition 等粒度统计信息。
② AUTO：根据分区类型，由 Oracle 确定统计信息采集粒度。
③ PARTITION：只采集 partition 粒度统计信息。
④ SUBPARTITION：只采集 subpartition 粒度统计信息
partname
分区表的某个分区名
判断某个表的统计信息是否过期脚本
exec dbms_stats.flush_database_monitoring_info;
– 刷新 sys.col_usage$ 和视图:sys.DBA_TAB_MODIFICATIONS
select owner, table_name name, object_type, stale_stats, last_analyzed
from dba_tab_statistics
where table_name in (table_name)
and owner = OWNER_NAME
and (stale_stats = YES or last_analyzed is null);
实验一查看统计信息是否过期
1. 创建一个实验表
CREATE TABLE TEST AS SELECT * FROM DBA_OBJECTS;
2. 收集统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname = SCOTT ,
tabname = TEST ,
estimate_percent = 100,
method_opt = for all columns size auto ,
no_invalidate = FALSE,
degree = 1,
cascade = TRUE);
END;
/
3. 刷新
exec dbms_stats.flush_database_monitoring_info;
4. 查看 test 表的统计的信息是否过期，显示空行表示没有过期
select owner, table_name name, object_type, stale_stats, last_analyzed from dba_tab_statistics where table_name in (TEST) and owner = SCOTT and (stale_stats = YES or last_analyzed is null);
—– 结果空行 —–
5. 删除 20% 的数据，让统计信息过期
select count(*) from test;
delete from test where rownum =72388*0.2;
6. 再次刷新
exec dbms_stats.flush_database_monitoring_info;
7．查看统计信息是否过期，有结果返回表示统计信息过期
select owner, table_name name, object_type, stale_stats, last_analyzed from dba_tab_statistics where table_name in (TEST) and owner = SCOTT and (stale_stats = YES or last_analyzed is null);
OWNER NAME OBJECT_TYPE STA LAST_ANALYZED
—————————— —————————— ———— —
SCOTT TEST TABLE YES 2018-05-13 20:18:40
实验二 查看是什么操作让统计信息过期的脚本
select * from
(
select * from
(
select * from
(
select u.name owner, o.name table_name, null partition_name, null subpartition_name,
m.inserts, m.updates, m.deletes, m.timestamp,
decode(bitand(m.flags,1),1, YES , NO ) truncated,
m.drop_segments
from sys.mon_mods_all$ m, sys.obj$ o, sys.tab$ t, sys.user$ u
where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#
union all
select u.name, o.name, o.subname, null,
m.inserts, m.updates, m.deletes, m.timestamp,
decode(bitand(m.flags,1),1, YES , NO ),
m.drop_segments
from sys.mon_mods_all$ m, sys.obj$ o, sys.user$ u
where o.owner# = u.user# and o.obj# = m.obj# and o.type#=19
union all
select u.name, o.name, o2.subname, o.subname,
m.inserts, m.updates, m.deletes, m.timestamp,
decode(bitand(m.flags,1),1, YES , NO ),
m.drop_segments
from sys.mon_mods_all$ m, sys.obj$ o, sys.tabsubpart$ tsp, sys.obj$ o2,
sys.user$ u
where o.obj# = m.obj# and o.owner# = u.user# and
o.obj# = tsp.obj# and o2.obj# = tsp.pobj#
) where owner not like %SYS% and owner not like XDB
union all
select * from
(
select u.name owner, o.name table_name, null partition_name, null subpartition_name,
m.inserts, m.updates, m.deletes, m.timestamp,
decode(bitand(m.flags,1),1, YES , NO ) truncated,
m.drop_segments
from sys.mon_mods$ m, sys.obj$ o, sys.tab$ t, sys.user$ u
where o.obj# = m.obj# and o.obj# = t.obj# and o.owner# = u.user#
union all
select u.name, o.name, o.subname, null,
m.inserts, m.updates, m.deletes, m.timestamp,
decode(bitand(m.flags,1),1, YES , NO ),
m.drop_segments
from sys.mon_mods$ m, sys.obj$ o, sys.user$ u
where o.owner# = u.user# and o.obj# = m.obj# and o.type#=19
union all
select u.name, o.name, o2.subname, o.subname,
m.inserts, m.updates, m.deletes, m.timestamp,
decode(bitand(m.flags,1),1, YES , NO ),
m.drop_segments
from sys.mon_mods$ m, sys.obj$ o, sys.tabsubpart$ tsp, sys.obj$ o2,
sys.user$ u
where o.obj# = m.obj# and o.owner# = u.user# and
o.obj# = tsp.obj# and o2.obj# = tsp.pobj#
) where owner not like %SYS% and owner not like %XDB%
) order by inserts desc
) where rownum
可以发现是删除导致统计信息过期
案列 1 执行大批量的 update，立即手动收集统计信息
我在 10 点收集了统计信息，
10 点 过 5 分钟执行了 一个 大批量的 update 操作
你在 10 点 10 执行查询，但是我发现查询变慢了怎么办？
也就是说，某个表会突然发生大批的 DML 操作怎么办？
解决方法：
收集统计信息的脚本直接放 update 后面，如果不立即收集
那肯定要动态采样，动态采样默认是 2，没用
这种至少要 LEVEL 达到 6，才可能有效果
如果是偶尔性质的，那么就要注意统计信息收集策略，当他发生变化了就立即收集
如果是经常性质的，那么就在 SQL 里面加上动态采样的 HINT
查看采样率的脚本
SELECT owner,
table_name,
num_rows,
sample_size,
trunc(sample_size / num_rows * 100) estimate_percent
FROM DBA_TAB_STATISTICS
WHERE owner= SCOTT AND table_name= TEST
收集统计信息注意方法
实验 size auto 的方法
1 创建一个新的实验表
create table test as select * from dba_objects;
2 收集统计信息，这里注意方法是 size auto , Oracle 自己决定根据列的统计信息 (sys.col_usage$) 以及列的数据倾斜程度 (均匀分布程度) 决定哪些列需要收集直方图
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname = SCOTT ,
tabname = TEST ,
estimate_percent = 30,
method_opt = for all columns size auto ,
no_invalidate = FALSE,
degree = 1,
cascade = TRUE);
END;
/
3 查看统计信息
select a.column_name,
b.num_rows,
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 = SCOTT
and a.table_name = TEST
从上面的结果发现 HISTOGRAM 返回的是 none 没有直方图信息，是因为我们没有 select 查询，
4 接着我们执行 select 查询，
SELECT COUNT(*) FROM TEST WHERE OWNER= SCOTT
再次收集统计信息方法同步骤 2 和再次查看是否收集直方图同步骤 3
发现有 where 条件就可以收集直方图
实验 size repeat 的方法
1 创建一个新的实验表
create table test as select * from dba_objects;
2. 收集统计信息, 这里我们使用的 size repeat 只在已经有直方图的列上重新收集直方图. repeat 会确保在全局级别上对已经存在直方图的列重新收集直方图.
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname = SCOTT ,
tabname = TEST ,
estimate_percent = 30,
method_opt = for all columns size repeat ,
no_invalidate = FALSE,
degree = 1,
cascade = TRUE);
END;
/
3 查看统计信息
select a.column_name,
b.num_rows,
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 = SCOTT
and a.table_name = TEST
没有直方图的信息
实验 对某个列（test 表的 owner 列）收集直方图
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname = SCOTT ,
tabname = TEST ,
estimate_percent = 30,
method_opt = for owner columns size skewonly ,
no_invalidate = FALSE,
degree = 1,
cascade = TRUE);
END;
/
这里 for all 换成 for owner
到此，关于“怎么理解 Oracle 统计信息”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！
