2.统计信息
2.1 什么是统计信息
统计信息主要分为表的统计信息、列的统计信息、索引的统计信息、系统的统计信息、数据字典的统计信息以及动态性能视图基表的统计信息。
表的统计信息主要包含表的总行数（num_rows）、表的块数（blocks）以及行平均长度（avg_row_len）。可以通过查询数据字典DBA_TABLES获取表的统计信息。
列的统计信息主要包含列的基数，列中的空值数量，以及列的数据分布情况(直方图)。可以通过数据字典DBA_TAB_COL_STATISTICS查看列的统计信息。
索引的统计信息主要包含索引blevel(索引高度-1)，叶子块的个数(leaf_blocks)，以及集群因子(clustering_factor)。可以通过数据字典DBA_INDEXES查看索引的统计信息。
CBO 所有信息来自DBA_TABLES，而非 DBA_SEGMENTS，这也是oracle优化器设计不合理的地方。
我们一般使用DBA_SEGMENTS 查看表的大小，但是优化器不会看DBA_SEGMENTS, 他是通过dba_tables .....num_rows * avg_row_length 查看表的大小。
创建索引的时候，会自动收集索引的统计信息，如果是分区表的分区索引，创建索引时，收集的统计信息是不准确的。而创建表时不会收集表的统计信息。
2.2 统计信息重要参数设置
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'TAB_OWNER',
tabname => 'TAB_NAME',
estimate_percent => 根据表大小设置,
method_opt => 'for all columns size repeat',
no_invalidate => FALSE,
degree => 根据表大小，CPU资源和负载设置,
granularity => 'AUTO',
cascade => TRUE);
END;
/ownname 表示表的拥有者，不区分大小写。
tabname 表示表名字，不区分大小写。
estimate_percent 表示采样率，范围是0.000001～100。
一般对小于1GB的表进行100%采样，因为表很小，即使100%采样速度也比较快。
有时候小表有可能数据分布不均衡，如果没有100%采样，可能会导致统计信息不准。因此建议对小表100%采样。
一般对表大小在1GB～5GB的表采样50%，对大于5GB的表采样30%。
如果表特别大，有几十甚至上百GB，建议应该先对表进行分区，然后分别对每个分区收集统计信息。
一般情况下，为了确保统计信息比较准确，建议采样率不要低于30%。
method_opt => 'for all columns size skewonly' 表示对表中所有列收集自动判断是否收集直方图。
在实际工作中千万不要使用 method_ opt => 'for all columns size skewonly' 收集直方图信息，因为并不是表中所有的列都会出现在where条件中，对没有出现在where条件中的列收集直方图没有意义。
method_opt => 'for all columns size 1' 表示所有列都不收集直方图。
method_opt => 'for all columns size auto' （默认的收集方式） 表示对出现在where条件中的列自动判断是否收集直方图。
method_opt => 'for all columns size repeat' 表示当前有哪些列收集了直方图，现在就对哪些列收集直方图。
method_opt => 'for columns object_type size skewonly' 表示单独对OBJECT_TYPE列收集直方图，对于其余列，如果之前收集过直方图，现在也收集直方图。
建议：一个新的系统的，第一次用 method_opt => 'for all columns size 1' ，出现一个搞一个，然后repeat。
no_invalidate 表示共享池中涉及到该表的游标是否立即失效，默认值为DBMS_STATS.AUTO_INVALIDATE，表示让Oracle自己决定是否立即失效。
建议将no_invalidate参数设置为FALSE，立即失效。
因为我们发现有时候SQL执行缓慢是因为统计信息过期导致，重新收集了统计信息之后之后执行计划还是没有更改，原因就在于没有将这个参数设置为false。
degree 表示收集统计信息的并行度，默认为NULL。
如果表没有设置degree，收集统计信息的时候后就不开并行；
如果表设置了degree，收集统计信息的时候就按照表的degree来开并行。
可以查询DBA_TABLES.degree 来查看表的degree，一般情况下，表的degree都为1。
建议可以根据当时系统的负载、系统中CPU的个数以及表大小来综合判断设置并行度。
granularity 表示收集统计信息的粒度，该选项只对分区表生效，
默认为AUTO，表示让Oracle根据表的分区类型自己判断如何收集分区表的统计信息。
对于该选项，一般采用AUTO方式，也就是数据库默认方式。
cascade 表示在收集表的统计信息的时候，是否级联收集索引的统计信息。
默认值为DBMS_STATS.AUTO_CASCADE，表示让Oracle自己判断是否级联收集索引的统计信息。
我们一般将其设置为TRUE，在收集表的统计信息的时候，级联收集索引的统计信息。
2.3 检查统计信息是否过期
收集完表的统计信息之后，如果表中有大量数据发生变化，这时表的统计信息就过期了，我们需要重新收集表的统计信息，如果不重新收集，可能会导致执行计划走偏。
Oracle是怎么判断一个表的统计信息过期了呢？
当表中有超过10%的数据发生变化（INSERT，UPDATE，DELETE），就会引起统计信息过期。
数据字典 all_tab_modifications还可以用来判断哪些表需要定期降低高水位，
比如一个表经常进行insert、delete，那么这个表应该定期降低高水位，这个表的索引也应该定期重建。
除此之外，all_tab_modifications还可以用来判断系统中哪些表是业务核心表、表的数据每天增长量等。
查看统计信息是否过期：
begin
exec dbms_stats.flush_database_monitoring_info;
end;
/select owner, table_name name, object_type, stale_stats, last_analyzed
from dba_tab_statistics
where owner = 'SCOTT'
and table_name in ('TEST')
and (stale_stats = 'YES' or last_analyzed is null);STALE_STATS 显示为YES，说明表的统计信息过期了。如果 STALE_STATS 显示为NO，表示表的统计信息没有过期。
查看统计信息的过期原因
select table_owner, table_name, inserts, updates, deletes, timestamp
from all_tab_modifications
where table_owner = 'SCOTT'
and table_name = 'TEST';2.4 扩展统计信息
当where条件中有多个谓词过滤条件，但是这些谓词过滤条件彼此是有关系的而不是相互独立的，
这时我们可能需要收集扩展统计信息以便优化器能够估算出较为准确的行数（Rows）。
需要注意的是，扩展统计信息只能用于等值查询，不能用于非等值查询。
如果不想改写SQL，怎么才能让优化器得到比较准确的Rows呢？
（1）在Oracle11g之前可以使用动态采样（至少Level4）。
alter session set optimizer_dynamic_sampling = 4;（2）在Oracle11g以后，我们可以使用扩展统计信息将相关的列组合成一个列。
create table t as select level as id, level || 'a' as a, level || level || 'b' as b from dual connect by level < 100;
insert into t select * from t; 直到 T 表中有 3244032 行数据。
SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'T', '(A, B)') FROM DUAL;
DBMS_STATS.CREATE_EXTENDED_STATS(USER,'T','(A,B)')
---------------------------------------------------
SYS_STUNA$6DVXJXTP05EH56DTIR0X对表重新收集统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',
tabname => 'T',
estimate_percent => 100,
method_opt => 'for columns SYS_STUNA$6DVXJXTP05EH56DTIR0X size skewonly',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
/重新收集统计信息之后，扩展列 SYS_STUNA$6DVXJXTP05EH56DTIR0X 也收集了直方图。
收集完扩展统计信息之后，优化器就能估算出较为准确的Rows。
注意：扩展统计信息只能用于等值查询，不能用于非等值查询。非等值查询只能动态采样。
2.5 动态采样
如果一个表从来没收集过统计信息，默认情况下Oracle会对表进行动态采样（Level=2）以便优化器估算出较为准确的Rows，动态采样的最终目的就是为了让优化器能够评估出较为准确的Rows。
执行计划中 dynamic sampling used for this statement (level=2) 表示启用了动态采样，level表示采样级别，默认情况下采样级别为2。
动态采样的级别分为11级。
level0：不启用动态采样。
level1：当表（非分区表）没有收集过统计信息并且这个表要与另外的表进行关联（不能是单表访问），同时该表没有索引，表的数据块必须大于32个，满足这些条件的时候，Oracle会随机扫描表中32个数据块，然后评估返回的Rows。
level2：对没有收集过统计信息的表启用动态采样，采样的块数为64个，如果表的块数小于64个，表有多少个块就会采样多少个块。
level3：对没有收集过统计信息的表启用动态采样，采样的块数为64个。如果表已经收集过统计信息，但是优化器不能准确地估算出返回的Rows，而是靠猜，比如WHERE SUBSTR(owner,1,3)，这时会随机扫描64个数据块进行采样。
level4：对没有收集过统计信息的表启用动态采样，采样的块数为64个。如果表已经收集过统计信息，但是表有两个或者两个以上过滤条件（AND/OR），这时会随机扫描64个数据块进行采样，相关列问题就必须启用至少level4进行动态采样。level4采样包含了level3的采样数据。
level5：收集满足level4采样条件的数据，采样的块数为128个。
level6：收集满足level4采样条件的数据，采样的块数为256个。
level7：收集满足level4采样条件的数据，采样的块数为512个。
level8：收集满足level4采样条件的数据，采样的块数为1024个。
level9：收集满足level4采样条件的数据，采样的块数为4086个。
level10：收集满足level4采样条件的数据，采样表中所有的数据块。
level11：Oracle自动判断如何采样，采样的块数由Oracle自动决定。
如果表已经收集过统计信息并且优化器能够准确地估算出返回的Rows，即使添加了动态采样的HINT或者是设置了动态采样的参数为level3，也不会启用动态采样。
什么时候需要启用动态采样呢？
当系统中有全局临时表，就需要使用动态采样，因为全局临时表无法收集统计信息，我们建议对全局临时表至少启用level4进行采样。
当执行计划中表的Rows估算有严重偏差的时候，例如相关列问题，或者两表关联有多个连接列，关联之后Rows算少，或者是where过滤条件中对列使用了substr、instr、like，又或者是where过滤条件中有非等值过滤，或者groupby之后导致Rows估算错误，此时我们可以考虑使用动态采样，同样，我们建议动态采样至少设置为level4。
在数据仓库系统中，有些报表SQL是采用Obiee/SAPBO/Congnos自动生成的，此类SQL一般都有几十行甚至几百行，SQL的过滤条件一般也比较复杂，有大量的AND和OR过滤条件，同时也可能有大量的where子查询过滤条件，SQL最终返回的数据量其实并不多。
对于此类SQL，如果SQL执行缓慢，有可能是因为SQL的过滤条件太复杂，从而导致优化器不能估算出较为准确的Rows而产生了错误的执行计划。
我们可以考虑启用动态采样level6观察性能是否有所改善，我们曾利用该方法优化了大量的报表SQL。
最后，需要注意的是，不要在系统级更改动态采样级别，默认为2就行。系统级别如果超过2，可能就会挂了。
如果某个表需要启用动态采样，直接在SQL语句中添加HINT即可。
select /*+ dynamic_sampling(test 6) */ * from test;2.6 定制统计信息收集策略
优化器在计算执行计划的成本时依赖于统计信息，如果没有收集统计信息，或者是统计信息过期了，那么优化器就会出现严重偏差，从而导致性能问题。
因此要确保统计信息准确性。虽然数据库自带有JOB每天晚上会定时收集数据库中所有表的统计信息，但是如果数据库特别大，自带的JOB无法完成全库统计信息收集。
建议关闭数据库自带的统计信息收集JOB，根据实际情况自己定制收集统计信息策略。
-- 查询DML 的操作的脚本
-- 跑这个 脚本 一定要在收集统计信息之前！！！
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<=50;怎么样判断一个系统的核心表 ？ 是那种经常的INSERT 和update 操作的表。
直接查dba_tab_modifications这个不行吗？---sys.mon_mods$ 记录还未FLUSH到mon_mods_all$的信息。注意：该表不是实时的，需要等一会儿才会记录DML数据，但是它的刷新与sys
注意：该表不是实时的，需要等一会儿才会记录DML数据，但是它的刷新与sys.mon_mods_all$不一样。
sys.mon_mods_all$ 是DBA_TAB_MODIFICATIONS的基表。
收集统计信息会清空上面2个表的数据
收集 SCOTT 账户下统计信息过期了或者是从没收集过统计信息的表的统计信息
-- 此脚本只针对某个用户
DECLARE
CURSOR STALE_TABLE IS
SELECT OWNER,
SEGMENT_NAME,
CASE
WHEN SIZE_GB < 0.5 THEN
30
WHEN SIZE_GB >= 0.5 AND SIZE_GB < 1 THEN
20
WHEN SIZE_GB >= 1 AND SIZE_GB < 5 THEN
10
WHEN SIZE_GB >= 5 AND SIZE_GB < 10 THEN
5
WHEN SIZE_GB >= 10 THEN
1
END AS PERCENT,
8 AS DEGREE
FROM (SELECT OWNER,
SEGMENT_NAME,
SUM(BYTES / 1024 / 1024 / 1024) SIZE_GB
FROM DBA_SEGMENTS
WHERE OWNER = 'SCOTT'
AND SEGMENT_NAME IN
(SELECT /*+ UNNEST */ DISTINCT TABLE_NAME
FROM DBA_TAB_STATISTICS
WHERE (LAST_ANALYZED IS NULL OR STALE_STATS = 'YES')
AND OWNER = 'SCOTT')
GROUP BY OWNER, SEGMENT_NAME);
BEGIN
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
FOR STALE IN STALE_TABLE LOOP
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => STALE.OWNER,
TABNAME => STALE.SEGMENT_NAME,
ESTIMATE_PERCENT => STALE.PERCENT,
METHOD_OPT => 'for all columns size repeat',
DEGREE => 8,
GRANULARITY => 'ALL',
CASCADE => TRUE);
END LOOP;
END;
/全局临时表无法收集统计信息，我们可以抓出系统中的全局临时表，抓出系统中使用到全局临时表的SQL，
然后根据实际情况，对全局临时表进行动态采样，或者是人工对全局临时表设置统计信息（DBMS_STATS.SET_TABLE_STATS）。
下面脚本抓出系统中使用到全局临时表的SQL。
select b.object_owner, b.object_name, a.temporary, sql_text
from dba_tables a, v$sql_plan b, v$sql c
where a.owner = b.object_owner
and a.temporary = 'Y'
and a.table_name = b.object_name
and b.sql_id = c.sql_id;
浙公网安备 33010602011771号