oracle优化器统计信息相关

大家好，又见面了，我是你们的朋友全栈君。

优化器使用统计信息来生成每个sql语句最优的执行计划。准确的统计信息对于数据库的效率至关重要。

dba和程序开发人员都应该了解一些统计信息相关知识，这可以使你更好的理解为什么会生成一个你看到的执行计划，知其然且知其所以然。

统计信息存储在数据字典里，可以使用数据字典视图访问这些信息。主要包括以下这些统计信息（代码块里是获取这些信息的方法）:

• 表统计(行数，块数，平均行长度)

select table_name,num_rows,avg_row_len,block from dba_tables;

dba_tab_statistics具有dba_tables更详细的信息

如果是分区表，在dba_tab_partitions和dba_tab_subpartitions里查看分区和子分区的相关信息

• 列统计(列上不同值的数量(NDV),NULL值的数量，数据分布情况(直方图)，扩展统计)

select table_name，column_name，num_distinct，num_nulls，high_value，low_value，num_buckets，histogram from dba_tab_columns where table_name='EMP';

其中最大值和最小值是raw类型，可以使用dbms_stats.convert_raw_value过程将其转化为对应的类型值。因为是过程，无法在sql语句里使用，推荐使用utl_raw包的cast系列函数。
dba_tab_col_statistics具有更加详细的列统计信息

select table_name,column_name,endpoint_number,endpoint_value from dba_tab_histograms where table_name='XXX' and column_name='YYY' 如果是分区表可以使用 dba_part_col_statistics，dba_part_histograms，dba_subpart_col_statistics和dba_subpart_histograms查看分区和子分区的统计信息和直方图信息。 

• 索引统计(叶子块的数量，树高度，聚簇因子)

select index_name,table_name,leaf_blocks,blevel,distinct_keys,clustering_factor,num_rows from dba_indexes where table_name='XXX' and index_name='YYY';

dba_ind_statistics具有更详细的统计信息

如果是分区索引，使用dba_ind_partitions和dba_ind_subpartitions查看相关分区的信息

• 系统统计(I/O性能和使用情况，cpu性能和使用情况)

select * from sys.aux_stats$;

由于数据库的对象经常在改变，所以统计信息也要定时更新，以反应对象的真实情况。oracle有两种更新数据库统计信息的方式，一种是oracle在维护窗口定时更新统计信息(oracle推荐)，另一种是手动更新统计信息。

自动更新统计信息

自动更新统计信息在oracle的维护窗口执行(每个工作日的晚上10点到凌晨2点及周六和周日全天)。自动更新统计信息调用dbms_stats.gather_database_stats_job_proc过程。
注意自动更新统计信息任务依赖于更新监视特性是否启用，如果该特性没有启用，自动更新统计信息任务不能探测失效的统计。设置statistics_level为typical(默认)或者all启用更新监视特性。

• 使用dbca创建数据库时勾选启用自动维护任务开启自动更新统计信息任务
• 手动开启自动更新统计信息任务

begin dbms_auto_task_admin.enable( client_name=>'auto optimizer stats collection', operation=>null, window_name=>null );
end;

收集统计信息需要考虑的几个问题

什么时候需要手动统计

大部分情况自动更新统计信息收集的统计信息已经足够了。但是由于统计信息只在维护窗口执行的，所以有可能表的数据已经在维护窗口前被修改了很多(删除和重建表，批量处理等操作)，以至于统计信息失效了。

对于这样的表可以使用两种方法来处理:
– 利用如果统计信息为NULL，oracle使用动态收集必须统计信息的特性。

begin
dbms_stats.delete_table_stats('SCOTT','EMP');
dbms_stats.lock_table_stats('SCOTT','EMP');
end;

将表的统计信息删除并锁定表的统计信息，达到数据库使用动态统计特性的目的，但是参数optimizer_dynamic_sampling参数必须设置为大于2的值。

• 当表的数据在有代表性的时刻收集统计信息，然后锁定该统计信息。这种方法比第一种方法更加有效率。

恢复前一个版本的统计信息

统计信息被修改时，oracle会自动保存老版本的统计信息，便于以后恢复。使用dbms_stats里的restore相关函数进行恢复。

手动收集统计信息

当需要使用手动方法收集统计信息时，使用oracle提供的dbms_stats包的相关过程收集相关的统计信息。

• gather_index_stats收集索引统计信息
• gather_table_stats收集表，列和索引的统计信息
• gather_schema_stats收集方案内所有对象的统计信息
• gather_dictionary_stats收集所有数据字典对象的统计信息
• gather_database_stats收集数据库内所有对象的统计信息

和以上收集统计信息相关函数有关的几个重要参数

• 使用抽样

使用estimate_percent参数控制抽样，oracle推荐使用dbms_stats.auto_sample_size兼顾效率和统计信息准确性，也可以设置任意的1到100的数。

• 并行执行

可以使用并行执行加快统计信息的收集速度。oracle推荐使用dbms_stats.auto_degree,让oracle选择一个合适的并行度
– 分区对象

对于分区表，oracle可以对独立的分区或者整个表进行统计。

使用参数granularity控制使用分区，子分区或者全局统计方式收集统计信息。全局和分区统计对应用程序都很重要。oracle推荐设置granularity为AUTO让oracle决定使用什么粒度收集。

• 列统计和直方图

收集列上的数据分布情况，使用method_opt参数指定收集直方图的方式。oracle推荐使用FOR ALL COLUMNS SIZE AUTO，oracle自动决定那个列需要直方图，每个直方图的桶的数量。当然也可以手动指定那个列需要直方图和每个直方图桶的数量。

oracle里的直方图是一种对数据布情况进行描述的工具。构建直方图的主要目的是帮助优化器在数据严重偏斜时做出正确的决策。表中列的数据分布情况会影响优化器对访问路径的选择，使用索引还是全表扫描，这时如果where子句过滤谓词有一个合理正确的直方图，将对优化器做出正确决定产生巨大作用。

两种最常用使用直方图的情形
一是where子句引用的列的值存在严重偏斜(如果子句不引用，创建直方图没有意义)，二是当多表连接时，由于列值分布偏斜，导致优化器选择错误的连接顺序。

创建直方图的方法，使用参数method_opt:
设置为for all column size skewonly基于索引里的列的数据分布情况决定是否创建直方图和怎么创建直方图。
设置for all column size auto基于索引里的列的数据分布情况和列的负载情况决定是否创建直方图和怎么创建直方图

• 确定统计失效

oracle使用表更新监视特性来确定一个对象是否需要更新统计信息，当statistics_level设置为typical或者all时启用表更新监视特性。可以查看视图user_table_modifications查看insert，update和delete的近似数量。当监视表更新了10%数据时会认为统计信息失效了，需要更新统计信息。

• 设置手动更新统计信息的参数默认值，可以使用oem或者dbms_stats.set_*_prefs设置参数的默认值。

系统统计信息

系统统计信息描述I/O和cpu性能和使用情况，优化器估计每个sql语句所需的I/O和cpu资源，系统统计信息使优化器能更准确的估计IO和cpu成本，从而使优化器选择更加好的执行计划，oracle强烈建议收集系统统计信息。

oracle有两种收集系统统计的方式，一种是有负载方式和模拟一个负载(无负载方式)，使用dbms_stats.gather_system_stats过程收集系统统计信息。该过程必须有dba权限或者gather_system_statistics角色才能执行。

当有负载系统统计被收集，无负载系统统计信息被忽略，当系统刚刚启动时无负载系统统计被设为默认值。

有负载系统统计

主要包括这几个统计信息，单块和多块读时间(sreadtim和mreadtim)，连续多块读的平均块数(mbrc)，cpu速度(cpuspeed)，I/O子系统可以处理的最大系统吞吐量(maxthr)，平均并行子吞吐量(slavethr)。

使用以下两种方式收集有负载统计信息:

方法1 
在负载窗口开始处运行                                                      exec dbms_stats.gather_system_stats('start');
然后在负载窗口结束处执行
exec dbms_stats.gather_system_stats('stop');

方法2
exec dbms_stats.gather_system_stats('interval',interval=>N);
该语句表示收集接下来N分钟的系统统计信息。

无负载系统统计

无负载系统统计包括io传输速度(iotfrspeed)，io寻道时间(ioseektim)和cpu速度(cpuspeednw)。

使用dbms_stats.gather_system_stats()收集无负载系统统计信息。

动态统计信息

为了获取比较准确的估计信息，当优化器统计信息缺失时，oracle自动收集动态统计信息。oracle在解析sql语句过程使用递归sql扫描表的一小部分随机抽样数据块得到动态统计信息。

当设置数据库参数optimizer_dynamic_sampling或者在sql语句里使用该提示，这个值为动态统计级别，在oracle11g里可以设置从0到11的整数值。动态统计级别控制数据库什么时候收集动态统计信息和动态统计抽样的数据块的大小。

• 0表示禁用动态统计。
• 2为默认值，表示语句里至少有一个表没有统计信息时使用动态统计
• 11，当优化器觉得有必要使用时，就是用动态统计，该等级是从11.2.0.4新增的，优化器自动决定动态统计是否有用以及为sql语句使用那个动态统计级别。