oracle11g新特性-自適應(yīng)游標(biāo)與 SQL 計劃管理

dazheng 2007-12-18

展開全文

自適應(yīng)游標(biāo)與 SQL 計劃管理

使用在每次智能選擇正確計劃的綁定變量，并確保新的執(zhí)行計劃在使用前已經(jīng)過完善。

到目前為止，很多人都已經(jīng)了解大量使用綁定變量來提高性能的方法；對于尚不清楚這種方法的用戶，我將盡力以最簡單的方式介紹該方法的核心概念。（同時，我還建議您訪問 Tom Kyte 的 asktom.oracle.com。在那里，您將了解使用綁定變量改善 SQL 語句性能的重要性，以及如何在幾種語言中使用這些變量。）

假定您的 CUSTOMERS 表擁有一個名為 STATE_CODE 的列，該列使用美國州名兩位字母的縮寫存儲客戶所在州的信息，如 CT、NY 等等。如果希望找出來自康涅狄格州 (‘CT‘) 且購買次數(shù)達(dá)三次以上的客戶的數(shù)量，您最可能執(zhí)行以下查詢：

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

當(dāng)您執(zhí)行上述查詢時，Oracle 必須執(zhí)行分析活動，為您執(zhí)行的 SQL 語句生成執(zhí)行計劃。分析過后，即可執(zhí)行查詢。在概念上，分析與編譯軟件中的代碼類似；如果您使用 C++ 編寫代碼，則不能在操作系統(tǒng)中運行這些代碼－首先，您必須編譯這些代碼，使它們成為可執(zhí)行文件。分析活動從 SQL 語句中生成可執(zhí)行文件。

現(xiàn)在，假設(shè)另一位用戶發(fā)布了如下所示的語句：

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

該語句幾乎與上述查詢完全相同，除了一點：搜索的 state_code 為 NY 而非 CT。理想情況下，分析過的代碼與前一查詢相同，且將在運行時應(yīng)用文字值。但 Oracle 將查詢的編寫方式解釋為不同的方式，因此必須再次對第二個查詢進行分析。

假設(shè)查詢按以下方式編寫：

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

第一個查詢和第二個查詢將分別傳遞 CT 和 NY 作為 <StateCode> 的值。此時，不必重新對查詢進行分析。

在此示例中，<StateCode> 在概念上被稱為綁定變量，該變量是將在執(zhí)行期間傳遞的值的占位符。綁定變量的表示格式為 :VariableName，如下所示：

where state_code = :state_code

如果您的代碼中不含有綁定變量，而是使用 where state_code = ‘CT‘ 等對文字值的引用，您可以通過指定一個初始化參數(shù)將所有文字強制轉(zhuǎn)換成綁定變量：

cursor_sharing = force

該參數(shù)將導(dǎo)致語句 where state_code = ‘CT‘ 被重新編寫為 where state_code = ":SYS_0001"，其中 SYS_0001 是系統(tǒng)生成的變量名。此方法將使這些語句變成相同的語句。

綁定變量的問題

既然綁定變量如此有效，我們?yōu)槭裁床灰恢笔褂眠@種變量呢？我們不是擁有一種靈丹妙藥 — cursor_sharing — 可以將所有糟糕的代碼轉(zhuǎn)換成可共享的語句嗎？（那些已經(jīng)熟悉其中理由（尤其是綁定觀察概念）的讀者可以直接跳至“自適應(yīng)游標(biāo)”一節(jié)。）

假設(shè) STATE_CODE 列有一個索引。該列中的值如下所示：

select state_code, count(1)
from customers
group by state_code;
ST   COUNT(1)
-- ----------
NY     994901
CT       5099

如您所見，數(shù)據(jù)出現(xiàn)了嚴(yán)重的偏差；大約 5% 的行中含有 ‘CT‘，而其余的行中含有 ‘NY‘?？紤]到各州的人口數(shù)量，得到這種結(jié)果不足為奇?，F(xiàn)在，讓我們看一看為之前顯示的查詢生成了哪種類型的執(zhí)行計劃：


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‘)

該查詢執(zhí)行了一次全表掃描－由于該查詢返回 95％的行，且索引掃描將非常昂貴，因此這是一次正確的操作?，F(xiàn)在，使用 ‘CT‘ 執(zhí)行同一個查詢：


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 的行數(shù)僅占總行數(shù)的 5%，因此進行索引掃描是有利的。

讓我們看一看使用綁定變量時的行為。以下是 Oracle 數(shù)據(jù)庫 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)

優(yōu)化程序選擇對 CUSTOMERS 表進行全表掃描。當(dāng)我們僅搜索 CT（其數(shù)量只占記錄總數(shù)的 5％）時，難道不應(yīng)該使用索引嗎？是什么原因使優(yōu)化程序選擇全表掃描而非索引掃描呢？

答案是一種稱為綁定觀察 的現(xiàn)象。先前，當(dāng)您使用設(shè)置為 ‘NY‘ 的綁定變量值運行該查詢時，優(yōu)化程序必須為查詢的第一次運行進行艱難的分析。在這樣做的同時，優(yōu)化程序觀察綁定變量來確定為其分配的值。該值是 ‘NY‘。由于 ‘NY‘ 的數(shù)量大約占總行數(shù)的 95%，優(yōu)化程序選擇了全表掃描（與預(yù)期的情況相同）。另外，它還凍結(jié)了查詢的計劃。接下來，當(dāng)我們使用設(shè)置為 ‘CT‘ 的變量值運行同一個查詢時，優(yōu)化程序不會重新計算計劃，而是使用了與之前相同的計劃，即使該計劃不是滿足目標(biāo)的最佳方案。如果您在查詢中使用了文字值 ‘CT‘ 而非綁定變量，那么優(yōu)化程序會選擇正確的計劃。

因此，如您所見，盡管綁定變量在大多數(shù)情況下都非常有效，當(dāng)值的選擇性將顯著影響計劃時（正如在此示例中，值 ‘CT‘ 和 ‘NY‘ 的選擇性分別為 5％和 95％），綁定變量并不可靠。如果數(shù)據(jù)分布均勻，所有值的選擇性幾乎相同，執(zhí)行計劃將保持不便。因此，聰明的 SQL 編碼人員將會選擇在何時打破使用綁定變量的基本準(zhǔn)則，改用文字值。

自適應(yīng)游標(biāo)

但如果您沒有很多聰明的編碼人員，或者沒有時間重新編寫這些語句，該怎么辦？Oracle 是否提供了一些智能的替代方案？

是這樣的。使用 Oracle 數(shù)據(jù)庫 11g，游標(biāo)突然擁有了一種新的智能。不是在執(zhí)行查詢的時候盲目使用已緩存的執(zhí)行計劃，而是在綁定變量的值更改時，根據(jù)實際情況確定是否需要重新計算計劃。如果游標(biāo)中含有綁定變量，數(shù)據(jù)庫會對其進行觀察，確定傳遞給變量的值的類型以及是否需要重新計算計劃。如果需要重新計算計劃，則游標(biāo)標(biāo)記為 "Bind-Sensitive"。

之前顯示的示例查詢可以很好地表現(xiàn)這一點。數(shù)據(jù)庫將基于綁定變量的值使用正確的優(yōu)化程序方案。您不需要執(zhí)行任何操作；上述操作將自動執(zhí)行。

字典視圖 V$SQL 已經(jīng)修改，添加了兩列：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 對游標(biāo)進行觀察，并確定值變化的方式。如果不同的值可能會改變計劃，則游標(biāo)標(biāo)記為 "Bind-Sensitive"，IS_BIND_SENSITIVE 列顯示 "Y"。在幾次執(zhí)行后，數(shù)據(jù)庫對游標(biāo)和值有了更多了解，并確定游標(biāo)是否應(yīng)根據(jù)值的變化來改變計劃。如果情況如此，則游標(biāo)被稱為 "Bind-Aware"，IS_BIND_AWARE 列顯示 "Y"?？偨Y(jié)：Bind-Sensitive 游標(biāo)是可能會更改計劃的游標(biāo)，而 Bind-Aware 游標(biāo)是實際更改計劃的游標(biāo)。

一個新視圖 V$SQL_CS_HISTOGRAM 顯示了 SQL 語句執(zhí)行的次數(shù)，為每個子游標(biāo)劃分了三個存儲區(qū)，如下所示：

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

由于自適應(yīng)游標(biāo)共享特性根據(jù)綁定變量的值使用正確的計劃，數(shù)據(jù)庫必須在某處存儲這些信息。它通過另一個新視圖 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 顯示了標(biāo)記為 Bind-Aware 或 Bind-Sensitive 的游標(biāo)執(zhí)行的操作。

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

該視圖顯示了數(shù)據(jù)庫記錄的有關(guān)執(zhí)行的統(tǒng)計數(shù)據(jù)。EXECUTIONS 列顯示了使用綁定變量的不同的值執(zhí)行查詢的次數(shù)。輸出中的 PEEKED 列（顯示為 "P"）顯示優(yōu)化程序是否通過觀察綁定變量獲得適當(dāng)?shù)姆桨浮?

這些視圖顯示了一些額外信息，您不需要通過這些信息了解此特性的工作方式。數(shù)據(jù)庫自動激活和使用自適應(yīng)游標(biāo)。

SQL 計劃管理

您看到過多少次下面的情況：一個查詢擁有可能的最佳計劃，但一些事情突然發(fā)生，導(dǎo)致該計劃被拋棄。這些事情可能是某人重新對表進行了分析，或者 star_transformation 等影響優(yōu)化程序的參數(shù)被改變 — 各種可能性是無窮無盡的。出于絕望，您可能會禁止對數(shù)據(jù)庫進行任何更改，這意味著不收集數(shù)據(jù)庫統(tǒng)計數(shù)據(jù)、不更改任何參數(shù)等等。

但這說起來容易做起來難。當(dāng)數(shù)據(jù)模式改變時會發(fā)生什么？以自適應(yīng)游標(biāo)一節(jié)中顯示的示例為例。現(xiàn)在，CUSTOMERS 表中填充了來自紐約的客戶，因此 STATE_CODE 大部分為 "NY"。因此，當(dāng)執(zhí)行含有如下所示謂詞的查詢時：

where state_code = ‘CT‘

系統(tǒng)執(zhí)行一次全表掃描而非索引掃描。當(dāng)謂詞為：

where state_code = ‘CT‘

由于僅將返回幾行結(jié)果，因此系統(tǒng)使用索引。然而，如果模式發(fā)生改變－假設(shè)，突然出現(xiàn)大量來自康涅狄格 (state_code = ‘CT‘) 的客戶，導(dǎo)致含有 CT 的結(jié)果的百分比升至 70％，此時會發(fā)生什么？在該情況下，CT 查詢應(yīng)使用全表掃描。但是，由于您已禁止收集優(yōu)化程序統(tǒng)計數(shù)據(jù)，優(yōu)化程序不會了解模式的更改，并且會繼續(xù)提供無效率的索引掃描路徑。您可以做些什么？

如果 Oracle 使用最優(yōu)計劃，但在統(tǒng)計數(shù)據(jù)收集或數(shù)據(jù)庫參數(shù)等底層因素更改時重新評估該計劃，此時，當(dāng)且僅當(dāng)新計劃更有效時數(shù)據(jù)庫才會使用，結(jié)果如何？該方案非常理想，不是嗎？它在 Oracle 數(shù)據(jù)庫 11g 中已成為可能。讓我們看一看這種方案的實現(xiàn)方式。

SQL 計劃基準(zhǔn)線設(shè)定

在 Oracle 數(shù)據(jù)庫 11g 中，當(dāng)一個已經(jīng)計算好的優(yōu)化程序計劃由于底層因素的更改而需要更新時，新計劃不會立即實施。Oracle 會對這個新計劃進行評估。僅當(dāng)它比原有計劃更有效時，Oracle 才會實施新計劃。此外，還可以使用工具和接口來查看為每個查詢計算的計劃的歷史，以及這些計劃的對比情況。

當(dāng) Oracle 將一個語句確定為多次執(zhí)行或“可重復(fù)的”語句，聲明周期開始。一旦確定了一個可重復(fù)語句，數(shù)據(jù)庫即會捕獲它的計劃，并將該計劃作為 SQL 計劃基準(zhǔn)線存儲在數(shù)據(jù)庫一個稱為 SQL 管理庫 (SMB) 的邏輯結(jié)構(gòu)中。當(dāng)出于任何原因為該查詢計算新計劃時，新計劃也存儲在 SMB 中。因此，SMB 用于存儲查詢的每個計劃、計劃的生成方式等等。

計劃不會自動存儲在 SMB 中。如果上述情況屬實，SMB 將存儲每類查詢的所有計劃，并將變得十分龐大。因此，您可以并且應(yīng)該控制 SMB 存儲的查詢的數(shù)量。執(zhí)行該操作有兩種方法：自動為 SMB 中的所有可重復(fù)查詢設(shè)定基準(zhǔn)線，或手動加載應(yīng)設(shè)定基準(zhǔn)線的查詢

讓我們先看一個簡單的示例：通過將數(shù)據(jù)庫參數(shù) optimizer_capture_sql_plan_baselines（默認(rèn)值為 FALSE）的值設(shè)置為 TRUE，您可以使 SQL 計劃管理特性自動捕獲所有可重復(fù)查詢的 SQL 計劃基準(zhǔn)線。很幸運，這是一個動態(tài)參數(shù)。

SQL> alter system optimizer_capture_sql_plan_baselines = true;

該語句執(zhí)行后，所有可重復(fù)語句的執(zhí)行計劃都作為 SQL 計劃基準(zhǔn)線存儲在 SMB 中。SQL 計劃基準(zhǔn)線存儲在名為 DBA_SQL_PLAN_BASELINES 的視圖中。您也可以在 Enterprise Manager 中看到這些內(nèi)容。要檢查設(shè)定了基準(zhǔn)線的計劃，請打開 EM 并單擊 "Server" 選項卡，如下圖所示：

在該頁單擊 Query Optimizer 部分中的 SQL Plan Control，這將打開下方顯示的 SPM 主頁面。

單擊 SQL Plan Baseline 選項卡，該操作將打開如下所示的屏幕：

這是 SQL 計劃基準(zhǔn)線的主屏幕。您將在屏幕左上角看到配置參數(shù)。Capture SQL Plan Baselines 顯示為 TRUE，該值是您使用 ALTER SYSTEM 命令啟用的。該參數(shù)下方是設(shè)置為 TRUE（默認(rèn)值）的 Use SQL Plan Baselines。它表示，如果存在 SQL 計劃基準(zhǔn)線，則為查詢使用該基準(zhǔn)線。

每當(dāng)為查詢生成一個新計劃，原有計劃就保留在 SMB 的歷史中。然而，這也意味著 SMB 中將擠滿計劃歷史。一個參數(shù)可以控制計劃保留的星期數(shù)，它顯示在 Plan Retention (Weeks) 的文本框中。在本屏幕中，該參數(shù)設(shè)置為 53 周。如果一個 SQL 計劃基準(zhǔn)線的未使用時間超過 53 周，該基準(zhǔn)線將被自動清除。

該屏幕的中間部分有一個搜索框，可用于搜索 SQL 語句。在此處輸入一個搜索字符串，然后按下 Go，您將看到如上圖中顯示的 SQL 語句和相關(guān)計劃。每個設(shè)定了基準(zhǔn)線的計劃都有大量與之相關(guān)的狀態(tài)信息。讓我們看一看這些信息：

Enabled - 一個設(shè)定了基準(zhǔn)線、必須啟用以便加以考慮的計劃
Accepted - 一個設(shè)定了基準(zhǔn)線的計劃，該計劃被視為查詢的可接受計劃
Fixed - 如果一個計劃被標(biāo)記為 FIXED，則優(yōu)化程序在確定最佳計劃時只考慮該計劃。因此，如果一個查詢的五個計劃設(shè)定了基準(zhǔn)線，其中三個標(biāo)記為 "fixed"，那么優(yōu)化程序在選擇最佳計劃時只考慮上述三個計劃。
Auto-Purge - 計劃是否應(yīng)自動清除

DBA_SQL_PLAN_BASELINES 視圖中提供了上述信息和額外的信息：

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

如果單擊計劃的名稱，則將顯示計劃的詳細(xì)信息。以下是輸出結(jié)果：

在這些詳細(xì)信息中，您可以看到查詢的解釋計劃，以及其他相關(guān)信息，如該計劃是否為可接受、已啟用或固定計劃等等。另一個重要的屬性是 "Origin"，它顯示 AUTO-CAPTURE，表示由于已將 optimizer_capture_sql_plan_baselines 設(shè)置為 TRUE，因此系統(tǒng)自動捕獲該計劃。

單擊 Return，返回到如上圖中顯示的計劃列表?，F(xiàn)在，選擇一個狀態(tài)不為可接受的計劃并單擊 Evolve，查看系統(tǒng)是否會檢查該計劃來獲取一個可能更有效的計劃。彈出以下屏幕。

此屏幕中需要注意的重點是 Verify Performance 單選按鈕。如果您希望檢查計劃，并將其性能與該查詢當(dāng)前 SQL 計劃基準(zhǔn)線的性能進行比較，您應(yīng)該選中該按鈕。單擊 OK。屏幕顯示比較報告：

-------------------------------------------------------------------------------
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

這是一份較好的比較報告，顯示了計劃的對比情況。如果顯示特定計劃擁有更好的性能，優(yōu)化程序?qū)⑹褂迷撚媱?。如果新計劃的性能改進并不明顯，系統(tǒng)不會接收或使用該計劃。SQL 性能管理允許您直接看到各計劃的對比情況，從而使用最適當(dāng)?shù)挠媱潯?

通過執(zhí)行 DBMS_SPM 程序包，您可以手動更改計劃的可接受狀態(tài)：

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 計劃基準(zhǔn)線，使優(yōu)化程序不能使用該計劃。稍后，您可以再次啟用該計劃，使該計劃重新獲得使用。要禁用計劃，使用以下命令：

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 語句的計劃由一條基準(zhǔn)線固定，解釋計劃會清楚地顯示出來。在計劃的末尾，您將看到一行內(nèi)容，確定該計劃已由一條基準(zhǔn)線固定。

差別與存儲大綱

如果您熟悉存儲大綱，您一定在考慮它與 SQL 計劃管理有何差別。它們似乎在做同一件事：為查詢強制一個特定的執(zhí)行計劃。但他們確實有細(xì)微的差別，即，使用 SQL 計劃管理，系統(tǒng)可以評估基準(zhǔn)線以獲得更好的計劃，也可以激活新計劃來代替原有計劃。而大綱是固定的。除非禁用或使用其他概要文件代替，否則大綱不能被覆蓋。此外，計劃基準(zhǔn)線還擁有歷史數(shù)據(jù)，您可以通過這些數(shù)據(jù)了解一段時間內(nèi)計劃的發(fā)展情況。

相關(guān)問題可能包括：如果查詢上具有一個存儲大綱，而基準(zhǔn)線找到一個更好的方案，這時將出現(xiàn)什么情況？將會產(chǎn)生沖突，不是嗎？其實并非如此。當(dāng)使用大綱分析查詢時，系統(tǒng)將捕獲大綱強制的執(zhí)行計劃，將其作為查詢的 SQL 計劃基準(zhǔn)線。如果優(yōu)化程序為該語句找到其他的執(zhí)行計劃，系統(tǒng)也會捕獲此計劃，并將其存儲在 SMB 中。但它不會成為可接受的計劃。在使用該計劃之前，您必須執(zhí)行發(fā)展流程，證明新的執(zhí)行計劃優(yōu)于當(dāng)前的 SQL 計劃基準(zhǔn)線（原有存儲大綱）。

差別與存儲概要文件

概要文件不是“計劃”，而是基于數(shù)據(jù)、作為執(zhí)行計劃的一部分進行存儲的元數(shù)據(jù)。因此，在使用概要文件時，查詢計劃可以根據(jù)謂詞發(fā)生變更。而在使用 SQL 計劃基準(zhǔn)線時，無論謂詞中的值是什么，計劃都是相同的。

用例

那么，您可以在哪些示例場景中使用此特性？最好的示例就是升級或其他參數(shù)發(fā)生變化時。為一組查詢設(shè)定基準(zhǔn)線的一種方法是，使用 SQL 調(diào)整工具集，然后將語句從 STS 加載到 SPM。這樣，您可以在 Oracle 數(shù)據(jù)庫 10g 中生成一個 STS，將其導(dǎo)出，然后導(dǎo)入 Oracle 數(shù)據(jù)庫 11g 中，隨后運行 DBMS_SPM.UNPACK_STGTAB_BASELINE 程序包，導(dǎo)入作為 SQL 計劃基準(zhǔn)線的執(zhí)行計劃。稍后，當(dāng)優(yōu)化程序找到更好的計劃時，會將該計劃添加到 SMB 中，允許您進行比較。

結(jié)論

要了解數(shù)據(jù)庫目前如何智能地對待收到的各種請求以及如何作出回應(yīng)，自適應(yīng)游標(biāo)和 SQL 計劃管理僅僅是其中的兩個示例。這兩個特性允許您在兩個方面都獲得最大的好處－利用自適應(yīng)游標(biāo)，您可以使用綁定變量，且不存在使用非最佳計劃的風(fēng)險；使用 SQL 計劃管理時，執(zhí)行計劃并不是固定不變的，而會在保持短期內(nèi)穩(wěn)定性的同時，隨時間的推移不斷變化。

返回“Oracle 數(shù)據(jù)庫 11g：面向 DBA 和開發(fā)人員的重要特性”主頁

Arup Nanda (arup@proligence.com) 是 Starwood Hotels and Resorts 的數(shù)據(jù)庫系統(tǒng)經(jīng)理，從事 Oracle 的 DBA 職業(yè)十多年，并且在 2003 年由《Oracle Magazine》評選為“年度 DBA”。Arup 經(jīng)常在 Oracle 相關(guān)活動中發(fā)表演講，并在 Oracle 相關(guān)雜志上撰寫文章，他是紐約 Oracle 用戶群執(zhí)行委員會的成員，并且是一位 Oracle ACE。他與其他人合作編寫了《Oracle Privacy Security Auditing》(Rampant TechPress) 一書。