Oracle性能优化之oracle里表、索引、列的统计信息

liukun2009

一、表的统计信息
　　表的统计信息用于描述表的详细信息，包括记录数（num_rows）、表块的数量（blocks）、平均行长度（avg_row_len）等典型维度。这些维度可以通过数据字典表DBA_TABLES、DBA_TAB_PARTITIONS和DBA_TAB_SUBPARTITIONS来分别查看表、分区表的分区和分区表的子分区的统计信息。
二、索引的统计信息
　　索引的统计信息描述了索引的详细信息，它包含了索引的层级（blevel）、叶子块数量（leaf_blocks）、聚簇因子（clustering_factor）等典型维度。这些维度可以通过数据字典视图DBA_INDEXES、DBA_IND_PARTITIONS和DBA_IND_SUBPARTITIONS来分别查看索引、分区索引的分区、局部分区索引的子分区的统计信息。
1、层级（level）
　　层级表示从根节点到叶子块的深度，层级被CBO用于计算访问索引叶子块的成本，层级越大，表示从根节点到叶子块所需要访问的数据块的数量就越多，耗费的i/o就会越多，索引访问的成本就会越大。在数据库里如果需要降低索引的层级，需要rebuild才可以。
2、聚簇因子的含义及重要性
　　oracle数据库中，聚簇因子是指按照索引键值排序的索引行和存储于对应表中的数据行的存储顺序的相似程度。oracle数据库按照如下算法计算聚簇因子：
　　（1）聚簇因子初始值为1.
　　（2）oracle首先定位到目标索引处于最左边的叶子块。
　　（3）从最左边叶子块的第一个索引键值所在的索引行开始顺序扫描，在顺序扫描的过程中，oracle会比较当前索引行的rowid和之前那个索引行的rowid，如果这两个rowid并不是指向同一个表块，那么oracle就将聚簇因子的当前值递增1；如果这两个rowid是指向同一个表块，oracle就不改变聚簇因子的值。oracle在比对rowid时并不会回表去访问相应的表块。
　　（4）上述的比对过程会持续下去，知道扫描完目标索引的所有索引块的所有索引行。
　　（5）上述顺序扫描完成后，聚簇因子的当前值就是索引统计信息中的clustering_factor，oracle将其存储在数据字典里。
　　由以上的过程可知：聚簇因子高的索引走索引范围扫描时比相同条件下聚簇因子低的索引要耗费更多的物理i/o，所以聚簇因子高的索引走索引范围扫描的成本会比相同条件下聚簇因子低的索引走索引范围扫描的成本高。即聚簇因子越小越好。
　　oracle数据库中，能够降低聚簇因子的唯一方法就是对表中数据按照目标索引的索引键值排序后重新存储。
　　oracle数据库里，cbo在计算索引范围扫描（index range scan）的成本计算公式入下：
　　（*）IRS COST=I/O COST+CPU COST
　　（*）I/O COST=INDEX ACCESS I/O COST+TABLE ACCESS I/O COST
　　（*）index access i/o cost=blevel_celt(#leaf_blocks*ix_sel)
　　（*）table access i/o cost=celt(clustering_factor*ix_sel_with_filters)
　　从这个公式可以推断出走索引范围扫描的成本可以近似看作是与聚簇因子成正比。因此，聚簇因子值得大小实际对CBO判断是否走相关索引起着至关重要的作用。
3、列的统计信息
　　oracle里列的统计信息用于描述oracle数据库里列的详细信息，包括列的distinct值（num_distinct）、列的null值（num_nulls）得数量、列的最小值（low_value）、列的最大值（high_value）等一些典型维度。可以通过数据字典dba_tab_col_statistics、dba_part_col_statistics和dba_subpart_col_statistics分别查看表、分区表的分区、分区表的子分区的列的统计信息。
　　（1）列的distinct值（上述数据字典中字段num_distinct表示distinct值数量），cbo用num_distinct值来计算目标列做等值查询时的可选择率。
　　（2）上述字典中的字段num_nulls存储的就是目标列的null值数量，cbo用num_null值来评估对目标列施加“is null”或“is not null”条件后的返回结果集cardinality。另外cbo还用num_nulls值来调整对有null值得目标列做等值查询时的可选择率selectivity。
　　对目标列进行等值查询时可选择率计算公式：selectivity=(1/num_distinct)*((num_rows-num_nulls)/num_rows)
　　（3）上述字典的列low_value和high_value值就是目标列的最小值和最大值，cbo通过low_value和high_value来计算目标列进行范围查询时可选择率selectivity的值。
　　没有直方图，目标列范围查询可选择率计算公式（略）。
3.1直方图（histogram）
1）直方图含义
　　在oracle数据库中，CBO会默认认为目标列的数据在最小值low_value和最大值high_value之间是均匀分布的，并且会根据这个均匀分布的原则来计算对目标列施加查询条件后的可选择率以及结果集的cardinality，进而据此来计算成本值并且选择执行计划。但是目标列的数据是均匀分布的这个原则并不总是准确的，在实际的系统中，我们很容易看到一些目标列的数据分布式不均匀的，甚至是极度倾斜的，分布极不均匀。对这样的列如果还按照均匀分布的原则去计算可选择率和结果集，并据此来计算成本，选择执行计划，那么CBO所选择的执行计划就可能是不合理的，甚至是错误的。
　　对于上述问题，oracle引入了直方图。直方图是一种特殊的列的统计信息，它描述了目标列的数据分布情况。可以通过数据字典视图DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS和DBA_SUBPART_HISTOGRAMS来分别查看表、分区表的分区和分区表的子分区的直方图统计信息。
　　如果对目标列收集了直方图，则意味着CBO将不再认为该列上的数据是分布不均匀的，CBO就会用该目标列上的直方图统计信息来计算对该列施加查询条件后的可选择率和返回的结果集的cardinility，进而据此计算成本并选择相应的执行计划。即直方图就是oracle为了专门准确评估这种目标列分布不均匀的可选择率和结果集cardinility的方法。
2）直方图类型
　　oracle数据库里的直方图使用了一种称为bucket的方式来描述目标列的数据分布。根据bucket的数量，直方图分为2类：
　　（1）frequency类型直方图：存储在数据字典里用于描述目标列直方图的bucket数量等于目标列的distinct数量。
　　oracle直方图针对于文本类型的列收集直方图统计信息，则oracle只会将该文本字段头32位字符取出来，并将其转换成一个浮点数。然后将这个浮点数作为直方图统计信息存储在数据字典里。
　　（2）heigh balanced类型直方图：存储在数据字典里用于描述目标列直方图的bucket数量小于目标列的distinct数量。