来自农村的老实娃
分类： Oracle
2007-11-27 14:26:34
到目前为止，很多人都已经了解大量使用绑定变量来提高性能的方法；对于尚不清楚这种方法的用户，我将尽力以最简单的方式介绍该方法的核心概念。（同时，我还建议您访问 Tom Kyte 的 。在那里，您将了解使用绑定变量改善 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 =第一个查询和第二个查询将分别传递 CT 和 NY 作为and times_purchased > 3;
在此示例中，
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 ROWS_PROCESSED BUFFER_GETS CPU_TIME
------------ ------------------- - ---------- -------------- ----------- ----------
1 22981142 Y 1 9592 3219 0
0 22981142 Y 1 9592 3281 0
该视图显示了数据库记录的有关执行的统计数据。EXECUTIONS 列显示了使用绑定变量的不同的值执行查询的次数。输出中的 PEEKED 列（显示为 "P"）显示优化程序是否通过观察绑定变量获得适当的方案。
这些视图显示了一些额外信息，您不需要通过这些信息了解此特性的工作方式。数据库自动激活和使用自适应游标。
您看到过多少次下面的情况：一个查询拥有可能的最佳计划，但一些事情突然发生，导致该计划被抛弃。这些事情可能是某人重新对表进行了分析，或者 star_transformation 等影响优化程序的参数被改变 — 各种可能性是无穷无尽的。出于绝望，您可能会禁止对数据库进行任何更改，这意味着不收集数据库统计数据、不更改任何参数等等。
但这说起来容易做起来难。当数据模式改变时会发生什么？以自适应游标一节中显示的示例为例。现在，CUSTOMERS 表中填充了来自纽约的客户，因此 STATE_CODE 大部分为 "NY"。因此，当执行含有如下所示谓词的查询时：
where state_code = 'CT'系统执行一次全表扫描而非索引扫描。当谓词为：
where state_code = 'CT'由于仅将返回几行结果，因此系统使用索引。然而，如果模式发生改变 － 假设，突然出现大量来自康涅狄格 (state_code = 'CT') 的客户，导致含有 CT 的结果的百分比升至 70％，此时会发生什么？在该情况下，CT 查询应使用全表扫描。但是，由于您已禁止收集优化程序统计数据，优化程序不会了解模式的更改，并且会继续提供无效率的索引扫描路径。您可以做些什么？
如果 Oracle 使用最优计划，但在统计数据收集或数据库参数等底层因素更改时重新评估该计划，此时，当且仅当新计划更有效时数据库才会使用，结果如何？该方案非常理想，不是吗？它在 Oracle 数据库 11g 中已成为可能。让我们看一看这种方案的实现方式。
当 Oracle 将一个语句确定为多次执行或“可重复的”语句，声明周期开始。一旦确定了一个可重复语句，数据库即会捕获它的计划，并将该计划作为 SQL 计划基准线存储在数据库一个称为 SQL 管理库 (SMB) 的逻辑结构中。当出于任何原因为该查询计算新计划时，新计划也存储在 SMB 中。因此，SMB 用于存储查询的每个计划、计划的生成方式等等。
计划不会自动存储在 SMB 中。如果上述情况属实，SMB 将存储每类查询的所有计划，并将变得十分庞大。因此，您可以并且应该控制 SMB 存储的查询的数量。执行该操作有两种方法：自动为 SMB 中的所有可重复查询设定基准线，或手动加载应设定基准线的查询
让我们先看一个简单的示例：通过将数据库参数 optimizer_capture_sql_plan_baselines（默认值为 FALSE）的值设置为 TRUE，您可以使 SQL 计划管理特性自动捕获所有可重复查询的 SQL 计划基准线。很幸运，这是一个动态参数。
SQL> alter system optimizer_capture_sql_plan_baselines = true;该语句执行后，所有可重复语句的执行计划都作为 SQL 计划基准线存储在 SMB 中。SQL 计划基准线存储在名为 DBA_SQL_PLAN_BASELINES 的视图中。您也可以在 Enterprise Manager 中看到这些内容。要检查设定了基准线的计划，请打开 EM 并单击 "Server" 选项卡，如下图所示：
每当为查询生成一个新计划，原有计划就保留在 SMB 的历史中。然而，这也意味着 SMB 中将挤满计划历史。一个参数可以控制计划保留的星期数，它显示在 Plan Retention (Weeks) 的文本框中。在本屏幕中，该参数设置为 53 周。如果一个 SQL 计划基准线的未使用时间超过 53 周，该基准线将被自动清除。
该屏幕的中间部分有一个搜索框，可用于搜索 SQL 语句。在此处输入一个搜索字符串，然后按下 Go，您将看到如上图中显示的 SQL 语句和相关计划。每个设定了基准线的计划都有大量与之相关的状态信息。让我们看一看这些信息：
SQL> desc DBA_SQL_PLAN_BASELINES Name Null? Type ----------------------------------------- -------- --------------- SIGNATURE NOT NULL NUMBER SQL_HANDLE NOT NULL VARCHAR2(30) SQL_TEXT NOT NULL CLOB PLAN_NAME NOT NULL VARCHAR2(30) CREATOR VARCHAR2(30) ORIGIN VARCHAR2(14) PARSING_SCHEMA_NAME VARCHAR2(30) DESCRIPTION VARCHAR2(500) VERSION VARCHAR2(64) CREATED NOT NULL TIMESTAMP(6) LAST_MODIFIED TIMESTAMP(6) LAST_EXECUTED TIMESTAMP(6) LAST_VERIFIED TIMESTAMP(6) ENABLED VARCHAR2(3) ACCEPTED VARCHAR2(3) FIXED VARCHAR2(3) AUTOPURGE VARCHAR2(3) OPTIMIZER_COST NUMBER MODULE VARCHAR2(48) ACTION VARCHAR2(32) EXECUTIONS NUMBER ELAPSED_TIME NUMBER CPU_TIME NUMBER BUFFER_GETS NUMBER DISK_READS NUMBER DIRECT_WRITES NUMBER ROWS_PROCESSED NUMBER FETCHES NUMBER END_OF_FETCH_COUNT NUMBER如果单击计划的名称，则将显示计划的详细信息。以下是输出结果：
单击 Return，返回到如上图中显示的计划列表。现在，选择一个状态不为可接受的计划并单击 Evolve，查看系统是否会检查该计划来获取一个可能更有效的计划。弹出以下屏幕。
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
PLAN_LIST = SYS_SQL_PLAN_b5429522ee05ab0e
SYS_SQL_PLAN_b5429522e53beeec
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SYS_SQL_PLAN_b5429522e53beeec
-----------------------------------
It is already an accepted plan.
Plan: SYS_SQL_PLAN_b5429522ee05ab0e
-----------------------------------
Plan was verified: Time used 3.9 seconds.
Failed performance criterion: Compound improvement ratio <= 1.4.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 1 1
Elapsed Time(ms): 3396 440 7.72
CPU Time(ms): 1990 408 4.88
Buffer Gets: 7048 5140 1.37
Disk Reads: 4732 53 89.28
Direct Writes: 0 0
Fetches: 4732 25 189.28
Executions: 1 1
这是一份较好的比较报告，显示了计划的对比情况。如果显示特定计划拥有更好的性能，优化程序将使用该计划。如果新计划的性能改进并不明显，系统不会接收或使用该计划。SQL 性能管理允许您直接看到各计划的对比情况，从而使用最适当的计划。
通过执行 DBMS_SPM 程序包，您可以手动更改计划的可接受状态：
declare
ctr binary_integer;
begin
ctr := dbms_spm.alter_sql_plan_baseline (
sql_handle => 'SYS_SQL_e0b19f65b5429522',
plan_name => 'SYS_SQL_PLAN_b5429522ee05ab0e',
attribute_name => 'ACCEPTED',
attribute_value => 'NO'
);
end;
您可以禁用一个 SQL 计划基准线，使优化程序不能使用该计划。稍后，您可以再次启用该计划，使该计划重新获得使用。要禁用计划，使用以下命令： declare
ctr binary_integer;
begin
ctr := dbms_spm.alter_sql_plan_baseline (
sql_handle => 'SYS_SQL_e0b19f65b5429522',
plan_name => 'SYS_SQL_PLAN_b5429522ee05ab0e',
attribute_name => 'ENABLED',
attribute_value => 'NO'
);
end;
如果一个特定 SQL 语句的计划由一条基准线固定，解释计划会清楚地显示出来。在计划的末尾，您将看到一行内容，确定该计划已由一条基准线固定。
如果您熟悉存储大纲，您一定在考虑它与 SQL 计划管理有何差别。它们似乎在做同一件事：为查询强制一个特定的执行计划。但他们确实有细微的差别，即，使用 SQL 计划管理，系统可以评估基准线以获得更好的计划，也可以激活新计划来代替原有计划。而大纲是固定的。除非禁用或使用其他概要文件代替，否则大纲不能被覆盖。此外，计划基准线还拥有历史数据，您可以通过这些数据了解一段时间内计划的发展情况。
相关问题可能包括：如果查询上具有一个存储大纲，而基准线找到一个更好的方案，这时将出现什么情况？将会产生冲突，不是吗？其实并非如此。当使用大纲分析查询时，系统将捕获大纲强制的执行计划，将其作为查询的 SQL 计划基准线。如果优化程序为该语句找到其他的执行计划，系统也会捕获此计划，并将其存储在 SMB 中。但它不会成为可接受的计划。在使用该计划之前，您必须执行发展流程，证明新的执行计划优于当前的 SQL 计划基准线（原有存储大纲）。
概要文件不是“计划”，而是基于数据、作为执行计划的一部分进行存储的元数据。因此，在使用概要文件时，查询计划可以根据谓词发生变更。而在使用 SQL 计划基准线时，无论谓词中的值是什么，计划都是相同的。
那么，您可以在哪些示例场景中使用此特性？最好的示例就是升级或其他参数发生变化时。为一组查询设定基准线的一种方法是，使用 SQL 调整工具集，然后将语句从 STS 加载到 SPM。这样，您可以在 Oracle 数据库 10g 中生成一个 STS，将其导出，然后导入 Oracle 数据库 11g 中，随后运行 DBMS_SPM.UNPACK_STGTAB_BASELINE 程序包，导入作为 SQL 计划基准线的执行计划。稍后，当优化程序找到更好的计划时，会将该计划添加到 SMB 中，允许您进行比较。
要了解数据库目前如何智能地对待收到的各种请求以及如何作出回应，自适应游标和 SQL 计划管理仅仅是其中的两个示例。这两个特性允许您在两个方面都获得最大的好处 － 利用自适应游标，您可以使用绑定变量，且不存在使用非最佳计划的风险；使用 SQL 计划管理时，执行计划并不是固定不变的，而会在保持短期内稳定性的同时，随时间的推移不断变化。