自动计划捕获：
SQL> sho parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
查询会话sid：
SQL> select userenv('SID') from dual;
USERENV('SID')
--------------
39
创建测试表：
SQL> sho user
USER is "SCOTT"
SQL> create table ming as select * from emp;
Table created.
查询：
SQL> set autot traceonly
SQL> select * from ming where empno=7934;
Execution Plan
----------------------------------------------------------
Plan hash value: 406648510
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MING | 1 | 87 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7934)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1022 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
另一个会话查询sql_id：
SQL> select PREV_SQL_ID,SID FROM v$session where sid=39;
PREV_SQL_ID SID
------------- ----------
4dmybh8upytx7 39
使用SQL_ID 从cursor cache中手工捕获执行计划：
SET SERVEROUTPUT ON
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '&sql_id');
DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
END;
/
old 4: l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '&sql_id');
new 4: l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '4dmybh8upytx7');
Plans Loaded: 1
PL/SQL procedure successfully completed.
使用DBA_SQL_PLAN_BASELINES视图查看SPM 信息：
col sql_handle for a35
col plan_name for a35
set lin 300
SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE 'select * from ming where empno=79%'
AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX
----------------------------------- ----------------------------------- -------------- --- --- ---
SYS_SQL_cbb79f0d76388c93 SQL_PLAN_crdwz1pv3j34m7c0756fd MANUAL-LOAD YES YES NO
再次查询，查看sql plan baseline是否被使用：
SQL> set autotrace on
SQL> select * from ming where empno=7934;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
Execution Plan
----------------------------------------------------------
Plan hash value: 406648510
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MING | 1 | 87 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7934)
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_crdwz1pv3j34m7c0756fd" used for this statement ---baseline被使用了
Statistics
----------------------------------------------------------
18 recursive calls
13 db block gets
15 consistent gets
0 physical reads
4828 redo size
1022 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
创建索引：
SQL> create index idx_ming_empno on ming(empno);
Index created.
这个时候最优的执行计划显然是走索引，但是因为sql plan baseline存在的缘故，最优的执行计划会直接被摒弃。验证一下可得现在还是在走全表扫描。
- SQL plan baseline "SQL_PLAN_crdwz1pv3j34m7c0756fd" used for this statement
接下来我把走索引的执行计划加入到baseline中去，估计这个时候优化器就会选择索引了，然后我再把全表扫描的执行计划标记为fixed，然后就会继续走全表扫了，下面验证：
查询此时的baseline：
col sql_handle for a35
col plan_name for a35
set lin 300
SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE 'select * from ming where empno=79%'
AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX
----------------------------------- ----------------------------------- -------------- --- --- ---
SYS_SQL_cbb79f0d76388c93 SQL_PLAN_crdwz1pv3j34m7c0756fd MANUAL-LOAD YES YES NO --acccept列是yes
SYS_SQL_cbb79f0d76388c93 SQL_PLAN_crdwz1pv3j34md2cc3f2f AUTO-CAPTURE YES NO NO --acccept列是no
将索引那条执行计划evolve，evolve就是把该执行计划变为accept。
SQL> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => '&sql_handle') FROM dual;
Enter value for sql_handle: SYS_SQL_cbb79f0d76388c93
old 1: SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => '&sql_handle') FROM dual
new 1: SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_cbb79f0d76388c93') FROM dual
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_CBB79F0D76388C93')
--------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_cbb79f0d76388c93
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
--------------------------------------------------------
Report Summary
------------------------------------------------
There were no SQL plan baselines that required processing.
再次查询：
SQL> col sql_handle for a35
SQL> col plan_name for a35
SQL> set lin 300
SQL> SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
2 FROM dba_sql_plan_baselines
3 WHERE sql_text LIKE 'select * from ming where empno=79%'
4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX
----------------------------------- ----------------------------------- -------------- --- --- ---
SYS_SQL_cbb79f0d76388c93 SQL_PLAN_crdwz1pv3j34m7c0756fd MANUAL-LOAD YES YES NO
SYS_SQL_cbb79f0d76388c93 SQL_PLAN_crdwz1pv3j34md2cc3f2f AUTO-CAPTURE YES YESNO
现在accept都是yes。
现在应该都是走索引了：
SQL> set autotrace on
SQL> select * from ming where empno=7934;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
Execution Plan
----------------------------------------------------------
Plan hash value: 4239086873
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MING | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_MING_EMPNO | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7934)
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_crdwz1pv3j34md2cc3f2f" used for this statement --现在是索引的baseline了。
Statistics
----------------------------------------------------------
19 recursive calls
13 db block gets
17 consistent gets
0 physical reads
4876 redo size
1026 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
接下来把全表扫变成fixed
查询baseline中的执行计划：
SQL> select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic'));
Enter value for sql_handle: SYS_SQL_cbb79f0d76388c93
old 1: select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic'))
new 1: select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => 'SYS_SQL_cbb79f0d76388c93', format => 'basic'))
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_cbb79f0d76388c93
SQL text: select * from ming where empno=7934
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_crdwz1pv3j34m7c0756fd Plan id: 2080855805
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 406648510
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| MING |
----------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_crdwz1pv3j34md2cc3f2f Plan id: 3536600879
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 4239086873
------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| MING |
| 2 | INDEX RANGE SCAN | IDX_MING_EMPNO |
------------------------------------------------------
34 rows selected.
baseline中有两个执行计划。
将全表的执行计划fixed：
SET SERVEROUTPUT ON
DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => '&sql_handle',
plan_name => '&plan_name',
attribute_name => 'fixed',
attribute_value => 'YES');
DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/
Enter value for sql_handle: SYS_SQL_cbb79f0d76388c93
old 5: sql_handle => '&sql_handle',
new 5: sql_handle => 'SYS_SQL_cbb79f0d76388c93',
Enter value for plan_name: SQL_PLAN_crdwz1pv3j34m7c0756fd
old 6: plan_name => '&plan_name',
new 6: plan_name => 'SQL_PLAN_crdwz1pv3j34m7c0756fd',
Plans Altered: 1
PL/SQL procedure successfully completed.
下面验证执行计划：
SQL> set autot traceonly
SQL> select * from ming where empno=7934;
Execution Plan
----------------------------------------------------------
Plan hash value: 406648510
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 261 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MING | 3 | 261 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("EMPNO"=7934)
Note
-----
- SQL plan baseline "SQL_PLAN_crdwz1pv3j34m7c0756fd" used for this statement
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
1022 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以发现又变成全表扫了。
fixed的执行计划可以有多个，优化器会在fixed执行计划中选择，而忽略其他执行计划，即使那些执行计划是best-cost plan。
删除某个SQL的baseline
SET SERVEROUTPUT ON
DECLARE
v_plans_dropped PLS_INTEGER;
BEGIN
v_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
sql_handle => 'SYS_SQL_cbb79f0d76388c93',
plan_name => NULL);
DBMS_OUTPUT.put_line(v_plans_dropped);
END;
/
SQL> select * from ming where empno=7934;
Execution Plan
----------------------------------------------------------
Plan hash value: 4239086873
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MING | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_MING_EMPNO | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7934)
Note
-----
- dynamic sampling used for this statement (level=2) --已经没有baseline的痕迹了，也根据cost使用的索引。
Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
1026 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
dba_sql_plan_baselines中也没有那两条记录了。
分享标题：oracle11gSPM手动绑定执行计划
当前链接：
http://chengdu.cdxwcx.cn/article/jsehps.html
