2017-04-25 DBA日记，oracle的统计值研究

引言：

SQL语句执行快与慢全靠基于成本优化器生成执行计划的优与劣，基于成本优化器生成执行计划的优劣全靠统计值的准确与否。所以说统计值的正确性将直接影响数据库执行SQL语句的效率，不得不重视啊。

统计值收集目标：

及时，准，快

范围：

1. 数据库级 (建议不要收集)

2. schema级

3. 表级

4. 系统级 (建议不要收集)

收集策略：

1. 及时收集

1.1 新表，初始加载数据（insert into select等）后，随即人工收集。代码如下：

exec SYS.DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HQQ',TABNAME=>'QQT',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO'，DEGREE=>4,CASCADE=>TRUE);

1.2 旧表，数据量变化在30%以上，如果是慢慢累积上去，可以每日设一个时间点定时检查；如果是批量变化在30%以上，需立即收集，代码如下：

2. 准，就是收集统计值的正确性，具体如下：

2.1 采样大小的定义，建议采用自动，对应的代码如下：estimate_percent => dbms_stats.auto_sample_size

2.2 字段直方图收集，为数据倾斜比较厉害的字段，可以对生成全表扫描，或索引扫描的执行计划。对应的代码如下：method_opt => 'for all columns size auto' 自动判断;

2.3 扩展统计信息收集：

2.4 由优化器自动判断收集那些统计值：options='gather auto|gather stale|gather empty' <<<<<<<<<<<该选项只有schema 和 database级别才有。

3. 快，快速完成，避免长时间资源冲突。

3.1 degree: 并行度，通过指定平行度，加速统计值，减少收集时间。

小结代码:

表级：exec SYS.DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HQQ',TABNAME=>'QQT',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO'，DEGREE=>4,CASCADE=>TRUE);

schema级：

exec SYS.DBMS_STATS.GATHER_SCHEMA_STATS(ownname=>'HQQ',ESTIMATE_PERCENT=,METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO',OPTIONS=>'GATHER AUTO',CASCADE=>TRUE); <<<<<<这里一旦指定auto，连并系度的都是auto的

4. 补充策略：

4.1 分区表收集方式：

1）按分区进行收集，历史分区不要收集。

2）代码展示：

DBMS_STATS.GATHER_TABLE_STATS(ownname=>'schema_name',tabname=>'table_name',partname=>'partition name',estimate_percent=>dbms_stats.auto_sample_size,method_opt=>'for all columns size auto',cascade=>true,granularity=>'all|auto|global|partition|subpartition|global and subpartition',degree=>需要综合考滤物理CPU的个数而定)

granularity参数是专门为分区表而设的，分别有如下值：

all:收集所有分区、子分区，全局的统计值，也就是最消耗资源的。

auto:默认值，由分区类型而定。

global:收集全局统计值

global and partition :收集全局和分区统计值

partition:只收集当前分区统计值。

subpartitions:收集子分区层级的统计值。

注意：partname 与granularity 组合使用，就是只收集指定分区的统计值，如果不指定partname，而指定了granularity，则收集是的选项值以上所有层级，所有分区表的统计值，这将会非常消耗资源和浪费时间去重复收集已有的，稳定的统计值。

cascade: true同时收集索引值。

例子：exec SYS.DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HQQ',TABNAME=>'QQT_P_T1',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,GRANULARITY=>'AUTO',METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');

--理论值：统计所有分区、子分区、全局统计值

--实际如预期值

exec SYS.DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HQQ',TABNAME=>'QQT_P_T1',partname=>'P2002',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,GRANULARITY=>'AUTO',METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');

--期望值，只收集p2002 当前值分区

--实际值，由于使用了granularity=>auto，所以收集p2002相关联层级的当前、全局、子分区的统计值

exec SYS.DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HQQ',TABNAME=>'QQT_P_T1',partname=>'P2002',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,GRANULARITY=>'PARTITION',METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');

--期望值，只收集p2002 当前值分区

--实际值，由于使用了granularity=>PARTITION，所以只收集p2002当前分区的统计值

exec SYS.DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HQQ',TABNAME=>'QQT_P_T1',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,GRANULARITY=>'PARTITION',METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');

--期望值，只收集所有分区

--实际值，由于使用了granularity=>PARTITIO ，并且没有指定partname，所以收集所有分区的统计值

exec SYS.DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HQQ',TABNAME=>'QQT_P_T1',partname=>'P2002',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,GRANULARITY=>'SUBPARTITION',METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');

--期望值，只收集p2002下的所有子分区

--实际值，由于使用了granularity=>PARTITION，所以只收集p2002下所有子分区的统计值

exec SYS.DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HQQ',TABNAME=>'QQT_P_T1',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,GRANULARITY=>'SUBPARTITION',METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');

--期望值，只收集所有子分区

--实际值，由于使用了granularity=>PARTITION，所以收集所有子分区的统计值

exec SYS.DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HQQ',TABNAME=>'QQT_P_T1',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,GRANULARITY=>'AUTO',METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');

--期望值，收集所有

--实际值，由于使用了granularity=>auto，所以收集p2002相关联层级的当前、全局、子分区的统计值

exec SYS.DBMS_STATS.GATHER_TABLE_STATS(ownname=>'HQQ',TABNAME=>'QQT_P_T1',partname=>'P2002_MONTH1',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,GRANULARITY=>'AUTO',METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');

--期望值，收集所有与P2002_MONTH1垂直关联的所有分区

--实际值，由于使用了granularity=>auto，所以收集P2002_MONTH1垂直相关联层级的当前、全局、子分区的统计值

总结：partname=>'分区名或子分区名' + granularity=>'auto' 纵向（上下）收集与partname相关的统计值

没有指定partname, granularity=>'auto' 全收集

partname=>'分区名或子分区名' + granularity=>'global|partition|subpartition' 当收集具体partname

没有指定partname,但指定了 granularity=>'global|partition|subpartition' 收集granularity指定层级所有的分级值。

所以为了及时，快，对于分区表的统计值收集一定要指定partname 和 granularity 为非auto,degree这个参数也一定要指定。

最后预告一下，下次将讨论extended_stats的使用。