Oracle SQL baseline

Oracle SQL baseline是Oracle用來取代outline的新功能。其原理和outline比較類似，都是將預先設定好的plan或者hint儲存在資料字典中，當SQL匹配的時候，採用預訂好的plan。

1. 通常用在第三方程式的SQL中，這樣的SQL Text通常不能被重寫，如果CBO不能自動生成理想的執行計劃，則需要DBA透過新增hint等方法人工干預。
2. 也常用來固定關鍵SQL的執行計劃。如應用從開發資料庫部署到產品資料庫，可以將outline或者baseline從測試庫輸出輸入到產品資料庫中。
3. 也用在資料庫升級的時候，防止因為升級帶來的optimzer或者其他引數變化導致升級後SQL執行計劃發生改變。

。 這裡有一個小技巧：如果在生產環境中，我們不能透過調整統計資訊讓SQL生成正確的執行計劃，這樣就沒有辦法生成正確的 outline，這時我們可以在SQL上新增hint，強制SQL走到正確的執行計劃上，然後生成outline，由於此時SQL text發生了變化導致signature變化，需要直接修改outline的字典表ol$hints，交換兩個hint的OL_NAME欄位，用此方法 來產生SQL的stored outline. 但這樣直接更新ol$的方法不被Oracle支援。

採用Oracle SQL baseline的DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE方法，可以將新增hint後的SQL在shared pool中的執行計劃賦予給要最佳化的SQL。原理有點類似outline。

如下SQL包括表emp和dept的關聯。採用的是MERGE JOIN。

SYS@ADGSTBY: SQL> show parameters baseline

NAME TYPE VALUE
———————————— ———–
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE

SCOTT@ADGSTBY: SQL>set autotrace on

SCOTT@ADGSTBY: SQL> select ename,dname from dept,emp where dept.deptno=emp.deptno;
14 rows selected.
Execution Plan
———————————————————-
Plan hash value: 2865896559
—————————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————-
| 0 | SELECT STATEMENT | | 14 | 252 | 4 (25)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 252 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 1 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 98 | 3 (34)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 98 | 2 (0)| 00:00:01 |
—————————————————————————————-
Predicate Information (identified by operation id):
—————————————————
4 - access(”DEPT”.”DEPTNO”=”DEPTNO”)
filter(”DEPT”.”DEPTNO”=”DEPTNO”)

假如我們想讓他執行HASH JOIN。首先，需要在shared pool中有一個 emp 和 dept hash join的執行計劃。

SCOTT@ADGSTBY: SQL> select /*+ use_hash(dept,emp)*/ ename,dname from dept,emp where dept.deptno=emp.deptno;
14 rows selected.

Execution Plan
———————————————————-
Plan hash value: 1093152308
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 14 | 252 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 252 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 44 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 |
—————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 - access(”DEPT”.”DEPTNO”=”DEPTNO”)

可以得到新增hint的SQL的SQL_ID和PLAN_HASH_VALUE。

SQL> select sql_id,plan_hash_value from v$sql where sql_text=
’select /*+ use_hash(dept,emp)*/ ename,dname from dept,emp where dept.deptno=emp.deptno’;
SQL_ID PLAN_HASH_VALUE
————- —————
2jsaagf7grtr4 1093152308

採用如下方法，

DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
sql_text IN CLOB,
fixed IN VARCHAR2 := ‘NO’,
enabled IN VARCHAR2 := ‘YES’)
RETURN PLS_INTEGER;

SQL> var nRet number
SQL> exec :nRet := dbms_spm.load_plans_from_cursor_cache(sql_id=>’2jsaagf7grtr4′,
plan_hashvalue=>’1093152308′,
sql_text=>to_clob(’select ename,dname from dept,emp where dept.deptno=emp.deptno’));
PL/SQL procedure successfully completed.

dbms_spm包下大多是function，需要多寫個變數。使用to_clob可以將第三方應用的SQL文字轉為CLOB。

再次執行SQL，發現SQL採用了HASH JOIN的baseline.

SCOTT@ADGSTBY: SQL> select ename,dname from dept,emp where dept.deptno=emp.deptno;
14 rows selected.
Execution Plan
———————————————————-
Plan hash value: 1093152308
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 14 | 252 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 252 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 44 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 98 | 2 (0)| 00:00:01 |
—————————————————————————
Predicate Information (identified by operation id):
—————————————————
1 - access(”DEPT”.”DEPTNO”=”DEPTNO”)
Note
—–
- SQL plan baseline “SQL_PLAN_fzq88m4p6n60f8447c07a” used for this statement

[@more@]