自動計劃捕獲：
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
創(chuàng)建測試表：
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中手工捕獲執(zhí)行計劃：
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
創(chuàng)建索引：
SQL> create index idx_ming_empno on ming(empno);
Index created.
這個時候最優(yōu)的執(zhí)行計劃顯然是走索引，但是因為sql plan baseline存在的緣故，最優(yōu)的執(zhí)行計劃會直接被摒棄。驗證一下可得現(xiàn)在還是在走全表掃描。
- SQL plan baseline "SQL_PLAN_crdwz1pv3j34m7c0756fd" used for this statement
接下來我把走索引的執(zhí)行計劃加入到baseline中去，估計這個時候優(yōu)化器就會選擇索引了，然后我再把全表掃描的執(zhí)行計劃標記為fixed，然后就會繼續(xù)走全表掃了，下面驗證：
查詢此時的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
將索引那條執(zhí)行計劃evolve，evolve就是把該執(zhí)行計劃變?yōu)閍ccept。
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
現(xiàn)在accept都是yes。
現(xiàn)在應(yīng)該都是走索引了：
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 --現(xiàn)在是索引的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中的執(zhí)行計劃：
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中有兩個執(zhí)行計劃。
將全表的執(zhí)行計劃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.
下面驗證執(zhí)行計劃：
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
可以發(fā)現(xiàn)又變成全表掃了。
fixed的執(zhí)行計劃可以有多個，優(yōu)化器會在fixed執(zhí)行計劃中選擇，而忽略其他執(zhí)行計劃，即使那些執(zhí)行計劃是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) --已經(jīng)沒有baseline的痕跡了，也根據(jù)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中也沒有那兩條記錄了。
網(wǎng)頁題目：oracle11gSPM手動綁定執(zhí)行計劃
本文地址：
http://weahome.cn/article/jsehps.html
重慶分公司
重慶分公司
