Oracle 11g新特性之自适应游标与计划管理（1）

作者：Arup Nanda 

到目前为止，很多人都已经了解大量使用绑定变量来提高性能的方法；对于尚不清楚这种方法的用户，我将尽力以最简单的方式介绍该方法的核心概念。（同时，我还建议您访问 Tom Kyte 的 asktom.oracle.com。在那里，您将了解使用绑定变量改善 SQL 语句性能的重要性，以及如何在几种语言中使用这些变量。）

假定您的 CUSTOMERS 表拥有一个名为 STATE_CODE 的列，该列使用美国州名两位字母的缩写存储客户所在州的信息，如 CT、NY 等等。如果希望找出来自康涅狄格州 ('CT') 且购买次数达三次以上的客户的数量，您最可能执行以下查询：

select count(1)
from customers
where state_code = 'CT'
and times_purchased > 3;

当您执行上述查询时，Oracle 必须执行分析活动，为您执行的 SQL 语句生成执行计划。分析过后，即可执行查询。在概念上，分析与编译软件中的代码类似；如果您使用 C++ 编写代码，则不能在操作系统中运行这些代码 － 首先，您必须编译这些代码，使它们成为可执行文件。分析活动从 SQL 语句中生成可执行文件。

现在，假设另一位用户发布了如下所示的语句：

select count(1)
from customers
where state_code = 'NY'
and times_purchased > 3;

该语句几乎与上述查询完全相同，除了一点：搜索的 state_code 为 NY 而非 CT。理想情况下，分析过的代码与前一查询相同，且将在运行时应用文字值。但 Oracle 将查询的编写方式解释为不同的方式，因此必须再次对第二个查询进行分析。

假设查询按以下方式编写：

select count(1)
from customers
where state_code = <StateCode>
and times_purchased > 3;

第一个查询和第二个查询将分别传递 CT 和 NY 作为 的值。此时，不必重新对查询进行分析。

在此示例中， 在概念上被称为绑定变量，该变量是将在执行期间传递的值的占位符。绑定变量的表示格式为 :VariableName，如下所示：

where state_code = :state_code

如果您的代码中不含有绑定变量，而是使用 where state_code = 'CT' 等对文字值的引用，您可以通过指定一个初始化参数将所有文字强制转换成绑定变量：

cursor_sharing = force

该参数将导致语句 where state_code = 'CT' 被重新编写为 where state_code = ":SYS_0001"，其中 SYS_0001 是系统生成的变量名。此方法将使这些语句变成相同的语句。

绑定变量的问题
既然绑定变量如此有效，我们为什么不一直使用这种变量呢？我们不是拥有一种灵丹妙药 — cursor_sharing — 可以将所有糟糕的代码转换成可共享的语句吗？（那些已经熟悉其中理由（尤其是绑定观察概念）的读者可以直接跳至“自适应游标”一节。）

假设 STATE_CODE 列有一个索引。该列中的值如下所示：

select state_code, count(1)
from customers
group by state_code;

ST   COUNT(1)
-- ----------
NY     994901
CT       5099

如您所见，数据出现了严重的偏差；大约 5% 的行中含有 'CT'，而其余的行中含有 'NY'。考虑到各州的人口数量，得到这种结果不足为奇。现在，让我们看一看为之前显示的查询生成了哪种类型的执行计划：

SQL> set autot traceonly explain
SQL> select * from customers where state_code = 'NY' and times_purchased > 3
  2  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2008213504

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   895K|    26M|  1532   (9)| 00:00:19 |
|*  1 |  TABLE ACCESS FULL| CUSTOMERS |   895K|    26M|  1532   (9)| 00:00:19 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TIMES_PURCHASED">3 AND "STATE_CODE"='NY')

该查询执行了一次全表扫描 － 由于该查询返回 95％ 的行，且索引扫描将非常昂贵，因此这是一次正确的操作。现在，使用 'CT' 执行同一个查询：

SQL> c/NY/CT
  1* select * from customers where state_code = 'CT' and times_purchased > 3
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 4876992

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |  4589 |   138K|    56   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS     |  4589 |   138K|    56   (2)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IN_CUST_STATE |  5099 |       |    12   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TIMES_PURCHASED">3)
   2 - access("STATE_CODE"='CT')

它使用了索引。同样，这也是正确的操作。含有 CT 的行数仅占总行数的 5%，因此进行索引扫描是有利的。
让我们看一看使用绑定变量时的行为。以下是 Oracle 数据库 10g 中的演示行为。

 

SQL> var state_code varchar2(2)
SQL> exec :state_code := 'CT'

PL/SQL procedure successfully completed.

SQL> select max(times_purchased) from customers where state_code = :state_code
  2  /

Execution Plan
----------------------------------------------------------
Plan hash value: 296924608

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |     6 |  1511   (8)| 00:00:19 |
|   1 |  SORT AGGREGATE    |           |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| CUSTOMERS |   500K|  2929K|  1511   (8)| 00:00:19 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("STATE_CODE"=:STATE_CODE)

优化程序选择对 CUSTOMERS 表进行全表扫描。当我们仅搜索 CT（其数量只占记录总数的 5％）时，难道不应该使用索引吗？是什么原因使优化程序选择全表扫描而非索引扫描呢？
答案是一种称为绑定观察 的现象。先前，当您使用设置为 'NY' 的绑定变量值运行该查询时，优化程序必须为查询的第一次运行进行艰难的分析。在这样做的同时，优化程序观察绑定变量来确定为其分配的值。该值是 'NY'。由于 'NY' 的数量大约占总行数的 95%，优化程序选择了全表扫描（与预期的情况相同）。另外，它还冻结了查询的计划。接下来，当我们使用设置为 'CT' 的变量值运行同一个查询时，优化程序不会重新计算计划，而是使用了与之前相同的计划，即使该计划不是满足目标的最佳方案。如果您在查询中使用了文字值 'CT' 而非绑定变量，那么优化程序会选择正确的计划。

因此，如您所见，尽管绑定变量在大多数情况下都非常有效，当值的选择性将显著影响计划时（正如在此示例中，值 'CT' 和 'NY' 的选择性分别为 5％ 和 95％），绑定变量并不可靠。如果数据分布均匀，所有值的选择性几乎相同，执行计划将保持不便。因此，聪明的 SQL 编码人员将会选择在何时打破使用绑定变量的基本准则，改用文字值。

自适应游标
但如果您没有很多聪明的编码人员，或者没有时间重新编写这些语句，该怎么办？Oracle 是否提供了一些智能的替代方案？

是这样的。使用 Oracle 数据库 11g，游标突然拥有了一种新的智能。不是在执行查询的时候盲目使用已缓存的执行计划，而是在绑定变量的值更改时，根据实际情况确定是否需要重新计算计划。如果游标中含有绑定变量，数据库会对其进行观察，确定传递给变量的值的类型以及是否需要重新计算计划。如果需要重新计算计划，则游标标记为 "Bind-Sensitive"。

之前显示的示例查询可以很好地表现这一点。数据库将基于绑定变量的值使用正确的优化程序方案。您不需要执行任何操作；上述操作将自动执行。

字典视图 V$SQL 已经修改，添加了两列：IS_BIND_SENSITIVE 和 IS_BIND_AWARE。让我们看一看它们的使用方法：

select is_bind_sensitive, is_bind_aware, sql_id, child_number
from v$sql
where sql_text =
'select count(1) from customers where state_code = 
:state_code and times_purchased > 3'

I I SQL_ID        CHILD_NUMBER
- - ------------- ------------
Y Y 7cv5271zx2ttg            0
Y N 7cv5271zx2ttg            1

让我们看一看这些列的含义。Oracle 对游标进行观察，并确定值变化的方式。如果不同的值可能会改变计划，则游标标记为 "Bind-Sensitive"，IS_BIND_SENSITIVE 列显示 "Y"。在几次执行后，数据库对游标和值有了更多了解，并确定游标是否应根据值的变化来改变计划。如果情况如此，则游标被称为 "Bind-Aware"，IS_BIND_AWARE 列显示 "Y"。总结：Bind-Sensitive 游标是可能会更改计划的游标，而 Bind-Aware 游标是实际更改计划的游标。

一个新视图 V$SQL_CS_HISTOGRAM 显示了 SQL 语句执行的次数，为每个子游标划分了三个存储区，如下所示：

select * from v$sql_cs_histogram
where sql_id = '7cv5271zx2ttg'
/

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBER  BUCKET_ID      COUNT
-------- ---------- ------------- ------------ ---------- ----------
45C8218C 2144429871 7cv5271zx2ttg            5          0          0
45C8218C 2144429871 7cv5271zx2ttg            5          1          2
45C8218C 2144429871 7cv5271zx2ttg            5          2          0
45C8218C 2144429871 7cv5271zx2ttg            4          0          8
... and so on ...
45C8218C 2144429871 7cv5271zx2ttg            0          2          0

由于自适应游标共享特性根据绑定变量的值使用正确的计划，数据库必须在某处存储这些信息。它通过另一个新视图 V$SQL_CS_SELECTIVITY 显示这些信息，该视图显示传递给绑定变量的不同值的选择性。

select * from v$sql_cs_selectivity
where sql_id = '7cv5271zx2ttg'
/

ADDRESS  HASH_VALUE SQL_ID        CHILD_NUMBE PREDICATE   R LOW      HIGH
-------- ---------- ------------- ----------- ----------- - -------- ----------
45C8218C 2144429871 7cv5271zx2ttg           5 =STATE_CODE 0 0.895410   1.094391
45C8218C 2144429871 7cv5271zx2ttg           4 =STATE_CODE 0 0.004589   0.005609
45C8218C 2144429871 7cv5271zx2ttg           4 =STATE_CODE 1 0.002295   0.002804
45C8218C 2144429871 7cv5271zx2ttg           3 =STATE_CODE 0 0.002295   0.002804
45C8218C 2144429871 7cv5271zx2ttg           0 =STATE_CODE 0 0.004589   0.005609

该视图显示了大量信息。PREDICATE 列显示了用户使用的各种谓词（WHERE 条件）。LOW 和 HIGH 值显示传递的值的范围。
最后，第三个新视图 V$SQL_CS_STATISTICS 显示了标记为 Bind-Aware 或 Bind-Sensitive 的游标执行的操作。

select     child_number,
bind_set_hash_value,
peeked,
executions,
rows_processed,
buffer_gets,
cpu_time
from v$sql_cs_statistics
where sql_id = '7cv5271zx2ttg';

CHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS
------------ ------------------- - ----------
           1            22981142 Y          1
           0            22981142 Y          1
ROWS_PROCESSED BUFFER_GETS   CPU_TIME
-------------- ----------- ----------
 9592        3219          0
 9592        3281          0

 该视图显示了数据库记录的有关执行的统计数据。EXECUTIONS 列显示了使用绑定变量的不同的值执行查询的次数。输出中的 PEEKED 列（显示为 "P"）显示优化程序是否通过观察绑定变量获得适当的方案。
这些视图显示了一些额外信息，您不需要通过这些信息了解此特性的工作方式。数据库自动激活和使用自适应游标。

1