透過SPM手動新增執行計劃到baseLine

透過SPM方法手動將新的執行計劃新增到baseLine裡面，這相當就是把
並非系統算出來的“最優”的執行計劃放在最佳化器裡，在往後的查詢中，最佳化器
自動呼叫該執行計劃。以下是演示過程：

---建立測試表：

SQL> create table t3 (id int);

Table created.

---插入測試資料：
SQL> insert into t3 select level from dual connect by level<=100000;

100000 rows created.

---執行查詢語句：

SQL> select count(*) from t3 where id=1;

  COUNT(*)

----------

         1

Execution Plan

----------------------------------------------------------

Plan hash value: 463314188

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |    13 |    44   (3)| 00:00:01 |

|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |

|*  2 |   TABLE ACCESS FULL| T3   |     2 |    26 |    44   (3)| 00:00:01 |

---------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("ID"=1)

Note

-----

   - dynamic sampling used for this statement (level=2)

---檢視share pool資訊：

SQL> col sql_text for a50

SQL> select sql_id,sql_text from v$sql where sql_text like 'select count(*) from t3 where id=1';

SQL_ID        SQL_TEXT

------------- --------------------------------------------------

82w9knc509xx4 select count(*) from t3 where id=1

---給測試表建立索引：

SQL>  create index idx_t3 on t3(id);

Index created.

SQL> select count(*) from t3 where id=1;

Execution Plan

----------------------------------------------------------

Plan hash value: 2808915302

----------------------------------------------------------------------------

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |        |     1 |    13 |     1   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |        |     1 |    13 |            |          |

|*  2 |   INDEX RANGE SCAN| IDX_T3 |     1 |    13 |     1   (0)| 00:00:01 |

----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---檢視baseline中的資訊：

SQL> 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 count(*) from t3 where id=1%';

no rows selected

---手工裝載執行計劃再次檢視baseline及演進資訊：

SQL> DECLARE

  2   my_plans PLS_INTEGER;

  3  BEGIN

  4   my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( sql_id => '82w9knc509xx4');

  5  END;

  6  /

PL/SQL procedure successfully completed.

---再次檢視baseline中的資訊：

SQL> 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 count(*) from t3 where id=1';

SQL_HANDLE           SQL_TEXT        PLAN_NAME       ORIGIN

-------------------- --------------- --------------- --------------

VERSION                                                          CREATED

---------------------------------------------------------------- ----------

LAST_MODIF LAST_EXECU LAST_VERIF ENA ACC FIX

---------- ---------- ---------- --- --- ---

SQL_f83b4cd9c6676181 select count(*) SQL_PLAN_ghfucv MANUAL-LOAD

                      from t3 where  736fsc1f140b502

                     id=1

11.2.0.4.0                                                       17-OCT-16

                                                                 12.30.54.0

                                                                 00000 PM

17-OCT-16                        YES YES NO

12.30.54.0

00000 PM

#新增成功。