CBO学习----03--选择率(Selectivity)
来源:互联网 发布:新华it软件学校 编辑:程序博客网 时间:2024/06/06 09:59
第3章 单表选择率(Single Table Selectivity)
Selectivity是优化器估算Rows(Cards)的重要依据。
/**************************************************************************************************************************************/
3.1 Getting Started
select count(*)from audience
where month_no = 12
;
从优化器的角度,分析1200名听众中,估算12月出生的人数
(1)user_tab_col_statistics.num_distinct=12
(2)user_tab_histograms指出low(1),high(12),均匀分布
(3)user_tab_col_statistics.density=1/12
(4)month_no=12,单列,均一,所以user_tab_col_statistics.density可用
(5)low(1)<=12<=high(12)
(6)user_tab_col_statistics.num_nulls=0,没有空值
(7)user_tables.num_rows=1200
(8)1200/12=100
本章代码附件中:
birth_month_01.sql
hack_stats.sql
birth_month_01.sql构建表,先进行两次查询系统表,后做count(*)查询两次。
两次之间可在其他session执行hack_stats.sql,修改表的统计信息,看哪些统计项能影响rows的计算
(1)将表名和列名填入hack_stats.sql,并修改表的行数numrows
define m_source_table='AUDIENCE'define m_source_column='month_no'--m_avgrlen := m_avgrlen + 25;m_numrows:=m_numrows+1200;交叉执行后,rows从100变成200
测试后将numrows复原
(2)修改distcnt和density
打开Column statistics的注释
--m_distcnt:=m_distcnt+12;m_density:=m_density/2;经多次测试,distcnt的修改不起作用,说明仅有density参与计算(Oracle版本10204)。
并非像书中所提的那样,可能9i和10gR1中,没有直方图时是用distcnt来计算rows,Oracle之后的版本又改进了
测试后将density复原
/**************************************************************************************************************************************/
begindbms_stats.gather_table_stats(user,'audience',cascade => true,estimate_percent => null,method_opt => 'for all columns size 1');end;/
其中method_opt => 'for all columns size 1',指不收集直方图,8i和9i的默认值
method_opt =>'for all columns size auto'10g默认值,可以通过下面方法读取
SQL> select dbms_stats.get_param('METHOD_OPT') from dual;DBMS_STATS.GET_PARAM('METHOD_OPT')---------------------------------------------------------FOR ALL COLUMNS SIZE AUTO已选择 1 行。
/**************************************************************************************************************************************/
3.2 Null Values
将null加入到rows的计算中假定10%的人不记得自己的生日在几月
本章代码附件中:
birth_month_02.sql
该脚本中,将120行设置为空值,最终rows=90
说明优化器排除了null对估算rows的影响;density依然是1/12,并没有改变,说明density是减去空值数量后的结果。
/**************************************************************************************************************************************/
3.3 Using Lists
select count(*)from audience
where month_no in (6,7,8)
;
开始研究month_no in (6,7,8),这种条件时,rows的计算方式
本章代码附件中:
in_list.sql
在没有重复值时,rows计算无误
/**************************************************************************************************************************************/
8i中计算错误的原因:
select count(*) from audience where month_no in (6,7,8);
select /* +use_concat */count(*) from audience where month_no in (6,7,8);
/**************************************************************************************************************************************/
本章代码附件中:
in_list_02.sql
将月份从12扩大到1000,density为0.001
获得列表3-1,用来对比8i到10g的计算值,版本间影响不大
/**************************************************************************************************************************************/
本章代码附件中:
oddities.sql
在存有重复值、空值、过界值时,计算rows的状况,10204明显有所改善,考虑了最大最小值(书中原版本未考虑过界值的问题)
仅仅在有空值时,不能正确计算rows,但也比之前版本误差小。
/**************************************************************************************************************************************/
10104的线性衰变
oracle在10.2之前采用过一个更为复杂的线性衰变算法,即超过最大值或低于最小值时,单值rows的斜率为 "1/最大值-最小值"。
通过上面10204的测试,这个算法在新版本中被取代。
本章代码附件中:
in_list_10g.sql
在10104中的结果:
10104比10102略有改进,10204比10104改进就更加明显了,虽然还有缺陷(null上),但说明oracle的opt团队在不断的更新代码。
/**************************************************************************************************************************************/
3.4 Range
本章代码附件中:ranges.sql
总结各个版本,统计如下:
从上表发现的几条规律:
(1)"(" 和 "]"的差距为100
(2)超过边界值为100
(3)有绑定变量的rows,开闭区间并无影响
(4)10-13行,单个绑定变量的range,前期版本定为5%(1200*5%=60),10204之后较为复杂(有待研究)
(5)14-18行,5%*5%=2.5/1000,1200*5%*5%=3行
(6)19-20行,超出最大最小值范围时,给出一个固定的选择率。本章代码selectivity_one.sql给出了该环节9i的测试代码。
本章代码附件中:
selectivity_one.sql
构建一个4列的nologging表,3000行;对比4个边界上的定值查询和">"查询,rows是一样的。
说明超过边界值的查询所给出的选择率,跟等于边界值查询的选择率是一样的。
/**************************************************************************************************************************************/
CARDINALITY
基数的变化,对于执行计划的后续选择至关重要
/**************************************************************************************************************************************/
绑定变量和区间
因为colx like 'A%'可以等效为colx >='A' and colx <='B';colx like :b1似乎也可以等效为between and的结构;但是他们两个的选择率是不同的,前者是5%后者是5%*5%。
本章代码附件中:
like_test.sql
构建一个10W行的表,绑定变量查询的rows为5000,正好是5%;其他定值查询都不同;但使用lower(col) like的方式,选择率也变为5%。
说明opt处理谓词表达式时,是按照变量来处理的,且为单个变量。
/**************************************************************************************************************************************/
Selectivity = “required range” / “total available range”
下面通过几个Case,来验证上面公式,并得出前面列表的结果。
Case 1
month_no > 8
(8,)
Selectivity = (high_value – limit) / (high_value – low_value) = (12 – 8) / (12 – 1) = 4/11
Cardinality = 1,200 * 4 / 11 = 436.363636
Case 2
month_no >= 8
[8,)
Selectivity = (high_value – limit) / (high_value – low_value) + 1/num_distinct = 4/11 + 1/12
Cardinality = 1,200 * (4/11 + 1/12) = 536.363636
Cases 3 and 4
Selectivity (3) = (limit – low_value) / (high_value – low_value) = (8 – 1) / (12 – 1) = 7/11
Selectivity (4) = (8 – 1) / (12 – 1) + 1/12 = 7/11 + 1/12
Cases 5 and 6
month_no between 6 and 9
Selectivity = (9 – 6) / (12 – 1) + 1/12 + 1/12 --( >= , <= )
Cases 7, 8, 9
Selectivity (7) = (9 – 6) / (12 – 1) + 1/12 --( >= , < )
Selectivity (8) = (9 – 6) / (12 – 1) + 1/12 --( > , <= )
Selectivity (9) = (9 – 6) / (12 – 1) --( > , <)
/**************************************************************************************************************************************/
BIND VARIABLE PEEKING(绑定变量窥视)
绑定变量可以共享游标,使OLTP减少了大量硬解析;但本章中也看出了它的一个弊端(引起rows的计算错误,统统5%)
因此,9i引入了绑定变量窥视
在绑定变量的SQL语句,第一次执行时,窥视一下变量的值,给出正确的rows,并选择最优路径,之后提炼出可与后面语句共享的部分,无论绑定变量是否变化,都会使用该共享。
在变量为字符串类型时,字符串长度变化较大时,执行计划会有所变化。
具体如何变化有待研究
/**************************************************************************************************************************************/
range上10104的更新
本章代码附件中:
ranges_10g.sql
正如前面提到的,在10104,超出边界值时,默认采用一个斜率为 "1/最大值-最小值" 的线性衰变
但在10204上又将默认改回去了
具体是哪个参数控制的,有待研究
/**************************************************************************************************************************************/
3.5 Two Predicates(双谓词)
wheremonth_no > 8
or month_no <= 8
此时计算的rows=986
本章代码附件中:
ranges_02.sql
类似上面的条件,列出了1-12的所有rows
优化器仅仅将条件判断为:两个由or连接起来的谓词
selectivity(predicate1 AND predicate2) = selectivity(predicate1) * selectivity(predicate2)
selectivity(predicate1 OR predicate2) = selectivity(predicate1) + selectivity(predicate2) - selectivity(predicate1 AND predicate2)
selectivity(NOT predicate1) = 1 – selectivity(predicate1)
本章代码附件中:
two_predicate_01.sql
解释了上面两个谓词时的运算
bind_between.sql
分析了绑定变量下,两个谓词时的选择率
/**************************************************************************************************************************************/
3.6 多个谓词的相关问题
计算一下下面的选择率where
month_no > 8 -- (predicate 1)
or month_no <= 8 -- (predicate 2)
selectivity(predicate1)简称s(p1),以此类推
s(p1) = (12 – 8) / (12 – 1) = 4/11
s(p2) = (8 – 1) / (12 – 1) + 1/12 = 7/11 + 1/12
s(p1 and p2) = 4/11 * (7/11 + 1/12)
s(p1 or p2) = s(p1) + s(p2) - s(p1 and p2) = 4/11 + 7/11 + 1/12 - 4/11 * (7/11 + 1/12) =0.8216
1200*0.8216=986
/**************************************************************************************************************************************/
三个谓词时会如何呢?
month_no in (6,7,8)就类似于三个谓词被or连接
sel(A or B or C) = sel(A) + sel(B) + sel(C) – Sel(A)sel(B) – Sel(B)sel(C) – sel(C)sel(A) + Sel(A)Sel(B)Sel(C)
带入month_no in (6,7,8)会得出相应的rows=276
如果一个表中,不同列有依赖关系时,如何计算选择率呢?例如月份列+星座列
9i动态采样、10gfrofile (有待研究)
- CBO学习----03--选择率(Selectivity)
- (4)选择率(selectivity)的相关问题
- (1)oracle单表选择率(selectivity)——计算执行计划的基数
- CBO学习----02--表扫描(Tablescans)
- ORACLE学习笔记(一) -- RBO 和 CBO
- How to measure Index Selectivity (索引区分度)
- CBO(基于规则的优化方式)模式下优化器模式的选择
- CBO学习笔记9:histograms --part1
- CBO参数学习之查询隐藏参数
- oracle CBO优化器何时会选择全表扫描
- MySQL5.6的10053,CBO如何选择执行计划
- 索引的Selectivity
- 直方图(Histogram)对CBO的影响
- Oracle优化器(RBO与CBO)
- Oracle 优化器(RBO,CBO)
- CBO和RBO介绍(好)
- Oracle优化器(RBO与CBO)
- Oracle优化器(RBO与CBO)
- Linux配置Android环境。
- POJ--3253 -- Fence Repair
- spring filter 过滤器 注入 对象 属性
- RAC-RAC双向DDL同步
- SVN设置强制提交者填写注释信息
- CBO学习----03--选择率(Selectivity)
- js判断浏览器
- awk 编程
- C++中覆盖、重载、隐藏的概念
- Linux mysql安装无法启动 /etc下无mysql配置文件,mysql 无法启动,/var/run/mysqld/mysqld.sock找不到
- kernel常用.gitignore配置
- 可变长参数列表误区与陷阱——va_arg不可接受的类型
- JavaScript中setTimeout()方法和setInterval()的区别
- C++构造和析构以及虚函数应用