首页 > Oracle > Oracle 优化一之基数,选择性,直方图。
2013
08-29

Oracle 优化一之基数,选择性,直方图。

基数(Cardinality)列唯一键(Distinct_keys)的数量,比如性别,该列只有男女之分,所以这一列基数是2。主键列的基数等于行数(主键列不允许重复)。
选择性(Selectivity)列唯一键(Distinct_Keys)与行数(Num_Rows)的百分比值。
直方图当某列数据分布不均衡,为了让CBO能生成最佳的执行计划,我们可能需要对表收集直方图,直方图最大的桶数(Bucket)是254。收集直方图是一个很耗时的过程,如无必要,千万别去收集直方图。在11G之前,收集直方图会带来一个隐患,如果有绑定变量,收集直方图会造成绑定变量窥探。

1. 查看表的真实基数和选择性:
第一种直接从表中查出基数和选择性,但生产环境,如果表很大的话,查询的时候导致buffer cache不够用,会导致大量的I/O,会对数据库产生一定的影响
select count(distinct column_name),
count(*) total_rows,
count(distinct column_name) / count(*) * 100 selectivity
from table_name;
第二种方法,首先我们应该看下该表的大小,如果很大的表,超过了SGA的buffer cache很大,那生产环境就用这种办法了,根据统计信息来查看
select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = upper(‘&owner’)
and a.table_name = upper(‘&table_name’)
and a.column_name = upper(‘&column_name’);
上面的第二种方法依赖于统计信息,如果统计信息收集过期,查询出来的基数,选择性也没有参考意义。
下面我们来简单测试一下:
1. 使用scott用户创建test表:
create table test as select * from dba_objects;
2. 收集test表的统计信息:

3. 查询表的基数,选择性

这里我们可以看到,当桶数(num_buckets)小于254的时候,num_buckets=cardinality,也就是基数和桶数一致,每一个桶记录了一个桶有多少行,比如OWNER这一行,桶数为31,基数为31,每个桶只有一行,但object_name这一行,桶数为254(最大就是254),基数为53773,这里oracle就不能100%准确的判断有多少行,但由于统计信息,oracle通过算法可以得知99%的准确率。如果基数低于254,由于没有操作最大桶数254,所以oracle可以100%得知该列行数。
4. 下面创建一个索引在owner上,由于owner上选择性很低,我们来看一下索引使用情况
create index idx on test(owner);
5. 执行sql查询owner=’SYS’的值,打开执行计划,看一下执行计划的情况

这里我们可以看到,在执行计划部分,执行了全表扫描,返回41897行满足条件的数据,在Statistics也可以看到最终返回41897行(41897 rows processed)。
6. 执行sql查询owner=’SCOTT’的值,看下执行计划的情况

这里只返回了42行数据,使用索引扫描,看来oracle判断还是非常准确的,这是在统计信息,收集直方图的情况下,oracle判断还是很精确。
7. 下面我们再看一下没有统计信息的情况,oracle的判断,执行如下sql,不收集统计信息

8. 查看一下现在表的基数和选择性收集情况

如上我们可以看出所有的列都没有直方图了,然后我们再来看一下查询情况。
9. 执行sql查询owner=’SYS’的值

这里可以看到,执行计划选择了索引扫描,执行计划估算返回的行为2954行,但实际返回41897,估算不准确,这里oracle估算返回2954行=实际表总行数(91579)/该列基数(31)
10. 我们再来看下执行sql查询owner=’SCOTT’的值

这里实际返回42行,但oracle估算还是返回2954行。
由于我们后面没有收集直方图,oracle会认为列数据分布是均衡的,无论查看sys还是scott,估算的结果都是2954,所以走了索引扫描。
所以如果不收集直方图,oracle认为某个列分布永远都是均衡的。
最后编辑:
作者:Jerry
一个积极向上的小青年,热衷于分享--Focus on DB,BI,ETL