保持执行计划的稳定性之存储纲要
在oracle中，除了让优化器根据上下文选择最佳执行计划外，还有多种方式可以加强执行计划的稳定性，包括优化器提示、存储纲要及SQL计划管理。优化器提示通常用于人为地影响之下计划。
存储纲要（Stored Outlines）是oracle 11G之前一直不接触源代码固定执行计划的唯一方式，其本质和优化器提示相同，在内部将用户接受的执行计划通过一个提示集合保存在数据字典中，在SQL语句解析时，优化器检查是否有可用的存储纲要，如果有则直接使用，而不生产新的执行计划。存储纲要及其提示分别存储在OL$、OL$HINTS和OL$NODES中，而用户应该通过*_OUTLINES及*_OUTLINE_HINTS数据字典访问。
首先基于对统计信息的调整得到较优的执行计划，然后根据此创建存储纲要。
将SQL进行优化，生成所希望的执行计划：
SQL> grant create any outline to scott;
Grant succeeded.
SQL> grant execute_catalog_role to scott;
Grant succeeded.
SQL> conn scott/tiger
Connected.
SQL> explain plan for
select e.empno,e.ename,d.dname from emp e, dept d where e.deptno=d.deptno;
2
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 364 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 182 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
18 rows selected.
SQL> exec dbms_stats.set_table_stats(ownname => 'SCOTT',tabname => 'EMP',numrows => 1000000);
PL/SQL procedure successfully completed.
SQL> explain plan for
select e.empno,e.ename,d.dname from emp e, dept d where e.deptno=d.deptno;
2
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 24M| 17 (65)| 00:00:01 |
|* 1 | HASH JOIN | | 1000K| 24M| 17 (65)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 1000K| 12M| 11 (73)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
15 rows selected.
为优化后的SQL语句创建存储纲要：
SQL> CREATE OUTLINE emp_dept FOR CATEGORY scott_outlines ON select e.empno,e.ename,d.dname from emp e, dept d where e.deptno=d.deptno;
Outline created.
SQL> select name,category,sql_text from user_outlines where category='SCOTT_OUTLINES';
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CATEGORY
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
EMP_DEPT
SCOTT_OUTLINES
select e.empno,e.ename,d.dname from emp e, dept d where e.deptno=d.deptno
SQL> select e.empno,e.ename,d.dname from emp e, dept d where e.deptno=d.deptno;
EMPNO ENAME DNAME
---------- -------------------- ----------------------------
7369 SMITH RESEARCH
7499 ALLEN SALES
7521 WARD SALES
7566 JONES RESEARCH
7654 MARTIN SALES
7698 BLAKE SALES
7782 CLARK ACCOUNTING
7839 KING ACCOUNTING
7844 TURNER SALES
7900 JAMES SALES
7902 FORD RESEARCH
7934 MILLER ACCOUNTING
7876 ADAMS RESEARCH
7788 SCOTT RESEARCH
14 rows selected.
--确认存储纲要为SQL保存的执行计划是所希望得到
SQL> select node,stage,join_pos,hint from user_outline_hints where name = 'EMP_DEPT';
NODE STAGE JOIN_POS HINT
---------- ---------- ---------- --------------------------------------------------------------------------------
1 1 0 USE_HASH(@"SEL$1" "E"@"SEL$1")
1 1 0 LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
1 1 2 FULL(@"SEL$1" "E"@"SEL$1")
1 1 1 FULL(@"SEL$1" "D"@"SEL$1")
1 1 0 OUTLINE_LEAF(@"SEL$1")
1 1 0 ALL_ROWS
1 1 0 DB_VERSION('12.2.0.1')
1 1 0 OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
9 rows selected.
SQL> select name,category,used from user_outlines;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CATEGORY
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USED
------------
EMP_DEPT
SCOTT_OUTLINES
UNUSED
启用存储纲要和重写，删除统计信息，然后执行SQL，确认即使没有统计信息，执行计划仍然是所希望的到的。
SQL> alter session set query_rewrite_enabled=true;
Session altered.
SQL> alter session set use_stored_outlines=SCOTT_OUTLINES;
Session altered.
SQL> select e.empno,e.ename,d.dname from emp e, dept d where e.deptno=d.deptno;
EMPNO ENAME DNAME
---------- -------------------- ----------------------------
7369 SMITH RESEARCH
7499 ALLEN SALES
7521 WARD SALES
7566 JONES RESEARCH
7654 MARTIN SALES
7698 BLAKE SALES
7782 CLARK ACCOUNTING
7839 KING ACCOUNTING
7844 TURNER SALES
7900 JAMES SALES
7902 FORD RESEARCH
7934 MILLER ACCOUNTING
7876 ADAMS RESEARCH
7788 SCOTT RESEARCH
14 rows selected.
SQL> select name,category,used from user_outlines;
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CATEGORY
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
USED
------------
EMP_DEPT
SCOTT_OUTLINES
USED
SQL> exec dbms_stats.delete_table_stats(ownname => 'SCOTT',tabname => 'EMP');
PL/SQL procedure successfully completed.
SQL> explain plan for
select e.empno,e.ename,d.dname from emp e, dept d where e.deptno=d.deptno;
2
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 409 | 18814 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 409 | 18814 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 52 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 409 | 13497 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- outline "EMP_DEPT" used for this statement
19 rows selected.
禁用存储纲要，并执行SQL，确保执行计划又回到了不希望得到的非优化状态。
SQL> alter session set use_stored_outlines=false;
Session altered.
SQL> explain plan for
select e.empno,e.ename,d.dname from emp e, dept d where e.deptno=d.deptno; 2
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 844388907
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 644 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 14 | 644 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 52 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 462 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 462 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("E"."DEPTNO"="D"."DEPTNO")
filter("E"."DEPTNO"="D"."DEPTNO")
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
22 rows selected.
这样就可以确保emp和dept总是通过哈希连接进行关联了。除了直接create stored outline外，还可以通过dbms_outln.create_outline创建存储纲要。
