CBO学习----03--选择率(Selectivity)

第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复原

/**************************************************************************************************************************************/

begin
dbms_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明显有所改善,考虑了最大最小值(书中原版本未考虑过界值的问题)

条件 8i 92/10102 10204
month_no = 25  100 100 1
month_no in (4, 4) 100 100 100
month_no in (3, 25)  192 200 100
month_no in (3, 25, 26)  276 300 101
month_no in (3, 25, 25, 26)  276 300 101
month_no in (3, 25, null)  276 300 200
month_no in (:b1, :b2, :b3)  276 300 300

仅仅在有空值时,不能正确计算rows,但也比之前版本误差小。

/**************************************************************************************************************************************/

10104的线性衰变

oracle在10.2之前采用过一个更为复杂的线性衰变算法,即超过最大值或低于最小值时,单值rows的斜率为 "1/最大值-最小值"。

通过上面10204的测试,这个算法在新版本中被取代。

本章代码附件中:

in_list_10g.sql

在10104中的结果:

条件 10102 10104
month_no = 13  100 91
month_no = 15  100 73
month_no in (13,15) 200 164
month_no in (16,18) 200 109

10104比10102略有改进,10204比10104改进就更加明显了,虽然还有缺陷(null上),但说明oracle的opt团队在不断的更新代码。

/**************************************************************************************************************************************/

3.4 Range

本章代码附件中:

ranges.sql

总结各个版本,统计如下:

谓词 8i 92/10102 10204 数学表达式 真实值
1 month_no > 8  437 436 436 (8, 400
2 month_no >= 8  537 536 536 [8, 500
3 month_no < 8  764 764 764  ,8) 700
4 month_no <= 8  864 864 864  ,8] 800
5 month_no between 6 and 9  528 527 527 [6,9] 400
6 month_no >= 6 and month_no <= 9  528 527 527 [6,9] 400
7 month_no >= 6 and month_no < 9  428 427 427 [6,9) 300
8 month_no > 6 and month_no <= 9  428 427 427 (6,9] 300
9 month_no > 6 and month_no < 9  328 327 327 (6,9) 200
10 month_no > :b1  60 60 101 (:b1,            
11 month_no >= :b1  60 60 101 [:b1,            
12 month_no < :b1  60 60 101  ,:b1)           
13 month_no <= :b1  60 60 101  ,:b1]           
14 month_no between :b1 and :b2  4 3 9 [:b1,:b2]        
15 month_no >= :b1 and month_no <= :b2  4 3 9 [:b1,:b2]        
16 month_no >= :b1 and month_no < :b2  4 3 9 [:b1,:b2)        
17 month_no > :b1 and month_no < :b2  4 3 9 (:b1,:b2)        
18 month_no > :b1 and month_no <= :b2  4 3 9 (:b1,:b2]        
19 month_no > 12  100 100 100 (12, 0
20 month_no between 25 and 30  100 100 100 [25,30] 0

其中8i的4和9i的3,是没有区别的(10进制和二进制转换时有个小数),只是8i在计算式使用上浮,9i用的舍入

从上表发现的几条规律:

(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

条件 10102 10104 10204
month_no between 6 and 9  527 527 527
month_no between 14 and 17 100 82 100
month_no between 18 and 21 100 45 100
month_no between 24 and 27 100 1 100

正如前面提到的,在10104,超出边界值时,默认采用一个斜率为 "1/最大值-最小值" 的线性衰变

但在10204上又将默认改回去了

具体是哪个参数控制的,有待研究

/**************************************************************************************************************************************/

3.5 Two Predicates(双谓词)

where

month_no > 8

or month_no <= 8

此时计算的rows=986

本章代码附件中:

ranges_02.sql

类似上面的条件,列出了1-12的所有rows

  rows 
1 1,108
2 1,110
3 1,040
4 989
5 959
6 948
7 957
8 986
9 1,035
10 1,103
11 1,192
12 1,200

优化器仅仅将条件判断为:两个由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 (有待研究)

上一篇:StringBuffer 可变参数拼接


下一篇:netty服务端客户端启动流程分析