oracle 11gSPM手动绑定执行计划

自动计划捕获：
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 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'))

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中也没有那两条记录了。