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执行计划发生改变。

stored outline是Oracle 9i中提供的调整执行计划的方法。 这里有一个小技巧：如果在生产环境中，我们不能通过调整统计信息让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@]

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

转载于:http://blog.itpub.net/9907339/viewspace-1054901/