聊聊直方图的作用

最新推荐文章于 2023-10-27 21:55:29 发布

cuiwangxie1183

最新推荐文章于 2023-10-27 21:55:29 发布

阅读量2.7k

点赞数

文章标签：数据库人工智能

当我们执行一条sql语句，CBO是如何选择走哪个执行计划呢？这时我们会想到数据库的统计信息，正是有了统计信息才使得CBO做出正确的判断，统计信息包括多方面的信息，下面我们重点来看看直方图。

直方图的作用是：告诉CBO，一个列中，不同的值的所占的总数的比例。Oracle的直方图有两种：一种是频率直方图（FREQUENCY HISTOGRAM),当列中Distinct_keys较少（小于254），如果不手工指定直方图桶数（BUCKET),Oracle就会自动的创建频率直方图，并且桶数（BUCKET)等于Distinct_Keys.另外一种是高度平衡直方图（HEIGHT BALANCED)，当列中Distinct_keys大于254,如果不手工指定直方图桶数（BUCKET)，Oracle就会自动的创建高度平衡直方图。下面我们来做一个实验来测试一下：

首先生成一张测试表，并收集统计信息包括收集所有列的直方图

可以看到OWNER行Distinct_keys为19小于254则默认的桶数为19，属于频率直方图。而OBJECT_NAME行Distinct_keys为40850大于254则默认的桶数为254，属于高度平衡直方图。

下面来测试一下直方图的作用，先在owner列创建索引

下面来执行这条语句看一下执行计划：

Select * from test where owner=’SYS’;（很奇怪为什么不走索引呢？）在test中总共有67885条数据而owner=’SYS’有31145条数据，如果这时还走索引的话，首先要在索引上找到对应的rowid然后再通过rowid回表，这个时候再走索引并不是最优了消耗的资源反而更大，可以看到这里全表扫描的consistent gets 为3007，我们再看执行计划rows这一列，id=1 CBO预估返回31145行，而最终结果确实是返回31145行

select * from test where owner=’SCOTT’;这时CBO选择走了索引，因为owner=’SCOTT’只有14行这时走索引只需拿到14行rowid然后回表，这比起走全表扫描大大的节省了资源，提高了查询速度。

为了说明问题我们删除直方图：

删除直方图之后再来执行Select * from test where owner=’SYS’;可以看到这时CBO选择走了索引为什么这时候又选择走索引呢，我们看执行计划rows这一列是3573 也就是CBO认为只需返回3573行数据，从67885中选择3573行数据，CBO必然认为这时走索引优于走全表扫描。可以看到现在的consistent gets 为4955,大于之前的走全表扫描的

删除直方图之后执行Select * from test where owner=’SCOTT’;依然是走索引，我们来看一下rows这一列依然是3573行而最终真正返回的是14行，原来在oracle数据库中，CBO会默认认为目标列的数据在其最小值LOW_VALUE和最大值HIGH_VALUE之间是均匀分布的，并且会按照这个这个均匀分布原则来计算对目标列施加查询条件后的可选择率以及结果集的cardinality,进而据此来计算成本值并选择执行计划。

那是不是所有的表的列都要收集直方图呢，这样就可以让CBO能够正确的做出选择，收集直方图可能会导致绑定变量窥探(在9i,10g几乎没有什么好办法解决，在11g里面出一个自适应游标，解决了绑定变量窥探这个可以查看故障优化小组输出文档《 17.Adaptiver Cursor Sharing》)下面在ORACLE 10g环境中测试一下。

生成测试表：create table test as select * from dba_objects; 并且收集直方图

执行语句：select * from test where owner=’SYS’;可以看出CBO做出正确的选择