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

发表评论

电子邮件地址不会被公开。