Oracle查询优化

查询优化

    本章讨论SQL处理、优化方法，以及查询优化器(通常称为优化器)如何选择执行SQL的特定计划。

    这一章包含以下部分:

• 查询优化器的概述
• 优化器访问路径的概述
• 连接概述
• 阅读和理解执行计划
• 控制优化器行为

1.1查询优化器的概述

    优化器是Oracle数据库内置的软件，它决定了执行SQL语句的最有效方式。

    本节包含以下主题:

• 优化器操作
• 查询优化器的组件
• 绑定变量窥视

1.1.1优化器操作

    数据库可以以多种方式执行SQL语句，比如全表扫描、索引扫描、嵌套循环和散列连接。在确定执行计划时，优化器会考虑与对象相关的许多因素和查询中的条件。这是SQL处理中重要的一步，它会极大地影响执行时间。

    当用户提交执行的SQL语句时，优化器执行以下步骤:

1. 优化器根据可用的访问路径和提示为SQL语句生成一组潜在的计划。

2. 优化器根据数据字典中的统计数据估计每个计划的成本。统计信息包括关于表、索引和被语句访问的分区的数据分布和存储特征的信息。

   成本是与使用特定计划执行语句所需的预期资源的估计值成比例的。优化器根据估计的计算机资源计算访问路径和连接请求的成本，其中包括输入/输出、CPU和内存。

   与成本较低的计划相比，成本较高的系列计划执行的时间更长。当使用并行计划时，资源使用与运行时间没有直接关系。

3. 优化器对计划进行比较，并选择最低成本的计划。

   优化器的输出是一个执行计划，它描述了最优的执行方法。这些计划显示了Oracle数据库用于执行SQL语句的步骤的组合。每个步骤要么从数据库中检索行，要么为用户准备它们。

对于Oracle数据库处理的任何SQL语句，优化器将执行下表中列出的操作。

操作	描述
评估表达式和条件	优化器首先对包含常量的表达式和条件进行尽可能全面的评估。
声明转换	对于涉及到相关子查询或视图的复杂语句，优化器可能会将原始语句转换为等价的连接语句。
选择优化的目标	优化器确定优化的目标。参见“选择优化器目标”。
选择访问路径	对于由语句访问的每个表，优化器选择一个或多个可用的访问路径来获取表数据。请参阅“优化器访问路径的概述”。
选择加入命令	对于连接多于两个表的连接语句，优化器将首先选择哪一对表连接在一起，然后将哪个表连接到结果，以此类推。请参阅“查询优化器如何选择连接的执行计划”。

有时，您可能拥有比优化器可用的特定应用程序数据更多的信息。在这种情况下，您可以使用SQL语句中的提示来指示优化器如何执行语句。

1.1.2查询优化器的组件

查询优化器操作包括:

• 查询转换

• 估计

• 生成计划
  

下图展示了优化器组件。

1.1.2.1查询转换

语句的每个查询部分都被称为查询块。查询转换器的输入是经过解析的查询，它由一组查询块表示。

在下面的示例中，SQL语句由两个查询块组成。圆括号中的子查询是内部查询块。外部查询块是SQL语句的其余部分，它检索由子查询提供id的部门中的雇员的姓名。

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

查询表单决定了查询块是如何相互关联的。转换器决定是否将原来的SQL语句重写为可以更有效地处理的语义等价的SQL语句。

查询转换器使用了几个查询转换技术，包括以下内容:

• 视图合并
• 谓词推入
• 子查询展开
• 使用物化视图重写查询

这些转换的任何组合都可以应用于给定的查询。

1.1.2.1.1视图合并

查询中引用的每个视图都被解析器扩展为一个单独的查询块。块本质上表示视图定义，因此是视图的结果。优化器的一个选项是单独分析视图查询块，并生成一个视图子计划。然后，优化器通过使用视图子计划来生成整个查询计划，从而处理其余的查询。这种技术通常会导致一个次优查询计划，因为视图是单独优化的。

在视图合并中，转换器将表示视图的查询块合并到包含的查询块中。例如，假设您创建了如下视图:

CREATE VIEW employees_50_vw AS
  SELECT employee_id, last_name, job_id, salary, commission_pct, department_id
  FROM   employees
  WHERE  department_id = 50;

然后，您可以按照以下的方式查询视图:

SELECT employee_id
FROM   employees_50_vw 
WHERE  employee_id > 150;

优化器可以使用视图合并来将employees_50_vw的查询转换为以下等价查询:

SELECT employee_id
FROM   employees
WHERE  department_id = 50 
AND    employee_id > 150;

视图合并优化适用于只包含选择、投影和连接的视图。也就是说，可合并的视图不包含集合运算、聚合函数、 DISTINCT, GROUP BY, CONNECT BY、等等。

为了使优化器能够对用户发出的任何查询使用视图合并，您必须将合并任意视图特权授予用户。将合并视图特权授予特定视图的用户，以使优化器可以使用视图合并来查询这些视图。只有在特定的条件下才需要这些特权，例如，由于安全检查失败，查询时视图不会被合并。

1.1.2.1.2谓词推入

在谓词推入中，优化器“将”相关谓词从包含的查询块“推入”视图查询块。对于未合并的视图，此技术改进了未合并视图的子计划，因为数据库可以使用推入式谓词来访问索引或作为过滤器使用。

例如，假设您创建了一个引用两个employee表的视图。视图的定义是使用UNION 集合操作符的复合查询，如下所列:

CREATE VIEW all_employees_vw AS
  ( SELECT employee_id, last_name, job_id, commission_pct, department_id
    FROM   employees )
  UNION
  ( SELECT employee_id, last_name, job_id, commission_pct, department_id
    FROM   contract_workers );

然后，您可以按照以下的方式查询视图:

SELECT last_name
FROM   all_employees_vw
WHERE  department_id = 50;

因为视图是一个复合查询，所以优化器不能将视图的查询合并到访问查询块中。相反，优化器可以通过将WHERE子句条件部门=50的谓词转换为视图的复合查询来转换访问语句。等效转换查询如下:

SELECT last_name
FROM   ( SELECT employee_id, last_name, job_id, commission_pct, department_id
         FROM   employees
         WHERE  department_id=50
         UNION
         SELECT employee_id, last_name, job_id, commission_pct, department_id
         FROM   contract_workers
         WHERE  department_id=50 );

1.1.2.1.3子查询展开

在子查询展开中，优化器将一个嵌套的查询转换成等价的连接语句，然后优化连接。这种转换使优化器能够利用连接优化器技术。只有当得到的连接语句保证返回与原始语句相同的行，并且如果子查询不包含像AVG这样的聚合函数时，优化器才能执行此转换。

例如，假设您连接为user sh并执行以下查询:

SELECT * 
FROM   sales
WHERE  cust_id IN ( SELECT cust_id FROM customers );

因为客户表的custid列是主键，优化器可以将复杂查询转换为以下连接语句，该语句保证返回相同的数据:

SELECT sales.* 
FROM   sales, customers
WHERE  sales.cust_id = customers.cust_id;

如果优化器不能将一个复杂的语句转换成一个连接语句，那么它就会为父语句和子查询选择执行计划，就好像它们是单独的语句一样。然后优化器执行子查询，并使用返回来执行父查询的行。为了提高整个查询计划的执行速度，优化器可以有效地命令子计划。

1.1.2.1.4使用物化视图重写查询

物化视图就像一个查询，结果是数据库具体化并存储在一个表中。当数据库找到与与物化视图相关联的查询的用户查询时，数据库可以根据物化视图重新编写查询。这种技术改进了查询执行，因为大多数查询结果都是预先计算的。

查询转换器查找与用户查询兼容的任何物化视图，并选择一个或多个物化视图来重写用户查询。使用物化视图来重写查询是基于成本的。也就是说，如果没有物化视图生成的计划比物化视图生成的计划成本更低，优化器不会重新编写查询。

考虑下面的物化视图，cal月中销售，它汇总了每个月销售的美元数量:

CREATE MATERIALIZED VIEW cal_month_sales_mv
  ENABLE QUERY REWRITE 
AS
  SELECT t.calendar_month_desc, SUM(s.amount_sold) AS dollars
  FROM   sales s, times t 
  WHERE  s.time_id = t.time_id
  GROUP BY t.calendar_month_desc;

假设一个月的销售数字大约是100万。这个观点是每月卖出的美元数量的预先计算的总量。请考虑下面的查询，该查询要求每个月销售的金额的总和:

SELECT t.calendar_month_desc, SUM(s.amount_sold)
FROM   sales s, times t
WHERE  s.time_id = t.time_id
GROUP BY t.calendar_month_desc;

在没有查询重写的情况下，数据库必须直接访问销售表，并计算销售的金额的总和。该方法涉及从销售中读取数百万行，这必然会增加查询响应时间。join还会进一步减慢查询响应，因为数据库必须计算几百万行中的连接。在重写查询时，优化器会透明地重新编写查询:

SELECT calendar_month, dollars
FROM   cal_month_sales_mv;

1.1.2.2评估

评估器决定一个给定执行计划的总体成本。评估器有三种不同的方法来达到这个目的:

• 选择性

这个估量表示来自行集的行数的一小部分，选择性与一个查询谓词绑定，例如lastname='Smith'，或谓词的组合。

• 基数

这个度量表示行集中的行数。

• 成本

这个度量单位代表被使用的工作单位或资源单位。查询优化器使用磁盘输入、CPU使用和内存使用作为工作单元。

如果统计数据可用，那么评估器就会使用它们来计算这些度量。统计数据提高了这些措施的准确性。

1.1.2.2.1选择性

选择性表示行集中的一小部分行。行集可以是一个基表、一个视图，也可以是由操作符组成的一个连接或一个组的结果。选择性与查询谓词绑定，如lastname='Smith'，或谓词组合，如lastname='Smith' AND jobtype='Clerk'。

谓词从行集中过滤特定数量的行。因此，谓词的选择性表明有多少行通过谓词测试。选择性范围从0.0到1.0。0.0的选择性意味着没有从行集中选择行，而1.0的选择性意味着所有的行都是被选中的。当值接近0时，当值接近1.0时，谓词会变得更有选择性，而当值接近0时，则会选择更少的选择性(或更多的非选择性)。

优化器根据统计数据是否可用来估计选择性:

• 统计数据不可用

优化器根据优化采样初始化参数的值，要么使用动态统计数据，要么使用内部默认值。数据库使用不同的内部默认值，这取决于谓词类型。例如，一个相等谓词(lastname='Smith')的内部默认值比一个范围谓词(lastname>'Smith')要低，因为一个相等的谓词被期望返回一个更小的行数。参见“控制动态统计”。

• 统计数据可用

当统计数据可用时，评估人员使用它们来估计选择性。假设有150个不同的员工姓。对于相等谓词lastname='Smith'，选择性是lastname的不同值的倒数，在本例中是。因为查询选择了包含150个不同值中的1行。

如果在lastname列上有一个直方图，那么估计器使用的是柱状图，而不是不同的值的数量。柱状图捕获了一个列中不同值的分布，因此它产生了更好的选择性估计，特别是对于包含倾斜数据的列。看到“查看直方图”。

1.1.2.2.2基数

基数表示行集中的行数。在这个上下文中，行集可以是一个基表、一个视图，也可以是由操作符组成的一个连接或组的结果。

1.1.2.2.3成本

成本代表操作中使用的工作或资源的单位。优化器使用磁盘输入、CPU使用和内存使用作为工作单元。该操作可以扫描一个表，通过使用索引、连接两个表或对行集进行排序来访问表中的行，成本是数据库执行查询并产生结果时所要发生的工作单元的数量。

访问路径决定从基表获取数据所需的工作单元数。访问路径可以是一个表扫描，一个快速的索引扫描，或者一个索引扫描。

• 表扫描或快速全索引扫描

在一个表扫描或快速全索引扫描中，数据库在一个输入/输出中从磁盘读取多个块。因此，扫描的成本取决于要扫描的块的数量和多块读取计数的值。

• 索引扫描

索引扫描的成本取决于b树的级别、要扫描的索引叶块的数量，以及使用索引键中的rowid来获取的行数。使用rowids获取行的成本取决于索引聚类因素。请参见“对块进行评估，而不是行”。

连接成本表示连接的两个行集的单个访问成本的组合，加上连接操作的成本。

1.1.2.3生成计划

计划生成器通过尝试不同的访问路径、连接方法和连接顺序来探索查询块的各种计划。许多计划是可能的，因为不同的访问路径、连接方法和连接顺序，数据库可以使用它们来产生相同的结果。生成器的目的是用最低的成本来选择这个计划。

1.1.2.3.1连接顺序

连接顺序是不同连接项，例如表被访问和连接在一起的顺序。假设数据库连接了table1、table2和table3。连接顺序如下:

1. 数据库访问表1。
2. 数据库访问table2并将其行连接到table1。
3. 数据库访问table3，并将其数据连接到table1和table2之间的连接的结果。

1.1.2.3.2查询子计划

优化器通过一个单独的查询块来表示每个嵌套的子查询或未合并的视图，并生成一个子计划。数据库分别从底部优化查询块。因此，数据库首先优化最内部的查询块，并为它生成一个子计划，然后最后生成代表整个查询的外部查询块。

查询块的可能计划的数量与FROM子句中的连接项的数量成比例。这个数字随着连接项的数量呈指数级增长。例如，5个表的连接的可能计划要比两个表的连接的可能计划要多得多。

1.1.2.3.3计划选择截止

计划生成器使用内部的截止时间来减少在寻找最低成本计划时所尝试的计划的数量。截止日期是基于当前最佳计划的成本。如果当前的最大成本是巨大的，那么计划生成器就会探索其他的计划来寻找更低的成本计划。如果当前的最佳成本是很小的，那么选择器就会迅速结束搜索，因为进一步的成本改进将不会很重要。

如果计划生成器从最初的连接顺序开始，产生一个成本接近于最优的计划，那么终止工作就会很好。找到一个好的初始连接顺序是一个困难的问题。

1.1.3绑定变量窥视

在绑定变量窥视(也称为绑定窥视)中，当数据库执行一个语句的硬解析时，优化器会查看绑定变量中的值。

当查询使用文字时，优化器可以使用文字值来查找最佳方案。然而，当查询使用绑定变量时，优化器必须选择最好的计划，而不需要在SQL文本中出现文字。这个任务可能非常困难。通过查看绑定值，优化器可以确定WHERE子句条件的选择性，就像使用了字面量一样，从而改进了计划。

例1.1：绑定窥视

假设在数据库中存在以下100,000行emp表。这个表有如下定义:

SQL> DESCRIBE emp

Name                   Null?    Type
---------------------- -------- ----------------------------------
ENAME                           VARCHAR2(20)
EMPNO                           NUMBER
PHONE                           VARCHAR2(20)
DEPTNO                          NUMBER

在deptno列中，数据明显倾斜。值10是在99.9%的行中找到的。每一个其他deptno值(0到9)都存在于1%的行中。您已经收集了该表的统计信息，因此在deptno列中生成了一个直方图。您可以使用bind值9定义一个bind变量和查询emp，如下所列:

VARIABLE deptno NUMBER
EXEC :deptno := 9

SELECT /*ACS_1*/ count(*), max(empno) 
FROM   emp 
WHERE  deptno = :deptno;

查询返回10行：

COUNT(*) MAX(EMPNO)
---------- ----------
    10         99

为了生成查询的执行计划，数据库在硬解析期间偷看了值9。优化器生成了选择性估计，就好像用户已经执行了以下查询:

select /*ACS_1*/ count(*), max(empno)
from emp
where deptno = 9;

在选择计划时，优化器只在硬解析期间对绑定值进行观察。对于所有可能的值，这个计划可能不是最优的。

1.1.3.1自适应游标共享

自适应游标共享特性使一个包含绑定变量的语句可以使用多个执行计划。游标共享是“适应性”的，因为光标可以调整其行为，这样数据库就不会每次执行或绑定变量值时都使用相同的计划。

对于适当的查询，数据库会监控不同的绑定值的数据，以确保针对特定绑定值的游标的最佳选择。例如，优化器可能会为绑定值9和绑定值10的不同计划选择一个计划。光标共享是“自适应”的，因为光标调整了它的行为，所以相同的计划并不总是用于每次执行或者绑定变量值。

默认情况下，为数据库启用了自适应游标共享，并且不能禁用。注意，自适应游标共享并不适用于包含超过14个绑定变量的SQL语句。

1.1.3.1.1绑定敏感游标

绑定敏感游标是一个最优计划取决于绑定变量值的游标，数据库监视一个使用不同绑定值的绑定敏感游标的行为，以确定哪个不同的计划是有益的。

优化器确定一个游标是否是绑定敏感游标包括以下内容:

• 优化器窥视了绑定变量的值来生成一个选择性的估算。
• 在包含绑定值的列上存在一个柱状图。

例1.2：绑定敏感游标

在该示例中，使用deptno的绑定值9来查询emp表。现在运行DBMS_XPLAN.DISPLAY_CURSOR，来显示查询计划:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

输出一下内容：

----------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time|
----------------------------------------------------------------------------------
|  0 | SELECT STATEMENT             |        |      |       |   2 (100)|         |
|  1 |  SORT AGGREGATE              |        |    1 |    16 |          |         |
|  2 |   TABLE ACCESS BY INDEX ROWID| EMP    |    1 |    16 |   2   (0)| 00:00:01|
|* 3 |    INDEX RANGE SCAN          | EMP_I1 |    1 |       |   1   (0)| 00:00:01|
----------------------------------------------------------------------------------

译者标注：在pl/sql中，选中SQL，按F5就可以直接查看。详细使用方法见DBMS_XPLAN.DISPLAY_CURSOR()看执行计划

该计划表明优化器选择了一个索引范围扫描，这是由于该值9的选择性(仅为1%)所期望的。您可以查询V$SQL来查看关于游标的统计信息:

COL BIND_SENSI FORMAT a10
COL BIND_AWARE FORMAT a10
COL BIND_SHARE FORMAT a10
SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI", 
       IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"
FROM   V$SQL
WHERE  SQL_TEXT LIKE 'select /*ACS_1%';

如下输出所示，该语句存在一个子游标，并已执行一次。少量的缓冲区被关联到子游标。因为deptno数据是倾斜的，所以数据库创建了一个直方图。这个直方图引导数据库将光标标记为绑定敏感的( IS_BIND_SENSITIVE是Y)。

CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
           0          1          56 Y          N          Y

对于带有新绑定值的查询的每次执行，数据库将记录新值的执行统计数据，并将其与前一个值的执行统计数据进行比较。如果执行统计数据差异很大,那么数据库标记光标为识别绑定。

1.1.3.1.2识别绑定游标

识别绑定游标是一个绑定敏感游标，它会针对不同的值选择不同的计划。在游标被识别之后，优化器根据绑定值和它的选择性估计选择未来执行的计划。

当一个带有绑定敏感游标的语句执行时，数据库将决定是否对光标进行标记。这个决定取决于光标是否为不同的绑定值产生了显著不同的数据访问模式。如果数据库标记了游标识别绑定，那么下一次光标执行该数据库时将执行以下操作:

• 基于绑定的值生成一个新的计划
• 标记为该语句生成的原始光标为不可共享的(V$SQL.IS_SHAREABLE是N)。这个游标不再可用，它将是第一个在共享的SQL区域中老化的游标。

例1.3：识别绑定游标

在该示例中，您使用bind值9来查询emp。现在使用bind值10来查询emp。该查询返回99,900行，其中包含值10:

COUNT(*)   MAX(EMPNO)
---------- ----------
99900      100000

因为这个语句的游标是绑定的，所以优化器假设游标可以被共享。因此，优化器值10的索引范围作为值9的。

V$SQL输出显示相同的绑定敏感的游标第二次执行(使用10的查询)，并且需要比第一次执行更多的缓冲区。

SELECT CHILD_NUMBER, EXECUTIONS, BUFFER_GETS, IS_BIND_SENSITIVE AS "BIND_SENSI", 
       IS_BIND_AWARE AS "BIND_AWARE", IS_SHAREABLE AS "BIND_SHARE"
FROM   V$SQL
WHERE  SQL_TEXT LIKE 'select /*ACS_1%';
 
CHILD_NUMBER EXECUTIONS BUFFER_GETS BIND_SENSI BIND_AWARE BIND_SHARE
------------ ---------- ----------- ---------- ---------- ----------
           0          2        1010 Y          N          Y

现在，您第二次使用值10执行查询。数据库比较了以前执行的统计数据，并将光标标记为二进制的。在这种情况下，优化器决定一个新计划是有必要的，因此它对语句执行一个硬解析，并生成一个新计划。新计划使用全表扫描，而不是索引范围扫描: