stored outlines遷移成SQL執行計劃基線

yuntui發表於2016-11-03

stored outline遷移
stored outline是對SQL語句的一組hint。hint指示最佳化器對SQL語句選擇一個特定的執行計劃。stored outline是一種遺留技術用於提供執行計劃的穩定。

stored outline遷移是使用者將stored outline轉換為SQL執行計劃基線的處理過程。SQL執行計劃基線是一組能保證提供良好效能的執行計劃。

stored outline遷移的目的
假設你依賴stored outlines來維護執行計劃的穩定來阻止效能的下降。Oracle提供了一種方便的方法來安全的將stored outlines轉換成SQL執行計劃基線。在轉換後,可以與stored outline一樣來維護執行計劃的穩定。透過SQL執行計劃基線可以使用更多先進的功能。

有以下問題需要解決:
.stored outlines不能隨著時間而進行演進。因此stored outline在建立時效能良好,但是在資料庫發生改變之後可能就變的效能很差。

.stored outline中的hints可能會變為無效,例如,一個index hint所指定的索引被刪除了。在這種情況下,資料庫仍然會使用outline,但不會排除無效索引,生成的執行計劃通常比原始執行計劃或由最佳化器所生成的當前執行計劃效能要差。

.對於一個SQL語句,最佳化器只能選擇在當前指定目錄中儲存在stored outline中所定義的執行計劃。最佳化器不能從不同目錄中選擇不同的stored outline或效能所有提高的當前執行計劃。

.stored outlines是一種被動的最佳化技術,它意味著你只能使用stored outline來解決已經出現的效能問題。例如,你可能建立一個stored outline來修正一個高負載SQL語句。在這種情況下,你可以使用stored outline在SQL語句變為高負載語句之前來代替主動對其進行最佳化。

stored outline遷移PL/SQL API可以使用以下方式來解決以上問題:
.SQL執行計劃基線能讓最佳化器使用同樣效能良好的執行計劃並且會隨著時間推移而進行演進。對於一個特定的SQL語句,可以在驗證新執行計劃不會影響效能之後將其新增到SQL執行計劃基線中。

.SQL執行計劃基線會阻止因為無效hint而讓執行計劃效能變差。如果儲存在執行計劃中的hint變為無效,那麼最佳化器將不能重複生成該執行計劃。在這種情況下,最佳化器選擇一種替代的可重複生成的執行計劃基線或者由最佳化器生成當前成本最低的執行墳墓。

.對於特定SQL語句,資料庫可以維護多個執行計劃基線。最佳化器會從一組效能良好的執行計劃中選擇。

stored outline遷移操作
stored outline遷移操作如下:
1.使用者呼叫一個函式指定要被遷移的outline
2.資料庫按照以下方式來處理outline:
a.透過執行計劃基線資料庫從outline中複製所要的資訊。資料庫基於outline中的資訊可以複製或計算。例如,在兩個方案中存在的SQL語句文字,資料庫可以從outline複製SQL文字到執行計劃基線中。

b.資料庫為了獲得outline中沒有的資訊需要重新解析hint。plan hash值與plan cost不能從outline中獲得,它需要重新解析hint。

c.資料庫建立執行計劃基線。

3.當資料庫第一次執行相同SQL語句時選擇SQL執行計劃基線時就能獲得丟失的資訊。編譯環境與執行統計資訊只有在執行計劃基線被解析與編譯時才可以使用。

Outline目錄與基線模組
outline是一組hint,而SQL執行計劃基線是一組執行計劃。因為它們是不同的技術,outline的有些功能不會精確對映成執行計劃基線的功能。例如,單個SQL語句可以有多個outline,每一個屬於不同的outline目錄,但對於當前存在的執行計劃基線只有一個目錄default。

outline目錄:對一組stored outlines指定分組。可以使用不同的目錄來對SQL語句維護不同的stored outline。例如,單個語句在oltp與dw目錄中建立outline。每個stored outline只能屬於一個目錄。一個語句可以有多個outline儲存在不同目錄中,但在每個目錄中每個語句只能有一個outline。在執行遷移時,資料庫將每個outline目錄對映為SQL執行計劃基線的模組。預設的目錄名為default

基線模組:指定被執行的高階別函式。一個SQL執行計劃基線只能屬於一個模組。在outline被遷移到SQL執行計劃基線後,模組名預設值為outline目錄名。

基線目錄:只能有一個SQL執行計劃基線目錄存在。這個基線目錄叫default。在執行stored outline遷移時,SQL執行計劃基線的模組名被設定為stored outline的目錄名。在default目錄中一個SQL語句可以有多個SQL執行計劃基線。

當把stored outline遷移成SQL執行計劃基線時,Oracle資料庫將使用相同的名稱將每個outline目錄對映成SQL執行計劃基線模組。

dbms_spm執行stored outline遷移
dbms_spm包有以下函式用來執行stored outline遷移:
a.dbms_spm.migrate_stored_outline:將現有stored outline遷移為SQL執行計劃基線。可以使用以下格式來執行遷移:
.指定outline名稱,SQL文字,outline目錄或所有stored outlines
.指定outline名稱列表

b.dbms_spm.alter_sql_plan_baseline:改變與SQL語句相關的單個或所有執行計劃屬性。

c.dbms_spm.drop_migrated_stored_outline:,刪除已經補遷移為SQL執行計劃基線的stored outline。這個函式將找到dba_outlines中的stored outline並標記為migrated,並且從資料庫中刪除這些otulines。

與stored outline遷移相關的初始化引數:
.create_stored_outlines:決定Oracle資料庫是否自動建立與儲存outline。

.optimizer_capture_sql_plan_baselines:啟用與禁用自動識可重複SQL語句併為這些SQL語句生成SQL執行計劃基線。

.use_stored_outlines:判斷是否最佳化器使用stored outline來生成執行計劃。

.optimizer_use_sql_plan_baselines:啟用與禁用儲存在SQL Management Base中的SQL執行計劃基線。

與stored outline遷移相關的檢視
.dba_outlines:描述資料庫中的所有stored outline。migrated列對於outline遷移很重要並且它的值為not-migrated與migrated。當為migrated時,stored outline已經遷移為執行計劃基線並且不能再使用。

.dba_sql_plan_baselines:顯示為特定SQL語句當前所建立的SQL執行計劃基線。origin列指示執行計劃基線是怎麼建立的。當值為stored-outline時指示執行計劃基線是透過遷移outline而建立的。

stored outline遷移的基本操作:
1.stored outline遷移的準備操作:
檢查遷移條件並且決定要遷移的執行計劃基線的行為

2.選擇以下操作之一:
.使用SQL執行計劃管理功能來遷移outline
.當完全保留stored outline行為時遷移outline為執行計劃基線

3.執行遷移後的確認與清理

stored outline遷移的準備操作
1.使用SQL*Plus以sysdba許可權或有dbms_spm執行許可權的使用者登入資料庫

[oracle@db1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sat Oct 29 20:55:52 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

2.查詢資料庫中的stored outline

SQL> select name,category,sql_text from dba_outlines where migrated='NOT-MIGRATED';

no rows selected

3.決定那個stored outline滿足遷移條件:
.語句必須不是insert as select語句
.語句必須沒有引用遠端物件
.語句必須沒有私有stored outline

4.決定是否遷移所有outline,特定stored outline或者屬於某個特定outline目錄中的outlines。如果不打算遷移所有outline,那麼就要列出你要遷移的outline或outline目錄。

5.決定stored outline被遷移成SQL執行計劃基線時是使用固定執行計劃還是非固定執行計劃:
.固定執行計劃
一個固定執行計劃是凍結的。如果一個固定執行計劃使用執行計劃基線中的hint來重新生成,那麼最佳化器總是選擇成本最低的固定執行計劃而不是那些不固定的執行計劃基線。本質上,一個固定的執行計劃基線實際是使用有效hint的stored outline。當資料庫基於執行計劃基線中的hint並且使用執行計劃基線中相同plan hash值來建立執行計劃時,這個固定執行計劃就能被重新生成。如果多個hint中的一個變為無效,那麼資料庫不可能使用相同的plan hash值來建立執行計劃。在這種情裝飾品下,這種執行不可重新生成。當使用hint解析時,如果一個固定的執行計劃不能重新生成,那麼最佳化器將選擇不同的執行計劃,它可能是:
.SQL執行計劃基線中的另一個執行計劃。
.透過最佳化器重新生成執行計劃

在有些情況下,因為不同的執行計劃會出現效能差異,這時就需要進行SQL最佳化。

.非固定執行計劃
如果執行計劃基線沒有包含固定的執行計劃,那麼SQL Plan Management將考慮為SQL語句選擇一個等價的執行計劃。

6.在開始真下遷移之前,確保Oracle滿足以下條件:
.資料庫必須是企業版本
.資料庫必須是open且不能是暫停狀態
.資料庫必須不能是限制模式來訪問,只讀或遷移模式

使用SQL Plan Management來遷移outline
這個任務的目標是:
.為了允許SQL Plan Management來從執行計劃基線中為一個SQL語句選擇所有執行計劃來代替在遷移後應用相同的固定執行計劃。

.為了允許SQL執行計劃基線透過向基線中增加新的執行計劃來面對資料庫的改變

下面的例子假設以下條件成立:
.遷移所有outline

.想要執行計劃基線的模組名與被遷移的outline目錄名相同

.不想SQL執行計劃被固定
預設情況下,生成的執行計劃是不固定的並且SQL Plan Management當為SQL語句選擇執行計劃時會考慮所有等價的執行計劃。這個方法允許先進的執行計劃演進可以為SQL語句捕獲新的執行計劃,驗證它們的效能,並接受新執行計劃加入到執行計劃基線。

執行dbms_spm.migrate_stored_outline來遷移stored outline
下面的例子執行dbms_spm.migrate_stored_outline來遷移所有stored outline為固定的執行計劃基線:

declare
 my_report CLOB;
begin
 my_report:=dbms_spm.migrate_stored_outline(attribute_name=>'all');
end;

遷移outline並保留stored outline行為
這個任務的目標是為了把stored outline遷移成SQL執行計劃基線並且透過建立固定執行計劃基線來保留stored outline行為。一個固定執行計劃比其它執行計劃的優先順序高。如果執行計劃被固定,那麼執行計劃基線不能被演進。資料庫不會向包含固定執行計劃的基線增加新的執行計劃。

這種情況適用於以下場景:
.想要遷移目錄名為firstrow中的stored outline
.想執行計劃基線的模組名與被遷移的outline目錄名相同

將stored outline遷移為執行計劃基線:
1.

declare
 my_report CLOB;
begin
 my_outlines:=dbms_spm.migrate_stored_outline

(attribute_name=>'category',attribute_value=>'firstrow',fixed=>'YES');
end;
/

在完成遷移操作之後,SQL執行計劃基線的模組名為firstrow,目錄名為default。

執行遷移後的確認與清理
這個任務的目標是:
.為了配置資料庫使用執行計劃基線來代替使用那些已經被遷移為SQL執行計劃基線的stored outline
.為了將來執行的SQL語句建立SQL執行計劃基線來代替使用stored outline
.為了刪除那些已經遷移為SQL執行計劃基線的stored outline

這個任務適用於以下場景:
.已經完成了stored outline遷移的基本步驟
.一些stored outline可能是在oracle 10g之前被建立

下面說明optimizer_capture_sql_plan_baselines與create_stored_outlines引數的組合是如何決定資料庫建立stored outline與SQL執行計劃基線的:
create_stored_outlines為false,optimizer_capture_sql_plan_baselines為false時,當執行SQL語句時,資料庫不會建立stored outline或SQL執行計劃基線。

create_stored_outlines為false,optimizer_capture_sql_plan_baselines為true時,資料庫會自動識別重複的SQL語句併為這些語句生成SQL執行計劃基線。當執行SQL語句時,如果不存在SQL執行計劃基線就會使用default目錄名來生成新的SQL執行計劃基線。

create_stored_outlines為true,optimizer_capture_sql_plan_baselines為false時,Oracle資料庫會自動建立與儲存outline。當執行SQL語句時,如果不存在outline,就會使用目錄名default為SQL語句建立outline。

create_stored_outlines為category,optimizer_capture_sql_plan_baselines為false時,當執行SQL語句時,如果不存在outline,就會使用指定的目錄名為SQL語句建立新的stored outlines。

create_stored_outlines為true,optimizer_capture_sql_plan_baselines為true時,Oracle資料庫會自動為執行的每個查詢語句建立與儲存outline。自動識別重複SQL語句與為這些語句生成SQL執行計劃基線。當執行SQL語句時,資料庫會使用目錄名default來建立stored outline與SQL執行計劃基線。

create_stored_outlines為category,optimizer_capture_sql_plan_baselines為true,Oracle資料庫會自動為執行的每個查詢語句建立outline。自動識別重複SQL語句與為這些語句生成SQL執行計劃基線。當執行SQL語句時,資料庫會使用指定的目錄名建立stored outline並使用目錄名default來建立SQL執行計劃基線。

下面說明optimizer_use_sql_plan_baselines與use_stored_outlines引數的組合是如何決定資料庫使用stored outline與SQL執行計劃基線的:

use_stored_outlines為false,optimizer_use_sql_plan_baselines為false時,當為SQL語句選擇執行計劃時,資料庫不會使用stored outline或執行計劃基線。

use_stored_outlines為false,optimizer_use_sql_plan_baselines為true時,當為SQL語句選擇執行計劃時,資料庫只會使用SQL執行計劃基線。

use_stored_outlines為true,optimizer_use_sql_plan_baselines為false時,當為SQL語句選擇執行計劃時,資料庫會使用目錄名為default中的stored outline。

use_stored_outlines為category,optimizer_use_sql_plan_baselines為false時,當為SQL語句選擇執行計劃時,資料庫會使用指定目錄名中的stored outline。如果指定的目錄名中不存在stored outline,如果在default目錄中存在stored outline,那麼資料庫就會使用。

use_stored_outlines為true,optimizer_use_sql_plan_baselines為true時,當為SQL語句選擇執行計劃時,stored outline的優先順序比SQL執行計劃基線高。如果在default目錄中存在可以用於SQL語句的stored outline,那麼資料庫會使用stored outline,否則資料庫使用SQL執行計劃基線。

use_stored_outlines為category,optimizer_use_sql_plan_baselines為true時,當為SQL語句選擇執行計劃時,stored outline的優先順序比SQL執行計劃基線高。如果指定目錄或default目錄中存在可以用於SQL語句的stored outline,那麼資料庫會使用stored outline。否則,資料庫會使用SQL執行計劃基線。然而,如果stored outline有migrated屬性,那麼資料庫不會使用outline,如果存在SQL執行計劃基線,那麼資料庫會使用SQL執行計劃基線。

在完成stored outline遷移後將資料庫置於合適的狀態:
1.檢查遷移結果,看SQL執行計劃基線是否已經建立,確保執行計劃被啟用與接受:

SQL> select sql_handle,plan_name,origin,enabled,accepted,fixed,module from dba_sql_plan_baselines;
SQL_HANDLE                     PLAN_NAME                      ORIGIN         ENABLED ACCEPTED FIXED MODULE
------------------------------ ------------------------------ -------------- ------- -------- ----- --------------------------------
SQL_d0cb53f0573bcb74           SQL_PLAN_d1kumy1bmrkvnae69e7ae AUTO-CAPTURE   YES     YES      NO    emagent_SQL_oracle_database
SQL_9c0d7998b1d28680           SQL_PLAN_9s3btm2sx51n074830d3a AUTO-CAPTURE   YES     YES      NO    PL/SQL Developer
SQL_91430157076ba9df           SQL_PLAN_92hs1aw3qrafzb96d21b9 AUTO-CAPTURE   YES     YES      NO    JDBC Thin Client
SQL_fbd80d3a7daa592f           SQL_PLAN_grq0d79yunq9g3517892f AUTO-CAPTURE   YES     YES      NO    emagent_SQL_rac_database
SQL_c9327c795e035d87           SQL_PLAN_ckcmwg5g06rc70298c760 AUTO-CAPTURE   YES     YES      NO    emagent_SQL_rac_database
SQL_773b254f11d733b0           SQL_PLAN_7fft59w8xfcxh7d2358ba AUTO-CAPTURE   YES     YES      NO    emagent_SQL_rac_database
SQL_952e0dafe13297d3           SQL_PLAN_9abhdpzhm55ymff175d6b AUTO-CAPTURE   YES     YES      NO    emagent_SQL_rac_database
SQL_5bea1aec47de5c1d           SQL_PLAN_5ruhuxj3xwr0x3517892f AUTO-CAPTURE   YES     YES      NO    emagent_SQL_rac_database
SQL_11489cc82e27c733           SQL_PLAN_12k4wt0r2gjtmf1c17b40 AUTO-CAPTURE   YES     YES      NO    emagent_SQL_rac_database
SQL_da9311fb2fec8c40           SQL_PLAN_dp4sjzcryt320849be660 AUTO-CAPTURE   YES     YES      NO    emagent_SQL_rac_database
SQL_2097b7cb694841d0           SQL_PLAN_215xrtdnnhhfh35e87e58 AUTO-CAPTURE   YES     YES      NO    emagent_SQL_rac_database
SQL_df059c6821f89598           SQL_PLAN_dy1cwd0hzj5cs35e87e58 AUTO-CAPTURE   YES     YES      NO    emagent_SQL_rac_database
SQL_94a4564ac1318120           SQL_PLAN_9992q9b0m30902f8b24ae AUTO-CAPTURE   YES     YES      NO    
SQL_fc093754bbae13fe           SQL_PLAN_gs29rakxuw4zy37db554f AUTO-CAPTURE   YES     YES      NO    
SQL_2fd9b3dc9d848e02           SQL_PLAN_2zqdmvkfs93h25179cde9 AUTO-CAPTURE   YES     YES      NO    
SQL_73b82c249b7d0843           SQL_PLAN_77f1c4kdru223ebdc4e78 AUTO-CAPTURE   YES     YES      NO    
SQL_6184e8ed31386bf0           SQL_PLAN_63178xnsmhuzh561aa499 AUTO-CAPTURE   YES     YES      NO    
SQL_c42198d1d5f324f5           SQL_PLAN_c88csu7az697pebdc4e78 AUTO-CAPTURE   YES     YES      NO    
SQL_674112d2daaadf76           SQL_PLAN_6fh8kubdaprvq83c346df AUTO-CAPTURE   YES     YES      NO    
SQL_ffa1f1f91c5bca64           SQL_PLAN_gz8gjz4f5rkm4f59a06ad AUTO-CAPTURE   YES     YES      NO
SQL_af180c8ff9a1d861           SQL_PLAN_ay60cjzwu3q3182cd7aee AUTO-CAPTURE   YES     YES      NO    
SQL_3ba02daa5cc73416           SQL_PLAN_3r81dp9fcfd0q94b64494 AUTO-CAPTURE   YES     YES      NO    
SQL_9cc94d4239925ef4           SQL_PLAN_9tkad88wt4rrn5976b5eb AUTO-CAPTURE   YES     YES      NO    
SQL_1dc6cbd35acb4efd           SQL_PLAN_1vjqbuddcqmrx5d4b54d5 AUTO-CAPTURE   YES     YES      NO    
SQL_9ed410d70ee4f2fe           SQL_PLAN_9xp0huw7f9wry2f8b24ae AUTO-CAPTURE   YES     YES      NO    
SQL_f7a25d7938972912           SQL_PLAN_gg8kxg4w9fa8kebdc4e78 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_90cda4f1c4064ca9           SQL_PLAN_91md4y720cm5924d38443 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_c42ff7e665ca18ec           SQL_PLAN_c8bzrwtkwn67c55df0880 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_a56d8c52cbac8dc5           SQL_PLAN_aavccab5ut3f5a9b3d668 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_35a61a655e37564d           SQL_PLAN_3b9hucpg3fpkd5454b1ea AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_ac3326a11f142cac           SQL_PLAN_asct6n4gj8b5c76def5aa AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_581db5ae5093f1d2           SQL_PLAN_5h7dppt897wfk15aad75e AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_2531d59ec295a26c           SQL_PLAN_2acfpmv19b8mc6943321d AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_09070338bc78471e           SQL_PLAN_0k1s372y7hjsyebdc4e78 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_8bd89c2a8626630d           SQL_PLAN_8rq4w5a32cssdd7a28287 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_c99cfd0ca0ec6d27           SQL_PLAN_cm77x1khfsv97e0d1d869 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_1178b3f40ee9079d           SQL_PLAN_12y5myh7fk1wx7fa68824 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_30c826839bd031c7           SQL_PLAN_31k16hfdx0cf7ebdc4e78 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_75642813e87d55ba           SQL_PLAN_7at182gn7upduc9e4a408 AUTO-CAPTURE   YES     NO       NO    SEVERITY EVALUATION
SQL_75642813e87d55ba           SQL_PLAN_7at182gn7updu4d0fe611 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_0398f5dac9a26bd2           SQL_PLAN_0767pvb4u4uykaa9fb8f2 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_5bf2fdd320991dc8           SQL_PLAN_5rwrxuch9k7f815aad75e AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_faff45acb48c010c           SQL_PLAN_gpzu5pku8s08c3b4f0583 AUTO-CAPTURE   YES     NO       NO    SEVERITY EVALUATION
SQL_faff45acb48c010c           SQL_PLAN_gpzu5pku8s08c0a771b57 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_0edbf315864797cd           SQL_PLAN_0xqzm2q34g5ydf06d473d AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_6807bab99db0361a           SQL_PLAN_6h1xur6fv0dhu2e8a86b7 AUTO-CAPTURE   YES     YES      NO    PL/SQL Developer
SQL_1a45242d50349a41           SQL_PLAN_1nj945p8396k1c9e4a408 AUTO-CAPTURE   YES     NO       NO    SEVERITY EVALUATION
SQL_1a45242d50349a41           SQL_PLAN_1nj945p8396k14d0fe611 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_4c66704928a28228           SQL_PLAN_4stmh94na50j86943321d AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_7757ffeb83333a7e           SQL_PLAN_7fpzzxf1m6fmy68d74995 AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_17c90a44687b6622           SQL_PLAN_1gk8a8jn7qtj26943321d AUTO-CAPTURE   YES     YES      NO    SEVERITY EVALUATION
SQL_6d5efcdbb0af4493           SQL_PLAN_6urrwvfsayj4m4efadb75 AUTO-CAPTURE   YES     YES      NO    
SQL_2c0bedfc971b5441           SQL_PLAN_2s2zdzkbjqp212f8b24ae AUTO-CAPTURE   YES     YES      NO    OEM.BoundedPool
SQL_fc7e68bc886477c5           SQL_PLAN_gszm8rk468xy5f4b84801 AUTO-CAPTURE   YES     YES      NO    OEM.BoundedPool
SQL_9425c7639bc97782           SQL_PLAN_989f7cfdwkxw245768591 AUTO-CAPTURE   YES     YES      NO    OEM.BoundedPool
SQL_1205bbc63c6b2eeb           SQL_PLAN_141dvssy6qbrb47a21cb4 AUTO-CAPTURE   YES     YES      NO    OEM.BoundedPool
SQL_1367e948428a55f2           SQL_PLAN_16tz99118npgk7a54464c AUTO-CAPTURE   YES     YES      NO    emagent_SQL_oracle_database
SQL_a47e7f9f186b16f8           SQL_PLAN_a8zmzmwc6q5rs799d6e65 AUTO-CAPTURE   YES     YES      NO    emagent_SQL_oracle_database
SQL_03d675f2172c4dff           SQL_PLAN_07pmpy8bksmgz6d032274 AUTO-CAPTURE   YES     YES      NO    emagent_SQL_oracle_database
59 rows selected

2.可選操作,修改SQL執行計劃基線的屬性。例如,下面的語句將修改特定SQL語句的執行計劃基線為fixed:

SQL> declare
  2   v_cnt PLS_INTEGER;
  3  begin
  4   v_cnt:=dbms_spm.alter_sql_plan_baseline(
  5                           sql_handle=>'SQL_9c0d7998b1d28680',
  6                           attribute_name=>'FIXED',
  7                           attribute_value=>'YES');
  8   dbms_output.put_line('Plans altered: '|| v_cnt);
  9  end;
 10  /
PL/SQL procedure successfully completed

3.檢查原stored outline的狀態:

select name,owner,category,used,migrated from dba_outlines;

4.刪除那些已經被遷移到SQL執行計劃基線中的所有stored outline,下面的語將用來刪除dba_outlines中狀態為migrated的所有stored outline:

declare
 v_cnt PLS_INTEGER;
begin
 v_cnt:=dbms_spm.drop_migrated_stored_outline();
 dbms_output.put_line('Migrated stored outlines dropped: '|| v_cnt);
end;

5.設定初始化引數:
.當執行SQL語句時,資料庫建立SQL執行計劃基線但不建立stored outline
.當不存在等價的SQL執行計劃基線時,資料庫只使用stored outline

下面的例子,當執行SQL語句時,指示資料庫建立SQL執行計劃基線來代替stored outline。並且指示

資料庫當目錄allrows或default中的沒有被遷移到SQL執行計劃基線中的stored outline。否則資料

庫只使用SQL執行計劃基線。
alter system set create_stored_outline = false;
alter system set optimizer_capture_sql_plan_baselines = true;
alter system set optimizer_use_sql_plan_baselines = true;
alter session set use_stored_outlines = allrows;

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30633755/viewspace-2127707/,如需轉載,請註明出處,否則將追究法律責任。

相關文章