CBO相关原理--统计信息

Oracle优化器

Oracle优化器的作用就是为查询语句选择更有效的返回结果的路径。优化器分为两种：RBO和CBO。即分别是基于规则的和基于成本的优化器。
顾名思义，基于规则的优化器，在选择语句的执行路径时，是基于一系列带有优先级的规则来做决定。使用RBO时人工参与语句优化的手段有限。RBO在10g开始不再被支持，但是仍然可以通过修改优化器模式来使用。
CBO在oracle7中被引入，基本思想是给一条SQL语句的所有执行计划制定一个统一的度量，然后给所有执行计划“标价”，这个标注的价格就是执行计划的cost。要把cost算清楚需要两个重要元素：执行计划中数据对象的统计信息和cost算法。统计信息表征了数据对象本身影响执行计划效率的属性，比如行数，唯一键值数等等；cost算法将这些信息处理成具有统一标准的数值，作为cbo选择执行计划的依据。本文主要讨论统计信息的相关概念和原理。

收集统计信息的方法

统计信息的收集有两种方法：
1. 使用dbms_stats包
2. 使用analyze命令
系统统计信息由dbms_stats包产生。
统计信息储存在数据字典表里，cache在share pool中。
dbms_stats包会产生下列内容的统计信息：
表：
rows,blocks,average row length,global statistics,user statistics,sample size,last analyzed
列：
number of distinct values(NDV),number of nulls,data distribution(histogram),
索引:
number of leaf blocks,levels,clustering factor
系统统计信息：
IO performance and utilization
CPU performance and utilization

表统计信息

相关视图：dba_tables, dba_tab_[sub]partitions
用来计算：
1. 表和（子）分区的访问成本
2. Join的基数
3. Join的顺序
表统计信息有下面几个比较重要的内容：
1. num_rows：这是计算基数的基本要素。尤其对于做表连接的驱动表而要，表的行数尤其重要，它决定了被驱动表要被探测多少次。
2. blocks（exact）：blocks是指已使用的数据块数（used data blocks）。使用block数与db_file_multiblock_read_count参数可以大体计算出表访问的成本。
3. avg_row_len：表每行的平均长度，单位为字节。

表统计信息物理的储存在sys.tab$数据字典表里，下面三种统计信息只有在使用analyze命令的时候才会收集：empty_blocks（表中从未使用过的数据块数，也就是used data block与高水位之间的数据块，？存疑？），avg_space（表的数据块中平均空闲空间，单位是字节），chain_cnt（发生行链接的行数）。

索引统计信息

相关视图：dba_indexes, dba_ind_[sub]partitions
包括以下内容：
1. b*数 level，也就是blevel（Exact）。用来计算页块访问的成本。意指从根块到叶块的深度。深度为0表明根块和叶块是同一个。
2. 叶块数。用来计算索引全扫描的成本，range scan还要参考谓词所决定的叶块数。
3. 集群因子。表明了表中的数据按照索引的顺序的排序度。如果该值接近于块数，表明这张表排序良好。在这种情况下，索引单个叶块中的条目倾向于指向相同的数据块。如果该值和行数接近，说明表的排序很随机。这种情况下，索引单个叶块中的条目很可能指向不同的数据块。
4. ditinct keys。不同的键值数。对于唯一键和主键，该值和表的行数相同。
5. avg_leaf_blocks_per_key。索引中每个key对应的平均叶块数，四舍五入到整数。对于唯一键和主键，该值为1.
6. avg_data_blocks_per_key。索引中每个key对应的数据块数，四舍五入到整数。
7. num_rows。索引中的行数。
索引的统计信息物理的存放在sys.ind$数据字典表中。

列统计信息

相关视图：dba_tab_col_statistics，dba_tab_histograms,dba_[sub]part_col_statistics, dba_[sub]part_histograms
包括以下内容：
1. num_distinct。列中键值的数量，用于计算选择性。
2. low value(exact)和high value（exact）。CBO假设所有的数据类型在low value到high value的分布是均匀的。这两个值用来计算range的选择性。
3. num_nulls。用来计算可为空的列的选择性，以及isnull和is not null谓词的选择性。
4. density。只和直方图有关。用来计算nonpopular value的选择性。

默认统计信息

如果某些对象没有统计信息，那么数据库会对选择性，表的row length，索引的叶块数，NDV,表的基数等设置一个默认值。当然在大多数情况下，这些默认值是失真的。从9i开始，CBO可能会选择动态采样来获取所需的统计信息。

直方图

直方图储存了列的分布信息，对于键值非均匀分布（倾斜）的列，可以提供更好的选择性的估算。
直方图技术有两种：width balancing和height balancing.
直方图的桶的数量可以少于等于NDV。
默认情况下，CBO假设对于任何列，键值的分布是均匀的。直方图提供了更为有效的方法来识别列值的分布。这些信息储存在数据字典视图sys.histgrm和sys.hist_head中。可以通过dba_histograms视图来获取。
* width-balanced histograms
特性域分区为相同大小的桶。和财务数据使用的柱状图方法相同。
* htight-balanced histograms
除了最后一个桶，每个桶包含了相同数量的元素。最后一个桶可能包含较少的值。比如说，有一个1000行的列，取值范围从1到100.现在来创建一个10个桶的柱状图。在width-balanced柱状图中，桶的宽度相同，分别为1-10，11-20，21-30等等，而且每个桶记录了对应范围内的行数。在height-balanced柱状图中，每个桶有相同的高度，也就是每个桶都是100行。每个桶的末端由不同值的密度决定。当NDV和桶数相同时，Oracle使用width-balanced直方图。否则使用height-balanced直方图。
当使用dbms_stats包生成直方图时，可能会产生比NDV多的桶数，而analyze则不会。

在12c中，height balanced直方图成为过期特性，引入了新的混合类型直方图。

直方图内容较多，可以单独写一篇博文。
12c直方图官方文档：
https://docs.oracle.com/database/121/TGSQL/tgsql_histo.htm#TGSQL366

density算法：(1/NP values)*(NP buckets/buckets)
NP：nonpopular

动态采样

动态采样信息是在sql执行过程中由级联sql（recursive sql）获取的。对于使用临时表等场景非常适用，这也不难理解，对于涉及临时表的sql，在sql执行过程中对临时表的数据量和NDV等信息进行采集更为准确。
启用动态采样的方法：
* 设置optimizer_dynamic_sampling参数（可以设置为0～10）
* 使用hint /+ dynamic_sampling(t, num)/
http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF95254

system statistics

system statistics，即系统统计信息，通过dbms_stats包收集，可以用来对sql执行的cpu成本进行估算。系统统计信息储存在aux_stats$表中，包括以下内容：
* CPU速度（以MHz为单位）
* 单块读时间（ms）
* 多块读时间（ms）
* 平均多块读块数
有了这些内容，CBO就可以确认：
1. 单块读和多块读在处理时间上的差距；
2. 使用测算的平均多块读的块数来计算全表扫描所需要的读请求数。而不是使用db_file_multiblock_read_count参数指定的值；
3. 使CBO意识到不同访问路径在CPU消耗上的差别，但往往这种差别是可以忽略不计的。

统计物理IO相关信息的底层表：
x$kcfio
KCFIOFNO = file id
KCFIOPYR = number of physical reads
KCFIOPYW = number of physical writes
KCFIOSBR = number of single block reads
KCFIOSBT = cumulative single block reads time
KCFIOMBR = number of multiblock reads
KCFIOPRT = physical read time
KCFIOPWT = physical write time
KCFIOMBT = cumulative multiblock reads time
KCFIOPBR = number of physical blocks read
KCFIOPBW = number of physical blocks written
。。。

相关参数：
_optimizer_cost_model：
choose：如果没有收集统计信息，使用旧的算法；
io：一直使用旧算法，不管有没有收集统计信息
cpu:一直使用”cpu+io”的算法模型，如果没有收集统计信息，则使用CPU速度和单块读时间的默认值，多块读时间通过旧的算法公式计算。