oracle stalestats_深入理解oracle优化器统计数据(Optimizer Statistics)

理解oracle优化器统计数据

首先来介绍oracle数据库使用基于规则优化器(RBO)来决定如何执行一个sql语句.基于规则优化器顾名思义,它是遵循一组规则来判断一个sql语句的执行计划.这组规则是有排名的如果有两种可能的规则应该应用于一个sql语句,那么排名较低的规则会被使用

在oracle 7中,引入了基于成本的优化器使用优化器的功能得以增强包括并行执行,分区,还会考虑用户真实数据的内容和分布情况.基于成本的优化器会检查一个sql语句的所有执行计划并选择一个成本值最低的,这里的成本代表一个指定执行计划所要消耗的资源使用情况.一个执行计划成本值越低该执行计划越有效.为了让基于成本的优化器精确的判断一个执行计划的成本它必需有描述该语句所访问的所有对象(表和索引)的信息和描述运行这个语句的系统信息.这些需要的信息通常被称为优化器统计数据.理解和管理优化器统计数据是优化sql执行的关键.知道何时以及及时的怎样收集统计数据对于维护一个稳定的性能来说很关键.优化器统计数据包括以下方面的信息:

什么是优化器统计数据

收集统计数据

管理统计数据

其它类型的统计数据

什么是优化器统计数据

优化器统计数据是一组描述数据库和数据库中对象信息的集合.这些统计数据在优化器给每一个sql语句选择一个最优的执行计划时会被使用.统计数据存储在数据字典中且它们能通过数据字典视图比如象user_tab_statistics来进行访问.优化器统计数据与v$视图来查看的性能统计数据不同.v$视图中的信息是系统状态和执行sql工作负载的信息.

表和列统计数据

表统计数据包括了表中的行记录数,表用来存储这些数据的数据块数,表中的平均行长度等信息.优化器使用这些信息并结合其它的统计数据来计算执行计划中各个操作的成本并评估每一个操作将返回的行数.例如,一个表扫描的成本是使用表所使用的数据块数和参数db_file_multiblock_read_count来计算出来的.可以查询user_tab_statistics视图来查看表的统计数据.

列统计数据包括一个列中的不同值的个数(NDV)还有在这个列中的最小值/最大值.可能查询user_tab_col_statistics视图来查看这列的统计数据.优化器使用列统计数据并结合表的统计数据(行数)来评估一个sql操作将要返回的行数.例如一个表有100行记录且对一个有10个不同值的列使用等号谓词评估,那么优化器会假设统一的数据分布,那么评估的基数等于表的记录数除以这个列不同值的个数:100/10=10.

额外的列统计数据

基本表和列统计数据没有提供一种机制来告诉优化器关于表中或列中的数据的特性.例如,这些统计数据不能告诉优化器表中的列中的数据是否有倾斜或者列之间是否存在关联.数据特性的信息可以通过扩展基本的统计数据象,直方图,列组和表达式统计数据提供给优化器.

直方图

直方图告诉优化器关于列中的数据分布情况.缺省情况(没有直方图),优化器会假设一个列中的不同值会均匀分布的.同上所述,优化器评估一个等号谓词的基数是通过这个表中的行数除以这个等号谓词列中不同值的个数得到的.如果数据分布不是均匀的(比如数据倾斜),那么这样的基数评估就会出错.为了精确的反映非均匀的数据分布就需要对列创建直方图.直方图的存在改变了优化器评估基数的公式且会让优化器生成一个更精确的执行计划.

Oracle基于列的使用信息(SYS.COL_USAGE$)和数据的倾斜情况能自动判断这个列是否需要直方图.例如只在一个等号谓词中看到的唯一性列oracle是不会对这个唯一性列自动创建直方图.

有两种类型的直方图,频率直方图和高度平衡直方图.oracle会基于列中的不同值的个数来决定创建直方图的类型.

频率直方图

当列中的不同值的个数据小于254时会创建频率直方图.oracle使用下面的步骤来创建频率直方图:

1.      让我们假设oracle正对promotions表的promo_category_id创建一个频率直方图.第一步从promotions表中通过对promo_category_id排序来查询promo_category_id

2.      每一个promo_category_id被指派到它所属的直方图bucket中

在这个步骤中可能直方图的bucket数超过254,因此拥有相同值的bucket会被压缩到使用这个值的最高bucket中.在这种情况下,bucket 2到 115会被压缩到bucket 115,且bucket 484到503会被压缩到bucket 503直到总的bucket数仍然保持与列中不同值的个数相等.注意上面的步骤是出于演示.dbms_stats包对直接构造压缩直方图进行了优化.

优化器现在使用频率直方图可以精确的判断谓词promo_category_id的基数.例如,对于谓词promo_category_id=10,优化器首先需要判断在直方图中10作为end point的bucket的数量.通过找到endpoint为10的bucket 503,然后减去前面的bucket数,bucket 483,503-483=20.然后基数评估将使用下面的公式进行计算(number of bucket endpoints/ total number of bucket) * num_rows,20/503*503,所以在promotoins表中promo_category_id=10的记录有20行.

当列中的不同值的数理超过254时就会创建高度平衡直方图.在高度平衡直方图中,列值被划分到bucket中所以每一个bucket可能包含相同数量的行.oracle使用下面的步骤来创建一个高度平衡直方图.

1.      我们假设oracle将要对customers表中的cust_city_id列创建一个高度平衡直方图因cust_city_id列的不同值的数量超过了254.与频率直方图类似,第一步是执行ordered by cust_city_id子句来查询customers表中的cust_city_id.

2.      在customers表中有55500行直方图中最多有254个bucket.为了让每一个bucket中有一个相等的行数,oracle必须在每一个bucket中存入219行.第219行的cust_city_id将成为第一个bucket的endpoint在这种情况下是51043.第438行的cust_city_id将成为第二个bucket的endpoint并且一直到所有254buckets被填满为止.

3. 一旦bucket创建完成后oracle会检查是否第一个bucket的endpoint值是否是cust_city_id列中的最小值,如果不是,一个”zero”bucket会被添加到直方图中而且cust_city_id列中的最小值作为它的endpoint.

4. 与频率直方图一样最后的步骤是压缩高度平衡直方图并且删除有重复endpoint的bucket.在cust_city_id列的高度平衡直方图中51166是bucket24和bucket25的endpoint.因此bucket24将会被压缩到bucket 25中

5. 使用高度平衡直方图现在优化器能对谓词cust_city_id列进行更好的基数评估.例如,对于谓词cust_city_id=51806,优化器首先会检查在直方图中使用51806作为endpoint的有多少个bucket.在这种情况下136,137,138和139的endpoint是51806(查看user_histograms).优化器将会使用下面的计算公式:

(Number of bucket endpoints / total number of buckets) *  number of rows in the table

在这种情况下: 4/254 * 55500=874

然而如果谓词cust_city_id=52500它不是任何bucket的endpoint值那么优化器会使用一个不同的计算公式.对于只是一个bucket或者不是任何bucket的endpoint的值优化器将使用下面的计算公式:

DENSITY  * number of rows in the table

这里的density是对直方图使用内部算法计算出来的.density的值可以通过查看user_tab_col_statistics得到这个值是从oracle database10.2.0.4之前使用.这个值是为了向后兼容,这个值在oracle database 9i和前期的oracle database10g中使用.因此如果optimizer_features_enable被设置的版本比10.2.0.4前那么视图中的density值将会被使用.

扩展统计数据

在oracle database11g中引入了扩展列统计数据.扩展统计数据包括另外两种额外类型的统计数:列组和表达式统计数据.

列组

在真实的数据中,在相同表中存储在不同列中的数据之间通常是有关联的.例如,在customers表中,cust_state_province列会受country_id列的影响,比如当state为California那么country只能是United States.只使用基本的列统计数据优化器是没有办法知道真实数据之间的关系如果一个语句中的where子句中有多个列来自同一个表那么有可能会计算出错误的基数.通过将这些列作为一个团