溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本篇內容介紹了“SQL執行計劃管理可以分為哪些基本任務”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
使用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_TEXT3.只對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為34q7g1h59b79n的語句所存儲在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 hh34: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 hh34: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_id3.從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_name14.執行清除操作
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_51h2g54t2q38276fe3bd1 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
“SQL執行計劃管理可以分為哪些基本任務”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
