手工捕获sql执行计划并查看演进情况
前期准备：Sys下执行操作删除baseline里以有的执行计划（根据需要多次执行）
--sys操作
declare
my_plans pls_integer;
begin
my_plans :=DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => 'SQL_1c32abf260291c48', plan_name => 'SQL_PLAN_1scpby9h2k728c6c83284')
;
end;
/
再删除employees_bak。
HR@ORA11GR2>drop table employees_bak purge;
Table dropped.
--验证查看balinese
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
--正式操作：
1.关闭自动捕获参数
HR@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>show parameter optimizer_capture_sql_plan_baselines
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean TRUE
SYS@ORA11GR2> alter system set optimizer_capture_sql_plan_baselines=false;
System altered.
建表：
HR@ORA11GR2> create table employees_bak as select distinct last_name from employees;
Table created.
2.执行sql模拟业务（HR用户)
HR@ORA11GR2>select * from employees_bak where last_name='Vargas';
LAST_NAME
-------------------------
Vargas
3.查看cursor cache中的执行计划信息(sys用户)
--sys用户操作
col SQL_TEXT for a55
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'
4.查看baseline中的信息
--sys用户操作
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
5.手工装载执行计划再次查看baseline
--sys用户操作
declare
my_plans pls_integer;
begin
my_plans := dbms_spm.load_plans_from_cursor_cache( sql_id => '99yvub515tr63');
end;
/
PL/SQL procedure successfully completed.
6.再次查看Baseline信息：
col sql_handle for a20
col sql_text for a15
col plan_name for a15
col created for a10
col last_modified for a10
col last_executed for a10
col last_verified for a10
col version for a10
set pagesize 1000
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 MANUAL-LOAD 11.2.0.4.0
mployees_bak wh 9h2k728c6c83284
ere last_name='
Vargas'
13-OCT-16 13-OCT-16 YES YES NO
08.38.38.0 08.38.38.0
00000 PM 00000 PM
查询得：手工load（manual-load模式）的执行计划直接已经被演进到baseline里了！
7.通过执行sql进行验证，以选用baseline里的执行计划SQL_PLAN_1scpby9h2k728c6c83284：
--hr用户执行：
set autot on
select * from employees_bak where last_name='Vargas';
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
----------------------------------------------------------
18 recursive calls
13 db block gets
26 consistent gets
0 physical reads
3072 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
8.对sql进行优化，创建索引并重新执行sql
--hr用户操作
set autot off
create index idx_employees_bak on employees_bak(last_name);
Index created.
--重新执行sql：
select * from employees_bak where last_name='Vargas';
LAST_NAME
-------------------------
Vargas
手工捕获模式下，对同一条SQL的优化后的查询，即优化后的sql的演进模式以变为自动捕获AUTO-CAPTURE，且仅需执行一次就被捕获进baseline。
9.查看Baseline信息：
--sys用户
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 MANUAL-LOAD 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
08.38.38.0 08.38.38.0 08.41.39.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
08.43.28.0 08.43.28.0
00000 PM 00000 PM
注意：可以看到手工load执行计划模式，当相同的sql执行再被执行，其执行计划以自动捕获的方式被放到baseline里，但是第二个执行计划还没有被演进，即其执行计划不能被复用，实际上是存放在sqlplan history而没有真正意义上放进baseline里。
10.验证查看执行的sql选择的执行计划：
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 | | 3 | 42 | 3 (0)| 00:00:0
1 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES_BAK | 3 | 42 | 3 (0)| 00:00:0
1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_NAME"='Vargas')
Note
-----
- SQL plan baseline "SQL_PLAN_1scpby9h2k728c6c83284" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 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
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到执行计划是去的第一个SQL plan baseline "SQL_PLAN_1scpby9h2k728c6c83284"
HR@ORA11GR2>set autot off;
11.演进同一sql的第二个执行计划，以使其执行计划能够复用:
--sys操作
set serveroutput on
set long 10000
declare
report clob;
begin
report := dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SQL_1c32abf260291c48');
dbms_output.put_line(report);
end;
/
-------------------------------------------------------------------------------
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): .02 .014 1.43
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.
12.再次查看baseline验证：
--sys:
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 MANUAL-LOAD 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
08.38.38.0 08.38.38.0 08.41.39.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
08.43.28.0 08.51.15.0 08.51.15.0
00000 PM 00000 PM 00000 PM
查看相同sql的第二次执行计划已经被演进到baseline里；
13.验证查看执行的sql选择的执行计划：
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
----------------------------------------------------------
18 recursive calls
13 db block gets
26 consistent gets
0 physical reads
3064 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
查看相同sql的执行计划走的是索引扫描，其执行计划为SQL plan baseline "SQL_PLAN_1scpby9h2k728d8e34edf"，即baseline里的同一sql的最优sql_plan.
【优化】SPM(下）手工捕获sql执行计划并查看演进情况
-
不一样的天空w
2016-10-16 11:34:18 -
Oracle
- 原创