本帖最后由 王硕 于 2015-12-24 15:33 编辑
第二部分,基于成本的优化器
RBO可能会选择错误的执行计划,因为没有考虑目标SQL中所涉及的对象的实际数据量、实际数据分布等情况。
从7开始,引入CBO。成本是指ORACLE根据相关对象的统计信息计算出来一个值,它实际上代表了ORACLE根据相关统计信息估算出来的目标SQL的对应执行步骤 的I/O ,CPU 和网络资源的消耗量。
集的势(Cardinality) ,集合所包含的记录数,也就是结果集的行数。也就是针对整个目标SQL,此时Cardinality对该SQL最终执行结果所包含记录数的估算。某个执行步骤所对应的Cardinality值越大,那么其成本往往越大。
可选择率(Selectivity),简单说就是where后选择出来的数据占所有查询数据的百分比。
设置一个列为非空: alter table emp modify (mgr not null);
创建索引: create index idx_emp_mgr on emp(mgr);
使用DBMS_STATS包来对 表EMP、表EMP的所有的列、表EMP上的所有索引做统计信息:
exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'EMP',estimate_percent=>100,cascade => true,method_opt =>'for all columns size 1',no_invalidate => false);
讲索引IDX_EMP_MGR对应其索引页数据的统计信息修改为10万:
exec dbms_stats.set_index_stats(ownname=>'SCOTT',indname=>'IDX_EMP_MGR',numlblks=>100000,no_invalidate=>false);
可传递性(Transitivity)
利用可传递性对目标SQL做简单的等价改写仅仅试用于CBO。
1. 简单的谓语传递
t1.c1=t2.c1 and t1.c1=10 等价于 t1.c1=t2.c1 and t1.c1=10 and t2.c1=10
2. 连接谓语传递
t1.c1=t2.c1 adn t2.c1=t3.c1 等价于 t1.c1=t2.c1 and t2.c1=t3.c1 and t1.c1=t3.c1
3. 外连接谓语传递
t1.c1=t2.c1(+) and t1.c1=10 等价于 t1.c1=t2.c1(+) and t1.c1=10 and t2.c1(+)=10
CBO 的局限性:
1. CBO默认SQL语句WHERE条件中出现的各个列之间是独立的,没有关联:(动态采样,和多列统计信息两个解决方案也不能完美解决问题)
2. CBO会假设所有SQL都是单独执行的,并且互不干扰:(目标SQL执行所用到的 索引叶子块、数据块,可能已经被缓存在了Buffer Cache 中, 不需要重新获取相关信息)
3. CBO对直方图统计有诸多限制:
(1)12C 以前Frequency 类型的直方图对应的Bucket的数量不能超过254,如果超过就会使用Height Balanced 类型的直方图。Height Balanced类型的直方图不会记录所有的 nonpopular value值,可能会执行错误的执行计划。
(2)针对文本型的字段收集直方图统计,会去取文本值得头32个字节(实际是是15个字节),并将其换成一个浮点数并存储在直方图统计中,对于超过32 字节的字段,只要前32位相同,就会默认为后面的文本值也相同,并作出错误的判断。
4. CBO在解析多变关联的SQL,可能会漏选正确的执行计划: 多表关联会出现几何级数的连接方法,在11gR2中受隐含参数
_optimizer_max_permutations限制,只会考虑下面的2000 的值得连接方式。
SQL> select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from x$ksppi a, x$ksppcv b
where a.indx = b.indx and a.ksppinm like '_optimizer_max_permutations';
NAME VALUE
-------------------------------------------------------------------------------- -------------------
_optimizer_max_permutations 2000
|