目錄

1. 自動捕獲
2. 從library cache 中加載
3. 從SQL 調優級加載

1、自動捕獲

當optimizer_capture_sql_plan_baselines參數設置為TRUE，查詢優化器自動存儲新SQL計劃的基線，默認是FALSE。

第一個SQL語句被執行只是把signature插入到log日志中，第二次相同的語句執行時，一個SQL執行基線僅包含創建當前執行計劃并標記被接受。第三次執行相同的語句時，基線的中執行計劃和當前的執行計劃做比較，如果不匹配，根據當前的查詢優化器做評估，為了保存這些信息，會把當前的執行計劃寫入sql基線中并標記不接受。

當先的執行計劃被存儲到SQL執行基線中，以下兩種情況很重要做區分

• 如果他是SQL執行基線中第一個執行計劃，執行計劃被存儲并接受，隨后，查詢優化器會重用它。
• 如果SQL語句執行基線中不是第一個執行計劃，他會存儲不被接受，最后結果，查詢優化器不被使用。

2、從library cache 中加載

為了手工加載SQL執行基線加載到數據字典中基于在libarary cache中游標存儲，這個dbms_spm包中的load_plans_from_cursor_cache是可用的。

可以把執行計劃中本來是全表掃描的，不更改任何的SQL語句，使它走索引的模式。

ALTER SESSION SET optimizer_capture_sql_plan_baselines = TRUE;
SELECT /*+ full(t) */ * FROM employees  where employee_id>110;

ALTER SESSION SET optimizer_capture_sql_plan_baselines = FALSE;
 

重新運行語句

SELECT /*+ full(t) */ * FROM employees  where employee_id>110;

查看執行計劃

select * from table(dbms_xplan.display_cursor(null,null));
SQL_ID  b2j5kmkx67r6t, child number 3
-------------------------------------
SELECT /*+ full(t) */ * FROM employees  where employee_id>110
 
Plan hash value: 603312277
 
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |    97 |  6693 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_EMP_ID_PK |    97 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EMPLOYEE_ID">110)
 
Note
-----
   - SQL plan baseline
SQL_PLAN_6bu51x6d0cb5215f09a65

通過執行計劃中NOTE信息獲取sql_handle

 SELECT sql_handle
FROM dba_sql_plan_baselines
WHERE plan_name ='SQL_PLAN_6bu51x6d0cb5215f09a65';

再使語句走INDEX的模式,手工構造HINTS模式

 select /*+index(employees emp_emp_id_pk)*/ * from  employees where employee_id>110;
 
 select * from table(dbms_xplan.display_cursor(null,null));
SQL_ID  0gd91j77ppkc7, child number 0
-------------------------------------
select /*+index(employees emp_emp_id_pk)*/ * from  employees where 
employee_id>110
 
Plan hash value: 603312277
 
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |    97 |  6693 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_EMP_ID_PK |    97 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EMPLOYEE_ID">110)
 

你替換使用在sql基線中的執行計劃,加載的執行計劃關聯索引的,移除全部掃描

declare
ret number(20);
begin
ret := dbms_spm.load_plans_from_cursor_cache(sql_handle => 'SQL_65e8a1e99a062ca2',
sql_id => '0gd91j77ppkc7',
plan_hash_value => '603312277');
end;

declare
ret number(20);
begin
ret :=dbms_spm.drop_sql_plan_baseline(sql_handle => 'SQL_65e8a1e99a062ca2',
plan_name => 'SQL_PLAN_6bu51x6d0cb52cf314e9e');
end;

再次執行SQL語句

SELECT /*+ full(t) */ * FROM employees  where employee_id>110; 

select * from table(dbms_xplan.display_cursor(null,null));

SQL_ID  b2j5kmkx67r6t, child number 3
-------------------------------------
SELECT /*+ full(t) */ * FROM employees  where employee_id>110
 
Plan hash value: 603312277
 
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |    97 |  6693 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMP_EMP_ID_PK |    97 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EMPLOYEE_ID">110)
 
Note
-----
   - SQL plan baseline 
SQL_PLAN_6bu51x6d0cb5215f09a65 

全表掃描的SQL語句已經替換為index的模式來處理,為了確認是否使用了SQL基線,你可以核對V$SQL中的SQL_PLAN_BASELINE字段

3、從SQL TUNING SET加載

ret := dbms_spm.load_plans_from_sqlset(sqlset_name => 'test_sqlset',
sqlset_owner => user);