Manage SQL Plan Baselines in Oracle 12c
使用dbms_spm與dbms_xplan包來執行大部分的SQL執行計劃管理任務。SQL執行計劃管理可以分為以下基本任務:
.配置SQL執行計劃管理
.顯示SQL執行計劃基線中的執行計劃
.載入SQL執行計劃基線
.手動evolve執行計劃基線中的執行計劃
.刪除SQL執行計劃基線
.管理SQL Management Base(SMB)
.遷移Stored Outlines to SQL Plan Baselines
配置SQL執行計劃管理
.配置捕獲與使用SQL Plan Baselines
.管理SPM Evolve Advisor Task
配置捕獲與使用SQL Plan Baselines
可以使用optimizer_capture_sql_plan_baselines與optimizer_use_sql_plan_baselines引數來控制SQL plan管理。
SQL> show parameter sql_plan NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE
optimizer_capture_sql_plan_baselines的預設值為false。對於不在執行計劃歷史中的任何重複的SQL語句，資料庫不會對SQL語句自動建立一個初始的SQL Plan Baseline。如果optimizer_capture_sql_plan_baselines引數設定為true，那麼可以使用dbms_spm.configure過程來配置過濾器來判斷哪些SQL語句滿足捕獲條件。預設情況是沒有配置過濾器的，這意味著所有重複執行的SQL語句都滿足捕獲條件。
optimizer_use_sql_plan_baselines的預設值為true。對於已經在SQL plan baseline中存在的任何SQL語句，資料庫會自動向SQL plan baselines中以未接受的執行計劃來新增新的SQL plan。
對SQL Plan管理啟用自動初始化Plan捕獲
將optimizer_capture_sql_plan_baselines引數設定為true是對在plan歷史中不存在的任何SQL語句自動建立一個初始化SQL Plan baseline所必要的。預設情況下，當自動SQL plan baseline捕獲被啟用後，資料庫會為每個重複的SQL語句，包括所有遞迴SQL語句與監控SQL語句建立一個SQL Plan baseline。因此，自動捕獲功能可能會造成大量的SQL Plan Baseline。 為了限制捕獲的SQL Plan Baselines的數量可以使用dbms_spm.configure過程來配置過濾條件。optimizer_capture_sql_plan_baselines引數不控制自動向之前建立的SQL plan baseline新增新發現的執行計劃。
啟用自動捕獲SQL plan baseline操作如下:
1.以有相關許可權的使用者用SQL*Plus登入資料庫
[oracle@jytest1 ~]$ sqlplus sys/abcd@jypdb as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 12 21:50:10 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
2.顯示當前SQL Plan管理的設定情況
SQL> show parameter sql_plan NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE
3.為了對重複的SQL語句啟用自動生成SQL Plan Baseline執行下面的語句
SQL> alter system set optimizer_capture_sql_plan_baselines=true scope=both sid='*'; System altered. SQL> show parameter optimizer_capture_sql_plan_baselines NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean TRUE
當啟用SQL Plan Baselines自動捕獲功能後可以從下面的結果看到對重複的所有SQL語句進行了執行執行的捕獲
SQL> select t.sql_handle,t.sql_text,t.creator,t.origin from DBA_SQL_PLAN_BASELINES t; SQL_HANDLE SQL_TEXT CREATOR ORIGIN ------------------------ -------------------------------------------------------------------------------- --------- --------------- SQL_187ebe987c151d1b select value from v$nls_parameters where parameter = 'NLS_LENGTH_SEMANTICS' SYS AUTO-CAPTURE SQL_65afdf280fbfa69f select * from DBA_SQL_PLAN_BASELINES t SYS AUTO-CAPTURE SQL_6807bab99db0361a select value from v$sesstat where sid = :sid order by statistic# SYS AUTO-CAPTURE
為自動SQL Plan Baseline捕獲配置過濾條件
如果optimizer_capture_sql_plan_baselines設定為true，那麼你可以使用dbms_spm.configure過程來對重複執行的SQL語句建立一個自動捕獲過濾條件。自動過濾可以只捕獲想要的SQL語句並排除非關鍵語句，這樣可以節省SYSAUX表空間的使用。可以對不同的型別配置多個引數，也可以在單獨的語句中對相同的引數指定多個引數值，資料庫會進行組合。這種設定是附加型的：一個引數設定不會覆蓋之前的設定。例如，下面的過濾設定用來捕獲解析方案SYS或SYSTEM中的SQL語句:
exec dbms_spm.configure('auto_capture_parsing_schema_name','sys',true);
exec dbms_spm.configure('auto_capture_parsing_schema_name','system',true);
然而，不能在相同的過程中對相同的引數指定多個引數值。例如不能對AUTO_CAPTURE_SQL_TEXT指定多個SQL文字字串。DBA_SQL_MANAGEMENT_CONFIG檢視可以用來顯示當前引數值。
下面的操作假設optimizer_capture_sql_plan_baselines引數被設定為true。只要捕獲sh方案所有執行的SQL語句並且想要排除包含test_only文字的語句
1.以有相關許可權的使用者用SQL*Plus登入資料庫
[oracle@jytest1 ~]$ sqlplus sys/abcd@jypdb as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 12 21:50:10 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
2.為了刪除對解析方案與SQL文字已經存在的任何過濾條件執行以下語句:
SQL> exec dbms_spm.configure('auto_capture_parsing_schema_name',null,true);
PL/SQL procedure successfully completed.
SQL> exec dbms_spm.configure('auto_capture_sql_text',null,true);
PL/SQL procedure successfully completed.
SQL> select parameter_name, parameter_value
2 from dba_sql_management_config
3 where parameter_name like '%AUTO%';
PARAMETER_NAME PARAMETER_VALUE
-------------------------------- --------------------------------
AUTO_CAPTURE_PARSING_SCHEMA_NAME
AUTO_CAPTURE_MODULE
AUTO_CAPTURE_ACTION
AUTO_CAPTURE_SQL_TEXT
3.只對sh方案所執行的語句啟用自動捕獲
SQL> exec dbms_spm.configure('auto_capture_parsing_schema_name','sh',true);
PL/SQL procedure successfully completed.
4.從自動捕獲中排除任何包含test_only文字的語句
SQL> exec dbms_spm.configure('auto_capture_sql_text','%test_only%',false);
PL/SQL procedure successfully completed.
5.透過查詢dba_sql_management_config檢視來確認配置的過濾條件
SQL> col parameter_name format a32 SQL> col parameter_value format a32 SQL> select parameter_name, parameter_value 2 from dba_sql_management_config 3 where parameter_name like '%AUTO%'; PARAMETER_NAME PARAMETER_VALUE -------------------------------- -------------------------------- AUTO_CAPTURE_PARSING_SCHEMA_NAME parsing_schema IN (SH) AUTO_CAPTURE_MODULE AUTO_CAPTURE_ACTION AUTO_CAPTURE_SQL_TEXT (sql_text NOT LIKE %test_only%)
禁用所有SQL Plan Baselines
當optimizer_use_sql_plan_baselines引數設定為false時，資料庫不會使用任何SQL Plan Baseline。為了禁用所有SQL Plan baselines執行以下操作:
1.以有相關許可權的使用者用SQL*Plus登入資料庫
[oracle@jytest1 ~]$ sqlplus sys/abcd@jypdb as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 12 21:50:10 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> show parameter sql_plan NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean FALSE optimizer_use_sql_plan_baselines boolean TRUE
2.為了忽略所有現存的SQL Plan Baselines執行以下語句
SQL> alter system set optimizer_use_sql_plan_baselines=false scope=both sid='*'; System altered. SQL> show parameter sql_plan NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean FALSE optimizer_use_sql_plan_baselines boolean FALSE
管理SPM Evolve Advisor Task
SPM Evolve Advisor是一個SQL Advisor可以對最近新增到SQL Plan Baseline中的SQL Plan進行evolve。預設情況下，SYS_AUTO_SPM_EVOLVE_TASK在排程維護視窗中每天執行。SPM Evolve Advisor Task執行以下操作:
1.定位未接受的SQL Plan
2.對所有未接受的SQL Plan進行排名
3.在維護視窗儘可能的對大量的SQL Plan進行測試執行
4.選擇一個成本最低的執行計劃與每個未接受的執行計劃進行比較
5.使用基於成本的演算法來自動接受比現有已接受的執行計劃效能更好的任何未接受的執行計劃
啟用與禁用SPM Evolve Advisor Task
對於自動SPM Evolve Advisor Task沒有單獨的排程客戶端存在。一個排程客戶端控制著自動SQL Tuning Advisor與自動SPM Evolve Advisor。
配置自動SPM Evolve Advisor Task
透過使用dbms_spm.set_evolve_task_parameter過程來指定任務引數來配置自動SQL Plan Evolve。因為SYS_AUTO_SPM_EVOLVE_TASK任務的所有者為SYS，只有SYS使用者可以設定任務引數。
dbms_spm.set_evolve_task_parameter有以下引數
alternate_plan_source:決定新增SQL Plan的搜尋源:cursor_cache,automatic_workload_repository或sql_tuning_sets。可以使用+號來組合多個引數值，預設值為cursor_cache+automatic_workload_repository
alternate_plan_baseline:決定那個替代plan應該被載入。EXISING它是預設值，使用現有的SQL Plan baseline來為語句載入SQL plan。NEW不使用現有SQL plan baseline來為語句載入SQL plan，並且會建立一個新的SQL Plan baseline。可以使用+號來組合多個引數值。
alternate_plan_limit:指定可以載入SQL Plan的最大數量，預設值為0。
accept_plans:指定是否自動接受建議的SQL Plan。當accept_plans設定為true(預設值)時，SQL Plan管理自動接受由SPM Evolve Advisor Task所建議的所有SQL Plan。當設定為false時，如果找到替代的SQL plan，SPM Evolve Advisor Task會驗證SQLPlan並生成一個報告，但不會evolve這個SQL plan。
下面的操作假如滿足以下條件
.想要資料庫自動接受SQL Plan
.想在任務每次執行1200秒後就會超時
.想要evolve任務在共享SQL區與AWR檔案庫中查詢最多500個SQL Plan
設定自動evolve任務引數
1.以sys使用者登入資料庫
[oracle@jytest1 ~]$ sqlplus sys/abcd@jypdb as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 12 21:50:10 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
2.查詢sys_auto_spm_evolve_task任務的當前引數設定情況
SQL> col parameter_name format a25 SQL> col value format a42 SQL> select parameter_name, parameter_value as "value" 2 from dba_advisor_parameters 3 where ( (task_name = 'SYS_AUTO_SPM_EVOLVE_TASK') and 4 ( (parameter_name = 'ACCEPT_PLANS') or 5 (parameter_name LIKE '%ALT%') or 6 (parameter_name = 'TIME_LIMIT') ) ); PARAMETER_NAME value ------------------------- ------------------------------------------ TIME_LIMIT 3600 ALTERNATE_PLAN_LIMIT 10 ALTERNATE_PLAN_SOURCE CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY ALTERNATE_PLAN_BASELINE EXISTING ACCEPT_PLANS TRUE
3.使用以下PLSQL塊來配置sys_auto_spm_evolve_task任務自動接收SQL plan,在共享SQL區與AWR檔案庫中查詢最多500個SQL plan，並且在執行20分鐘後任務就會超時終止。
SQL> begin 2 dbms_spm.set_evolve_task_parameter(task_name => 'SYS_AUTO_SPM_EVOLVE_TASK', parameter => 'TIME_LIMIT', value => '1200'); 3 dbms_spm.set_evolve_task_parameter(task_name => 'SYS_AUTO_SPM_EVOLVE_TASK', parameter => 'ACCEPT_PLANS', value => 'true'); 4 dbms_spm.set_evolve_task_parameter(task_name => 'SYS_AUTO_SPM_EVOLVE_TASK', parameter => 'ALTERNATE_PLAN_LIMIT', value => '500'); 5 end; 6 / PL/SQL procedure successfully completed.
4.確認sys_auto_spm_evolve_task任務的當前引數設定情況
SQL> col parameter_name format a25 SQL> col value format a42 SQL> select parameter_name, parameter_value as "value" 2 from dba_advisor_parameters 3 where ( (task_name = 'SYS_AUTO_SPM_EVOLVE_TASK') and 4 ( (parameter_name = 'ACCEPT_PLANS') or 5 (parameter_name LIKE '%ALT%') or 6 (parameter_name = 'TIME_LIMIT') ) ); PARAMETER_NAME value ------------------------- ------------------------------------------ TIME_LIMIT 1200 ALTERNATE_PLAN_LIMIT 500 ALTERNATE_PLAN_SOURCE CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY ALTERNATE_PLAN_BASELINE EXISTING ACCEPT_PLANS true
顯示SQL執行計劃基線中的執行計劃
為了檢視指定SQL語句儲存在SQL Plan Baseline中的SQL Plan，可以使用dbms_xplan.display_sql_plan_baseline 函式。這個函式使用儲存在plan history中的執行計劃資訊來顯示SQL Plan。它有以下引數:
sql_handle:語句的 SQL handle可以透過連線v$sql.sql_plan_baseline與dba_sql_plan_baselines.plan_name列來進行查詢
plan_name:語句執行計劃的名字
假設要顯示SQL ID為34q7g1h49b79n的語句所儲存在SQL Plan Baseline中的執行計劃執行下面的語句
SQL> select * from hr.jobs;
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
AD_PRES President 20080 40000
AD_VP Administration Vice President 15000 30000
AD_ASST Administration Assistant 3000 6000
FI_MGR Finance Manager 8200 16000
FI_ACCOUNT Accountant 4200 9000
AC_MGR Accounting Manager 8200 16000
AC_ACCOUNT Public Accountant 4200 9000
SA_MAN Sales Manager 10000 20080
SA_REP Sales Representative 6000 12008
PU_MAN Purchasing Manager 8000 15000
PU_CLERK Purchasing Clerk 2500 5500
JOB_ID JOB_TITLE MIN_SALARY MAX_SALARY
---------- ----------------------------------- ---------- ----------
ST_MAN Stock Manager 5500 8500
ST_CLERK Stock Clerk 2008 5000
SH_CLERK Shipping Clerk 2500 5500
IT_PROG Programmer 4000 10000
MK_MAN Marketing Manager 9000 15000
MK_REP Marketing Representative 4000 9000
HR_REP Human Resources Representative 4000 9000
PR_REP Public Relations Representative 4500 10500
19 rows selected.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 4gc64454ax64x, child number 1
-------------------------------------
select * from hr.jobs
Plan hash value: 944056911
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS FULL| JOBS | 19 | 627 | 3 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / JOBS@SEL$1
Outline Data
-------------
/*+
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
OPT_PARAM('optimizer_dynamic_sampling' 0)
ALL_ROWS
NO_PARALLEL
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "JOBS"@"SEL$1")
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "JOBS"."JOB_ID"[VARCHAR2,10], "JOBS"."JOB_TITLE"[VARCHAR2,35],
"JOBS"."MIN_SALARY"[NUMBER,22], "JOBS"."MAX_SALARY"[NUMBER,22]
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel thre
shold
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- SQL plan baseline SQL_PLAN_bmz5xhst6b31y41975532 used for this statement
45 rows selected.
SQL> SELECT PLAN_TABLE_OUTPUT
2 FROM V$SQL s, DBA_SQL_PLAN_BASELINES b,
3 TABLE(
4 DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(b.sql_handle,b.plan_name,'basic')
5 ) t
6 WHERE s.EXACT_MATCHING_SIGNATURE=b.SIGNATURE
7 AND b.PLAN_NAME=s.SQL_PLAN_BASELINE
8 AND s.SQL_ID='4gc64454ax64x';
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_b9fcbd8632658c3e
SQL text: select * from hr.jobs
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_bmz5xhst6b31y41975532 Plan id: 1100436786
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
Plan rows: From dictionary
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 944056911
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| JOBS |
----------------------------------
20 rows selected.
上面的結果顯示SQL ID為4gc64454ax64x的執選執行計劃名字叫SQL_PLAN_bmz5xhst6b31y41975532並且是被自動捕獲的。
載入SQL執行計劃基線
使用dbms_spm可以批次載入一組現有的執行計劃到一個SQL Plan Baseline中。dbms_spm包可以從以下來源載入執行計劃:
.AWR:要從AWR快照載入執行計劃，那麼必須指定快照開始與結束的範圍，另外也可以應用過濾條件來只載入滿足條件的執行計劃。預設情況下，資料庫在下一次執行SQL語句時最佳化器就會使用載入到SQL Plan Baseline中的執行計劃。
.共享SQL區:直接從共享SQL區來載入執行計劃。透過對模組名，方案名或SQL ID應用過濾條件可以標識需要被捕獲的SQL語句或一組SQL語句。資料庫在下一次執行SQL語句時最佳化器就會使用載入到SQL Plan Baseline中的執行計劃。當應用程式SQL已經透過手寫hints進行過最佳化之後直接從共享SQL區中載入執行計劃是非常有用的。因為你可能不能更改SQL包括hint，使用SQL Plan Baseline可以確保應用程式SQL使用最優的執行計劃。
.SQL tuning set(STS):捕獲SQL工作量的執行計劃到一個STS中，然後載入執行計劃到SQL Plan Baselines中。資料庫在下一次執行SQL語句時最佳化器就會使用載入到SQL Plan Baseline中的執行計劃。從STS中批次載入執行計劃是在資料庫升級後防止執行計劃迴歸有效的方法。
.Staging table:使用dbms_spm包可以定義一個staging表，dbms_spm.pack_stgtab_baseline過程可以複製SQLPlan baseline到一個staging表中，並使用Oracle data pump將共staging表傳輸到另一個資料庫。在目標資料庫中，使用dbms_spm.unpack_stgtab_baseline過程來從staging表中把SQL plan baseline載入到SMB中。
.Stored outline:遷移stroed outlines到SQL Plan Baselines中。在遷移之後，你可以透過SQL Plan管理所提供的更高階的功能來維護相同的執行計劃穩定性。
從AWR載入執行計劃
假設我們要將下面的查詢語句的執行計劃載入到SQL Plan Baseline中，那麼要確保使用者sh有查詢dba_hist_snapshot和dba_sql_plan_baselines檢視，執行dbms_workload_repository.create_snapshot和dbms_spm.load_plans_from_awr的許可權
SELECT /*LOAD_AWR*/ * FROM sh.sales WHERE quantity_sold > 40 ORDER BY prod_id;
為了從AWR中載入執行計劃到SQL Plan Baselines中執行以下操作
1.以有相關許可權的使用者登入到資料庫，然後查詢最近生成的3個AWR快照
SQL> select * 2 from (select instance_number,snap_id, snap_level, 3 to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin 4 from dba_hist_snapshot 5 order by snap_id desc) 6 where rownum < = 3; INSTANCE_NUMBER SNAP_ID SNAP_LEVEL BEGIN --------------- ---------- ---------- ----------------- 1 7061 1 14/02/19 16:00:09 2 7061 1 14/02/19 16:00:09 1 7060 1 14/02/19 15:00:35
2.查詢sh.sales表，使用load_awr標記來識別這個SQL語句
SQL> select /*load_awr*/ * 2 from sh.sales 3 where quantity_sold > 40 4 order by prod_id; no rows selected
3.生成一個新的AWR快照
SQL> exec dbms_workload_repository.create_snapshot; PL/SQL procedure successfully completed.
4.查詢最近生成的3個AWR快照來確保新的AWR快照已經生成了
SQL> select * 2 from (select instance_number,snap_id, snap_level, 3 to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin 4 from dba_hist_snapshot 5 order by snap_id desc) 6 where rownum < = 3; INSTANCE_NUMBER SNAP_ID SNAP_LEVEL BEGIN --------------- ---------- ---------- ----------------- 1 7062 1 14/02/19 17:00:09 2 7062 1 14/02/19 17:00:09 1 7061 1 14/02/19 16:00:09
5.使用最近生成的2個AWR快照來載入執行計劃
SQL> variable v_plan_cnt number SQL> exec :v_plan_cnt := dbms_spm.load_plans_from_awr(begin_snap => 7061, end_snap =>7062); PL/SQL procedure successfully completed.
6.查詢資料字典來確保load_awr語句的執行計劃被載入到SQL Plan Baselines中了
SQL> col sql_handle format a20 SQL> col sql_text format a20 SQL> col plan_name format a30 SQL> col origin format a20 SQL> select sql_handle, sql_text, plan_name, 2 origin, enabled, accepted 3 from dba_sql_plan_baselines 4 where sql_text like '%load_awr%'; SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC -------------------- -------------------- ------------------------------ -------------------- --- --- SQL_495d29c5f4612cda select /*load_awr*/ SQL_PLAN_4kr99sru62b6u54bc8843 MANUAL-LOAD-FROM-AWR YES YES * from sh.sales where quantity_sold > 40 order by prod_id
7.再次執行load_awr語句，檢視其執行計劃可以看到SQL plan baseline SQL_PLAN_4kr99sru62b6u54bc8843 used for this statement這樣的資訊，說明生成的執行計劃基線應用到該語句了
SQL> select /*load_awr*/ *
2 from sh.sales
3 where quantity_sold > 40
4 order by prod_id;
no rows selected
SQL> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID dybku83zppk0d, child number 1
-------------------------------------
select /*load_awr*/ * from sh.sales where quantity_sold > 40 order by
prod_id
Plan hash value: 3803407550
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 511 (100)| | | |
| 1 | SORT ORDER BY | | 1 | 29 | 511 (2)| 00:00:01 | | |
| 2 | PARTITION RANGE ALL| | 1 | 29 | 510 (2)| 00:00:01 | 1 | 28 |
|* 3 | TABLE ACCESS FULL | SALES | 1 | 29 | 510 (2)| 00:00:01 | 1 | 28 |
----------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / SALES@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
OPT_PARAM('optimizer_dynamic_sampling' 0)
ALL_ROWS
NO_PARALLEL
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "SALES"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("QUANTITY_SOLD">40)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22],
"SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22],
"SALES"."PROMO_ID"[NUMBER,22], "SALES"."QUANTITY_SOLD"[NUMBER,22],
"SALES"."AMOUNT_SOLD"[NUMBER,22]
2 - (rowset=256) "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22],
"SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22],
"SALES"."PROMO_ID"[NUMBER,22], "QUANTITY_SOLD"[NUMBER,22],
"SALES"."AMOUNT_SOLD"[NUMBER,22]
3 - (rowset=256) "SALES"."PROD_ID"[NUMBER,22], "SALES"."CUST_ID"[NUMBER,22],
"SALES"."TIME_ID"[DATE,7], "SALES"."CHANNEL_ID"[NUMBER,22],
"SALES"."PROMO_ID"[NUMBER,22], "QUANTITY_SOLD"[NUMBER,22],
"SALES"."AMOUNT_SOLD"[NUMBER,22]
Note
-----
- automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold
- SQL plan baseline SQL_PLAN_4kr99sru62b6u54bc8843 used for this statement
64 rows selected.
從共享SQL區載入執行計劃
假設要從共享SQL區將下面的查詢語句的執行計劃載入到SQL Plan Baseline中需要執行以下操作
1.執行SQL語句
SQL> SELECT /*LOAD_CC*/ * 2 FROM sh.sales 3 WHERE quantity_sold > 40 4 ORDER BY prod_id; no rows selected
2.查詢v$sql檢視查詢執行語句的SQL ID
SQL> SELECT SQL_ID, CHILD_NUMBER AS "Child Num", 2 PLAN_HASH_VALUE AS "Plan Hash", 3 OPTIMIZER_ENV_HASH_VALUE AS "Opt Env Hash" 4 FROM V$SQL 5 WHERE SQL_TEXT LIKE 'SELECT /*LOAD_CC*/%'; SQL_ID Child Num Plan Hash Opt Env Hash ------------- ---------- ---------- ------------ 09x8cz4wrn655 0 3803407550 4099961812
3.從共享SQL區載入指定語句的執行計劃到SQL Plan Baseline中
SQL> VARIABLE v_plan_cnt NUMBER SQL> EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id =>'09x8cz4wrn655'); PL/SQL procedure successfully completed.
4.查詢dba_sql_plan_baselines檢視來確認語句的執行計劃是否載入到SQL Plan Baselines中了
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, 2 ORIGIN, ENABLED, ACCEPTED 3 FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%LOAD_CC%'; SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC -------------------- -------------------- ------------------------------ -------------------- --- --- SQL_f6cb7f742ef93547 SELECT /*LOAD_CC*/ * SQL_PLAN_gdkvzfhrgkda754bc8843 MANUAL-LOAD-FROM-CUR YES YES SOR-CACHE FROM sh.sales WHERE quantity_sold > 40 ORDER BY prod_id
從SQL Tuning Set中載入執行計劃
一個SQL Tuning Set是一個資料庫物件它包括一個或多個SQL語句，執行統計資訊與執行上下文資訊。假設SQLTuning Set包含下面的語句，要從SQL Tuning Set中載入該語句的執行計劃到SQL Plan Baselines中要執行以下操作
1.執行SQL語句並找到其SQL ID
SQL> SELECT /*LOAD_STS*/ * 2 FROM sh.sales 3 WHERE quantity_sold > 40 4 ORDER BY prod_id; no rows selected SQL> SELECT SQL_ID, CHILD_NUMBER AS "Child Num", 2 PLAN_HASH_VALUE AS "Plan Hash", 3 OPTIMIZER_ENV_HASH_VALUE AS "Opt Env Hash" 4 FROM V$SQL 5 WHERE SQL_TEXT LIKE 'SELECT /*LOAD_STS*/%'; SQL_ID Child Num Plan Hash Opt Env Hash ------------- ---------- ---------- ------------ bma11r5a6r26j 0 3803407550 4099961812
2.將執行的SQL語句載入到SQL Tuning Set中
SQL> exec dbms_sqltune.create_sqlset(sqlset_name=>'sql_tuning_set');
PL/SQL procedure successfully completed.
SQL> DECLARE
2 cur DBMS_SQLTUNE.SQLSET_CURSOR;
3 BEGIN
4 OPEN cur FOR
5 SELECT VALUE(P)
6 FROM table(DBMS_SQLTUNE.SELECT_CURSOR_CACHE('sql_id=''bma11r5a6r26j''',
7 NULL,
8 NULL,
9 NULL,
10 NULL,
11 1,
12 NULL,
13 'ALL')) P;
14 DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'sql_tuning_set',
15 populate_cursor => cur);
16 END;
17 /
PL/SQL procedure successfully completed.
SQL> SELECT SQL_TEXT FROM DBA_SQLSET_STATEMENTS WHERE SQLSET_NAME = 'sql_tuning_set';
SQL_TEXT
--------------------
SELECT /*LOAD_STS*/
*
FROM sh.sales
WHERE quantity_sold
> 40
ORDER BY prod_id
3.從SQL Tuning Set中載入執行計劃到SQL Plan Baseline中
SQL> VARIABLE v_plan_cnt NUMBER SQL> EXECUTE :v_plan_cnt := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(sqlset_name => 'sql_tuning_set',basic_filter => 'sql_text like ''SELECT /*LOAD_STS*/%''' ); PL/SQL procedure successfully completed.
basic_filter引數指定了一個where子句用來只載入需要的SQL語句,v_plan_cnt用來儲存從SQL Tuning Set所載入的執行計劃數。
4.查詢資料字典來確保SQL Tuning Set中的語句的執行計劃是否成功載入到SQL Plan Baselines中
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, 2 ORIGIN, ENABLED, ACCEPTED 3 FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%LOAD_STS%'; SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC -------------------- -------------------- ------------------------------ -------------------- --- --- SQL_a8632bd857a4a25e SELECT /*LOAD_STS*/ SQL_PLAN_ahstbv1bu98ky54bc8843 MANUAL-LOAD-FROM-STS YES YES * FROM sh.sales WHERE quantity_sold > 40 ORDER BY prod_id
5.刪除SQL Tuning Set
SQL> exec dbms_sqltune.drop_sqlset(sqlset_name=>'sql_tuning_set'); PL/SQL procedure successfully completed.
從Staging Table中載入執行計劃
有時可能需要從一個源資料庫傳輸最最佳化的執行計劃到一個目標資料庫那麼需要執行以下操作
1.使用create_stgtab_baseline過程來建立一個staging表
SQL> BEGIN 2 DBMS_SPM.CREATE_STGTAB_BASELINE ( 3 table_name => 'stage1'); 4 END; 5 / PL/SQL procedure successfully completed.
2.在源資料庫中，將SQL Plan Baseline從SQL管理基礎框架中打包到staging表中
DECLARE v_plan_cnt NUMBER; BEGIN v_plan_cnt := DBMS_SPM.PACK_STGTAB_BASELINE ( table_name => 'stage1' , enabled => 'yes' , creator => 'spm' ); END; /
3.將staging表stage1使用Oracle Data Pump Export匯出到一個dump檔案中
4.將dump檔案傳輸到目標資料庫
5.在目標資料庫中，使用Oracle Data Pump Import將dump檔案中的資料匯入到staging表stage1中
6.在目標資料庫中，將SQL Plan Baseline從staging表中解壓到SQL管理基礎框架中
DECLARE v_plan_cnt NUMBER; BEGIN v_plan_cnt := DBMS_SPM.UNPACK_STGTAB_BASELINE ( table_name => 'stage1' , fixed => 'yes' ); END; /
SQL Plan baselines Evolve
這裡將介紹如何使用命令列來evolve sql plan baselines。為了evolve一個特定的sql執行計劃執行以下操作:
1.建立一個evolve任務
2.設定evolve任務引數
3.執行evolve任務
4.實現任務中給出的建議
5.顯示任務執行的結果
下面將舉例來說明，假設滿足以下條件
.資料庫沒有啟用自動evolve任務
.對下面的查詢建立一個SQL Plan Baseline
SELECT /* q2_group_by */ prod_name, sum(quantity_sold) FROM products p, sales s WHERE p.prod_id = s.prod_id AND p.prod_category_id =204 GROUP BY prod_name;
.想要建立兩個索引來提高查詢語句的效能，如果使用索引的效能比SQL Plan Baseline中的當前執行計劃的效能好那麼就evolve該執行計劃
為了evolve一個特定的執行計劃需要執行以下操作
1.執行初始化設定操作
清空共享池與緩衝區快取
SQL> ALTER SYSTEM FLUSH SHARED_POOL; System altered. SQL> ALTER SYSTEM FLUSH BUFFER_CACHE; System altered.
啟用自動捕獲SQL Plan Baselines
SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true; System altered. SQL> show parameter sql_plan NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_capture_sql_plan_baselines boolean TRUE optimizer_use_sql_plan_baselines boolean TRUE
以sh使用者登入到資料庫，然後設定SQLPLUS的顯示引數
[oracle@jytest1 ~]$ sqlplus sh/sh@jypdb SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 14 23:30:09 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Last Successful login time: Thu Feb 14 2019 23:01:23 +08:00 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> SET PAGES 10000 LINES 140 SQL> SET SERVEROUTPUT ON SQL> COL SQL_TEXT FORMAT A20 SQL> COL SQL_HANDLE FORMAT A20 SQL> COL PLAN_NAME FORMAT A30 SQL> COL ORIGIN FORMAT A12 SQL> SET LONGC 60535 SQL> SET LONG 60535 SQL> SET ECHO ON
2.執行SQL語句，因此可以自動捕獲它
執行下面的SQL語句
SQL> SELECT /* q1_group_by */ prod_name, sum(quantity_sold) 2 FROM products p, sales s 3 WHERE p.prod_id = s.prod_id 4 AND p.prod_category_id =203 5 GROUP BY prod_name; PROD_NAME SUM(QUANTITY_SOLD) -------------------------------------------------- ------------------ Envoy External 6X CD-ROM 11526 Model SM26273 Black Ink Cartridge 15910 PCMCIA modem/fax 28800 baud 19278 Multimedia speakers- 3" cones 10969 Internal 8X CD-ROM 11197 Deluxe Mouse 11609 Model CD13272 Tricolor Ink Cartridge 12321 Model NM500X High Yield Toner Cartridge 6466 18" Flat Panel Graphics Monitor 4415 External 8X CD-ROM 13886 SIMM- 8MB PCMCIAII card 17544 PCMCIA modem/fax 19200 baud 20467 Envoy External 8X CD-ROM 14850 Envoy External Keyboard 2857 External 6X CD-ROM 11732 Model A3827H Black Image Cartridge 17314 Internal 6X CD-ROM 8533 17" LCD w/built-in HDTV Tuner 4874 SIMM- 16MB PCMCIAII card 14191 Multimedia speakers- 5" cones 10419 Standard Mouse 8714 21 rows selected.
查詢資料字典確認在SQL Plan Baseline中不存在執行計劃，因為只有重複執行的SQL語句才會被捕獲
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, 2 ACCEPTED, FIXED, AUTOPURGE 3 FROM DBA_SQL_PLAN_BASELINES 4 WHERE SQL_TEXT LIKE '%q1_group%'; no rows selected
再次執行SQL語句
SQL> SELECT /* q1_group_by */ prod_name, sum(quantity_sold) 2 FROM products p, sales s 3 WHERE p.prod_id = s.prod_id 4 AND p.prod_category_id =203 5 GROUP BY prod_name; PROD_NAME SUM(QUANTITY_SOLD) -------------------------------------------------- ------------------ Envoy External 6X CD-ROM 11526 Model SM26273 Black Ink Cartridge 15910 PCMCIA modem/fax 28800 baud 19278 Multimedia speakers- 3" cones 10969 Internal 8X CD-ROM 11197 Deluxe Mouse 11609 Model CD13272 Tricolor Ink Cartridge 12321 Model NM500X High Yield Toner Cartridge 6466 18" Flat Panel Graphics Monitor 4415 External 8X CD-ROM 13886 SIMM- 8MB PCMCIAII card 17544 PCMCIA modem/fax 19200 baud 20467 Envoy External 8X CD-ROM 14850 Envoy External Keyboard 2857 External 6X CD-ROM 11732 Model A3827H Black Image Cartridge 17314 Internal 6X CD-ROM 8533 17" LCD w/built-in HDTV Tuner 4874 SIMM- 16MB PCMCIAII card 14191 Multimedia speakers- 5" cones 10419 Standard Mouse 8714 21 rows selected.
3.查詢資料字典來確保執行計劃已經被載入到SQL Plan Baseline中了，下面的查詢顯示執行計劃已經被接受，這意味著執行計劃已經儲存在SQL Plan Baselines中了。origin列顯示為AUTO-CAPTURE，這意味著執行計劃是被自動捕獲的
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, 2 ORIGIN, ENABLED, ACCEPTED, FIXED 3 FROM DBA_SQL_PLAN_BASELINES 4 WHERE SQL_TEXT LIKE '%q1_group%'; SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC FIX -------------------- -------------------- ------------------------------ ------------ --- --- --- SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES NO y */ prod_name, sum( quantity_sold) FROM products p, sal es s WHERE p.prod_id = s. prod_id AND p.prod_category_ id =203 GROUP BY prod_name
4.下面對SQL語句進行解析並驗證最佳化器是否會使用SQL Plan Baseline中的執行計劃
SQL> EXPLAIN PLAN FOR 2 SELECT /* q1_group_by */ prod_name, sum(quantity_sold) 3 FROM products p, sales s 4 WHERE p.prod_id = s.prod_id 5 AND p.prod_category_id =203 6 GROUP BY prod_name; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic +note')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 3535171836 ------------------------------------------ | Id | Operation | Name | ------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | HASH GROUP BY | | | 2 | HASH JOIN | | | 3 | TABLE ACCESS FULL | PRODUCTS | | 4 | PARTITION RANGE ALL| | | 5 | TABLE ACCESS FULL | SALES | ------------------------------------------ Note ----- - SQL plan baseline "SQL_PLAN_0gwbcfvzskcu242949306" used for this statement 16 rows selected.
從執行計劃的Note部分可以看到SQL Plan Baseline已經應用到這個SQL語句了
5.建立兩個索引用來提高上面SQL語句的效能
SQL> CREATE INDEX ind_prod_cat_name ON products(prod_category_id, prod_name, prod_id); Index created. SQL> CREATE INDEX ind_sales_prod_qty_sold ON sales(prod_id, quantity_sold); Index created.
6.再次執行SQL語句，因為啟用了自動捕獲功能，所以新的執行計劃會被載入到SQL Plan Baseline中
SQL> SELECT /* q1_group_by */ prod_name, sum(quantity_sold) 2 FROM products p, sales s 3 WHERE p.prod_id = s.prod_id 4 AND p.prod_category_id =203 5 GROUP BY prod_name; PROD_NAME SUM(QUANTITY_SOLD) -------------------------------------------------- ------------------ Envoy External 6X CD-ROM 11526 Model SM26273 Black Ink Cartridge 15910 PCMCIA modem/fax 28800 baud 19278 Multimedia speakers- 3" cones 10969 Internal 8X CD-ROM 11197 Deluxe Mouse 11609 Model CD13272 Tricolor Ink Cartridge 12321 Model NM500X High Yield Toner Cartridge 6466 18" Flat Panel Graphics Monitor 4415 External 8X CD-ROM 13886 SIMM- 8MB PCMCIAII card 17544 PCMCIA modem/fax 19200 baud 20467 Envoy External 8X CD-ROM 14850 Envoy External Keyboard 2857 External 6X CD-ROM 11732 Model A3827H Black Image Cartridge 17314 Internal 6X CD-ROM 8533 17" LCD w/built-in HDTV Tuner 4874 SIMM- 16MB PCMCIAII card 14191 Multimedia speakers- 5" cones 10419 Standard Mouse 8714 21 rows selected.
7.查詢資料字典來確保新的執行計劃被載入到SQL Plan Baseline中了
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED
2 FROM DBA_SQL_PLAN_BASELINES
3 WHERE SQL_HANDLE IN ('SQL_07f16c76ff893342')
4 ORDER BY SQL_HANDLE, ACCEPTED;
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------------- -------------------- ------------------------------ ------------ --- ---
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu20135fd6c AUTO-CAPTURE YES NO
y */ prod_name, sum(
quantity_sold)
FROM products p, sal
es s
WHERE p.prod_id = s.
prod_id
AND p.prod_category_
id =203
GROUP BY prod_name
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES
y */ prod_name, sum(
quantity_sold)
FROM products p, sal
es s
WHERE p.prod_id = s.
prod_id
AND p.prod_category_
id =203
GROUP BY prod_name
上面的查詢結果顯示新的執行計劃是為被接受的。
8.再次解析SQL語句並驗證最佳化器是不是使用原始沒有索引的執行計劃
SQL> EXPLAIN PLAN FOR 2 SELECT /* q1_group_by */ prod_name, sum(quantity_sold) 3 FROM products p, sales s 4 WHERE p.prod_id = s.prod_id 5 AND p.prod_category_id =203 6 GROUP BY prod_name; Explained. SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(null, null, 'basic +note')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 3535171836 ------------------------------------------ | Id | Operation | Name | ------------------------------------------ | 0 | SELECT STATEMENT | | | 1 | HASH GROUP BY | | | 2 | HASH JOIN | | | 3 | TABLE ACCESS FULL | PRODUCTS | | 4 | PARTITION RANGE ALL| | | 5 | TABLE ACCESS FULL | SALES | ------------------------------------------ Note ----- - SQL plan baseline "SQL_PLAN_0gwbcfvzskcu242949306" used for this statement 16 rows selected.
上面的Note部分指示最佳化器使用了原始的沒有索引的執行計劃
9.以管理員使用者登入資料庫，然後建立一個evolve任務它包含未被接受執行計劃相關的所有SQL語句
[oracle@jytest1 ~]$ sqlplus sys/xxzx7817600@jypdb as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 14 23:48:51 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> VARIABLE cnt NUMBER SQL> VARIABLE tk_name VARCHAR2(50) SQL> VARIABLE exe_name VARCHAR2(50) SQL> VARIABLE evol_out CLOB SQL> EXECUTE :tk_name := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle => 'SQL_07f16c76ff893342',plan_name => 'SQL_PLAN_0gwbcfvzskcu20135fd6c'); PL/SQL procedure successfully completed. SQL> SELECT :tk_name FROM DUAL; :TK_NAME -------------------------------------------------------------------------------------------------------------------------------- TASK_11
10.執行evolve任務
SQL>EXECUTE :exe_name :=DBMS_SPM.EXECUTE_EVOLVE_TASK(task_name=>:tk_name); PL/SQL procedure successfully completed. SQL>SELECT :exe_name FROM DUAL; :EXE_NAME --------------------------------------------------------------------------- EXEC_1
11.檢視報告
EXECUTE :evol_out := DBMS_SPM.REPORT_EVOLVE_TASK( task_name=>:tk_name,
execution_name=>:exe_name );
SELECT :evol_out FROM DUAL;
GENERAL INFORMATION SECTION
--------------------------------------------------------------------------
Task Information:
---------------------------------------------
Task Name : TASK_11
Task Owner : SYS
Execution Name : EXEC_1
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 02/15/2019 17:49:32
Finished : 02/15/2019 17:49:35
Last Updated : 02/15/2019 17:49:35
Global Time Limit : 2147483646
Per-Plan Time Limit : UNUSED
Number of Errors : 0
---------------------------------------------------------------------------
SUMMARY SECTION
---------------------------------------------------------------------------
Number of plans processed : 1
Number of findings : 1
Number of recommendations : 1
Number of errors : 0
---------------------------------------------------------------------------
DETAILS SECTION
---------------------------------------------------------------------------
Object ID : 2
Test Plan Name : SQL_PLAN_0gwbcfvzskcu20135fd6c
Base Plan Name : SQL_PLAN_0gwbcfvzskcu242949306
SQL Handle : SQL_07f16c76ff893342
Parsing Schema : SH
Test Plan Creator : SH
SQL Text : SELECT /*q1_group_by*/ prod_name,
sum(quantity_sold)
FROM products p, sales s
WHERE p.prod_id=s.prod_id AND p.prod_category_id=203
GROUP BY prod_name
Execution Statistics:
-----------------------------
Base Plan Test Plan
---------------------------- ------------------------
Elapsed Time (s): .044336 .012649
CPU Time (s): .044003 .012445
Buffer Gets: 360 99
Optimizer Cost: 924 891
Disk Reads: 341 82
Direct Writes: 0 0
Rows Processed: 4 2
Executions: 5 9
FINDINGS SECTION
---------------------------------------------------------------------------
Findings (1):
-----------------------------
1. The plan was verified in 2.18 seconds. It passed the benefit criterion
because its verified performance was 2.01 times better than that of the
baseline plan.
Recommendation:
-----------------------------
Consider accepting the plan. Execute
dbms_spm.accept_sql_plan_baseline(task_name => 'TASK_11', object_id => 2,
task_owner => 'SYS');
EXPLAIN PLANS SECTION
---------------------------------------------------------------------------
Baseline Plan
-----------------------------
Plan Id : 1
Plan Hash Value : 1117033222
---------------------------------------------------------------------------
| Id| Operation | Name | Rows | Bytes |Cost | Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 861 | 924 | 00:00:12|
| 1 | HASH GROUP BY | | 21 | 861 | 924 | 00:00:12|
| *2| HASH JOIN | |267996|10987836 | 742 | 00:00:09|
| *3| TABLE ACCESS FULL | PRODUCTS | 21 | 714 | 2 | 00:00:01|
| 4 | PARTITION RANGE ALL | |918843| 6431901 | 662 | 00:00:08|
| 5 | TABLE ACCESS FULL | SALES |918843| 6431901 | 662 | 00:00:08|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("P"."PROD_ID"="S"."PROD_ID")
* 3 - filter("P"."PROD_CATEGORY_ID"=203)
Test Plan
-----------------------------
Plan Id : 2
Plan Hash Value : 20315500
---------------------------------------------------------------------------
|Id| Operation | Name | Rows | Bytes | Cost| Time |
---------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 21| 861| 891|00:00:11|
| 1| SORT GROUP BY NOSORT| | 21| 861| 891|00:00:11|
| 2| NESTED LOOPS | |267996|10987836| 891|00:00:11|
|*3| INDEX RANGE SCAN |IND_PROD_CAT_NAME | 21| 714| 1|00:00:01|
|*4| INDEX RANGE SCAN |IND_SALES_PROD_QTY| 12762| 89334| 42|00:00:01|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 3 - access("P"."PROD_CATEGORY_ID"=203)
* 4 - access("P"."PROD_ID"="S"."PROD_ID")
報告顯示使用兩個索引的執行計劃比原始執行計劃效能更好
12.實現evolve任務所給出的建議
SQL>EXECUTE :cnt := DBMS_SPM.IMPLEMENT_EVOLVE_TASK( task_name=>:tk_name,execution_name=>:exe_name ); PL/SQL procedure successfully completed.
13.查詢資料字典來確保新的執行計劃已經是接受狀態
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED
2 FROM DBA_SQL_PLAN_BASELINES
3 WHERE SQL_HANDLE IN ('SQL_07f16c76ff893342')
4 ORDER BY SQL_HANDLE, ACCEPTED;
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------------- -------------------- ------------------------------ -------------------- --- ---
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu242949306 AUTO-CAPTURE YES YES
y */ prod_name, sum(
quantity_sold)
FROM products p, sal
es s
WHERE p.prod_id = s.
prod_id
AND p.prod_category_
id =203
GROUP BY prod_name
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------------- -------------------- ------------------------------ -------------------- --- ---
SQL_07f16c76ff893342 SELECT /* q1_group_b SQL_PLAN_0gwbcfvzskcu2ae9b4305 AUTO-CAPTURE YES YES
y */ prod_name, sum(
quantity_sold)
FROM products p, sal
es s
WHERE p.prod_id = s.
prod_id
AND p.prod_category_
id =203
GROUP BY prod_name
14.執行清除操作
SQL> VARIABLE cnt NUMBER
SQL> EXEC :cnt := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_07f16c76ff893342');
PL/SQL procedure successfully completed.
SQL> DELETE FROM SQLLOG$;
13 rows deleted.
SQL> commit;
Commit complete.
SQL> DROP INDEX IND_SALES_PROD_QTY_SOLD;
Index dropped.
SQL> DROP INDEX IND_PROD_CAT_NAME;
Index dropped.
刪除SQL Plan Baselines
可以從SQL Plan Baselines中刪除一些或所有執行計劃。
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
2 ORIGIN, ENABLED, ACCEPTED
3 FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%q3_group_by%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN ENA ACC
-------------------- -------------------- ------------------------------ -------------------- --- ---
SQL_50c02f29322b0d02 SELECT SQL_HANDLE, S SQL_PLAN_51h1g54t2q38276fe3bd1 AUTO-CAPTURE YES YES
QL_TEXT, PLAN_NAME,
ORIGIN, ENABLED, ACC
EPTED
FROM DBA_SQL_PLAN_BA
SELINES WHERE SQL_TE
XT LIKE '%q3_group_b
y%'
SQL_6d39c79190585ca9 SELECT /* q3_group_b SQL_PLAN_6uff7k685hr5942949306 AUTO-CAPTURE YES YES
y */ prod_name, sum(
quantity_sold)
FROM products p, sal
es s
WHERE p.prod_id = s.
prod_id
AND p.prod_category_
id =205
GROUP BY prod_name
SQL_6d39c79190585ca9 SELECT /* q3_group_b SQL_PLAN_6uff7k685hr59ae9b4305 AUTO-CAPTURE YES NO
y */ prod_name, sum(
quantity_sold)
FROM products p, sal
es s
WHERE p.prod_id = s.
prod_id
AND p.prod_category_
id =205
GROUP BY prod_name
SQL> DECLARE
2 v_dropped_plans number;
3 BEGIN
4 v_dropped_plans := DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => 'SQL_6d39c79190585ca9');
5 DBMS_OUTPUT.PUT_LINE('dropped ' || v_dropped_plans || ' plans');
6 END;
7 /
PL/SQL procedure successfully completed.
SQL> SELECT SQL_HANDLE, SQL_TEXT, PLAN_NAME,
2 ORIGIN, ENABLED, ACCEPTED
3 FROM DBA_SQL_PLAN_BASELINES WHERE SQL_HANDLE='SQL_6d39c79190585ca9';
no rows selected
管理SQL Management Base
SQL Management Base是資料字典的一部分，它儲存在SYSAUX表空間中。它儲存語句日誌,執行計劃歷史記錄，SQL執行計劃基線與SQL Profiles。使用dbms_spm.configure過程可以對SMB進行選項設定與維護SQL Plan Baselines。dba_sql_management_config檢視可以用來檢視SMB的當前配置資訊。下面介紹parameter_name列可以設定的引數列表:
space_budget_percent:SQL Management Base可以使用的SYSAUX表空間的最大百分比。預設值是10%。允許的範圍是1%到50%。
plan_retention_weeks:在清除之前沒有被使用的執行計劃需要保留多少周，預設值是53。
auto_capture_parsing_schema_name:它是(% LIKE a OR % LIKE b ...) AND (%NOT LIKE c AND % NOT LIKE d ...)形式的列表，它代表瞭解析方案名過濾。
auto_capture_module:它是(% LIKE a OR % LIKE b ...) AND (%NOT LIKE c AND % NOT LIKE d ...)形式的列表，它代表了模組過濾。
auto_capture_action:它是(% LIKE a OR % LIKE b ...) AND (%NOT LIKE c AND % NOT LIKE d ...)形式的列表，它代表了操作過濾。
auto_capture_sql_text:它是(% LIKE a OR % LIKE b ...) AND (%NOT LIKE c AND % NOT LIKE d ...)形式的列表，它代表了SQL文字過濾。
修改SMB空間使用限制
一個由SMB所呼叫的每週執行一次的後臺程式將會檢測空間使用情況。當超過定義限制時，後臺程式將會寫一個告警資訊到alert日誌檔案中。資料庫會每週生成一個告警資訊直到SMB空間限制被增加為止，SYSAUX表空間被增加為止或者透過清除SQL Management物件(sql plan baselines或sql profiles)來減少SMB所使用的空間為止。
1.檢視當前SMB所用空間的限制大小,從查詢結果可以看到當前大小是SYSAUX表空間大小的10%
SQL> col parameter_name for a30 SQL> col %_LIMIT for a20 SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE AS "%_LIMIT", 2 ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES 3 WHERE TABLESPACE_NAME = 'SYSAUX' ) AS SYSAUX_SIZE_IN_MB, 4 PARAMETER_VALUE/100 * 5 ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES 6 WHERE TABLESPACE_NAME = 'SYSAUX' ) AS "CURRENT_LIMIT_IN_MB" 7 FROM DBA_SQL_MANAGEMENT_CONFIG 8 WHERE PARAMETER_NAME = 'SPACE_BUDGET_PERCENT'; PARAMETER_NAME %_LIMIT SYSAUX_SIZE_IN_MB CURRENT_LIMIT_IN_MB ------------------------------ -------------------- ----------------- ------------------- SPACE_BUDGET_PERCENT 10 1260 126
2.將SMB所用空間限制大小修改為SYSAUX表空間大小的30%
SQL> EXECUTE DBMS_SPM.CONFIGURE('space_budget_percent',30);
PL/SQL procedure successfully completed.
3.確認SMB所有空間限制大小是否成功被修改為SYSAUX表空間大小的30%
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE AS "%_LIMIT", 2 ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES 3 WHERE TABLESPACE_NAME = 'SYSAUX' ) AS SYSAUX_SIZE_IN_MB, 4 PARAMETER_VALUE/100 * 5 ( SELECT sum(bytes/1024/1024) FROM DBA_DATA_FILES 6 WHERE TABLESPACE_NAME = 'SYSAUX' ) AS "CURRENT_LIMIT_IN_MB" 7 FROM DBA_SQL_MANAGEMENT_CONFIG 8 WHERE PARAMETER_NAME = 'SPACE_BUDGET_PERCENT'; PARAMETER_NAME %_LIMIT SYSAUX_SIZE_IN_MB CURRENT_LIMIT_IN_MB ------------------------------ -------------------- ----------------- ------------------- SPACE_BUDGET_PERCENT 30 1260 378
修改SMB中的Plan Retention Policy
每週排程清除任務來管理由SQL Plan Management所使用的空間。這個任務是一個在維護視窗內自動執行的任務。資料庫會自動清除超過Plan Retention期限而沒有被使用的執行計劃，它是執行計劃儲存在SMB中的last_executed欄位來標識的。缺生活上的執行計劃保留週期是53周。這個週期可以設定的範圍是5到523周。
1.檢視當前執行計劃保留週期
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE 2 FROM DBA_SQL_MANAGEMENT_CONFIG 3 WHERE PARAMETER_NAME = 'PLAN_RETENTION_WEEKS'; PARAMETER_NAME PARAMETER_ ------------------------------ ---------- PLAN_RETENTION_WEEKS 53
2.修改執行計劃保留週期為105周
SQL> EXECUTE DBMS_SPM.CONFIGURE('plan_retention_weeks',105);
PL/SQL procedure successfully completed.
3.確保執行計劃保留週期是否成功被修改為105周
SQL> SELECT PARAMETER_NAME, PARAMETER_VALUE 2 FROM DBA_SQL_MANAGEMENT_CONFIG 3 WHERE PARAMETER_NAME = 'PLAN_RETENTION_WEEKS'; PARAMETER_NAME PARAMETER_ ------------------------------ ---------- PLAN_RETENTION_WEEKS 105
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-2636130/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Manually Evolve and Accept SQL PLAN BASELINES in Oracle 12cSQLOracle
- sql plan baselines（一）SQL
- SQL Plan Baselines 實驗01SQL
- Oracle OCP IZ0-053 Q478(SQL Plan Baselines)OracleSQL
- Oracle OCP 1Z0 053 Q246(SQL Plan Baselines)OracleSQL
- oracle 11g sql plan baseline(2)調整baselines屬性OracleSQL
- Oracle SQL Plan Baseline 學習OracleSQL
- 【SQL_PLAN】Oracle 透過檢視sql_plan 格式化執行計劃SQLOracle
- 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 SQL調優之'PLAN_TABLE' is old versionOracleSQL
- Oracle 11g SQL效能的新特性（三）- SQL Plan ManagementOracleSQL
- SQL Plan Management(SPM)SQL
- Oracle 12c 新SQL提示（hint）OracleSQL
- oracle execution planOracle
- SQL Plan Management介紹SQL
- (轉):學習Oracle動態效能表-(6)-V$SQL,V$SQL_PLANOracleSQL
- oracle 11g sql plan baseline(1)基本使用OracleSQL
- Oracle EXPLAIN PLAN用法OracleAI
- Oracle SYSTEM_PLANOracle
- Oracle simple resource planOracle
- Oracle Performance Tune PlanOracleORM
- 【DBA】Oracle 11g 針對SQL效能的新特性（三）- SQL Plan ManagementOracleSQL
- sql_plan_baselineSQL
- SQL PLAN Management的測試SQL
- WRH$_SQL_PLAN 被鎖SQL
- 11g-sql plan managementSQL
- sql plan baseline（二）SQL
- oracle11g中SQL最佳化（SQL TUNING）新特性之SQL Plan Management(SPM)OracleSQL
- Oracle -- 深入體會PLAN_TABLE、PLAN_TABLE$Oracle
- Oracle --- PLAN_TABLE$和PLAN_TABLE區別Oracle
- PL/SQL Developer連線到Oracle 12cSQLDeveloperOracle
- Oracle SQL執行計劃歷史資訊表DBA_HIST_SQL_PLAN的使用OracleSQL
- zt_oracle11g sql baseline與sql execution plan執行計劃OracleSQL
- oracle explain plan for的用法OracleAI