《基于 Oracle 的 SQL 优化》笔记 第一章 Oracle 里的优化器

发表于由

CBO 相关基础

cardinality ：指定结果集的行数，实际上表示对目标 SQL 的某个具体步骤的执行结果所包含记录数的估算。如果是针对整个目标 SQL，那么此时的 cardinality 就表示对该 SQL 最终执行结果所包含记录数的估算。cardinality 值越大，对应的成本就越高。

selectivity 选择度：是指施加指定谓词条件后返回结果集的记录数占未施加任何谓词条件的原始结果集的记录数的比率。计算公式为 selectivity = 指定谓词条件后返回结果集的记录数 / 未施加任何谓词条件的原始结果集的记录数。这个取值处于 0 - 1 之间，值越小越好，说明选择性高（能过滤更多不符合条件的数据）。

对于每一种具体情况，selectivity 会有不同的计算公式。

最简单的情况是对目标列做等值查询时 selectivity 的计算公式，在目标列上没有直方图且没有 NULL 值的情况下，用目标列做等值查询时的 selectivity 的计算公式为 selectivity = 1 / NUM_DISTINCT，NUM_DISTINCT 表示目标列的 distinct 值的数量。

transitivity 可传递性：是 CBO 在查询转换中所做的第一件事，是指 CBO 可能会对原目标 SQL 做简单的等价改写，即在原目标 SQL 中加上根据该 SQL 现有的谓词条件推算出来的新的谓词条件，这么做的目的是提供更多的执行路径给 CBO 做选择，进而得到更高效执行计划的可能性。

Oracle 中可传递性分下列三种情形：

  • 简单的谓词传递：比如 t1.c1 = t2.c1 and t1.c1 = 10 ，额外添加 t2.c1 = 10。
  • 连接谓词传递：比如 t1.c1 = t2.c1 and t2.c1 = t3.c1，额外添加 t1.c1 = t3.c1。
  • 外连接谓词传递：比如 t1.c1 = t2.c1(+) and t1.c1 = 10，额外添加 t2.c1 (+)= 10。

CBO的缺陷：

  1. CBO 会默认目标 SQL 语句 where 条件中出现的各个列之间是独立的，没有关联关系。
  2. CBO 会假设所有的目标 SQL 都是单独执行的，并且互不干扰。比如没有考虑到缓存，就会高估成本。
  3. CBO 对直方图统计信息有诸多限制。
  4. CBO 在解析多表关联的目标 SQL 时，可能会漏选正确的执行计划。随着多表关联的目标 SQL 所包含的数量的递增，各表之间的连接顺序会呈几何级数增长，即该 SQL 各种可能的执行路径的总数也会呈几何级数增长。Oracle 不可能检查所有的执行计划，因此可能会漏选。

优化器基础知识

优化器模式

优化器的模式用于决定在 Oracle 解析目标 SQL 时所用优化器的类型，以及决定当使用 CBO 时计算成本值的侧重点。

优化器的模式由参数 OPTIMIZER_MODE 的值来决定，可选的取值有：RULE, CHOOSE, FIRST_ROWS_n(n=1, 10, 100, 1000), FIRST_ROWS, ALL_ROWS。

可用这样的语句更改模式： alter session set optimizer_mode = rule;

各个值的含义如下：

  • RULE：表示 Oracle 将使用 RBO 来解析目标 SQL，此时目标 SQL 所涉及的各个对象的统计信息对于 RBO 来说没有任何作用。
  • CHOOSE：oracle 9i 中的默认值。表示只要目标 SQL 中所涉及的表对象中有一个有统计信息，那么 Oracle 在解析该 SQL 时就会使用 CBO；如果该 SQL 涉及的所有表对象都没有统计信息，就使用 RBO。
  • FIRST_ROWS_n(n=1, 10, 100, 1000)：CBO 在选择执行计划时所采用的原则不再是选择成本最小的执行路径，而是能够以最快速度返回头 n(n=1, 10, 100, 1000) 条记录所对应的执行路径。
  • FIRST_ROWS：在 oracle 9i 中已过时的参数。
  • ALL_ROWS：oracle 10g 及后续版本的默认值。CBO 在计算该 SQL 的各条执行路径的成本值时侧重点在于最佳的吞吐量。

结果集（row source）

结果集是指包含指定执行结果的集合。对于 CBO，对应执行计划中的列（rows）反应的就是 CBO 对于相关执行步骤所对应输出结果的记录数（即 Cardianlity）的估算值。

访问数据的方法

访问表中数据的方法有两种，一种是直接访问表；另一种是先访问索引，再回表（如果通过索引就能得到所需数据，则不需要回表）。

访问表中数据的方法有两种：全表扫描和 rowid 扫描。

  • 全表扫描：Oracle 在访问目标表里的数据时，会从该表所占用的第一个区（EXTENT）的第一个块开始扫描，一直扫描到该表的高水位线（High Water Mark，HWM），这段范围内的所有数据块 Oracle 都必须读到。
    期间 Oracle 会对读到的所有数据施加目标 SQL 里 where 条件中的过滤条件，返回符合条件的数据。Oracle 在做全表扫描操作时会使用多块读，这在目标表的数据量不大时执行效率是很高的。全表扫描的最大问题在于走全表扫描的目标 SQL 的执行时间会不稳定、不可控，会随着目标数据量的增加而递增。

  • rowid 扫描：rowid 表示 Oracle 中数据行记录所在的物理存储地址。rowid 扫描有两种方式，一种是根据用户输入的 rowid 的值直接去访问对应的数据行记录，另一种是先访问相关索引，得到 rowid 后再回表访问对应数据行记录。

高水位线（high water mark，HWM）：水库涨水时，水位会上移，水库放水后，曾经的最高水位的痕迹还可以看见。在 Oracle 中，如果对目标表不停地插入数据，当分配给该表的现有空间不足时，高水位线就会向上移。delete 无法降低高水位线，truncate 可以。高水位线的这种特性带来的副作用是，即使用 delete 把目标表中的所有数据都删了，高水位线还会在原来的位置，此时对该表的全表扫描操作所耗费的时间与之前相比不会有明显的改观。

最近生产环境就出了这种问题：用户登录后的凭证会保存在数据库里，有job定时把过期的凭证删除，这个查找过程所全表扫描。有天DBA说这个扫描太慢了，看看表里的数据是很少的。原因就是 HWM 带来的副作用。

表连接

表连接顺序：不管目标 SQL 中有多少个表做连接，Oracle 在实际执行该 SQL 时都只能先两两做表连接，再依次执行这样的两两表连接，直到目标 SQL 中所有表都已做连接完毕。

在 Oracle 中两个表之间的表连接方法有 排序合并连接、嵌套循环连接、哈希连接和笛卡尔连接四种。

排序合并连接

步骤为：

  1. 以目标 SQL 中指定的谓词条件（如果有）去访问表 T1，然后对访问结果按照 T1 的连接列进行排序，排好序的结果集作为结果集 1；
  2. 以目标 SQL 中的指定谓词条件（如果有）访问表 T2，对访问结果按照表 T2 中的连接列排序，作为结果集2；
  3. 对结果集1和结果集2 进行合并操作，从中取出匹配数据作为排序合并连接的最终执行结果。

排序合并连接的效率远不如哈希连接，但适用范围更广。哈希连接通常只能用于等值连接，而排序合并连接还能用于其他连接条件（如 <, <=, >, >=）。

通常，排序合并连接不适用于 OLTP 系统，因为对于 OLTP 系统排序是昂贵的操作。如果能避免排序操作，即使是 OLTP 系统还是可以用排序合并连接的。

嵌套循环连接

步骤为：

  1. 优化器会按照一定的规则来决定表 T1 和 T2 中谁是驱动表、谁是被驱动表。驱动表用于外层循环，被驱动表用于内层循环；
  2. 以目标 SQL 中指定的谓词条件（如果有）去访问驱动表，得到结果集1；
  3. 对于结果集1 中的每一条记录，按照连接条件去被驱动表查找满足条件的记录。

结果集1 中有多少条记录，就得去被驱动表查找多少次。

如果 Oracle 使用的是嵌套循环连接，且在被驱动表的连接列上存在索引，那么 Oracle 在访问该索引时通常会使用单块读，这意味着驱动结果集有多少条记录就得访问该索引多少次。如果所需的数据不能从索引获得，还得回表，这个回表操作通常也是单块读。

向量I/O：为了提高嵌套循环的执行效率，Oracle 11g 引入了向量I/O。引入向量I/O后，Oracle 可以将原先一批单块读所需要耗费的物理 I/O 组合起来，然后用一个向量I/O 去批量处理它们，这样就实现了在单块读的数量不降低的情况下减少这些单块读所需要耗费的物理 I/O 数量，也就提高了嵌套循环的效率。

优缺点：

  • 如果驱动表所对应的结果集1的记录数较少，且在被驱动表的连接列上又存在唯一索引（或者选择性很好的非唯一索引），那么嵌套循环的执行效率就会很高；如果驱动表所对应的结果集的记录数很多，即使被驱动表的连接列上有索引，执行效率也不会很高。
  • 对于大表作为驱动表，效率取决于谓词条件能否将驱动结果集的数据量降下来。
  • 嵌套循环有一个其他连接方法没有的优点是：可以实现快速响应。

哈希连接

步骤为：

优缺点及适应场景：

  • 哈希连接不一定会排序，大多数情况下不需要排序。
  • 哈希连接的驱动表所对应的连接列的选择性应尽可能好，因为这个选择性会影响对应的 Hash Bucket 中的记录数，而 Hash Bucket 中的记录数又会直接影响从该 Hash Bucket 中查找品牌记录的效率。**如果一个 Hash Bucket 里所包含的记录数过多，则可能会严重降低所对应哈希连接的执行效率，此时典型的表现是该哈希连接执行了很长时间都没有结束，数据库所在服务器上的 CPU 占用率很高，但目标 SQL 所消耗的逻辑读很低，因为此时大部分时间都耗费在了遍历上述 Hash Bucket 里的所有记录上，而遍历 Hash Bucket 里的记录这个动作发生在 PGA 的工作区里，所以不耗费逻辑读。
  • 哈希连接只适用于 CBO，也只能用于等值连接条件。
  • 哈希连接很适合于小表和大表之间做表连接且连接结果集的记录数较多的情形，特别是小表的连接列的选择性非常好的情形，这时哈希连接的执行时间就可以近似看作所和全表扫描那个大表所耗费的时间相当。
  • 当两个表做哈希连接时，如果在施加里目标 SQL 中指定的谓词条件后的都的数据量较小的那个结果集所对应的 Hash Table 能够完全容纳在内存中（PGA 的工作区），则此时的哈希连接的执行效率很高。

笛卡尔连接

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

发表回复

您的电子邮箱地址不会被公开。 必填项已用 * 标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据。