Oracle - 统计信息摘录-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_28440013/article/details/120509171

摘录《SQL 核心优化思想》

第二章统计信息

2.1、什么是统计信息

基数，直方图，集群因子等概念都需要通过统计信息去收集才能得到，收集统计信息是为了让执行器选择最佳执行计划。

统计信息主要分为表的统计信息，列的统计信息，索引的统计信息，系统的统计信息，数据字典的统计信息和动态性能视图的统计信息。

表的统计信息：主要包含表的总行数（NUM_ROWS),表的块数（BLOCKS）,以及行平均长度(AVG_ROW_LEN),通过查询数据字典DBA_TABLES获取表的统计信息。

CREATE TABLE T_STATS AS SELECT * FROM DBA_OBJECTS;

SELECT OWNER,TABLE_NAME,NUM_ROWS,BLOCKS,AVG_ROW_LEN FROM DBA_TABLES WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'T_STATS';

因为是新创建的表没有统计信息，所以需要通过下面脚本来收集统计信息

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 => 1,
	CASCADE => TRUE
	);
END;/

列的统计信息：主要包含列的基数(NUM_DISTINCT)，列中的空值数量(NUM_NULL)，以及列的数据分布情况（直方图,HISTOGRAM），我们可以通过数据字典DBA_TAB_COL_STATISTICS查看列的统计信息

--查看列的统计信息
SELECT COLUMN_NAME,NUM_DISTINCT,NUM_NULLS,NUM_BUCKETS,HISTOGRAM FROM DBA_TAB_COL_STATISTICS
WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'T_STATE';

--查看列和表的统计信息
SELECT A.COLUMN_NAME,B.NUM_ROWS,A.NUM_NULLS,B.BLOCKS,B.AVG_ROW_LEN,A.NUM_DISTINCT,
       ROUND(A.NUM_DISTINCT / B.NUM_ROWS * 100 , 2) AS SELECTIVITY,
       A.NUM_BUCKETS,A.HISTOGRAM
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 = 'T_STATE'

索引的统计信息：主要包含索引BLEVEL（索引高度-1），叶子块的个数（LEAF_BLOCKS）以及集群因子(CLUSTERING_FACTORY)。我们可以通过数据字典DBA_INDEXES查看索引的统计信息(索引的统计信息是在创建索引时自动生成的)。

--查看索引的统计信息  
SELECT BLEVEL,LEAF_BLOCKS,CLUSTERING_FACTOR,STATUS FROM DBA_INDEXES
WHERE OWNER = 'SCOTT' AND INDEX_NAME = 'IDX_T_STATE_ID'

--单独为某列索引收集统计信息
BEGIN 
    DBMS_STATS.GATHER_INDEX_STATS(OWNNAME => 'SCOTT',
        INDNAME => 'IDX_T_STATE_ID');
END;

2.2、统计信息的重要参数设置

--我们通常用以下脚本收集索引和列的统计信息
BEGIN
	DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT',
	TABNAME => 'T_STATS',
	ESTIMATE_PERCENT => 根据表大小设计(INT),
	METHOD_OPT => 'FOR ALL COLUMNS SIZE REPEAT',
	NO_INVALIDATE => FALSE,
	DEGREE => 根据表大小，CPU资源和负载设置（INT）,
	GRANULARITY =>'AUTO',
	CASCADE => TRUE
	);
END;/

参数说明：

OWNNAME表示表的拥有者，不区分大小写
TABNAME表示表的名字，不区分大小写
GRANULARITY 表示收集统计信息的粒度，该选项只对分区表生效，默认为AUTO,表示让Oracle根据表的分区类型自己判断如何收集分区表的统计信息，一般采用AUTO，数据库的默认方式。
ESTIMATE_PERCENT 表示采样率，范围是0.000 001 ~ 100。小于1G的一般采用100%采样，表小所以没有效率问题，1G~5G的表建议50%，大于5G的表采样30%。表特别大，几十上百G建议先分区，在对每个分区收集统计信息，一般来说，为了保证统计信息的准确性，一般不低于30%的采样。

--我们可以通过下面的SQL查看表的采样率
SELECT OWNER,TABLE_NAME,NUM_ROWS,SAMPLE_SIZE,ROUND(SAMPLE_SIZE / NUM_ROWS * 100) ESTIMATE_PERCENT
FROM DBA_TAB_STATISTICS WHERE TABLE_NAME = 'T_STATE' AND OWNER = 'SCOTT';

METHOD_OPT 用于控制收集直方图策略
- METHOD_OPT => 'FOR ALL COLUMNS SIZE 1’表示所有列都不收集直方图
- METHOD_OPT => ‘FOR ALL COLUMNS SIZE skewonly’ 表示对表中所有列自动判断是否收集直方图，实际操作中不要使用，因为并不是所有的列都会出现在where 条件中，没有出现在where中收集直方图是没有任何意义的
- METHOD_OPT => ‘FOR ALL COLUMNS SIZE AUTO’ 对出现在where条件中的列自动判断是否收集直方图，默认参数就是AUTO
- METHOD_OPT => ‘FOR ALL COLUMNS SIZE REPEAT’ 表示当前有哪些列收集了直方图，现在就对这些列再收集直方图。对一个运行稳定的系统建议采用此种方法
- METHOD_OPT => ‘FOR COLUMNS OBJECT_TYPE SIZE SKEWONLY’ 表示单独对OBJECT_TYPE这列收集直方图，对于其他列，如果之前也收集了，现在也收集
NO_INVALIDATE 表示共享池中涉及到的游标是否立即失效。默认值为DBMS_STATS.AUTO_INVALIDATE,表示让Oracle判断是否立即失效。建议设置为FALSE,立即失效。
DEGREE表示收集统计信息的并行度，默认为NULL，不设置则不开并行，设置了后根据表的DEGREE开并行，可以通过查看DBA_TABLES.DEGREE 查看表的并行
CASCADE 表示在收集表的统计信息是否级联收集索引的统计信息，默认为DBMS_STATS.AUTO_CASCADE,让Oracle自己决定是否级联信息，建议改成TRUE,级联收集索引信息

2.3、检查统计信息是否过期

很多时候由于统计信息过期，优化器所采用的计划都是根据老的统计信息所执行的，因此保存统计信息不过期对优化采取的执行路径是非常必要的。

检验执行计划是否过期，先刷新数据库监控信息

--刷新数据库监控信息
BEGIN 
	DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
END;/

--查看统计信息是否过期，STALE_STATS 显示为Yes的时候表示统计信息过期了，反之则没过期
--查看之前不许刷新数据库监控信息
SELECT OWNER,TABLE_NAME,OBJECT_TYPE,STALE_STATS,LAST_ANALYZED
FROM DBA_TAB_STATISTICS WHERE OWNER = 'SCOTT' AND TABLE_NAME = 'T_STATE';

--查看统计信息过期的原因
SELECT TABLE_OWNER,TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP
FROM ALL_TAB_MODIFICATIONS
    WHERE TABLE_OWNER = 'SCOTT' AND TABLE_NAME = 'T_STATE';

在Oracle中，当有10%的数据发生了变化是，就会引起统计信息过期。

如果单个SQL关联多个表，如果想查询这个SQL里面的表的统计信息是否过期可以通过以下步骤

SQL:
SELECT * FROM EMP E , DEPT D WHERE E.DEPTNO = D.DEPTNO;
--通过EXPLAN PLAN FOR 把SQL属性放到PLAN_TABLE 表里
EXPLAN PLAN FOR SELECT * FROM EMP E , DEPT D WHERE E.DEPTNO = D.DEPTNO;
--获得该SQL的表名和Owner
SELECT OBJECT_OWNER,OBJECT_NAME FROM PLAN_TABLE WHERE OBJECT_TYPE LIKE '%TABLE%';

2.4、扩展统计信息

2.5、动态采样

如果一个表没有进行过统计信息的收集，默认下Oracle会对表进行动态采样，动态采用的目的就是为了优化器能够评估出较为准确的rows。

--创建新表
CRAETE TABLE T_DYNA AS SELECT * FROM DBA_OBJECTS;
--创建的新表没有收集过统计信息
--查看执行计划会提示NOTE : - dynamic sampling used for this statement (level=2)
SELECT * FROM T_DYNA;

动态采样的级别分为11级，默认为2级，level表示采样等级：

level 0：不启用动态采样
level 1：当表没有收集统计信息，并且这个表要与另外的表关联且该表没有索引，数据块大小大于32个，才会进行动态采样。
level 2：对没有收集过统计信息的表进行动态采样，采样的块数为64块，如果表块数不够64块，有多少就采样多少。
level 3：对没有收集过统计信息的表进行动态采样，采样的块数为64块；如果表收集过统计信息但是优化器不能够准确判断出返回的rows，比如where substr（owner，1，3），这时会随机扫描64个数据块来进行采样。
level 4：对没有收集过统计信息的表进行动态采样，采样的块数为64块；如果表收集过统计信息，但是表有两个或者两个以上的过滤条件（AND/OR)，这时会随机扫描64个数据块进行采样，相关列问题（扩展统计信息）必须采用至少level 4的动态采样。
level 5：收集满足level 4采样条件的数据，采样个数为128个。
level 6：收集满足level 4采样条件的数据，采样个数为256个。
level 7：收集满足level 4采样条件的数据，采样个数为512个。
level 8：收集满足level 4采样条件的数据，采样个数为1024个。
level 9：收集满足level 4采样条件的数据，采样个数为4086个。
level 10：收集满足level 4采样条件的数据，采样所有数据块。
level 11：Oracle自动判断如何采样，自动决定采样个数。