彻底搞懂Oracle直方图

数据最前线

于 2024-07-28 20:38:06 发布

阅读量440

点赞数 21

分类专栏： Oracle数据库专栏文章标签： oracle 数据库

本文链接：https://blog.csdn.net/oracle18c/article/details/140755861

版权

Oracle数据库专栏专栏收录该内容

4 篇文章 0 订阅

订阅专栏

Oracle优化器根据数据库对象统计信息来评估访问特定数据的成本，这些信息主要分为三个维度：

表、索引等对象级统计信息，包含表行数、平均行长、索引页块数量等，
列、字段等统计信息，包含字段的唯一值数量、空值数量、字段最大值最小值等；
数据字典、固定对象以及系统级的统计信息。

大多数情况下这些信息是够用的，但是当列数据分布不均匀时，优化器对于某些列值的评估不够准确，导致SQL执行效率问题。这就是直方图的设计初衷。

直方图的作用

优化器默认数据分布是均匀的，没有更详细的信息时，会基于唯一值来(1/NDV – Number of Distinct Values)评估数据的选择度。比如一张表有1000行数据，某个列的Num Distinct值为100，则其选择度为1/100，也就是说基于这个列的等值查询，优化器认为会返回10 (1000 * 1/100)行数据。
但现实世界往往没有这么简单，如果这张表是用来记录某个中学全年级学生考试成绩，现在需要分别统计分数为80分和30分的学生人数。按照实际的经验，0 ~ 50分的很少，95 ~ 100的可能也不多，大多数数据可能会位于70 ~ 90分之间。所以以下两条SQL的执行成本是相同的吗？

select count(*) from 成绩表 where 得分=80;
select count(*) from 成绩表 where 得分=10;

查询10分的学生人数可能是个位数，甚至为零，这个时候选择索引扫描是高效的；80分的学生人数则可能达到500人，占据这张表的80%数据量，这个时候使用全表扫描更加合理。

这个场景下，优化器如何来评估最优的执行计划呢？Oracle对这个问题的解决方案是直方图。
直方图是一种统计报告图，由一系列高度不等的纵向条纹或线段表示数据分布的情况，一般横轴表示数据类型，纵轴表示该类型数据出现的频率。针对数据分布不均匀的场景，Oracle利用直方图来表示字段值的分布信息，以帮助优化器评估出更优的路径。

直方图的分类

为了更好的应对不同的场景，Oracle 11g及之前的版本定义了两种类型的直方图：Frequency Histograms和Height Balanced Histograms。

Frequency Histograms

Frequency直方图最多使用254个Bucket (也称为 END POINT) 来存储数据的分布，当一张表的唯一值跨度小于254时，每个唯一值都可以用一个独立的Bucket来表示。比如上面的学生成绩表，最多只有100个不同值，这种直方图称为Frequency Histograms，能够精确的呈现每个值的分布情况。

Height Balanced Histograms

如果唯一值大于254，没有足够的Bucket表示所有的值，则需要采用稀疏策略，将数据划分为不同的区间对应到相应的Bucket中。Height Balanced直方图的每个Bucket包含相同数量的唯一值，通过每个Bucket中最高值和最低值来计算数据的分布。

举个例子，帮助大家更好的理解这两个概念。
准备1张测试表，其中b=5的数据9991条，b=1…4和9996…10000的值各1条。

drop table HTAB2;
create table HTAB2 (a number, b number);
create index HTAB1_B on HTAB1(b);

insert into HTAB1 ( A,B) values ( 1,1);
insert into HTAB1 ( A,B) values ( 2,2);
insert into HTAB1 ( A,B) values ( 3,3);
insert into HTAB1 ( A,B) values ( 4,4);
insert into HTAB1 ( A,B) values ( 9996,9996);
insert into HTAB1 ( A,B) values ( 9997,9997);
insert into HTAB1 ( A,B) values ( 9998,9998);
insert into HTAB1 ( A,B) values ( 9999,9999);
insert into HTAB1 ( A,B) values ( 10000,10000);
commit;

begin
 for i in 5 .. 9995 loop
   insert into HTAB1 ( A,B) values ( i,5);
   if (mod(i,100) = 0) then
     commit;
   end if;
 end loop;
 commit;
end;
/

收集该表直方图，因为唯一值只有10个，所以生成了FREQUENCY直方图。

exec DBMS_STATS.GATHER_TABLE_STATS (NULL,'HTAB1', method_opt => 'FOR ALL COLUMNS SIZE AUTO');

### 查看直方图信息
select COLUMN_NAME,NUM_DISTINCT,raw_to_number(LOW_VALUE) Low,raw_to_number(HIGH_VALUE) High,DENSITY,NUM_NULLS,
       NUM_BUCKETS,LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM
from user_tab_columns
where table_name = 'HTAB1';

COL   NUM_DISTINCT        LOW       HIGH DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZE SAMPLE_SIZE HISTOGRAM
----- ------------ ---------- ---------- ------- --------- ----------- ------------ ----------- ------------------------------
A            10000          1      10000       0         0           1 26-JUL-24          10000 NONE
B               10          1      10000       0         0          10 26-JUL-24          10000 FREQUENCY

select table_name TAB, column_name COL, ENDPOINT_NUMBER, ENDPOINT_VALUE
from dba_histograms
where table_name='HTAB1' --and column_name='B'
order by COL, ENDPOINT_NUMBER;

TAB                  COL                  ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- -------------------- --------------- --------------
HTAB1                A                                  0              1
HTAB1                A                                  1          10000
HTAB1                B                                  1              1
HTAB1                B                                  2              2
HTAB1                B                                  3              3
HTAB1                B                                  4              4
HTAB1                B                               9995              5
HTAB1                B                               9996           9996
HTAB1                B                               9997           9997
HTAB1                B                               9998           9998
HTAB1                B                               9999           9999
HTAB1                B                              10000          10000

12 rows selected.

这里ENDPOINT_VALUE表示的是列的唯一值，ENDPOINT_NUMBER则代表的是累计的行数。

当ENDPOINT_VALUE=2时，ENDPOINT_NUMBER=2，在它之前的ENDPOINT_NUMBER=1，说明ENDPOINT_VALUE=2这个值只出现了一次；
当ENDPOINT_VALUE=5时，ENDPOINT_NUMBER=9995，在它之前的ENDPOINT_NUMBER=4，说明ENDPOINT_VALUE=5这个值出现了9995-4=9991次。

接下来我们设置B列的Bucket数量为8，模拟Bucket数量不够的情况。

exec DBMS_STATS.GATHER_TABLE_STATS (NULL,'HTAB1', method_opt => 'FOR COLUMNS B SIZE 8');

select COLUMN_NAME,NUM_DISTINCT,raw_to_number(LOW_VALUE) Low,raw_to_number(HIGH_VALUE) High,DENSITY,NUM_NULLS,
       NUM_BUCKETS,LAST_ANALYZED,SAMPLE_SIZE,HISTOGRAM
from user_tab_columns
where table_name = 'HTAB1';

COL   NUM_DISTINCT        LOW       HIGH DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZE SAMPLE_SIZE HISTOGRAM
----- ------------ ---------- ---------- ------- --------- ----------- ------------ ----------- ------------------------------
A            10000          1      10000       0         0           1 26-JUL-24          10000 NONE
B               10          1      10000       0         0           8 26-JUL-24          10000 HEIGHT BALANCED

select table_name TAB, column_name COL, ENDPOINT_NUMBER, ENDPOINT_VALUE
from dba_histograms
where table_name='HTAB1' and column_name='B'
order by COL, ENDPOINT_NUMBER;

TAB                  COL                  ENDPOINT_NUMBER ENDPOINT_VALUE
-------------------- -------------------- --------------- --------------
HTAB1                B                                  0              1
HTAB1                B                                  7              5
HTAB1                B                                  8          10000

这个时候创建出来的HEIGHT BALANCED直方图，HEIGHT BALANCED直方图的每个Bucket包含相同数量的值，也是为什么称为高度平衡直方图的原因。上面的例子中，一共10000个值，8个Bucket每个对应1250个值，Bucket 0包含了1-5的值；Bucket 1~7的ENDPOINT_VALUE相同都是5，为了节省存储空间，Oracle没有保存所有的行；Bucket 8包含了5~10000的值。

相信细心的同学已经看出来了，Bucket 0和Bucket 8中其实也包含了5的数据，并且5的次数还不少。为了区分这个这种情况，Oracle又引入了Non-popular values和Popular values的概念。

Non-popular values是指某个ENDPOINT_VALUE仅在一个Bucket中出现，因此它的选择度是 1/所有Non-popular的数量；
Popular values则是指这个ENDPOINT_VALUE在多个Bucket中出现，比如上面例子中的5。在计算选择度时，Popular values需要通过它在所有ENDPOINT中的占比来计算。

不过这种方案也并不是完美的，并不能覆盖到所有的场景。比如有些值出现的频率比较高，几乎占满了整个Bucket，但是又没有分布到两个Bucket，这种值不会被认为是Popular values，按照Non-popular values的算法来评估选择度，由此可能会对优化器评估产生错误的影响，为此12c之后引入了Hybird直方图。

Hybrid Histogram

Hybird直方图结合了Frequency和HEIGHT BALANCED直方图的优点，这种“两全其美”的方法使优化器在"准Popular values"场景下能够获得更好的选择度评估。

Hybird直方图中，每个ENDPOINT_VALUE只会被分配到一个Bucket中，通过计算ENDPOINT_VALUE在这个Bucket中重复出现的次数，来获得数据分布的准确信息。通过这种方法，优化器可以精确评估出"准Popular values"的选择度。

为了帮助大家更好的理解Hybird直方图的工作原理，这里引用官方文档中的案例介绍Hybird直方图的算法。

假定我们有以下数量的硬币。
在这里插入图片描述

为了统计每种硬币的数量，我们将硬币放入到3个桶中。
按照HEIGHT BALANCED直方图的分配方式，每个桶中的硬币数量相同，因此每个桶中的硬币分配如下。这种分配方式下，5和25被分配在两个桶中，有可能会导致优化器计算不准确。
在这里插入图片描述

为此我们根据Hybird直方图工作原理重新调整了硬币分配，不再要求每个桶中的硬币数量相同，但是每种硬币只会放在同一个桶中，由此每个桶中的硬币分配如下图所示。
现在统计每个桶中每种硬币出现的次数，结合ENDPOINT_NUMBER和ENDPOINT_VALUE，即可精确得到每种硬币的数量。
在这里插入图片描述

Top Frequency

12c中还引入了Top Frequency直方图，可以看做是Frequency直方图的升级版，这类直方图不再保存Non-popular values的信息，在Bucket数量固定的情况下，将Bucket用于保存更多的Popular values，从而减少生成HEIGHT BALANCED直方图的概率。

除了Bucket数量的限制之外，Top Frequency直方图的使用，还需要满足限定条件：由TOP n个Popular values占据行的百分比等于或大于阈值p，这里p = (1-(1/n))*100, n 为Bucket数量。这个限定条件主要是确保所有的Popular values都有独立的Bucket保存数据。

新版本的增强

除了新增了Top Frequency和Hybird直方图外，12c之后还有一些其他小的增强：

最大Bucket数量从254增加到2048，这将大大减少HEIGHT BALANCED和Hybird直方图的使用频率；
对于字符数据，直方图只统计字段的前32个字符，假如某个字段长度超过32，并且其前面的32个字符都相同，则会被认为是同一个值。12c之后字符统计长度从32增加到64。

直方图的管理

直方图收集

直方图是统计信息的一种，其收集方法是在表等统计信息的基础上添加额外的参数来实现。比如，以下的语句就是在收集表统计信息的同时，额外收集SAL列的直方图信息，并且设定用10个Bucket来保存这些信息。

exec DBMS_STATS.GATHER_TABLE_STATS (NULL,'EMP', method_opt => 'FOR COLUMNS sal SIZE 10');

为了大家学习和日后查阅方便，这里列举常见的直方图收集语法。

- FOR ALL [INDEXED | HIDDEN ] COLUMNS [size_clause]
- FOR COLUMNS [size clause] column [size_clause] [,column [size_clause]...]

其中，
size_clause 可以定义为 size {integer | REPEAT | AUTO | SKEWONLY}
column 可以定义为 column_name | extension name | extension

- integer: 用于保存直方图的桶数目，11g取值范围 [1, 254]
- REPEAT: 仅收集那些已经收集过直方图的列
- AUTO: Oracle基于数据分布和列的负载自主决定收集哪些列的直方图
- SKEWONLY: Oracle基于数据分布自主决定收集哪些列的直方图

总结

这篇文章给大家介绍了Oracle数据库中直方图的使用和管理。
现实世界中的数据并不总是均衡的，为了更好的评估这类数据的访问成本，Oracle引入两种基础类型的直方图 FREQUENCY 和 HEIGHT BALANCED 来解决这个问题。

FREQUENCY直方图中，每个唯一值的数据都有对应Bucket可以保存，但是Bucket的数量有限；
当唯一值超过Bucket上限时，Oracle使用HEIGHT BALANCED直方图对数据进行一定的稀疏，会将唯一值均匀保存到每个Bucket中，并将出现在多个Bucket中的值定义为Popular Values，采用特殊的算法计算其选择度；
某些值出现的频率很频繁，但是并没有出现在两个Bucket中，Non-Popular Values的算法又不能正确的评估出这类数据的选择度。为此Oracle 12c以后引入了Hybird直方图，这种直方图结合了FREQUENCY 和 HEIGHT BALANCED的优点，每个ENDPOINT_VALUE不再均匀分布到每个Bucket中，并且相同的值只出现在同一个Bucket中，由此能够更加精确的评估"准Popular Values"数据的选择度。

以上就是这篇文章的所有内容，文中有描述不清楚的，或者大家有疑问，欢迎留言讨论。