oracle表分析根据表规模取样

1. Oracle自动表分析

（1）自动统计信息任务

select client_name, task_name, operation_name, status from dba_autotask_task;

是由GATHER_STATS_PROG这个program来执行的自动表分析任务

select program_type, program_action, enabled from dba_scheduler_programs where program_name='GATHER_STATS_PROG';

可以看出来GATHER_STATS_PROG这个program调用了存储过程dbms_stats.gather_database_stats_job_proc来进行统计信息收集

（2）手动执行oracle的自动分析任务

exec dbms_stats.gather_database_stats_job_proc

（3）禁止oracle自动表分析

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

（4）自动表分析的具体参数

优先分析从来没分析过的表或者该变量超过10%的表，虽然样本是auto_sample，但100%的样本量是自动任务考虑的

The GATHER_DATABASE_STATS_JOB_PROC procedure collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows)

Using the GATHER AUTO option： dbms_stats.auto_sample_size，prefer ESTIMATE_PERCENT--100%，METHOD_OPT--FOR ALL COLUMNS SIZE AUTO

The primary difference between DBMS_STATS.GATHER_DATABASE_STATS is that the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure prioritizes the database objects that require statistics, so that those objects which most need updated statistics are processed first.

This ensures that the most-needed statistics are gathered before the maintenance window closes.

 

所以综上所述，oracle的自动表分析虽然优先统计变化大的表，但是ESTIMATE_PERCENT取值优先100%，任务耗时长，不可取

 

2. 手动表分析ESTIMATE_PERCENT，METHOD_OPT配置

（1）METHOD_OPT参数

METHOD_OPT有2大类，6小类

2大类：FOR ALL COLUMNS（所有列里面） ，FOR ALL INDEXED COLUMNS （索引列里面）

每个大类size有3中选择AUTO，SKEWONLY，REPEAT

 

FOR ALL (INDEXED) COLUMNS SIZE SKEWONLY是对所有（索引）列检查数据分布情况，若（索引）列数据分布不均则收集直方图

FOR ALL (INDEXED) COLUMNS SIZE AUTO只对where后面曾经限制过的（索引）列检查数据分布情况，若（索引）列分布不均则收集直方图

 

直方图可以在dba_tab_columns的HISTOGRAM中查询出

 

比如某张表TEST_STAT6，在OBJECT_ID上有索引IDX_TEST_STAT6，且OBJECT_ID数据分布不均

1.使用skewonly收集统计信息

exec dbms_stats.gather_schema_stats(OWNNAME => 'TEST',ESTIMATE_PERCENT => dbms_stats.auto_sample_size,METHOD_OPT=> 'FOR ALL INDEXED COLUMNS SIZE SKEWONLY',DEGREE => 4,GRANULARITY => 'GLOBAL',CASCADE => TRUE);

 

select COLUMN_NAME,NUM_DISTINCT,NUM_NULLS,HISTOGRAM,SAMPLE_SIZE from dba_tab_columns where table_name='TEST_STAT6';

可以看出统计信息收集后，对object_id列生成了hybrid混合直方图

2.使用auto收集统计信息

分为两种情况

a. 没有在where后面限制object_id

删除刚刚用skewonly收集的统计信息

exec dbms_stats.delete_table_stats(OWNNAME => 'TEST',TABNAME => 'TEST_STAT6');

重新收集：

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'TEST',TABNAME => 'TEST_STAT6',ESTIMATE_PERCENT => dbms_stats.auto_sample_size,METHOD_OPT=> 'FOR ALL INDEXED COLUMNS SIZE AUTO',DEGREE => 2,GRANULARITY => 'GLOBAL',CASCADE => TRUE);

此时没有生成直方图

 

b. 限制过object_id,且已经被刷在sys.col_usage$中

删除刚刚用auto收集的统计信息

exec dbms_stats.delete_table_stats(OWNNAME => 'TEST',TABNAME => 'TEST_STAT6');

执行select count(*) from  TEST_STAT6 where OBJECT_ID<1000;

且手动刷COL_USAGE$： EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

查询TEST_STAT6的object_id是否在COL_USAGE$中：

可以发现TEST_STAT6的第四列object_id已经被刷进COL_USAGE$

 

重新收集统计信息：

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'TEST',TABNAME => 'TEST_STAT6',ESTIMATE_PERCENT => dbms_stats.auto_sample_size,METHOD_OPT=> 'FOR ALL INDEXED COLUMNS SIZE AUTO',DEGREE => 2,GRANULARITY => 'GLOBAL',CASCADE => TRUE);

此时生成了直方图

 

由a,b可以看出使用auto时，会查询列是否被where条件限制过，且是否在col_usage$中，若是的话，则会对列生成直方图，否则不生成

 

关于COL_USAGE$：

1.oracle为了监控column的使用情况，引入了col_usage$基表，col_usage$会记录数据库运行期间column作为谓词被使用的情况；

2.SMON进程会每隔15分钟，将SGA中的内容刷新到COL_USAGE$基表，我们也可以手工调用DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO来刷新col_usage$的内容；

3.在实例shutdown时，smon会清除部分无效的col_usage$内容（例如某张表已被删除）

4.隐含参数_column_tracking_level设为0可关闭column追踪

 

参数METHOD_OPT测试结论：

oracle建议使用FOR ALL COLUMNS SIZE AUTO，经测试：

FOR ALL  COLUMNS SIZE AUTO的速度快于FOR ALL  COLUMNS SIZE SKEWONLY

FOR ALL INDEXED COLUMNS SIZE AUTO的速度快于FOR ALL INDEXED COLUMNS SIZE SKEWONLY

所以我们如果想加快收集速度，且不影响统计信息质量，可以采用FOR ALL INDEXED COLUMNS SIZE AUTO

 

（2）ESTIMATE_PERCENT参数

 

表级别/ESTIMATE_PERCENT	10	5	1	0.1	0.01	0.005
十万级表 (TEST_STAT2)	表误差千级，索引误差万级	表误差万级，索引误差万级	表误差万级，索引误差万级	oracle由样本值的最小限制，经测试，大约5000左右，所以对于十万级表0.5以下的percent值就不用考虑了，再降低花费的时间也没有缩小，故对于十万级及以下表使用10，5，1均可
百万级表(TEST_STAT3)	表误差万级，索引误差小10万级	表误差万级，索引误差大10万级	表误差大万级，索引误差几十万级	表误差十万级，索引误差几十万级	对于百万级的表可以使用10，5，1
千万级表（TEST_STAT）	表误差万级，索引误差小百万级	表误差万级，索引误差小百万级	表误差十万级，索引误差百万级	表误差大十万级，索引误差百万级	表误差百万级，索引误差百万级	对于千万级表，0.01以下耗时与0.1差不多，不降反增，故千万级表的取值可以为5，1，0.1
亿级表 (TEST_STAT4)	耗时太长，不考虑	表误差十万级	表误差小几十万级	表误差大几十万级	表百万误差，但是耗时反而比0.1长	表百万误差，经测试，采样百分比不一定越小越好，0.01，0.005，0.0001耗时反而大于0.1，可以选用0.1，1，5的采样

 

oracle由样本值的最小限制，经测试，大约5000左右，所以对于十万级表0.5以下的percent值就不用考虑了，再降低花费的时间也没有缩小，经测试，采样百分比不一定越小越好，0.01，0.005，0.0001耗时反而大于0.1

对于十万级及以下表使用10，5，1均可  

对于百万级的表可以使用10，5，1  

对于千万级表，0.01以下耗时与0.1差不多，不降反增，故千万级表的取值可以为5，1，0.1

对于亿级表，0.01，0.001，0.0001耗时反而增长，故亿级表的取值可以为5，1，0.1