Spm的作用及应用场合：
1、 Spm的主要作用：通过管理sql执行计划， 提高和稳定系统性能。
2、应用场合：数据库升级；不间断的系统数据改变；业务系统新模块的开发。
自动捕获sql执行计划并演进
1）查询baseline信息：
SYS@ORA11GR2>select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines ;
no rows selected
2）查询参数optimizer_capture_sql_plan_baselines显示是否开启自动捕获sql_plan
修改参数开启自动捕获执行计划（自动捕获特指重复执行的sql语句，单次执行捕获不了）
SYS@ORA11GR2>show parameter optimizer_capture_sql_plan_baselines
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
更改参数：
SYS@ORA11GR2>alter system set optimizer_capture_sql_plan_baselines=true;
System altered.
SYS@ORA11GR2>show parameter optimizer_capture_sql_plan_baselines
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines Boolean TRUE
3）创建表：
HR@ORA11GR2>create table employees_bak as select distinct last_name from employees;
Table created.
HR@ORA11GR2>
查询sql模拟业务（一）
1. 第一次执行
HR@ORA11GR2>select * from employees_bak where last_name='Vargas';
LAST_NAME
-------------------------
Vargas
2.查询shared pool中的关于这条sql的信息：（以sys用户执行）
SYS@ORA11GR2>select sql_id,sql_text from v$sql where sql_text like 'select * from employees_bak where last_name=%';
SQL_ID SQL_TEXT
------------- --------------------------------------------------
99yvub515tr63 select * from employees_bak where last_name='Varga s'
3. 查询baseline信息
SYS@ORA11GR2>select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from employees_bak where last_name=%';
no rows selected
查询sql模拟业务（二）
1.第二次执行：
SYS@ORA11GR2>conn hr/hr
Connected.
HR@ORA11GR2>select * from employees_bak where last_name='Vargas';
LAST_NAME
-------------------------
Vargas
HR@ORA11GR2>conn / as sysdba
Connected.
2.查询shared pool中的关于这条sql的信息：（以sys用户执行）
SYS@ORA11GR2>select sql_id,sql_text from v$sql where sql_text like 'select * from employees_bak where last_name=%';
SQL_ID SQL_TEXT
------------- -----------------------------------------------------
99yvub515tr63 select * from employees_bak where last_name='Vargas'
99yvub515tr63 select * from employees_bak where last_name='Vargas'
同样的sql，其sql_sid相同；
3.查询baseline中的信息
SYS@ORA11GR2>col sql_handle for a20
SYS@ORA11GR2>col sql_text for a15
SYS@ORA11GR2>col plan_name for a15
SYS@ORA11GR2>col created for a10
SYS@ORA11GR2>col last_modified for a10
SYS@ORA11GR2>col last_executed for a10
SYS@ORA11GR2>col last_verified for a10
SYS@ORA11GR2>col version for a10
SYS@ORA11GR2>set pagesize 1000
SYS@ORA11GR2>select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from employees_bak where last_name=%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN VERSION CREATED LAST_MODIF LAST_EXECU LAST_VERIF ENA ACC FIX
-------------------- ----------------------------------------------------- --------------- -------------- ---------- ----------
SQL_1c32abf260291c48 select * from employees_bak where last_name='Vargas' SQL_PLAN_1scpby AUTO-CAPTURE 11.2.0.4.0 13-OCT-16 13-OCT-16 13-OCT-16 YES YES NO
9h2k728c6c83284 04.26.02.0 04.26.02.0 04.26.02.0
00000 PM 00000 PM 00000 PM
4.验证查看到的baseline里执行计划
HR@ORA11GR2>set autot trace
SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled
SP2-0611: Error enabling STATISTICS report
HR@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>grant PLUSTRACE to hr;
grant PLUSTRACE to hr
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
解决：
SYS@ORA11GR2>@$ORACLE_HOME/sqlplus/admin/plustrce.sql
SYS@ORA11GR2>
SYS@ORA11GR2>drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SYS@ORA11GR2>create role plustrace;
Role created.
SYS@ORA11GR2>grant select on v_$sesstat to plustrace;
Grant succeeded.
SYS@ORA11GR2>grant select on v_$statname to plustrace;
Grant succeeded.
SYS@ORA11GR2>grant select on v_$mystat to plustrace;
Grant succeeded.
SYS@ORA11GR2>grant plustrace to dba with admin option;
Grant succeeded.
SYS@ORA11GR2>set echo off
再授权给hr用户：
SYS@ORA11GR2>grant plustrace to hr;
Grant succeeded.
SYS@ORA11GR2>conn hr/hr
Connected.
HR@ORA11GR2>set autot trace
HR@ORA11GR2>select * from employees_bak where last_name='Vargas';
Execution Plan
----------------------------------------------------------
Plan hash value: 3604099949
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:0
1 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES_BAK | 1 | 14 | 3 (0)| 00:00:0
1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_NAME"='Vargas')
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_1scpby9h2k728c6c83284" used for this statement
Statistics
----------------------------------------------------------
39 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
531 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
5.对employees_bak表进行优化，创建索引并重新执行sql
HR@ORA11GR2>set autot off;
HR@ORA11GR2>desc employees_bak
Name Null? Type
----------------------------------------- -------- ----------------------------
LAST_NAME NOT NULL VARCHAR2(25)
HR@ORA11GR2>create index idx_employees_bak on employees_bak(last_name);
Index created.
HR@ORA11GR2>select * from employees_bak where last_name='Vargas';
LAST_NAME
-------------------------
Vargas
6.查询shared pool及baseline中信息
HR@ORA11GR2>conn / as sysdba
Connected.
——Shared pool中信息：
SYS@ORA11GR2>select sql_id,sql_text from v$sql where sql_text like 'select * from employees_bak where last_name=%';
SQL_ID SQL_TEXT
--------------------------------------------------------------------------------
99yvub515tr63 select * from employees_bak where last_name='Vargas'
——Baseline信息：
SYS@ORA11GR2>col sql_handle for a20
SYS@ORA11GR2>col sql_text for a15
SYS@ORA11GR2>col plan_name for a15
SYS@ORA11GR2>col created for a10
SYS@ORA11GR2>col last_modified for a10
SYS@ORA11GR2>col last_executed for a10
SYS@ORA11GR2>col last_verified for a10
SYS@ORA11GR2>col version for a10
SYS@ORA11GR2>set pagesize 1000
SYS@ORA11GR2>select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from employees_bak where last_name=%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN VERSION
-------------------- --------------- --------------- -------------- ----------
CREATED LAST_MODIF LAST_EXECU LAST_VERIF ENA ACC FIX
---------- ---------- ---------- ---------- --- --- ---
SQL_1c32abf260291c48 select * from e SQL_PLAN_1scpby AUTO-CAPTURE 11.2.0.4.0
mployees_bak wh 9h2k728c6c83284
ere last_name='
Vargas'
13-OCT-16 13-OCT-16 13-OCT-16 YES YES NO
04.26.02.0 04.26.02.0 04.26.02.0
00000 PM 00000 PM 00000 PM
SQL_1c32abf260291c48 select * from e SQL_PLAN_1scpby AUTO-CAPTURE 11.2.0.4.0
mployees_bak wh 9h2k728d8e34edf
ere last_name='
Vargas'
13-OCT-16 13-OCT-16 YES NO NO
06.53.12.0 06.53.12.0
00000 PM 00000 PM
查看新的执行计划SQL_PLAN_1scpby9h2k728d8e34edf并没有被演进，即还没有被放到baseline里供以后复用。
7.演进新的执行计划
SYS@ORA11GR2>set serveroutput on
SYS@ORA11GR2>set long 10000
SYS@ORA11GR2>declare
2 report clob;
3 begin
4 report := dbms_spm.evolve_sql_plan_baseline(
5 sql_handle => 'SQL_1c32abf260291c48');
6 dbms_output.put_line(report);
7 end;
8 /
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------
Inputs:
SQL_HANDLE = SQL_1c32abf260291c48
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan:
SQL_PLAN_1scpby9h2k728d8e34edf
------------------------------------
Plan was
verified: Time used .02 seconds.
Plan passed performance criterion: 3 times
better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status:
COMPLETE COMPLETE
Rows Processed: 1
1
Elapsed Time(ms): .022 .013 1.69
CPU Time(ms): 0 0
Buffer Gets:
3 1 3
Physical Read Requests: 0
0
Physical Write Requests: 0 0
Physical Read
Bytes: 0 0
Physical Write Bytes:
0 0
Executions: 1
1
-----------------------------------------------------------------------------
Report
Summary
------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1
PL/SQL procedure successfully completed.
8.再次查看baseline里的执行计划信息
SYS@ORA11GR2>select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from employees_bak where last_name=%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN VERSION
-------------------- --------------- --------------- -------------- ----------
CREATED LAST_MODIF LAST_EXECU LAST_VERIF ENA ACC FIX
---------- ---------- ---------- ---------- --- --- ---
SQL_1c32abf260291c48 select * from e SQL_PLAN_1scpby AUTO-CAPTURE 11.2.0.4.0
mployees_bak wh 9h2k728c6c83284
ere last_name='
Vargas'
13-OCT-16 13-OCT-16 13-OCT-16 YES YES NO
04.26.02.0 04.26.02.0 04.26.02.0
00000 PM 00000 PM 00000 PM
SQL_1c32abf260291c48 select * from e SQL_PLAN_1scpby AUTO-CAPTURE 11.2.0.4.0
mployees_bak wh 9h2k728d8e34edf
ere last_name='
Vargas'
13-OCT-16 13-OCT-16 13-OCT-16 YES YES NO
06.53.12.0 07.05.24.0 07.05.24.0
00000 PM 00000 PM 00000 PM
查看执行计划SQL_PLAN_1scpby9h2k728d8e34edf已经被演进到baseline了
9.再通过执行sql语句验证实际执行的执行计划是否是baseline里最优的执行计划:
SYS@ORA11GR2>conn hr/hr
Connected.
HR@ORA11GR2>set autot trace
HR@ORA11GR2>select * from employees_bak where last_name='Vargas';
Execution Plan
----------------------------------------------------------
Plan hash value: 3689813232
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 1 (0)| 00:0
0:01 |
|* 1 | INDEX RANGE SCAN| IDX_EMPLOYEES_BAK | 1 | 14 | 1 (0)| 00:0
0:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("LAST_NAME"='Vargas')
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_1scpby9h2k728d8e34edf" used for this statement
Statistics
----------------------------------------------------------
29 recursive calls
15 db block gets
25 consistent gets
0 physical reads
2984 redo size
531 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
HR@ORA11GR2>set autot off;
验证sql语句执行计划已经走索引了，且对应了baseline里最优的执行计划SQL plan baseline "SQL_PLAN_1scpby9h2k728d8e34edf"