使用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
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： 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 -------------------------------------------
-------------------------------------------------------- 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 YES NO
现在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'))
-------------------------------------------------------------------------------- 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
