oracle的统计信息优化,《基于Oracle的SQL优化》笔记第五章Oracle里统计信息...

CBO 是基于对各种不同执行路径成本的计算&＃xff0c;比较并选取成本值最小的执行路径来作为目标 SQL 的执行计划的&＃xff1b;而成本值的计算则是根据目标 SQL 所涉及的表、索引、列等相关对象的统计信息&＃xff0c;运用 CBO 固有的成本值计算公式计算出来的。所以&＃xff0c;对统计信息的了解成了理解 CBO、理解执行计划的关键。

Oracle 数据库里的统计信息分为六种类型&＃xff0c;包含对应的对象的信息&＃xff0c;维度不同&＃xff1a;

表的统计信息&＃xff1a;典型的维度有 记录数、表块(表里的数据块)的数量、平均行长度等。

索引的统计信息&＃xff1a;典型的维度有 索引的层级、叶子块的数量、聚族因子等。

列的统计信息&＃xff1a;典型的维度有 列的 distinct 值的数量、列的 null 值的数量、列的最小值、列的最大值以及直方图等。

系统统计信息&＃xff1a;所在数据库服务器的系统处理能力&＃xff0c;包含来 CPU 和 I/O 这两个维度&＃xff0c;借助于系统统计信息&＃xff0c;Oracle 可以知道目标数据库服务器的实际处理能力。

数据字典统计信息&＃xff1a;描述 Oracle 数据库里的数据字典基表(如 TAB$、IND$ 等)数据字典基表上的索引&＃xff0c;以及这些数据字典基表的列的详细信息&＃xff0c;与普通表、索引、列的统计信息没有本质区别。

内部对象统计信息&＃xff1a;描述 Oracle 数据库里的内部表(如 X$ 系列表&＃xff0c;其实只是 Oracle 自定义的内存结构)的信息信息&＃xff0c;并不占用实际的物理存储空间。

可以用 ANALYZE 命令(只能收集前四种&＃xff0c;且不能并行收集)或 DBMS_STATS 包来收集统计信息&＃xff0c;

在导入大量数据后应及时收集统计信息后才进行相关的后续业务操作(包括查询和修改)&＃xff0c;否则可能会由于实际数据量和统计信息里记录的数据量存在巨大差异而导致 CBO 选择错误的执行计划。

在这种情况下踩过坑&＃xff0c;第一次在 Oracle 数据库里把现有的业务表转为分区表时&＃xff0c;采用的是建立新的表&＃xff0c;然后把数据导过去&＃xff0c;索引建好&＃xff0c;切了表名&＃xff0c;应用起来后慢得不得了&＃xff0c;一看执行计划&＃xff0c;各种全表扫描&＃xff0c;哪怕唯一性的索引都所走索引全扫描&＃xff0c;就是因为统计信息没有。

聚族因子的含义及重要性

在 Oracle 数据库里&＃xff0c;聚族因子所指按照索引键值排序的索引行和存储于对应表中的数据行的存储顺序的相似程度。

Oracle 按照如下方法计算聚族因子的值&＃xff1a;

聚族因子的初始值为 1。

Oracle 首先定位到目标索引处于最左边的叶子块。

从最左边的叶子块的第一个索引键值所在的索引行开始顺序扫描&＃xff0c;在顺序扫描的过程中&＃xff0c;Oracle 会比对当前索引行的 rowid 和它之前的那个索引行的 rowid&＃xff0c;

如果这两个 rowid 并不是指向同一个表块(不需要回表)&＃xff0c;那么 Oracle 就将聚族因子的当前值递增 1 &＃xff0c;否则不变。

重复步骤 3 直至顺序扫描完目标索引所有叶子块里的所有索引行。

扫描完成后&＃xff0c;聚族因子的当前值就是索引统计信息中的 CLUSTERING_FACTOR&＃xff0c;Oracle 会将其存在数据字典里。

聚族因子低意味着走索引范围扫描后取得目标 rowid 再回表去访问对应表块的数据时&＃xff0c;相邻的索引行所对应的 rowid 极有可能处于同一个表块&＃xff0c;也就更容易命中缓存。

因此对于索引范围扫描&＃xff0c;聚族因子高的比低的需要耗费更多的物理 I/O &＃xff0c;成本更高。

在 Oracle 数据库里&＃xff0c;能够降低目标索引的聚族因子的唯一方法就是对表中数据块按照目标索引的索引键值排序后重新存储。但这种方法也可能会增加该表上其他索引的聚族因子的值。

聚族因子值的大小实际上对 CBO 判断是否走相关的索引起着至关重要的作用。

谓词越界 与 直方图

谓词越界是指如果对目标列指定的 where 查询条件不在该列的最大值与最小值之间&＃xff0c;CBO 就无法判断出针对该列的查询条件的可选择率&＃xff0c;所以只能用一个估算值来作为针对该列的查询条件的可选择率&＃xff0c;如果这个估算的可选择率与实际情况严重不符&＃xff0c;确实可能导致 CBO 评估出来的 Cardinality 出现严重偏差&＃xff0c;进而使 CBO 选错执行计划。

CBO 会默认认为目标列的数据在其最小值 LOW_VALUE 和最大值 HIGHT_VALUE 之间是均匀分布的&＃xff0c;并且会按照这个均匀分布原则来计算对目标列施加查询条件后的可选择率以及结果集的 Cardinality&＃xff0c;进而据此来计算成本值并选择执行计划。因此&＃xff0c;如果值是不均匀分布的&＃xff0c;CBO 可能选错执行计划。

如果对目标列收集了直方图(Histogram)&＃xff0c;则意味着 CBO 不再认为该目标列上的数据是均匀分布的&＃xff0c; CBO 会用该目标列上的直方图统计信息来计算对该列施加查询条件后的可选择率和返回结果集的 Cardinality&＃xff0c;进而据此计算成本并选择执行计划。

案例&＃xff1a;很多业务数据在处理完后会把状态字段 status 从 0 标记为 1&＃xff0c;时间越长&＃xff0c;状态为 1 的数据就占居了绝大部分&＃xff0c;每次查询都是查状态为 0 的数据&＃xff0c;因此可以在状态字段上建立索引。查看执行计划时&＃xff0c;status 对应的值用占位符或 1 时&＃xff0c;看到的执行计划都是全表扫描&＃xff0c;但是是 0 时&＃xff0c;则是走索引扫描&＃xff0c;这就是直方图的作用。

欢迎关注我的微信公众号: coderbee笔记&＃xff0c;可以更及时回复你的讨论。