如何调优 Oracle SQL系列文章：查询优化器介绍

如何调优 oracle sql系列文章 第四篇： 查询优化器概念之查询优化器介绍 。

4 查询优化器概念

本章描述了与查询优化器相关的最重要的概念，及其主要组件。

4.1 查询优化器介绍

查询优化器（简称为优化器）是内置数据库软件，用于确定 SQL 语句访问请求数据的最有效方法。

4.1.1 查询优化器的用途

优化程序尝试为 SQL 语句生成最佳执行计划。

优化程序在所有考虑的候选计划中选择成本最低的计划。优化程序使用可用的统计信息来计算成本。对于给定环境中的特定查询，成本计算考虑了查询执行的因素，例如I/O、CPU和通信。

例如，查询职位为经理的员工的信息。如果优化程序统计信息表明80％的员工是经理，那么优化程序可能会认为全表扫描最有效。但是，如果统计信息表明很少有员工是经理，那么通过读取索引后根据 rowid 进行回表访问可能比全表扫描更有效。

由于数据库有许多内部统计信息和工具可供使用，因此优化器通常比用户更有利于确定语句执行的最佳方法。因此，所有 SQL 语句都使用优化程序。

4.1.2 基于成本的优化

查询优化是选择执行 SQL 语句的最有效方法的过程。

SQL是一种非过程语言，因此优化器可以按任何顺序自由合并，重组和处理。数据库根据收集的有关访问数据的统计信息优化每个 SQL 语句。优化器通过检查多个访问方法（如全表扫描或索引扫描），不同的连接方法（如嵌套循环和散列连接），不同的连接顺序以及可能的转换来确定 SQL 语句的最佳计划。

对于给定的查询和环境，优化程序会为可能的计划的每个步骤分配相对数字成本，然后将这些值计算在一起以生成计划的总体成本估算。在计算替代计划的成本之后，优化程序选择具有最低成本估算的计划。出于这个原因，优化器有时被称为基于成本的优化器（CBO），以将其与基于规则的优化器（RBO）进行对比。

注意：
优化程序可能不会从一个版本的Oracle数据库到下一个版本做出相同的决策。 
在最新版本中，优化器可能会做出不同的决定，因为它可以获得更你全面的信息，并且可以进行更多的优化器转换。

4.1.3 执行计划

执行计划描述了 SQL 语句的推荐执行方法。

该计划显示了 Oracle 数据库用于执行 SQL 语句的步骤的组合。每个步骤要么从数据库物理上检索数据行，要么为发出语句的用户准备数据行。

执行计划显示整个计划的成本（在第0行中）和每个单独的操作。成本是一个内部单位，执行计划仅仅是显示它，以便与其它计划进行比较。 因此，您无法调整或更改成本值。

在下图中，优化程序为输入 SQL 语句生成两个可能的执行计划，使用统计信息估算其成本，比较其成本，然后选择成本最低的计划。

图4-1 执行计划

4.1.3.1 查询块

优化器的输入是 SQL 语句的解析表示。

原始 SQL 语句中的每个 SELECT 块由查询块在内部表示。 查询块可以是顶级语句，子查询或未合并视图。

示例4-1查询块

以下SQL语句由两个查询块组成。 括号中的子查询是内部查询块。 外部查询块（SQL语句的其余部分）检索由子查询提供ID的部门中的员工的名称。 查询表单确定查询块如何相互关联。

SELECT
 first_name, last_name
FROM
   hr.employees
WHERE
  department_id 
IN
(
SELECT
 department_id 
        
FROM
   hr.departments      
WHERE
  location_id = 1800
);

4.1.3.2 查询子计划

对于每个查询块，优化器生成查询子计划。

数据库从下到上分别优化查询块。 因此，数据库首先优化最里面的查询块并为其生成子计划，然后生成表示整个查询的外部查询块。

查询块的可能计划数与FROM子句中的对象数成比例。该数字随着对象的数量呈指数增长。例如，五个表的连接的可能计划明显高于两个表的连接的可能计划。

4.1.3.3 优化器的类比

优化器可以比作是在线旅行顾问。

骑自行车的人想要知道从A点到B点的最有效的自行车路线。查询就像指令“我需要从A点到B点的最有效路线”或“我需要从A点到到B点经过C点的最有效路线”。“旅行顾问使用内部算法来确定最有效的路线，该算法依赖于速度和难度等因素。骑车人可以通过使用诸如“我想尽快到达”或“我希望最简单的骑行”等指令来影响旅行顾问的决定。

在这个类比中，执行计划是旅行顾问生成的一个可能的路由。在内部，顾问可以将整个路由划分为几个子路由(子计划)，并分别计算每个子路由的效率。例如，旅行顾问可能会评估一个中等难度的子路由为15分钟，另一个难度最小的子路由为22分钟，以此类推。

顾问根据用户指定的目标以及有关道路和交通状况的可用统计数据，选择最有效（最低成本）的总体路线。统计越准确，建议越好。例如，如果顾问无法获取交通拥堵，道路封闭和恶劣的道路状况等信息，那么推荐的路线可能效率低（成本高）。