介绍histograms

最新推荐文章于 2022-10-12 18:08:36 发布

cjc198811

最新推荐文章于 2022-10-12 18:08:36 发布

阅读量1.8k

点赞数

文章标签：数据库

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

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

[@more@]

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;

这里绝对不是巧合，而是因为我们手工作的实验和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）

现在我们来研究一下histograms里的frequency histograms。
类似与我们刚才测试分布式查询时所建立的表t1，我们重建表t1，现在t1.skew这个列分布情况是skew=1出现一次，skew=2出现两次......skew=80出现80次。
建表脚本如下：

create table t1 (
skew not null,
padding
)
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <= 5000
)
select
/*+ ordered use_nl(v2) */
v1.id,
rpad('x',400)
from
generator v1,
generator v2
where
v1.id <= 80
and v2.id <= 80
and v2.id <= v1.id
order by
v2.id,v1.id
;

我们来收集frequency histograms：
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 120'
);
end;
/

我们查询user_table_histograms：
select
endpoint_number, endpoint_value
from
user_tab_histograms
where
column_name = 'SKEW'
and table_name = 'T1'
order by
endpoint_number
;

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
1 1
3 2
6 3
10 4
15 5
21 6
..................
..................
..................

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
3081 78
3160 79
3240 80

这个输出和我们最开始看到的height balanced histograms所表达的意思是不同的，这个输出对t1里每一个distinct都保留了一行（所以才说frequency histograms是只能用在distinct key <255的表上，因为histograms的最大bucket数是254）

从这个输出里面我们可以看到等于1的值有一个，等于1和2的值有3个（因此等于2的值有2个），等于1/2/3的值有6个。。。。。。从这个角度，我们常常把frequency histograms称为累计的frequency histograms。

我们可以通过分析函数把累计的信息转化为每一个distinct key在表t1出现的次数：
select
endpoint_value row_value,
curr_num - nvl(prev_num,0) row_count
from (
select
endpoint_value,
endpoint_number curr_num,
lag(endpoint_number,1) over (
order by endpoint_number
) prev_num
from
user_tab_histograms
where
column_name = 'SKEW'
and table_name = 'T1'
)
order by
endpoint_value
;

ROW_VALUE ROW_COUNT
---------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
.....................
.....................
.....................
ROW_VALUE ROW_COUNT
---------- ----------
78 78
79 79
80 80

如果你对于我们这个表1用dbms_stats来收集统计信息的时候，你会发现如果你使用的bucket是80，你不会得到一个frequency histograms的统计信息，而会得到一个height balanced histograms的统计信息，我想这绝对是dbms_stats的一个问题，因为如果使用analyze命令的for all columns size 80的话，你是可以得到frequency histograms的统计信息的。经过多次实验你会发现对于表t1，如果你想得到frequency histograms，你的bucket至少得设置为107。由于histograms的最大bucket为254个，所以如果你的distinct key是大于180的话，用dbms_stats是永远得不到frequency histograms的，这时候必须使用analyze。

现在我们对t1表进行一些测试，结果整理如下：

Predicate	Description	CBO	Human
skew = 40	Column = constant	40	40
skew = 40.5	Column = nonexistent, but in-range	1	0
skew between 21 and 24	Between range with mapped values	90	90
skew between 20.5 and 24.5	Between range with mapped values	90	90
skew between 1 and 2	Between range at extremes	3	3
skew between 79 and 80	Between range at extremes	159	159
4 and skew <8>skew > 4 and skew < 8	Greater than/less than range	18	18
skew = -10	Below high value	1	0
skew = 100	Above high value	1	0
skew between ¨C5 and ¨C3	Range below low value	1	0
skew between 92 and 94	Range above high value	1	0
skew between 79 and 82	Range crossing boundary	159	159
skew = :b1 Column = :bind	use bind	41	???
skew between :b1 and :b2	Column between :bind1 and :bind2	8	???

我们发现frequency histograms确实是够精确，除了绑定变量，所有的常量查询都得到了合理的cardinality，注意这里CBO在我们认为是0行的地方统一的看作是1行，实际上除非你的条件里面加入1=0这样的条件，否则CBO一般是不允许cardinality为0的。

对于绑定变量的测试，我们看到还是在skew = :b1的时候使用了典型的：行数/distinct key，在skew between :b1 and :b2 使用了：5%*5%*行数。
其实我们通过explain plan看到的关于绑定变量的计划通常是不准的，这一点我们可以用explain plan的计划和sql_trace=true里面的计划做对比就可以了。下面做个小测试：

建表：
create table t1
as
select
object_id
from
dba_objects
where
rownum <= 1000;

alter table t1 add c1 number default 1;

update t1 set object_id=3 where object_id>2;
这张表object_id=2有1行，object_id=3有999行。

建索引：
create index t1_i1 on t1(object_id);

收集统计信息：
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 10'
);
end;
/

我们看一下对于绑定变量b1=3的时候：

SQL> exec :b1 := 3;

PL/SQL procedure successfully completed.

SQL> select * from t1 where object_id= :b1;

999 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=500 Bytes=3000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=500 Bytes=3000)
2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=1 Card=500)

对于不使用绑定变量的时候：
SQL> select * from t1 where object_id=3;

999 rows selected.

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=999 Bytes=5994)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=999 Bytes=5994)

下面是我们的sql_trace里面看到的关于使用绑定变量的结果，显然还是走了全表扫描，和explain plan的输出不一样：
select *
from
t1 where object_id= :b1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 41 0.00 0.00 0 44 0 999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 43 0.00 0.00 0 44 0 999

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user id: 46

Rows Row Source Operation
------- ---------------------------------------------------
999 TABLE ACCESS FULL OBJ#(27781) (cr=44 r=0 w=0 time=899 us)

最后Jonathan提供了一个脚本来收集多于254个distinct value的表上的frequency histograms，脚本可以参考：c_skew_freq_02.sql (http://fusnow.itpub.net/resource/681/18332)

6）height balanced histogram（或Height-based histograms）

当我们给出的bucket数目不够大的时候，Oracle会以height balanced的方式记录histograms，也就是按照buckets的值把所有的数据平分，如果bucket是50，就把所有的数据平分为50等份，再告诉我们处于每个边界的值。

我们用前面见过的一张表来看height balanced histograms具体是什么样的：

create table t1 (
skew not null,
padding
)
as
with generator as (
select --+ materialize
rownum id
from all_objects
where rownum <= 5000
)
select
/*+ ordered use_nl(v2) */
v1.id,
rpad('x',400)
from
generator v1,
generator v2
where
v1.id <= &m_demo_size
and v2.id <= &m_demo_size
and v2.id <= v1.id
order by
v2.id,v1.id
;

create index t1_i1 on t1(skew);

然后我们以bucket为75建立histograms信息。
begin
dbms_stats.gather_table_stats(
user,
't1',
cascade => true,
estimate_percent => null,
method_opt => 'for all columns size 75'
);
end;
/

这张表的数据分布就是1出现1次，2出现2次，3出现3次......最后80出现80次，如果我们把histograms的信息查出来的话，类似于：
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
0 1
1 9
2 13
3 16
4 19
5 21
..................
..................
..................
62 73
64 74
65 75
67 76
69 77
71 78
73 79
75 80

59 rows selected.

我们发现查询user_tab_histograms只有59行输出，在仔细看看发现Oracle确实是产生了75个bucket，因为ENDPOINT_NUMBER的最大值是75，只不过在记录统计的信息Oracle进行了压缩，省略了一些bucket的输出，上面的输出其实可以展开成如下形式：

表1：

ENDPOINT_NUMBER ENDPOINT_VALUE
60 72
61 73
62 73
63 74
64 74
65 75
66 76
67 76
68 77
69 77
70 78
71 78
72 79
73 79
74 80
75 80

在进一步解释其他关于histograms的信息之前，我们现看一看user_tab_columns里的DENSITY这个列，

SQL> select density, NUM_DISTINCT, 1/NUM_DISTINCT from user_tab_columns where table_name='T1' and column_name='SKEW';

DENSITY NUM_DISTINCT 1/NUM_DISTINCT
---------- ------------ --------------
.013885925 80 .0125

当我们没有histograms信息的时候，DENSITY永远等于1/NUM_DISTINCT，但是当我们统计了histograms之后，DENSITY就会发生改变，这个改变随Oracle版本改变会有所不同：
8i gives a density of 0.006756757, creates 74 buckets, and reports 58 buckets.
9i gives a density of 0.013885925, creates 75 buckets, and reports 58 buckets.
10g gives a density of 0.013885925, creates 75 buckets, and reports 75 buckets.

让我们再回头关注我们的表t1的histograms信息，我们尤其关注一下ENDPOINT_VALUE=75的情况，之所以关注75是因为我们发现75周围的数值（比如74）都在输出当中出现了多次，只有75只出现了一次，我们自己知道75其实出现的次数应该是不必74在表里出现的次数少的，但不巧的是75在统计Histograms的时候时候出在了一个特殊的位置：

这种不巧会导致Oracle使用不同的机制来计算skew=75的cardinality，在oracle看来，当把histograms数据完全展开成上面表1的形式后，凡是在histograms信息里面出现2次或2次以上的数据都是常见的数据，叫做popular的数据，只出现一次或没出现的数据都是un-popular的数据。

对于popular的数据，对于=的条件，计算cardinality的公式是：
cardinality=总行数*（出现次数/bucket总数）

比如我们上面的74的cardinality= 3240 * （2/75) = 86.4，和我们下面的实验相符：

SQL> select count(*) from t1 where skew=74;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=1 Card=86 Bytes=258)

对于un-popular的数据，对于=的条件，计算cardinality的公式是：
总行数*DENSITY，这里就是3240*0.013885925=44.99：

SQL> select count(*) from t1 where skew=75;

SQL> select count(*) from t1 where skew=2;

range scan的公式是：
如果不跨整个的bucket:
Selectivity = (required range) / (high value - low value) + 2 * density
cardinality=每个bucket的行数*Selectivity

如果跨整个的bucket：
Selectivity = (required range) / (high value - low value) + 2 * density + 整个的bucket的个数
cardinality=每个bucket的行数*Selectivity

最后我们用一个更通用的例子（distinct key更多）来看一下有histograms的时候是如何计算range scan的cardinality的：

create table t1
as
/*
with generator as (
select --+ materialize
rownum n1
from all_objects
where rownum <= 5000
)
*/
select
/*+ ordered use_nl(v2) */
3000 + trunc(2000 * dbms_random.normal) n1,
lpad(rownum,10) small_vc,
rpad('x',100) padding
from
generator v1,
generator v2
where
rownum <= 10000
;

insert into t1
select
500 * (1 + trunc((rownum-1)/500)),
lpad(rownum,10),
rpad('x',100)
from
t1
;

commit;

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

histograms的信息大致如下：
.......
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
8 -120
9 17
10 117
11 251
12 357
13 450
19 500
20 520
21 598

首先如果不跨整个的bucket:
select
small_vc
from t1
where n1 between 100 and 200
;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=53 Card=63 Bytes=945)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=53 Card=63 Bytes=945)

按照公式计算：
Selectivity = (required range) / (high value - low value) + 2 * density =
(200–117)/(251-117) + (117-100)/(117-17) + 2 * 0.000177746 =
0.619403 + 0.17 + .000355486 =
0.789047508

Cardinality = selectivity * number of rows IN A BUCKET =
0.789047508 * 80 = 63.1238

如果跨多个bucket：
select
small_vc
from t1
where n1 between 400 and 600
;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=53 Card=685 Bytes=10275)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=53 Card=685 Bytes=10275)

按照公式计算：
Selectivity = (required range) / (high value - low value) + 2 * density + 整个的bucket的个数
(450 - 400) / (450 - 357) + (600 - 598) / (670 - 598) + 2 * 0.000177746 + 8 =
50 / 93 + 2 / 72 + 0.000355486 +8 =
0.537634 + 0.0277778 + 0.000355486 + 8=
8.565768

Cardinality = selectivity * number of rows IN A BUCKET =
8.565867 * 80 = 685.3

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/60217/viewspace-912310/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/60217/viewspace-912310/

cjc198811

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
介绍histograms

histograms一般被我们翻译成为直方图，实际上当我们统计了histograms信息之后，user_tab_histograms里面的信息确实可以以直方图的方式展示给我们。而这些信息描述的就是我们的表的信息的分布情况。我们这...
复制链接

扫一扫