通过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

#添加成功。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31392094/viewspace-2129901/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31392094/viewspace-2129901/