SQL Plan Management
任何資料庫應用程式的效能都嚴重依賴於它的查詢執行。當沒有任何使用者介入時Oracle最佳化器是完全能夠評估最優執行計劃的,SQL語句的執行計劃因為各種原因，比如：重新收集最佳化器統計資訊，改變最佳化器引數或方案/後設資料定義而可能會出現改變。但是不能保證執行計劃的改變總是會提高效能，因此會導致有些管理員會鎖定SQL的執行計劃(Stored Outline)或鎖定統計資訊。然而，這樣做會導致不能使用新版最佳化器為了提供高SQL效能所提供的一些新特性或新的訪問路徑。
Oracle的每一個新版本中都包含了一些新特性用來提高查詢的效能。然而某些應用（或部分應用）是靜態的並且比起在特定情況下提高效能來說可預測的效能是更可取的。Oracle11G提供了SPM來完全透時的控制執行計劃的演變。使用SPM最佳化器自動管理執行計劃並且確保只有已經或被驗證的執行計劃才會被使用。當找到SQL語句的新執行計劃時，但直到它被資料庫進行驗證或其效能比當前執行計劃高才會被使用。
SQL Plan Management是一種預防機制，記錄與評估SQL語句的執行計劃。這種機制可以建立一個SQL執行計劃基線，它是由一組已知效能良好的執行計劃組成。不管系統發生何種改變,SQL執行計劃基線被用來維護相關SQL的效能，提供了執行計劃的穩定性。
SPM確保SQL的效能不會因為執行計劃的改變而降低。為了保證這一點，只有SQL執行計劃ACCEPTED為YES才會被最佳化器所使用。
SPM有三個主要元件:
1.SQL執行計劃基線捕獲
建立SQL執行計劃基線這代表對所有相關SQL語句授受這個執行計劃。SQL執行計劃基線被儲存在SYSAUX表空間中的SQL Management BASE中的plan history中，可以透過dba_sql_plan_baselines檢視進行查詢。
2.SQL執行計劃基線選擇
確保只有ACCEPTED為YES的執行計劃才會被使用，使用SQL執行計劃基線並在plan history中跟蹤所有新的執行計劃。plan history由ACCEPTED為NO與ACCEPTED為YES的執行計劃組成。ACCEPTED為NO的執行計劃可能是沒有被驗證或者被拒絕(驗證了但效能比當前使用的執行計劃要差)
3.SQL執行計劃基線演進
對plan history中的所有沒有被驗證的執行計劃進行評估讓其成為接受的或拒絕的執行計劃
SQL執行計劃基線捕獲
為了讓SPM起作用必須首先使用當前基於成本的執行計劃來作為SQL Management Base，這將成為每個SQL語句的SQL執行計劃基線。有兩種不同的方法來建立SQL Management Base:
.自動捕獲執行計劃
.批次載入執行計劃
自動捕獲執行計劃
自動捕獲執行計劃是由引數optimizer_capture_sql_plan_baselines=true來開啟的(預設值為false)當自動捕獲執行計劃被啟用時，SPM檔案庫將會自動儲存任何重複執行的SQL語句的執行計劃。為了識別重複執行的SQL語句，最佳化器將會在每個SQL語句第一次被編譯時進行標識，如果這個SQL語句再次被執行或編譯，那麼就會將這個SQL語句標記為重複執行的SQL，將會為這個SQL語句建立SQL plan history，它將包含生成的執行計劃，比如:SQL文字，儲存概要，綁當定變數與編譯環境等資訊。當前基於成本的執行計劃將會作為SQL語句的第一個SQL執行計劃基線並且這個執行計劃的ACCEPTED會標記為YES，只有ACCEPTED為YES的執行計劃才會被使用，如果將來對於這個SQL語句產生了新的執行計劃，這個執行計劃將會增加到plan history中並且將會被標記為驗證過的。只有當它的效能比當前所使用的執行計劃更好時才會將ACCEPTED標記為YES。
批次載入執行計劃
批次載入執行計劃在資料庫從之前的版本升級致電Oracle 11g或部署新應用程式時很有用。批次載入可以與自動載入執行計劃結合使用或者代替自動載入執行計劃。執行計劃被批次載入時會自動接受建立的新SQL執行計劃基線或者向已經存在的增加一個。SQL anagement BASE可以使用三種不同的方法來批次載入執行計劃。
1.從指定的SQL Tuning Set(STS)中載入執行計劃
SQL> BEGIN
2 DBMS_SQLTUNE.CREATE_SQLSET(
3 sqlset_name => 'my_sql_tuning_set',
4 description => 'TEST');
5 END;
6 /
PL/SQL procedure successfully completed
SQL> DECLARE
2 l_cursor DBMS_SQLTUNE.sqlset_cursor;
3 BEGIN
4 OPEN l_cursor FOR
5 SELECT VALUE(p)
6 FROM TABLE (DBMS_SQLTUNE.select_cursor_cache (
7 NULL, -- basic_filter
8 NULL, -- object_filter
9 NULL, -- ranking_measure1
10 NULL, -- ranking_measure2
11 NULL, -- ranking_measure3
12 NULL, -- result_percentage
13 2, -- result_limit
'all') --attribute_list
14 ) p;
15
16 DBMS_SQLTUNE.load_sqlset (
17 sqlset_name => 'my_sql_tuning_set',
18 populate_cursor => l_cursor);
19 END;
20 /
PL/SQL procedure successfully completed
或者從awr中載入
SQL> DECLARE 2 l_cursor DBMS_SQLTUNE.sqlset_cursor; 3 BEGIN 4 OPEN l_cursor FOR 5 SELECT VALUE(p) 6 FROM TABLE (DBMS_SQLTUNE.select_workload_repository ( 7 29027, -- begin_snap 8 29028, -- end_snap 9 NULL, -- basic_filter 10 NULL, -- object_filter 11 NULL, -- ranking_measure1 12 NULL, -- ranking_measure2 13 NULL, -- ranking_measure3 14 NULL, -- result_percentage 15 2, -- result_limit 16 'all'--attribute_list 17 ) 18 ) p; 19 20 DBMS_SQLTUNE.load_sqlset ( 21 sqlset_name => 'my_sql_tuning_set', 22 populate_cursor => l_cursor); 23 END; 24 / PL/SQL procedure successfully completed
SQL> select * from dba_sqlset where name='my_sql_tuning_set';
ID NAME OWNER DESCRIPTION CREATED LAST_MODIFIED STATEMENT_COUNT
---------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------- ----------- ------------- ---------------
15 my_sql_tuning_set INSUR_CHANGDE TEST 2016/5/26 1 2016/5/26 12: 2
SQL> select * from dba_sqlset_statements where sqlset_name='my_sql_tuning_set';
SQLSET_NAME SQLSET_OWNER SQLSET_ID SQL_ID FORCE_MATCHING_SIGNATURE SQL_TEXT PARSING_SCHEMA_NAME PARSING_SCHEMA_ID PLAN_HASH_VALUE BIND_DATA BINDS_CAPTURED MODULE ACTION ELAPSED_TIME CPU_TIME BUFFER_GETS DISK_READS DIRECT_WRITES ROWS_PROCESSED FETCHES EXECUTIONS END_OF_FETCH_COUNT OPTIMIZER_COST OPTIMIZER_ENV PRIORITY COMMAND_TYPE FIRST_LOAD_TIME STAT_PERIOD ACTIVE_STAT_PERIOD OTHER PLAN_TIMESTAMP SQL_SEQ
------------------------------ ------------------------------ ---------- ------------- ------------------------ -------------------------------------------------------------------------------- ------------------------------ ----------------- --------------- -------------------------------------------------------------------------------- -------------- ------------------------------------------------ -------------------------------- ------------ ---------- ----------- ---------- ------------- -------------- ---------- ---------- ------------------ -------------- -------------------------------------------------------------------------------- ---------- ------------ ------------------- ----------- ------------------ -------------------------------------------------------------------------------- -------------- ----------
my_sql_tuning_set INSUR_CHANGDE 15 2823agph489xc 1.702412129134E19 select id,name,password from gl_czy where name ='系統管理' ZW4001 248 2543410975 R9_AppSrv.EXE 124908 32105 324 48 0 21 42 21 21 3 E289FB89A4E49800CE001000AEF9C3E2CFFA3310564145555195A110555555154554555859155544 3 2016/2/16 10:5 717
my_sql_tuning_set INSUR_CHANGDE 15 1hfffsrmgqhwp 1.11129101236943E19 select nvl(catalog_center,center_id) as catalog_center from bs_hospital_collate INSUR_CHANGDE 211 2429242715 JDBC Thin Client 141372 141372 11475 0 0 3825 3825 3825 3825 1 E289FB89A4E49800CE001000AEF9C3E2CFFA3310564145555195A110555555154554555859155544 3 2016/5/23 14:2 718
SQL> exec dbms_sqltune.create_stgtab_sqlset(table_name => ‘TABLE_SQLSET’,schema_name => ‘JY’); PL/SQL procedure successfully completed SQL> exec dbms_sqltune.pack_stgtab_sqlset(sqlset_name => ‘my_sql_tuning_set’,staging_table_name => ‘TABLE_SQLSET’,staging_schema_owner =>’JY ’); PL/SQL procedure successfully completed
匯出儲存STS
[IBMP740-1:oracle:/yb_oradata/RLZYbak]$expdp jy/jy directory=dump_RLZY dumpfile=stgtab_sqlset.dmp logfile=stgtab_sqlset.log tables=TABLE_SQLSET
< Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 26 May, 2016 16:57:34
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "INSUR_CHANGDE"."SYS_EXPORT_TABLE_01": insur_changde/******** directory=dump_RLZY dumpfile=stgtab_sqlset.dmp logfile=stgtab_sqlset.log tables=TABLE_SQLSET
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 576 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "INSUR_CHANGDE"."TABLE_SQLSET" 16.91 KB 2 rows
. . exported "INSUR_CHANGDE"."TABLE_SQLSET_CPLANS" 24.10 KB 5 rows
. . exported "INSUR_CHANGDE"."TABLE_SQLSET_CBINDS" 9.507 KB 0 rows
Master table "INSUR_CHANGDE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for INSUR_CHANGDE.SYS_EXPORT_TABLE_01 is:
/bak/dpdump/stgtab_sqlset.dmp
Job "INSUR_CHANGDE"."SYS_EXPORT_TABLE_01" successfully completed at 16:59:25
匯入STS
[oracle@jyrac1 ~]$ impdp jy/jy directory=dump_dir dumpfile=stgtab_sqlset.dmp remap_schema=insur_changde:jy remap_tablespace=hygeia:users Import: Release 11.2.0.4.0 - Production on Thu May 26 17:53:08 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "JY"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "JY"."SYS_IMPORT_FULL_01": jy/******** directory=dump_dir dumpfile=stgtab_sqlset.dmp remap_schema=insur_changde:jy remap_tablespace=hygeia:users Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "JY"."TABLE_SQLSET" 16.91 KB 2 rows . . imported "JY"."TABLE_SQLSET_CPLANS" 24.10 KB 5 rows . . imported "JY"."TABLE_SQLSET_CBINDS" 9.507 KB 0 rows Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Job "JY"."SYS_IMPORT_FULL_01" successfully completed at Thu May 26 17:59:33 2016 elapsed 0 00:06:05
unpack STS
SQL> begin 2 DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(sqlset_name => 'my_sql_tuning_set', 3 sqlset_owner => 'JY', 4 replace => true, 5 staging_table_name => 'TABLE_SQLSET', 6 staging_schema_owner => 'JY'); 7 END; 8 / PL/SQL procedure successfully completed
從STS載入SQL執行計劃基線
SQL> declare
2 ret number;
3 begin
4 ret := dbms_spm.load_plans_from_sqlset(sqlset_name => 'my_sql_tuning_set',sqlset_owner => 'INSUR_CHANGDE');
5 end;
6 /
PL/SQL procedure successfully completed
SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text from dba_sql_plan_baselines where parsing_schema_name in('ZW4001','INSUR_CHANGDE');
SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------- ------- --------
--------------------------------------------------------------------------------
SQL_c4a3ce0d598be4e4 SQL_PLAN_c98yf1pcsrt74ebcd9fdf MANUAL-LOAD YES YES select id,name,password from gl_czy where name ='系統管理'
SQL_b9bc48036c46e780 SQL_PLAN_bmg280dq4dtw0e85be7cc MANUAL-LOAD YES YES select nvl(catalog_center,center_id) as catalog_center from
bs_hospital_collate
2.使用Cursor Cache中的執行計劃
可以從cursor cache中直接載入SQL語句的執行計劃到SQL Management Base中。可以透過module名，方案或SQL_ID來進過濾，可以用來標記想要捕獲的SQL語句或一組SQL語句。
SQL> set autotrace traceonly SQL> select * from t1 where c1>4076; 924 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3617692013 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 957 | 3828 | 5 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T1 | 957 | 3828 | 5 (0)| 00:00:01 | -------------------------------------------------------------------------- SQL> select sql_text,sql_id,plan_hash_value from v$sqlarea where sql_text like '%c1>4076%'; SQL_TEXT SQL_ID PLAN_HASH_VALUE -------------------------------------------------------------------------------- ------------- --------------- select * from t1 where c1>4076 0006gg4zsmmcg 3617692013 SQL> var n number SQL> begin 2 :n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'0006gg4zsmmcg', plan_hash_value=>'3617692013', fixed =>'NO', enabled=>'YES'); 3 end; 4 / PL/SQL procedure successfully completed. SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select %c1>4076'; SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT ------------------------------ ------------------------------ -------------- ------- -------- -------------------------------------------------------------------------------- SQL_67b129b37635284b SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD YES YES select * from t1 where c1>4076
3.從staging table中unpack SQL執行計劃基線
在原系統中建立staing表
SQL> set autotrace traceonly
SQL> select * from t1 where c1>4076;
924 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 924 | 3696 | 4 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 924 | 3696 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1">4076)
Note
-----
- SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bbeaed07c" used for this statement
SQL> exec dbms_spm.create_stgtab_baseline(table_name => 'BASELINE_TABLE',table_owner => 'JY');
PL/SQL procedure successfully completed
將SQL執行計劃基線從SQL Management Base中載入到staing table中
SQL> declare
2 x number;
3 begin
4 x:=dbms_spm.pack_stgtab_baseline(table_name => 'BASELINE_TABLE',table_owner => 'JY',creator =>'JY');
5 end;
6 /
PL/SQL procedure successfully completed
SQL> declare
2 cursor c is select sql_handle, plan_name,origin, enabled, accepted,sql_text from dba_sql_plan_baselines where parsing_schema_name in('JY');
3 x number;
4 begin
5 for i in c loop
6 x:=dbms_spm.drop_sql_plan_baseline(sql_handle => ''||i.sql_handle,plan_name => ''||i.plan_name);
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed
SQL> select * from t1 where c1>4076;
924 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 924 | 3696 | 4 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 924 | 3696 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1">4076)
匯出staing table並在目標資料庫中匯入，這裡省略了相關操作。在目標資料庫將SQL執行計劃基線從staging table中載入到SQL Management Base中
SQL> declare
2 x number;
3 begin
4 x:=dbms_spm.unpack_stgtab_baseline(table_name => 'BASELINE_TABLE',table_owner => 'JY');
5 end;
6 /
PL/SQL procedure successfully completed
SQL> select * from t1 where c1>4076;
924 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 924 | 3696 | 4 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 924 | 3696 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1">4076)
Note
-----
- SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bbeaed07c" used for this statement
SQL執行計劃基線選擇
每次SQL被編譯時，最佳化器首先使用傳統的基於成本的方法來建立一個最優執行計劃。如果引數optimizer_use_plan_baselines設定為true(預設值)，那麼在基於成本的執行計劃被執行之前，最佳化器將嘗試在SQL語句的SQL執行計劃基線中找到一個匹配的執行計劃，這是作為記憶體操作來執行的，如果找到了匹配的執行計劃將使用該執行計劃。否則，將生成一個新的執行計劃並增加到plan history中，在執行計劃被授受之前必須被驗證。最佳化器將從該SQL所關聯的所有執行計劃中選擇一個成本最低的來執行。然而，如果系統的改變造成所有已經接受的執行計劃不能重現，那麼最佳化器將會使用新的執行計劃。
SQL執行計劃基線也可能影響最佳化器對執行計劃的選擇。SQL執行計劃基線可以被標記為固定(fixed),Fixed的SQL執行計劃基線指示最佳化器會優先選擇。如果最佳化器正在評估SQL執行計劃基線的成本並且有一個執行計劃是fixed，並且fixed的執行計劃可以重現，那麼最佳化器只會評估fixed執行計劃的成本。如果fixed的執行計劃不可以重現，那麼最佳化器將會評估其它的SQL執行計劃基線並從其中選擇一個成本最低的執行計劃。評估一個執行計劃的成本遠不及硬解析那樣昂貴。最佳化器不會考慮所有可能的訪問方法，只會考慮特定的訪問方法。
SQL執行計劃的演進
當最佳化器為SQL語句找到一個新執行計劃時，這個新的執行計劃會作為未被接受的執行計劃被增加到plan history中，在它成為接受(ACCEPTED=YES)的執行計劃之前需要被驗證。可以透過使用EM或執行dbms_spm.evolve_sql_plan_baseline來演進。使用這些方法有三種選擇:
1.只有新執行計劃比現有的SQL執行計劃基線效能更好才將ACCEPTED設定為YES
2.不進行效能驗證就將ACCEPTED設定為YES
3.執行效能比較並生成一個報告但不演進新的執行計劃
如果選擇選項1,它將觸發新的執行計劃被評估來比較是否它的效能要比被選擇的執行計劃好。如果是，那麼新的執行計劃會被增加到SQL執行計劃基線並且ACCEPTED設定為YES。否則將新執行計劃的ACCEPTED設定為NO並增加到plan history中，但它的last_verified屬性將更新為當前時間戳，並且會返回一個格式化的文字報告，它包含了新執行計劃與原執行計劃的效能統計資料。
如果選擇選項2,新執行計劃將會增加到SQL執行計劃基線中，而不對效能進行驗證並將ACCEPTED設定為YES，同樣也會生成報告。
如果選擇選項3,將會驗證新執行計劃的效能是否比已選擇的執行計劃好，但是就算效能更好也會不自動將新執行計劃的ACCEPTED設定為YES，在評估之後只會生成報告。
SQL Management Base的使用與管理
有兩個引數用來控制SPM
1.optimizer_capture_sql_plan_baselines:對重複執行的SQL語句是否自動捕獲新的SQL執行計劃基線。預設值是false。
2.optimizer_use_sql_plan_baselines:控制最佳化器是否使用SQL執行計劃基線。當啟用時，最佳化器在編譯SQL語句時會查詢SQL執行計劃基線中的執行計劃。如果找到，最佳化器將會從SQL執行計劃基線中選擇一個成本最低的執行計劃。預設值是true。
SQL Management Base空間消耗的管理
statment log,plan histories與SQL執行計劃基線都被儲存在SQL Management Base中。SQL Management Base是資料庫資料字典的一部分儲存在SYSAUX表空間中。預設情況下，SQL Management Base的空間限制不會超過SYSAUX表空間的10%。但是也可以使用dbms_spm.configure來進行修改讓其空間限制處於SYSAUX表空間的1%到50%之間。每週後臺程式都會檢測SQL Management Base所佔用的空間大小，並且當使用空間超過設定的限制，程式就會向alert.log記錄一條警告資訊。也有一個每週排程清除作業來管理SQL Management Base所佔用的空間。在維護視窗內這個任務會自動執行並且任何超過53周沒有被使用過的執行計劃將會被清除，因此確保只有SQL語句一旦執行就能保證一年之內相關執行計劃是可以使用的。可以使用dbms_spm.configure來將沒使用的執行計劃保留週期修改為5到523周這個範圍之內。
因為SQL Management Base使用SYSAUX表儂間來儲存執行計劃，如果SYSAUX表空間不能使用，那麼SPM也將不能使用。
透過DBA檢視監控SPM
dbs_sql_plan_baselines檢視顯示了關於當前為特定SQL語句所建立的SQL執行計劃基線。
SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text,fixed,autopurge from dba_sql_plan_baselines where parsing_schema_name in('JY') and
sql_handle='SQL_67b129b37635284b';
SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT
FIXED AUTOPURGE
------------------------------ ------------------------------ -------------- ------- --------
-------------------------------------------------------------------------------- ----- ---------
SQL_67b129b37635284b SQL_PLAN_6gc99qdv3aa2bbeaed07c AUTO-CAPTURE YES YES select * from t1 where c1>4076
NO YES
SQL_67b129b37635284b SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD NO YES select * from t1 where c1>4076
NO YES
為了檢視任何SQL執行計劃基線的詳細執行計劃可以執行dbms_xplan.display_sql_plan_baseline
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_67b129b37635284b',plan_name => 'SQL_PLAN_6gc99qdv3aa2bdbd90e8e'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_67b129b37635284b
SQL text: select * from t1 where c1>4076
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6gc99qdv3aa2bdbd90e8e Plan id: 3688435342
Enabled: NO Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 924 | 3696 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 924 | 3696 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1">4076)
24 rows selected
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_67b129b37635284b',plan_name => 'SQL_PLAN_6gc99qdv3aa2bbeaed07c'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_67b129b37635284b
SQL text: select * from t1 where c1>4076
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_6gc99qdv3aa2bbeaed07c Plan id: 3199127676
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 924 | 3696 | 4 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 924 | 3696 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1">4076)
24 rows selected
還可以透過使用v$sql檢視來檢視SQL語句是否使用了SQL執行計劃基線。如果SQL語句正使用SQL執行計劃基線，那麼dba_sql_plan_baselines中的plan_name就會與v$sql中的sql_plan_baseline有相同的值。
select sql_handle, plan_name,origin, enabled, accepted,a.sql_text,fixed,autopurge from dba_sql_plan_baselines a,v$sql b
where a.parsing_schema_name in('JY') and a.plan_name=b.sql_plan_baseline and a.signature=b.exact_matching_signature
and a.sql_text like '%select * from t1 where c1>4076%';
SQL執行計劃在以下情況可以用來提高或維護SQL效能:
1.資料庫升級
資料庫升級會安裝新版的最佳化器，這通常會改變一小部分SQL的執行計劃，大部分發生改變的執行計劃效能沒有變化或者有所提高。然而，特定的執行計劃發生改變可能造成效能的衰減。使用SQL執行計劃基線可以有效的最小化由資料庫升級而引起的效能衰減。
2.系統/資料發生變化
當系統/資料發生變化時可能會影響某些SQL的執行計劃，潛在造成效能衰減。使用SQL執行計劃能夠最小化效能衰減並且穩定SQL效能。
3.應用程式升級
開發新的應用程式意味著引入新的SQL語句。在標準測試環境中對於新SQL語句應用程式可能會使用合適的執行計劃。如果你的系統配置不同於測試環境，SQL執行計劃基線可以被用來產生更好的效能。
如果在cursor快取中有效能良好的執行計劃，那麼可以將其載入到SPM中，因此你可以使用SQL執行計劃基數來維護SQL的效能。
SQL> set autotrace traceonly
SQL> select * from t1 where c1>4076;
924 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 957 | 3828 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 957 | 3828 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
SQL> select sql_text,sql_id,plan_hash_value from v$sqlarea where sql_text like '%c1>4076%';
SQL_TEXT SQL_ID PLAN_HASH_VALUE
-------------------------------------------------------------------------------- ------------- ---------------
select * from t1 where c1>4076 0006gg4zsmmcg 3617692013
SQL> var n number
SQL> begin
2 :n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'0006gg4zsmmcg', plan_hash_value=>'3617692013', fixed =>'NO', enabled=>'YES');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select %c1>4076';
SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------- ------- --------
--------------------------------------------------------------------------------
SQL_67b129b37635284b SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD YES YES select * from t1 where c1>4076
SQL> select * from t1 where c1>4076;
924 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 957 | 3828 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 957 | 3828 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1">4076)
Note
-----
- SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bdbd90e8e" used for this statement
Statistics
----------------------------------------------------------
26 recursive calls
17 db block gets
89 consistent gets
0 physical reads
3060 redo size
13042 bytes sent via SQL*Net to client
1091 bytes received via SQL*Net from client
63 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
924 rows processed
可以看到SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bdbd90e8e" used for this statement資訊，說明對於該SQL語句以後的執行都將會使用所建立的SQL執行計劃基線。
在啟用SPM後，對於這些有了SQL執行計劃基線的SQL_ID，資料庫將不會再收集新的執行計劃(即使在SPM被禁用的情況下也不會再收集新的執行計劃)。
SPM預設值就是啟用，自動捕獲SQL執行計劃基線是禁用的
SQL> show parameter sql_plan_baselines NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE
啟用自動捕獲SQL執行計劃基線
SQL> alter system set optimizer_capture_sql_plan_baselines=true scope=both sid='*';
System altered.
SQL> create index idx_t1 on t1(c1);
Index created.
SQL> exec dbms_stats.gather_table_stats(ownname=>null,tabname=>'t1',estimate_percent=>100,method_opt=>'for all columns size repeat',cascade=>true);
PL/SQL procedure successfully completed.
SQL> select * from t1 where c1>4076;
924 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 957 | 3828 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 957 | 3828 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1">4076)
Note
-----
- SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bdbd90e8e" used for this statement
SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select %c1>4076';
SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT
------------------------------ ------------------------------ -------------- ------- --------
--------------------------------------------------------------------------------
SQL_67b129b37635284b SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD YES YES select * from t1 where c1>4076
可以看到最佳化器還是使用原來手動建立的SQL執行計劃基線，並沒有生成新的執行計劃，還是執行的全表掃描，並沒有使用我們建立的索引。
禁用SPM,啟用自動捕獲SQL執行計劃基線
SQL> alter system set optimizer_use_sql_plan_baselines=false scope=both sid='*'; System altered. SQL> select * from t1 where c1>4076; 924 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1369807930 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 957 | 3828 | 4 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| IDX_T1 | 957 | 3828 | 4 (0)| 00:00:01 | ---------------------------------------------------------------------------
可以看到執行計劃使用了對索引idx_t1的範圍掃描，而不是之前的全表掃描了。
SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select %c1>4076'; SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT ------------------------------ ------------------------------ -------------- ------- -------- -------------------------------------------------------------------------------- SQL_67b129b37635284b SQL_PLAN_6gc99qdv3aa2bbeaed07c AUTO-CAPTURE YES NO select * from t1 where c1>4076 SQL_67b129b37635284b SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD YES YES select * from t1 where c1>4076
可以看到ORIGN為AUTO-CAPTURE的SQL執行計劃基線就是自動捕獲的，ENABLED為YES，但是ACCEPTED為NO，這說明雖然建立了新的SQL執行計劃基線但是最佳化器不能使用。下面啟用SPM，看最佳化器是否使用新建立的SQL執行計劃基線。
SQL> alter system set optimizer_use_sql_plan_baselines=true scope=both sid='*';
System altered.
SQL> show parameter sql_plan_baselines
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean TRUE
optimizer_use_sql_plan_baselines boolean TRUE
SQL> select * from t1 where c1>4076;
924 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 957 | 3828 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 957 | 3828 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1">4076)
Note
-----
- SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bdbd90e8e" used for this statement
從SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bdbd90e8e" used for this statement資訊可以看到最佳化器還是使用我們手動建立的SQL執行計劃基線，對錶t1執行全表掃描。想要使用執行索引掃描的SQL執行計劃基線就要將其對應的ACCEPTED設定為YES並將使用全表掃描的SQL執行計劃基線的ACCEPTED設定為NO。但是11gr1與11gr2在修改SQL執行計劃基線的ACCEPTED屬性的方法是不同的。另外，一旦在11gr2中將SQL執行計劃基線的ACCEPTED設定為YES，就不能再將其設定為NO。為了不使用某個SQL執行計劃基線，只能將該SQL執行計劃基線的ENABLED設定為NO。
在11gr1中修改SQL執行計劃基線的ACCEPTED屬性值的方法如下:
SQL> DECLARE
2 x number;
3 BEGIN
4 x := dbms_spm.alter_sql_plan_baseline('SQL_67b129b37635284b','SQL_PLAN_6gc99qdv3aa2bbeaed07c',attribute_name=> 'ACCEPTED',attribute_value=> 'YES');
5 END;
6 /
PL/SQL procedure successfully completed.
如果在11gr2中使用這種方法，將會得到錯誤資訊:
SQL> DECLARE
2 x number;
3 BEGIN
4 x := dbms_spm.alter_sql_plan_baseline('SQL_67b129b37635284b','SQL_PLAN_6gc99qdv3aa2bbeaed07c',attribute_name=> 'ACCEPTED',attribute_value=> 'YES');
5 END;
6 /
ERROR at line 1:
ORA-38136: invalid attribute name ACCEPTED specified
ORA-06512: at "SYS.DBMS_SPM", line 2532
ORA-06512: at line 4
那麼如何在11gr2中修改SQL執行計劃基線的ACCEPTED值，可以使用dbms_spm.evolve_sql_plan_baseline與dbms_spm.alter_sql_plan_baseline。
SQL> SET SERVEROUTPUT ON
SQL> SET LONG 10000
SQL> DECLARE
2 x clob;
3 BEGIN
4 x := dbms_spm.evolve_sql_plan_baseline('SQL_67b129b37635284b', 'SQL_PLAN_6gc99qdv3aa2bbeaed07c',verify=>'NO' ,commit=>'YES');
5 DBMS_OUTPUT.PUT_LINE(x);
6 END;
7 /
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SQL_67b129b37635284b
PLAN_NAME = SQL_PLAN_6gc99qdv3aa2bbeaed07c
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = NO
COMMIT = YES
Plan: SQL_PLAN_6gc99qdv3aa2bbeaed07c
------------------------------------
Plan was changed to an accepted plan.
-------------------------------------------------------------------------------
Report Summary
-------------------------------------------------------------------------------
Number of plans verified: 0
Number of plans accepted: 1
PL/SQL procedure successfully completed.
這裡的verify引數在修改ACCEPTED為YES之前是否需要執行相應的執行計劃並與當前可以使用的執行計劃比較效能。如果ACCEPTED為NO的執行計劃的效能有所提高，那麼將執行計劃基線的ACCEPTED設定為YES。當這個引數設定為"YES"時，如果執行計劃的效能有所提高就將ACCEPTED修改為YES。當這個引數設定為"NO"時，直接將ACCEPTED修改為YES。
引數commit指定是否將ACCEPTED值從NO修改為YES。當設定為YES時，執行計劃基線會將ACCEPTED設定為YES並生成一個報告。當設定為NO時，一個報告會生成但不會改變ACCEPTED值。
SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select %c1>4076'; SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT ------------------------------ ------------------------------ -------------- ------- -------- -------------------------------------------------------------------------------- SQL_67b129b37635284b SQL_PLAN_6gc99qdv3aa2bbeaed07c AUTO-CAPTURE YES YES select * from t1 where c1>4076 SQL_67b129b37635284b SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD YES YES select * from t1 where c1>4076 SQL> DECLARE 2 x number; 3 BEGIN 4 x := dbms_spm.alter_sql_plan_baseline(sql_handle =>'SQL_67b129b37635284b',plan_name =>'SQL_PLAN_6gc99qdv3aa2bdbd90e8e',attribute_name =>'ENABLED',attribute_value =>'NO'); 5 END; 6 / PL/SQL procedure successfully completed. SQL> select sql_handle, plan_name,origin, enabled, accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select %c1>4076'; SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED SQL_TEXT ------------------------------ ------------------------------ -------------- ------- -------- -------------------------------------------------------------------------------- SQL_67b129b37635284b SQL_PLAN_6gc99qdv3aa2bbeaed07c AUTO-CAPTURE YES YES select * from t1 where c1>4076 SQL_67b129b37635284b SQL_PLAN_6gc99qdv3aa2bdbd90e8e MANUAL-LOAD NO YES select * from t1 where c1>4076
現在手動建立的使用全表掃描的SQL執行計劃基線的ENABLED為NO，新生成的使用索引範圍掃描的SQL執行計劃基線的ENABLED與ACCEPTED值為YES，說明最佳化器可以使用。
SQL> select * from t1 where c1>4076;
924 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 957 | 3828 | 4 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 957 | 3828 | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1">4076)
Note
-----
- SQL plan baseline "SQL_PLAN_6gc99qdv3aa2bbeaed07c" used for this statement
可以看到最佳化器確實使用了使用索引範圍掃描的SQL執行計劃基線。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2107763/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL Plan Management(SPM)SQL
- SQL Plan Management介紹SQL
- SQL PLAN Management的測試SQL
- 11g-sql plan managementSQL
- Oracle11g中SQL Plan Management （一）OracleSQL
- Oracle11g中SQL Plan Management （二）OracleSQL
- Oracle11g中SQL Plan Management （三）OracleSQL
- Oracle 11g 新特性 -- SQL Plan Management 示例OracleSQL
- Oracle 11g SQL效能的新特性（三）- SQL Plan ManagementOracleSQL
- 【DBA】Oracle 11g 針對SQL效能的新特性（三）- SQL Plan ManagementOracleSQL
- oracle11g中SQL最佳化（SQL TUNING）新特性之SQL Plan Management(SPM)OracleSQL
- SQL management baseSQL
- sql_plan_baselineSQL
- WRH$_SQL_PLAN 被鎖SQL
- sql plan baselines（一）SQL
- sql plan baseline（二）SQL
- SQL Server Management Studio (SSMS)SQLServerSSM
- Shared SQL Dependency Management (249)SQL
- 【SQL_PLAN】Oracle 透過檢視sql_plan 格式化執行計劃SQLOracle
- 使用V$SQL_PLAN檢視SQL
- v$sql_plan 檢視解析SQL
- 用EXPLAIN PLAN 分析SQL語句AISQL
- SAP ABAP SQL的execution plan和cacheSQL
- sql plan baseline使用心得SQL
- Retrieve SQL and Execution Plan from AWR SnapshotsSQL
- SQL Plan Baselines 實驗01SQL
- Oracle SQL Plan Baseline 學習OracleSQL
- SQL Server 2005：清空plan cacheSQLServer
- EXPLAIN PLAN and SQL*PLUS AUTOTRACE may not generate actual plansAISQL
- v$sql_plan這個檢視解析SQL
- Manage SQL Plan Baselines in Oracle 12cSQLOracle
- ORACLE SQL調優之'PLAN_TABLE' is old versionOracleSQL
- 【Explain Plan】檢視SQL的執行計劃AISQL
- explain plan VS execution planAI
- (轉):學習Oracle動態效能表-(6)-V$SQL,V$SQL_PLANOracleSQL
- Sql Server系列：Microsoft SQL Server Management Studio模板資源管理器SQLServerROS
- 新增輔助資料庫 (SQL Server Management Studio)資料庫SQLServer
- 啟用日誌傳送 (SQL Server Management Studio)SQLServer