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