首页 > 代码库 > Oracle性能分析12：对象统计信息
Oracle性能分析12：对象统计信息
对象统计信息描述数据是如何在数据库中存储的，查询优化器使用这些统计信息来做出正确的决定。Oracle中有三种类型的对象统计信息：表统计、列统计和索引统计。而在每种类型中，有细分为：表或索引级别的统计、分区级别统计和子分区级别的统计，后面两种只有在对象被分区和具有子分区的情况下才可用。
统计信息相关视图
表统计信息
表/索引级别的统计
user_tab_statistics
user_tables
分区级别的统计
user_tab_statistics
user_tab_partitions
子分区级别统计
user_tab_statistics
user_tab_subpartitions
列统计信息
表/索引级别的统计
user_tab_col_statistics
user_tab_histograms
分区级别的统计
user_part_col_statistics
user_part_histograms
子分区级别统计
user_subpart_col_statistics
user_subpart_histograms
索引统计信息
表/索引级别的统计
user_ind_statistics
user_indexes
分区级别的统计
user_ind_statistics
user_ind_partitions
子分区级别统计
user_ind_statistics
user_ind_subpartitions
创建测试表
这里将创建测试表T用于后面对统计信息的说明。
创建测试表
create table test as select rownum as id, round(dbms_random.normal * 1000) as val1, 100 + round(ln(rownum / 3.25 + 2)) as val2, 100 + round(ln(rownum / 3.25 + 2)) as val3, dbms_random.string('p', 250) as pad from all_objects where rownum <= 1000 order by dbms_random.value上面的语句创建了一个1000行的表，然后我们将val1列中的负值清空：
update test set val1 = null where val1 < 0;
为测试表添加主键和索引
alter table test add constraint test_pk primary key (id);create index test_val1 on test (val1);create index test_val2 on test (val2);
为测试表收集统计信息
begin dbms_stats.gather_table_stats(ownname => user, tabname => 'TEST', estimate_percent => 100, method_opt => 'for all columns size skewonly', cascade => TRUE);end;
表统计信息
下面是表统计信息中的关键字段：
select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len from user_tab_statistics where table_name = 'TEST'; NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN----------------------------------------------------------------------------------1000 39 0 0 0 265
下面是对字段含义的解释：
1）num_rows：表中数据的行数；
2）blocks：高水位线以下的数据块个数（高水位线详见“Oracle性能分析4：数据访问方法之全扫描”http://blog.csdn.net/tomato__/article/details/38981425）；
3）empty_blocks：高水位线以上的数据块个数，由于dbms_stats不计算该值，因此为0；
4）avg_space：表里数据块的平均空闲空间（字节），由于dbms_stats不计算该值，因此为0；
5）chain_cnt：涉及行链接和行迁移的总行数，由于dbms_stats不计算该值，因此为0（详见“Oracle行迁移和行链接”http://blog.csdn.net/tomato__/article/details/40146573）；
6）avg_row_len：表中平均每个记录的长度（字节）。
列统计信息
下面是列统计信息的最重要的统计信息字段：
select column_name, num_distinct, low_value, high_value, density, num_nulls, avg_col_len, histogram, num_buckets from user_tab_col_statistics where table_name = 'TEST';
下面是对这些字段的解释：
1）num_distinct：该列中不同值的数量；
2）low_value：该列的最小值。显示为内部存储的格式，对于字符串列只存储前32字节；
3）high_value：该列的最大值。显示为内部存储的格式，对于字符串列只存储前32字节；
4）density：0到1之间的一个小数。接近0表示对于列的过滤操作能去掉大多数行。接近1表示对于该列的过滤操作起不到什么作用。
如果没有直方图，该值的计算方法为：density=1/num_distinct。
如果有直方图，则根据不同的直方图类型有不同的计算方法。
5）num_nulls：该列中存储的NULL的总数；
6）avg_col_len：平均列大小，以字节表示；
7）histogram：表明是否有直方图统计信息，值包括：NONE（没有）、FREQUENCY（频率类型）和HEIGHT BALANCED（平均分布类型）；
8）num_buckets：直方图里的bucket的数量，最小为1，最大为254。
注：low_value和high_value表示为内部存储的格式，下面的存储过程可以得到test表的所有列的最大最小值：
declare l_val1 test.val1%type;begin for v in (select low_value, high_value from user_tab_col_statistics where table_name = 'TEST') loop dbms_stats.convert_raw_value(v.low_value, l_val1); dbms_output.put_line('low value : ' || l_val1); dbms_stats.convert_raw_value(v.high_value, l_val1); dbms_output.put_line('low value : ' || l_val1); end loop;end;直方图
查询优化器需要找到满足条件的数据行数，如果列的数据均匀分布的，则很容易根据最小值、最大值和唯一值总数就可以计算得到，这些信息在列统计信息中就可以得到。但如果数据不是均匀分布的，查询优化器则需要额外的信息才能做出正确估算。
这些查询优化器需要的关于数据不均匀分布的额外信息叫做直方图，存在两种类型的直方图：频度直方图（frequency histogram）和等高直方图（height-balanced histogram）。
频度直方图
频度直方图的本质特性如下：
1）桶数（即分类数）等于唯一值总数。对于每个桶来说，视图user_tab_histograms有一行数据与之对应；
2）列endpoint_value提供该值本身。该列为number类型，应此非数字类型的列必须要进行转换，只取前六个字节。这意味着直方图中存储的值的分布是基于列的前面部分，因而，固定前缀的字符串会使直方图的分布严重不均衡；
3）列endpoint_number是取值的累积出现次数，当前的endpoint_number减去上一个endpoint_number，就是当前行这个值的出现次数。
通过下面的方式就可以得到列val2的频次：
select column_name, endpoint_value, endpoint_number, endpoint_number - lag(endpoint_number, 1, 0) over(order by endpoint_number) as frequency from user_tab_histograms where table_name = 'TEST' and column_name = 'VAL2' order by endpoint_numberCOLUMN_NAME ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY-------------------------------------------------------VAL2 101 8 8VAL2 102 33 25VAL2 103 101 68VAL2 104 286 185VAL2 105 788 502VAL2 106 1000 212
下面用test表作为一个例子说明优化器怎样利用频度直方图精确估算查询返回的行数：
explain plan set statement_id '101' for select * from test where val2 = 101;explain plan set statement_id '102' for select * from test where val2 = 102;explain plan set statement_id '103' for select * from test where val2 = 103;explain plan set statement_id '104' for select * from test where val2 = 104;explain plan set statement_id '105' for select * from test where val2 = 105;explain plan set statement_id '106' for select * from test where val2 = 106;
然后我们查看执行计划对返回行数的估算：
select statement_id,cardinality from plan_table where id = 0;STATEMENT_ID CARDINALITY---------------------------------- 101 8 102 25 103 68 104 185 105 502 106 212
等高直方图
当一列的唯一值数量总是大于桶的允许最大数量（254）时，就不能使用频度直方图了，这是就只能使用等高直方图了。
等高直方图的主要特征如下：
1）桶数少于唯一值总数。除非被压缩，否则对应于每个桶，视图user_tab_histograms里都有一个包含端点号（endpoint number）的行与之对应，端点号0表明最小取值；
2）端点值（endpoint_value）就是列的数值。因为该列是number类型，非数字类型必须进行转换，此值仅取前六个字节；
3）endpoint_number列给出了桶号；
4）直方图不存储一个取值的频度。
等高直方图只存储列值属于某一个桶，如果有两个列值位于同一个桶，则其中一个将被忽略（压缩），这样的统计就可能导致估算不准确。在实践中，等高直方图不但可能导致错误的估算，还可能引起查询优化器估值的不稳定。
索引统计信息
下面的查询可以得到索引统计信息：
select index_name, blevel, leaf_blocks, distinct_keys, num_rows, clustering_factor, avg_leaf_blocks_per_key, avg_data_blocks_per_key from user_ind_statistics where table_name = 'TEST';
主要字段的含义如下：
1）blevel：为了访问叶子块而需要读取的分支块的数量，包括根块；
2）leaf_blocks：索引中的叶子块数；
3）distinct_keys：索引中的唯一键值总数；
4）num_rows：索引中的键值数；
5）clustering_factor：见“Oracle性能分析8：使用索引”http://blog.csdn.net/tomato__/article/details/39294655；
6）avg_leaf_blocks_per_key：存放一个键值的平均叶子块数，公式如下；
avg_leaf_blocks_per_key = leaf_blocks/distinct_keys
7）avg_data_blocks_per_key：表中单个键引用的平均数据块数，公式如下：
avg_data_blocks_per_key = clustering_factor/distinct_keys
Oracle性能分析12：对象统计信息