oracle存储提纲（stored outline）

oracle存储提纲（stored outline）用来提供稳定的执行计划。从oracle 11g开始，逐渐被sql计划继续取代。下面是存储提纲的具体过程，结果在oracle 11g r2版本测试

--创建测试表和索引
create table oln_test as select * from dba_tables;
set autotrace on;

SQL> create index idex_oln on oln_test (TABLE_NAME);

SQL> select OWNER from oln_test where table_name = 'OLN_TEST';
----------------------------------------------------------
Plan hash value: 3038230087

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| OLN_TEST | 1 | 34 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDEX_OLN | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

SQL> select /*+FULL(oln_test)*/ OWNER from oln_test where table_name = 'OLN_TEST';

----------------------------------------------------------
Plan hash value: 1307524366

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| OLN_TEST | 1 | 34 | 13 (0)| 00:00:01 |
------------------------------------------------------------------------------
-- 生成outline
-- Create the OUTLINE for ORIGINALSQL
CREATE OR REPLACE OUTLINE oln_to ON
select OWNER from oln_test where table_name = 'OLN_TEST';

-- Create the OUTLINE for HINTSQL
CREATE OR REPLACE OUTLINE oln_hint ON
select /*+FULL(oln_test)*/ OWNER from oln_test where table_name = 'OLN_TEST';
-- 交换outline

方法1：直接更新DBA_OUTLINES表（oracle官方不推荐）
SQL> conn / as sysdba
UPDATE DBA_OUTLINES
SET NAME=DECODE(NAME,'OLN_HINT','OLN_TO','OLN_TO','OLN_HINT')
WHERE NAME IN ('OLN_TO','OLN_HINT');
commit;

--验证结果，已使用outline
SQL> select OWNER from oln_test where table_name = 'OLN_TEST';
----------------------------------------------
Plan hash value: 1307524366

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33 | 1122 | 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| OLN_TEST | 33 | 1122 | 13 (0)| 00:00:01 |
------------------------------------------------------------------------------
Note
-----
- outline "OLN_HINT" used for this statement

有时候需要刷新内存
alter system flush shared_pool;

-- 方法2 通过私有outline来替换（推荐）


SQL> create private outline MY_to from oln_to;

SQL> create private outline MY_hint from oln_hint;

--必须和上面的命令使用同一个session
conn / as sysdba
UPDATE OL$HINTS
SET OL_NAME=DECODE(OL_NAME,'MY_HINT','MY_TO','MY_TO','MY_HINT')
WHERE OL_NAME IN ('MY_TO','MY_HINT');
commit;

set linesize 250;
col HINT_TEXT format a100;
select OL_name,HINT_TEXT from ol$hints;
-- 刷新内存中的outline信息
execute dbms_outln_edit.refresh_private_outline('MY_TO');
execute dbms_outln_edit.refresh_private_outline('MY_HINT');
--创建或更新public outline
create or replace outline OLN_TO from private MY_TO ;

--测试outline使用
--alter system set use_stored_outlines=DEFAULT;
select OWNER from oln_test where table_name = 'OLN_TEST';

-- drop the temporary OUTLINE HINTSQL
DROP OUTLINE oln_hint;

exec dbms_outln.drop_by_cat(cat => 'DEFAULT');

---10g以上版本可以通过shared pool中的sql生产outline
select owner from oln_test where table_name = 'OLN_TEST';
select sql_id,hash_value, child_number, sql_text from v$sql where sql_text like 'select count(*) from oln_test%';

SQL> -- to workaround Bug 5454975 fixed 10.2.0.4
SQL> alter session set create_stored_outlines = true;

exec dbms_outln.create_outline('3653752035',0);

SQL> exec dbms_outln.create_outline(hash_value => 646164864,child_number =>0);

SELECT COUNT(*) FROM WJ.OLN_TEST

select count(*) from wj.oln_test;