CBO学习笔记9:histograms --part1

 

这一节我们专门来介绍histograms

histograms一般被我们翻译成为直方图，实际上当我们统计了histograms信息之后，user_tab_histograms里面的信息确实可以以直方图的方式展示给我们。而这些信息描述的就是我们的表的信息的分布情况。
我们这一节将会主要介绍什么是直方图以及Oracle使用直方图的一些细节。

和以前一样，我们的讨论还是从一个实验开始：

1）什么是histograms

首先还是创建测试表：
execute dbms_random.seed(0)

create table t1
as
with kilo_row as (
select /*+ materialize */
rownum
from all_objects
where rownum <= 1000
)
select
trunc(7000 * dbms_random.normal) normal
from
kilo_row k1,
kilo_row k2
where
rownum <= 1000000
;

这个建表语句没有什么特别的，其中一个是使用了所谓的subquery factoring语法，这样我就不需要单独创建kilo_row这个中间表，另一个比较主要的就是通过dbms_random来生成表中的随机数，虽说是随机生成的数据，但如果大家用过dbms_random的话就会知道我们生成的这些数据分布是不平均的，你可以用
select normal, count(*) from t1 group by normal;
来看到具体的数据分布，应该是类似于下图的：

整个表的数据其实是大致上以0为中心分布，越靠近出现的次数越多，离0越远出现几率越小，在我的测试里，整个取值范围是在-32003和34660之间。
SQL> select min(normal), max(normal) from t1;

MIN(NORMAL) MAX(NORMAL)
----------- -----------
-32003 34660

现在我们的t1表有一百万行数据，现在我决定把这些行从小到大排列，然后分为10个组，每组十万行，并给每一个行分配一个组号，这样最小的就是组1，最大的就是组10。
我们可以用下面的分析函数实现这一点：

select
normal,
ntile(10) over (order by normal) tenth
from t1
;

这个查询的output类似与：

NORMAL TENTH
---------- ----------
-11929 1
-5081 2
-4837 3
-4366 4
-2348 5
-1316 6
-375 7
-244 8
4290 9
............
-8966 2
-8966 2
-8966 2
-8966 2
-8966 2
-8966 2
-8966 2
-8966 2
-8966 2
............
............
............
............
............
NORMAL TENTH
---------- ----------
0 10
0 10
0 10
0 10
0 10
0 10
0 10
0 10
0 10
0 10

在上面的查询的基础上，我们作如下查询：
select
tenth tenth,
min(normal) low_val,
max(normal) high_val,
max(normal) - min(normal) width,
round(100000 / (max(normal) - min(normal)),2) height
from (
select
normal,
ntile(10) over (order by normal) tenth
from t1
)
group by tenth
order by tenth
;

TENTH LOW_VAL HIGH_VAL WIDTH HEIGHT
---------- ---------- ---------- ---------- ----------
1 -32003 -8966 23037 4.34
2 -8966 -5883 3083 32.44
3 -5883 -3659 2224 44.96
4 -3659 -1761 1898 52.69
5 -1761 17 1778 56.24
6 17 1792 1775 56.34
7 1792 3678 1886 53.02
8 3678 5897 2219 45.07
9 5897 8974 3077 32.5
10 8974 34660 25686 3.89

这个输出的含义是（我们仅解释第一行，其他行的含义是一样的），如果把表t1按照从小到大排列，并且按照100000一组把表t1分成10组，那么第一组的最小的值是-32003，最大值是-8966，最小值和最大值差距是23037，在第一组里，平均每一个值在表t1里出现4.34次。通过这样的一组数据，我们就可以比较清楚的看到t1的数据分布情况，和我们上面的图里描述是差不多的。当然，如果我们分更过的组，反映的情况会更精确。

我们这索引做这些查询，是因为histograms所表达的信息是和我们上面的查询所表达的信息非常相似的。

现在我们对表T1统计histograms信息，同样我们也是把t1的数据分为10组（按照oracle的说法，10个bucket）：
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for columns normal size 10'
);
end;
/

查询user_tab_histograms的信息我们看到：
select ENDPOINT_NUMBER, ENDPOINT_VALUE from user_tab_histograms where TABLE_NAME='T1';

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 -32003
1 -8966
2 -5883
3 -3659
4 -1761
5 17
6 1792
7 3678
8 5897
9 8974
10 34660

这里的意思就是把t1的数据平均的分成10组（也是按大小排序，也是每组十万行），对于每一组在ENDPOINT_VALUE列上列出了临界值。这和我们前面对数据的分组其实是完全一样的，而且当我们对user_tab_histograms做如下查询之后，我们得到了和前面一样的结果：

SELECT ROWNUM TENTH,
PREV LOW_VAL,
CURR HIGH_VAL,
CURR - PREV WIDTH,
ROUND(100000 / (CURR - PREV),2) HEIGHT
FROM (SELECT ENDPOINT_VALUE CURR,
LAG(ENDPOINT_VALUE,1) OVER(ORDER BY ENDPOINT_NUMBER) PREV
FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'T1'
AND COLUMN_NAME = 'NORMAL')
WHERE PREV IS NOT NULL
ORDER BY CURR;

TENTH LOW_VAL HIGH_VAL WIDTH HEIGHT
---------- ---------- ---------- ---------- ----------
1 -32003 -8966 23037 4.34
2 -8966 -5883 3083 32.44
3 -5883 -3659 2224 44.96
4 -3659 -1761 1898 52.69
5 -1761 17 1778 56.24
6 17 1792 1775 56.34
7 1792 3678 1886 53.02
8 3678 5897 2219 45.07
9 5897 8974 3077 32.5
10 8974 34660 25686 3.89

这里绝对不是巧合，而是因为我们手工作的实验和Oracle收集histograms的方法是一致的，其实如果我们对dbms_stats.gather_table_stats的过程做sql_trace的话，我们会在trace文件中看到如下SQL：
select
min(minbkt),
maxbkt,
substrb(dump(min(val),16,0,32),1,120) minval,
substrb(dump(max(val),16,0,32),1,120) maxval,
sum(rep) sumrep,
sum(repsq) sumrepsq,
max(rep) maxrep,
count(*) bktndv,
sum(case when rep=1 then 1 else 0 end) unqrep
from
(
select
val,
min(bkt) minbkt,
max(bkt) maxbkt,
count(val) rep,
count(val) * count(val) repsq
from
(
select /*+
cursor_sharing_exact dynamic_sampling(0) no_monitoring
*/
"NORMAL" val,
ntile(10) over(order by "NORMAL") bkt
from
"TEST_USER"."T1" t
where
"NORMAL" is not null
)
group by val
)
group by
maxbkt
order by
maxbkt
;

从这里我们可以知道，histograms的作用就是用来描述你的表里的数据集的状态的，用来描述某个数据（或某个范围的数据）的出现比率的。

2）关于histograms的常规讨论：

histograms主要是在表的数据分布是不均匀分布的时候，用来帮助CBO计算selectivity和cardinality。
我们可以发现，Oracle会以两种方式来表达histograms的信息。
一种是在表里的distinct key比较少的时候（少于255个distinct key），Oracle会以一种叫做frequency histogram的方式来存储histograms信息，或者准确的说，这种方式应该叫做cumulative frequency histogram。
另一种就是当表里的distinct key比较多的时候（多于等于255），Oracle会以一种叫做height balanced histogram的方式来存储histograms信息，我们已经在刚才的的讨论中看到了这种表现方式。
这两种histograms还有两种更贴切的叫法：
Height-based histograms
Value-Based Histograms

我们知道在user_tab_columns里有这样两个列：NUM_DISTINCT和DENSITY，当没有统计histograms信息的时候，我们会看到DENSITY总是1/NUM_DISTINCT，但当我们统计了histograms后，
DENSITY的值就会有所改变。我们还会在后面看到，有时候虽然我们统计了histograms信息，但CBO会使用DENSITY来计算selectivity和cardinality。

3）histograms和绑定变量

我们知道当我们拥有了histograms的统计信息之后我们就可以使用这些信息计算我们的selectivity和cardinality。但是如果我们使用了绑定变量的时候，情况总会有所改变。
首先，在Oracle9i里面新引入了bind variable peeking的功能，这个功能我们前面讲过，是一个带绑定变量的SQL第一次parse的时候，让CBO可以根据绑定的具体的值来决定所要使用的执行计划，而以后如果遇到同样的SQL，即使绑定变量的值不一样，也不会在peek绑定变量的值，而是使用已经生成的计划。这里的一个潜在的问题就是如果我们有了histograms信息，而且我们的数据分布是一小部分数据的分布和其他部分的分布相差很远，那么当我们在做bind variable peeking，如果很不幸运的peek到了那一小部分的数据，就会导致以后所有的同样的SQL都使用了不恰当的执行计划。

当然这个bind variable peeking有时候也有意外，那就是如果我们存在shared pool里的执行计划信息或其他相关的信息由于某种原因失效了或者被age out of shared pool，那当我们再次运行这个SQL的时候，就会重新peek绑定变量的值，从而重新生成计划。关于执行计划信息或其他相关的信息的失效或age out，可以通过v$sql的reloads和invalidations字段获得。

和绑定变量有关的另一个就是参数cursor_sharing。
cursor_sharing这个参数有三个取值：

FORCE
Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

SIMILAR
Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

EXACT
Only allows statements with identical text to share the same cursor.

有时候，很可能是在OLTP的系统中，为了最大限度的减少SQL PARSE的消耗，让类似的SQL可以尽可能的重用，我们会考虑设置把cursor_sharing设置为force。当cursor_sharing被设置为force的时候，优化器会用系统指定的绑定变量来替代SQL里面所有的literal constants，然后以此为基础判断我们的shared pool里面是不是有可以重用的cursor。按照我们上面的讨论，设置cursor_sharing为force对histograms影响最大的。

这个问题可以有两个work around,一是在我们认为影响会很到的SQL里面加上hint /*+ cursor_sharing_exact */，这回告诉CBO对于这个SQL采用cursor_sharing=exact的策略。
另一个解决方法是设置cursor_sharing=similar，按照上面Oracle文档的说法，设置cursor_sharing为similar也会首先把SQL里的literals替换为绑定变量，并且也会在第一次分析SQL的时候做bind variable peeking，但是当以后重新运行类似的SQL的时候，CBO会查看如果发现新的绑定变量会影响到执行计划（当然，之所以会产生不同的执行计划往往是因为存在histograms），就会重新生成执行计划。经过一些实验，我们可以发现，当设置cursor_sharing=similar的时候，如果我们的条件是range scan或等于的条件，并且条件涉及的列上有histograms信息的时候，CBO会在分析SQL的时候对绑定变量做检查，如果发现新的绑定变量有可能影响SQL的执行计划，则会重新评估并生成新的计划。

但是往往我们在优化系统的一个方面的时候会导致其他方面的问题，cursor_sharing=similar就是一个很典型的例子，当我们这样的设置的时候，首先优化器的压力会变大，因为CBO要做很多的重新优化。
更严重的问题在于cursor_sharing=similar会导致同样的SQL（除了绑定变量的值不一样之外）在library cache里面拥有很多不同的执行计划，因为我们知道一个SQL下面的所有执行计划都是被一个latch保护的，所以cursor_sharing=similar会导致更严重的latch 争用。

因此当我们使用cursor_sharing=similar的时候，除非必要，无需统计histograms信息，因为我们要保证我们为了解决一个问题不会导致其他的更严重的问题。

最后，当你设置CURSOR_SHARING为similar和force的时候，使用OUTLINES和EXPLAIN PLAN会遇到一些问题。
当你使用explain plan for {sql statement}, 或者create outline for {sql statement} 这样的命令时，Oracle不会把里面的常量替换为绑定变量，而是使用常量来生成执行计划。对于explain plan来说，我们看到的计划很可能不是真正执行时使用的计划，而outline则更糟，因为即使你运行了相同的SQL，Oracle会在处理SQL之前把里面的常量先替换为绑定变量，这样你执行的SQL和outline里面的SQL是不匹配的，从而导致outline不会被使用。

4）什么时候Oracle无法使用histograms？

1. Oracle不能保证在join中可以充分使用histograms，如果你有一个列colx，Oracle只有你明确的指定了colx operation(<,>,=,in,between等等) 常量（这个常量当然也可以是通过bind variable peeking获得的）的时候，才会使用histograms，所以如果你执行了这样的SQL：

select
t1.v1, t2.v1
from
t1,
t2
where
t1.n2 = 99
and t1.n1 = t2.n1
;

如果我们在t1和t2上都有histograms，Oracle会在t1.n2=99这个条件上使用histograms，但Oracle不能在and t1.n1 = t2.n1这个条件上使用histograms，当然如果我们的条件改成：
t1.n2 = 99
and t1.n2 = t2.n1
这时候histograms就可以使用了，因为Oracle会自己把这个SQL改写成：
t1.n2 = 99
and
t2.n1 = 99

2. Oracle在分布式查询中不会使用远程表的histograms信息。
我们可以做一个简单的实验看到这个结果：

首先创建一个指向自己的database link：
SQL> create database link loopback connect to lii identified by lii using 'REPDB1.CHP.HP.COM';

Database link created.

然后我们创建表t1：
create table t1 (
skew, skew2, padding
)
as
select r1, r2, rpad('x',200)
from
(
select /*+ no_merge */
rownum r1
from all_objects
where rownum <= 80
) v1,
(
select /*+ no_merge */
rownum r2
from all_objects
where rownum <= 80
) v2
where r2 <= r1
order by r2,r1
;

alter table t1 modify skew not null;
alter table t1 modify skew2 not null;
create index t1_skew on t1(skew);

然后我们收集统计信息，包括histograms：

begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 90'
);
end;
/

现在我们看一下查询本地的t1和“远程的”t1有什么不同：
set autotrace traceonly explain

select
home.skew2,
away.skew2,
home.padding,
away.padding
from
t1 home,
t1@loopback away
where
home.skew = 5
and away.skew = 5
and home.skew2 = away.skew2
;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=33 Card=48 Bytes=16080)
1 0 HASH JOIN (Cost=33 Card=48 Bytes=16080)
2 1 REMOTE* (Cost=16 Card=41 Bytes=5248) LOOPBACK.CHP.HP.COM
3 1 TABLE ACCESS (FULL) OF 'T1' (Cost=16 Card=38 Bytes=7866)

2 SERIAL_FROM_REMOTE SELECT "SKEW","SKEW2","PADDING" FROM "T1" "AWAY" WHERE "SKEW"=5

select
home.skew2,
away.skew2,
home.padding,
away.padding
from
t1 home,
t1 away
where
home.skew = 5
and away.skew = 5
and home.skew2 = away.skew2
;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=33 Card=22 Bytes=910
8)

1 0 HASH JOIN (Cost=33 Card=22 Bytes=9108)
2 1 TABLE ACCESS (FULL) OF 'T1' (Cost=16 Card=38 Bytes=7866)
3 1 TABLE ACCESS (FULL) OF 'T1' (Cost=16 Card=38 Bytes=7866)

尽管t1和t1@loopback其实是同一个表，但plan里面显示的cardinality是不一样的，因为t1@loopback没有考虑histograms，对于t1@loopback，很显然cardinality是这样算出来的：

SQL> select num_distinct from user_tab_columns where table_name='T1' and column_name='SKEW';

NUM_DISTINCT
------------
80

SQL> select count(*) from t1;

COUNT(*)
----------
3240

SQL> select ceil(3240/80) cardinality from dual;

CARDINALITY
-----------
41

5）Frequency Histograms （或Value-Based Histograms）

 

来源：http://hi.baidu.com/richard_cxs/blog/item/7c3398510611a61a367abe50.html