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