存储概要(stored outline) 是固定执行计划的一种传统技术。在Oracle 11g以前要固定执行计划只能使用outline,Oracle11g中引入了SPM来固定执行计划，创建stored outline需要有create any outline权限，为了管理还需要有对dba_outlines的查询权限。
创建stored outline
在会话级outline
ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE | FALSE |
如果将create_stored_outlines设置为true，当outlines的目录名设置为default时，Oracle会为该会话所执行的SQL语句创建outlines。
SQL> var x varchar2(20)
SQL> exec :x:='Kabab'
PL/SQL procedure successfully completed.
SQL> alter session set create_stored_outlines=true;
Session altered.
SQL> select * from t1 where t_meal=:x;
T_ID T_MEAL
---------- --------------------
79999 Kabab
SQL> select * from dba_outlines;
NAME OWNER CATEGORY USED TIMESTAMP VERSION
SQL_TEXT SIGNATURE COMPATIBLE ENABLED FORMAT
------------------------------ ------------------------------ ------------------------------ ------ -----------
---------------------------------------------------------------- --------------------------------------------------------------------------------
-------------------------------- ------------ -------- ------
SYS_OUTLINE_16060109390284501 JY DEFAULT UNUSED 2016/6/1 9: 10.2.0.5.0
select * from t1 where t_meal=:x 7DE4EC0CD8B1DA6C96B213F5D6DC349C COMPATIBLE ENABLED NORMAL
可以看到虽然创建了存储概要，但是并没有使用，因为USED的值为UNUSED
SQL> select * from dba_outline_hints where owner='JY' and name='SYS_OUTLINE_16060109390284501';
NAME OWNER NODE STAGE JOIN_POS HINT
------------------------------ ------------------------------ ---------- ---------- ----------
--------------------------------------------------------------------------------
SYS_OUTLINE_16060109390284501 JY 1 1 1 INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."T_MEAL"))
SYS_OUTLINE_16060109390284501 JY 1 1 0 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_16060109390284501 JY 1 1 0 ALL_ROWS
SYS_OUTLINE_16060109390284501 JY 1 1 0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
SYS_OUTLINE_16060109390284501 JY 1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
SQL> alter session set use_stored_outlines=true;
Session altered.
SQL> var x varchar2(20)
SQL> exec :x:='Pasta'
PL/SQL procedure successfully completed.
SQL> select * from t1 where t_meal=:x;
T_ID T_MEAL
---------- --------------------
80000 Pasta
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7runhd24kgqsf, child number 0
-------------------------------------
select * from t1 where t_meal=:x
Plan hash value: 141743202
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1_T_MEAL | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T_MEAL"=:X)
Note
-----
- outline "SYS_OUTLINE_16060109390284501" used for this statement
23 rows selected.
从Note部分的outline “SYS_OUTLINE_16060109390284501” used for this statement可以确认语句使用了创建的outline
SQL> select * from dba_outlines;
NAME OWNER CATEGORY USED TIMESTAMP VERSION
SQL_TEXT SIGNATURE COMPATIBLE ENABLED FORMAT
------------------------------ ------------------------------ ------------------------------ ------ -----------
---------------------------------------------------------------- --------------------------------------------------------------------------------
-------------------------------- ------------ -------- ------
SYS_OUTLINE_16060109390284501 JY DEFAULT USED 2016/6/1 9: 10.2.0.5.0
select * from t1 where t_meal=:x 7DE4EC0CD8B1DA6C96B213F5D6DC349C COMPATIBLE ENABLED NORMAL
从dba_outlines.USED也可以看到现在变为了USED
删除outline
先将outline设置为UNUSED
SQL> exec dbms_outln.clear_used(name =>'SYS_OUTLINE_16060109390284501');
PL/SQL procedure successfully completed
SQL> select * from dba_outlines;
NAME OWNER CATEGORY USED TIMESTAMP VERSION
SQL_TEXT SIGNATURE COMPATIBLE ENABLED FORMAT
------------------------------ ------------------------------ ------------------------------ ------ -----------
---------------------------------------------------------------- --------------------------------------------------------------------------------
-------------------------------- ------------ -------- ------
SYS_OUTLINE_16060109390284501 JY DEFAULT UNUSED 2016/6/1 9: 10.2.0.5.0
select * from t1 where t_meal=:x 7DE4EC0CD8B1DA6C96B213F5D6DC349C COMPATIBLE ENABLED NORMAL
再删除
SQL> exec dbms_outln.drop_unused;
PL/SQL procedure successfully completed
SQL> select * from dba_outlines;
NAME OWNER CATEGORY USED TIMESTAMP VERSION
SQL_TEXT SIGNATURE COMPATIBLE ENABLED FORMAT
------------------------------ ------------------------------ ------------------------------ ------ -----------
---------------------------------------------------------------- --------------------------------------------------------------------------------
-------------------------------- ------------ -------- ------
在系统级创建outline
ALTER SYSTEM SET CREATE_STORED_OUTLINES = TRUE | FALSE | [NOOVERRIDE]
当设置为true时，Oracle将会对系统中执行的每个查询自动创建与存储outline。这些outline存储在default目录中。如果特定的查询在default目录中有了一个outline后，那么这个outline将仍然会被保留并且不会创建新的outline。当设置为nooverride时，指定系统级的设置是滞将会覆盖会话级的设置。
为指定的SQL语句创建outline
下面的命令为指定的SQL语句创建outline
CREATE OUTLINE
要想使用所创建的outline，那么执行的SQL语句的SQL TEXT必须与创建命令所使用的SQL TEXT相同。
SQL> create outline outline_test for category outline_test on select * from t1 where t_meal=:x;
Outline created.
SQL> select * from dba_outlines;
NAME OWNER CATEGORY USED TIMESTAMP VERSION
SQL_TEXT SIGNATURE COMPATIBLE ENABLED FORMAT
------------------------------ ------------------------------ ------------------------------ ------ -----------
---------------------------------------------------------------- --------------------------------------------------------------------------------
-------------------------------- ------------ -------- ------
OUTLINE_TEST JY OUTLINE_TEST UNUSED 2016/6/1 11 10.2.0.5.0
select * from t1 where t_meal=:x 7DE4EC0CD8B1DA6C96B213F5D6DC349C COMPATIBLE ENABLED NORMAL
SQL> select * from dba_outline_hints where owner='JY' and name='OUTLINE_TEST';
NAME OWNER NODE STAGE JOIN_POS HINT
------------------------------ ------------------------------ ---------- ---------- ----------
--------------------------------------------------------------------------------
OUTLINE_TEST JY 1 1 1 FULL(@"SEL$1" "T1"@"SEL$1")
OUTLINE_TEST JY 1 1 0 OUTLINE_LEAF(@"SEL$1")
OUTLINE_TEST JY 1 1 0 ALL_ROWS
OUTLINE_TEST JY 1 1 0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
OUTLINE_TEST JY 1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
SQL> alter session set use_stored_outlines=OUTLINE_TEST;
Session altered.
SQL> var x varchar2(20)
SQL> exec :x:='Pasta'
PL/SQL procedure successfully completed.
SQL> select * from t1 where t_meal=:x;
T_ID T_MEAL
---------- --------------------
80000 Pasta
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7runhd24kgqsf, child number 0
-------------------------------------
select * from t1 where t_meal=:x
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 47 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 11 | 47 (5)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("T_MEAL"=:X)
Note
-----
- outline "OUTLINE_TEST" used for this statement
22 rows selected.
从Note部分，可以看到outline “OUTLINE_TEST” used for this statement，说明使用了创建的outline。
当不使用outline时，优化器选择使用索引范围扫描，而不是使用outline时的全表扫描
SQL> alter session set use_stored_outlines=false;
Session altered.
SQL> select * from t1 where t_meal=:x;
T_ID T_MEAL
---------- --------------------
80000 Pasta
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7runhd24kgqsf, child number 1
-------------------------------------
select * from t1 where t_meal=:x
Plan hash value: 141743202
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T1_T_MEAL | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T_MEAL"=:X)
19 rows selected.
outline的使用
outline会覆盖所有其它优化器设置。可以在会话级与系统级进行设置
ALTER SESSION SET USE_STORED_OUTLINES = TRUE | FALSE |
ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE | FALSE |
如果use_stored_outlines设置为true,那么将会使用default目录中的outline
如果use)stored_outlines设置为目录，那么将会使用这个目录中的outline,设置目录时不需要使用引号。
为了永久的启用outline可以通过创建一个数据库启动触发器来设置use_stored_outlines
create or replace trigger enable_outlines_trig
after startup on database
begin
execute immediate('alter system set use_stored_outlines=true');
end;
使用现有游标来创建stored outline
SQL> select * from t1 where t_meal=:x;
T_ID T_MEAL
---------- --------------------
80000 Pasta
SQL> select hash_value, child_number, sql_text from v$sql where sql_text like 'select * from t1 where t_meal=:x';
HASH_VALUE CHILD_NUMBER SQL_TEXT
---------- ------------ --------------------------------------------------------------------------------
2301090574 0 select * from t1 where t_meal=:x
SQL> exec dbms_outln.create_outline(2301090574,0);
PL/SQL procedure successfully completed
SQL> select * from dba_outlines;
NAME OWNER CATEGORY USED TIMESTAMP VERSION
SQL_TEXT SIGNATURE COMPATIBLE ENABLED FORMAT
------------------------------ ------------------------------ ------------------------------ ------ -----------
---------------------------------------------------------------- --------------------------------------------------------------------------------
-------------------------------- ------------ -------- ------
SYS_OUTLINE_16060112321977339 JY DEFAULT UNUSED 2016/6/1 12 10.2.0.5.0
select * from t1 where t_meal=:x 7DE4EC0CD8B1DA6C96B213F5D6DC349C COMPATIBLE ENABLED NORMAL
如果是10.2.0.4在创建outline之前需要先进行以下设置
SQL> alter session set create_stored_outlines = true; -- This step is to avoid Bug:5454975 fixed 10.2.0.4
使用现在游标来创建stored outline的例子如下:
SQL> var x number
SQL> exec :x:=1
PL/SQL procedure successfully completed.
SQL> select * from t1 where c1=:x;
C1
----------
1
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0m63029gwn10n, child number 0
-------------------------------------
select * from t1 where c1=:x
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"=:X)
18 rows selected.
SQL> select hash_value, child_number, sql_text,sql_id from v$sql where sql_text like 'select * from t1%';
HASH_VALUE CHILD_NUMBER SQL_TEXT SQL_ID
---------- ------------ -------------------------------------------------------------------------------- -------------
1607074836 0 select * from t1 where c1=:x 0m63029gwn10n
SQL> exec dbms_outln.create_outline(1607074836,0);
PL/SQL procedure successfully completed.
SQL> select name,owner,category,used from dba_outlines;
NAME OWNER CATEGORY USED
------------------------------ ------------------------------ ------------------------------ ------
SYS_OUTLINE_16060115345355101 JY DEFAULT UNUSED
SQL> select * from dba_outline_hints where owner='JY' and name='SYS_OUTLINE_16060115345355101';
NAME OWNER NODE STAGE JOIN_POS HINT
------------------------------ ------------------------------ ---------- ---------- ----------
--------------------------------------------------------------------------------
SYS_OUTLINE_16060115345355101 JY 1 1 1 INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))
SYS_OUTLINE_16060115345355101 JY 1 1 0 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_16060115345355101 JY 1 1 0 ALL_ROWS
SYS_OUTLINE_16060115345355101 JY 1 1 0 DB_VERSION('11.2.0.4')
SYS_OUTLINE_16060115345355101 JY 1 1 0 OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
SYS_OUTLINE_16060115345355101 JY 1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
6 rows selected.
SQL> alter session set use_stored_outlines=true;
Session altered.
SQL> select * from t1 where c1=:x;
C1
----------
1
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 0m63029gwn10n, child number 1
-------------------------------------
select * from t1 where c1=:x
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("C1"=:X)
Note
-----
- outline "SYS_OUTLINE_16060115345355101" used for this statement
22 rows selected.
使用另一个存储概要的执行计划来编辑存储概要
SQL> alter session set create_stored_outlines=true;
Session altered.
SQL> var n number
SQL> exec :n:=499990;
PL/SQL procedure successfully completed.
SQL> select count(*) from t1 where c1>:n;
COUNT(*)
----------
10
SQL> select count(*) from t1 where c1>1;
COUNT(*)
----------
499999
SQL> alter session set create_stored_outlines=false;
Session altered.
查询自动创建outline是否成功，从下面的查询结果可以看到创建了两个outline
SQL> select name,owner,category,used from dba_outlines; NAME OWNER CATEGORY USED ------------------------------ ------------------------------ ------------------------------ ------ SYS_OUTLINE_16060210103585707 JY DEFAULT UNUSED SYS_OUTLINE_16060210120211108 JY DEFAULT UNUSED
测试使用了outline的执行计划是否是使用了绑定变量的使用索引扫描，使用literal值的使用全表扫描
SQL> alter session set use_stored_outlines=true;
Session altered.
SQL> select count(*) from t1 where c1>:n;
COUNT(*)
----------
10
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID fxm7fcktguhvb, child number 1
-------------------------------------
select count(*) from t1 where c1>:n
Plan hash value: 1339937034
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| IDX_T1_C1 | 10 | 40 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1">:N)
Note
-----
- outline "SYS_OUTLINE_16060210103585707" used for this statement
23 rows selected.
可以看到使用了绑定变量的SQL使用outline “SYS_OUTLINE_16060210103585707”,执行计划为索引范围扫描
SQL> select count(*) from t1 where c1>1;
COUNT(*)
----------
499999
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID dt7wfh731mssj, child number 1
-------------------------------------
select count(*) from t1 where c1>1
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 205 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| T1 | 499K| 1953K| 205 (6)| 00:00:03 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C1">1)
Note
-----
- outline "SYS_OUTLINE_16060210120211108" used for this statement
23 rows selected.
可以看到使用了literal值的SQL使用outline “SYS_OUTLINE_16060210120211108” ,执行计划为全表扫描。
下面将两个outline进行重命名,SYS_OUTLINE_16060210103585707(索引范围扫描)修改为OL1,SYS_OUTLINE_16060210120211108(全表扫描)修改为OL2.
SQL> alter outline SYS_OUTLINE_16060210103585707 rename to OL1; Outline altered. SQL> alter outline SYS_OUTLINE_16060210120211108 rename to OL2; Outline altered. SQL> select name,owner,category,used from dba_outlines; NAME OWNER CATEGORY USED ------------------------------ ------------------------------ ------------------------------ ------ OL1 JY DEFAULT USED OL2 JY DEFAULT USED
再次重新执行两个SQL语句，看是否能使用重命名之后的outline
SQL> select count(*) from t1 where c1>:n;
COUNT(*)
----------
10
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID fxm7fcktguhvb, child number 1
-------------------------------------
select count(*) from t1 where c1>:n
Plan hash value: 1339937034
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| IDX_T1_C1 | 10 | 40 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1">:N)
Note
-----
- outline "OL1" used for this statement
23 rows selected.
使用绑定变量的语句使用outline(OL1)，执行计划为索引范围扫描
SQL> select count(*) from t1 where c1>1;
COUNT(*)
----------
499999
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID dt7wfh731mssj, child number 0
-------------------------------------
select count(*) from t1 where c1>1
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 205 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| T1 | 499K| 1953K| 205 (6)| 00:00:03 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C1">1)
Note
-----
- outline "OL2" used for this statement
23 rows selected.
使用literal值的语句使用outline(OL2)，执行计划为全表扫描
如果两个outline属于不同的目录，可以执行以下语句修改为相同目录
SQL> alter outline OL1 change category to DEFAULT; Outline altered. SQL> alter outline OL2 change category to DEFAULT; Outline altered.
使用outline:OL1,OL2来创建两个私有outline:OLF,OLT,这里用outline(OL2/OLT)的信息来更新outline(OL1,OLF)，最终的目的是当执行原来使用绑定变是的语句是使用的outline将变成OL2(全表扫描)
SQL> create private outline OLF from OL1;
Outline created.
SQL> create private outline OLT from OL2;
Outline created.
SQL> select count(*) from ol$ where ol_name='OLF';
COUNT(*)
----------
1
SQL> select count(*) from ol$ where ol_name='OLT';
COUNT(*)
----------
1
SQL> update ol$ set hintcount=(select hintcount from ol$ where ol_name='OLT') where ol_name='OLF';
1 row updated.
SQL> delete from ol$ where ol_name='OLT';
1 row deleted.
SQL> update ol$ set ol_name='OLT' where ol_name='OLF';
1 row updated.
SQL> commit;
Commit complete.
重新同步私有outlien(OLT)
SQL> execute dbms_outln_edit.refresh_private_outline('OLT');
PL/SQL procedure successfully completed.
测试私有outline，查看使用绑定变量的语句的执行计划是不是变为全表扫描。
SQL> alter session set use_private_outlines=true;
Session altered.
SQL> var n number
SQL> exec :n:=499990;
PL/SQL procedure successfully completed.
SQL> select count(*) from t1 where c1>:n;
COUNT(*)
----------
10
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID fxm7fcktguhvb, child number 1
-------------------------------------
select count(*) from t1 where c1>:n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 205 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| T1 | 10 | 40 | 205 (6)| 00:00:03 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C1">:N)
Note
-----
- outline "OLT" used for this statement
23 rows selected.
使用私有outline(OLT)来替换公有outline(OL1)
SQL> create or replace outline OL1 from private OLT; Outline created.
当outline(OL1)使用私有outline(OLT)替之后，OL1变为UNUSED
SQL> select name,owner,category,used from dba_outlines; NAME OWNER CATEGORY USED ------------------------------ ------------------------------ ------------------------------ ------ OL1 JY DEFAULT UNUSED OL2 JY DEFAULT USED
查询OL1所包含的执行计划确实就变成了OL2的全表扫描了。
SQL> select * from dba_outline_hints where owner='JY' and name='OL1';
NAME OWNER NODE STAGE JOIN_POS HINT
------------------------------ ------------------------------ ---------- ---------- ----------
--------------------------------------------------------------------------------
OL1 JY 1 1 0 OUTLINE_LEAF(@"SEL$1")
OL1 JY 1 1 0 ALL_ROWS
OL1 JY 1 1 0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
OL1 JY 1 1 0 IGNORE_OPTIM_EMBEDDED_HINTS
OL1 JY 1 1 1 FULL(@"SEL$1" "T1"@"SEL$1")
执行查询(select count(*) from t1 where c1>:n)来测试outline(OL1)的执行计划是否为全表扫描
SQL> var n number
SQL> exec :n:=499990;
PL/SQL procedure successfully completed.
SQL> select count(*) from t1 where c1>:n;
COUNT(*)
----------
10
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID fxm7fcktguhvb, child number 2
-------------------------------------
select count(*) from t1 where c1>:n
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 205 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| T1 | 10 | 40 | 205 (6)| 00:00:03 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C1">:N)
Note
-----
- outline "OL1" used for this statement
23 rows selected.
编辑outline(适用于10g与11g)
语句(select count(*) from t1 where c1>1)已经创建了outline(OL2)，其执行计划为全表扫描，通过编辑方式将其修改为索引范围扫描。
SQL> select count(*) from t1 where c1>1;
COUNT(*)
----------
499999
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID dt7wfh731mssj, child number 0
-------------------------------------
select count(*) from t1 where c1>1
Plan hash value: 3724264953
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 205 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| T1 | 499K| 1953K| 205 (6)| 00:00:03 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C1">1)
Note
-----
- outline "OL2" used for this statement
23 rows selected.
使用outline(OL2)来创建私有outline(JYOL)
SQL> create private outline JYOL from OL2;
Outline created.
SQL> select count(*) from ol$ where ol_name='JYOL';
COUNT(*)
----------
1
通过更新ol$hints表来更新私有outline(JYOL)的执行计划，将全表扫描更新为索引范围扫描
SQL> select ol_name,hint#,hint_text,category from ol$hints where ol_name='JYOL';
OL_NAME HINT# HINT_TEXT CATEGORY
------------------------------ ---------- -------------------------------------- ----------------
JYOL 1 OUTLINE_LEAF(@"SEL$1") DEFAULT
JYOL 2 ALL_ROWS DEFAULT
JYOL 3 OPTIMIZER_FEATURES_ENABLE('10.2.0.5') DEFAULT
JYOL 4 IGNORE_OPTIM_EMBEDDED_HINTS DEFAULT
JYOL 5 FULL(@"SEL$1" "T1"@"SEL$1") DEFAULT
SQL> update ol$hints set hint_text='INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))' where hint#=5 and ol_name='JYOL';
1 row updated.
SQL> select ol_name,hint#,hint_text,category from ol$hints where ol_name='JYOL';
OL_NAME HINT# HINT_TEXT CATEGORY
------------------------------ ---------- ------------------------------------------ -------------
JYOL 1 OUTLINE_LEAF(@"SEL$1") DEFAULT
JYOL 2 ALL_ROWS DEFAULT
JYOL 3 OPTIMIZER_FEATURES_ENABLE('10.2.0.5') DEFAULT
JYOL 4 IGNORE_OPTIM_EMBEDDED_HINTS DEFAULT
JYOL 5 INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1")) DEFAULT
SQL> commit;
Commit complete.
使用编辑后的执行计划重新同步私有outline(JYOL)
SQL> execute dbms_outln_edit.refresh_private_outline('JYOL');
PL/SQL procedure successfully completed.
测试将全表扫描修改为索引范围扫描后的私有outline能否使用
SQL> alter session set use_private_outlines=true;
Session altered.
SQL> select count(*) from t1 where c1>1;
COUNT(*)
----------
499999
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID dt7wfh731mssj, child number 1
-------------------------------------
select count(*) from t1 where c1>1
Plan hash value: 1339937034
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1128 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| IDX_T1_C1 | 499K| 1953K| 1128 (2)| 00:00:14 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1">1)
Note
-----
- outline "JYOL" used for this statement
23 rows selected.
使用将全表扫描修改为索引范围扫描的私有outline(JYOL)来更新公有outline(OL2)，让OL2的执行计划从全表扫描变为索引范围扫描
SQL> create or replace outline OL2 from private JYOL; Outline created.
测试outline(OL2)的执行计划是否变为了索引范围扫描
SQL> alter session set use_private_outlines=false;
Session altered.
SQL> alter session set use_stored_outlines=true;
Session altered.
SQL> select count(*) from t1 where c1>1;
COUNT(*)
----------
499999
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID dt7wfh731mssj, child number 0
-------------------------------------
select count(*) from t1 where c1>1
Plan hash value: 1339937034
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1128 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| IDX_T1_C1 | 499K| 1953K| 1128 (2)| 00:00:14 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1">1)
Note
-----
- outline "OL2" used for this statement
23 rows selected.
从结果可以看到，OL2的执行计划确实从原来的全表扫描变为了索引范围扫描，说明修改执行计划是成功的。
传输stored outline(从一个数据库传输到另一个数据库,适用于9i及以版本)
1.将outline(OL2)的目录从DEFAULT修改为JY
SQL> alter outline OL2 change category to JY; Outline altered.
2.使用exp工具导入stored outlines
使用带query参数的exp命令是为了只导为目录JY中的outline数据。可以一次传输多个stored outlines
windows平台的导出命令如下:
exp system/
unix平台的导出命令如下:
exp system/
OpenVMS平台的导出命令如下:
exp system/
这里测试平台是linux,导出命令如下:
[oracle@weblogic28 ~]$ exp jy/jy file=/home/oracle/myoutln.dmp log=/home/oracle/myoutln.log tables=\(outln.ol\$,outln.ol\$hints,outln.ol\$nodes\) query=\"where category=\'JY\'\" statistics=none Export: Release 10.2.0.5.0 - Production on Thu Jun 2 12:06:04 2016 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set About to export specified tables via Conventional Path ... Current user changed to OUTLN . . exporting table OL$ 1 rows exported . . exporting table OL$HINTS 5 rows exported . . exporting table OL$NODES 1 rows exported Export terminated successfully without warnings.
将导出的outline数据文件传输到目标数据库服务器上
[oracle@jyrac1 ~]$ scp oracle@10.138.130.28:/home/oracle/myoutln.dmp /home/oracle/ The authenticity of host '10.138.130.28 (10.138.130.28)' can't be established. RSA key fingerprint is 25:39:c3:5e:d8:b7:fc:5f:54:b5:a1:ed:17:a1:a0:90. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '10.138.130.28' (RSA) to the list of known hosts. oracle@10.138.130.28's password: myoutln.dmp 100% 16KB 16.0KB/s 00:00
使用imp工具将导出的outline数据导入到目标数据库
[oracle@jyrac1 ~]$ imp jy/jy file=/home/oracle/myoutln.dmp full=y ignore=y Import: Release 11.2.0.4.0 - Production on Thu Jun 2 11:35:53 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing o Export file created by EXPORT:V10.02.01 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses ZHS16GBK character set (possible charset conversion) export client uses ZHS16GBK character set (possible charset conversion) . importing JY's objects into JY . importing OUTLN's objects into OUTLN . . importing table "OL$" 1 rows imported . . importing table "OL$HINTS" 5 rows imported . . importing table "OL$NODES" 1 rows imported Import terminated successfully without warnings.
启用stored outline
SQL> alter session set use_stored_outlines=JY;
Session altered.
SQL> select count(*) from t1 where c1>1;
COUNT(*)
----------
499999
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID dt7wfh731mssj, child number 1
-------------------------------------
select count(*) from t1 where c1>1
Plan hash value: 1970818898
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 12 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | INDEX RANGE SCAN| IDX_T1 | 4999 | 19996 | 12 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1">1)
Note
-----
- outline "OL2" used for this statement
23 rows selected.
对于10g及以版本也可以使用datapump来导出与导入outline数据
导出:
expdp outln/password directory=DUMP_DIR dumpfile=testoutln.dmp tables=OL$,OL$HINTS,OL$NODES query=\”where category=\’JY\’\”
或
expdp jy/jy directory=DUMP_DIR dumpfile=testoutln.dmp query=\”where category=\’JY\’\” schemas=outln
[oracle@jyrac1 ~]$ expdp jy/jy directory=DUMP_DIR dumpfile=testoutln.dmp query=\"where category=\'JY\'\" schemas=outln Export: Release 11.2.0.4.0 - Production on Thu Jun 2 11:53:41 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Starting "JY"."SYS_EXPORT_SCHEMA_01": jy/******** directory=DUMP_DIR dumpfile=testoutln.dmp query="where category='JY'" schemas=outln Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 256 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION . . exported "OUTLN"."OL$HINTS" 13.49 KB 5 rows . . exported "OUTLN"."OL$" 10.32 KB 1 rows . . exported "OUTLN"."OL$NODES" 7.820 KB 1 rows Master table "JY"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for JY.SYS_EXPORT_SCHEMA_01 is: /home/oracle/testoutln.dmp Job "JY"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jun 2 12:05:08 2016 elapsed 0 00:10:38
导入:
impdp outln/password directory=DUMP_DIR dumpfile=testoutln.dmp tables=OL$,OL$HINTS,OL$NODES table_exists_action=truncate
或
impdp jy/jy directory=DUMP_DIR dumpfile=testoutln.dmp remap_schema=outln:outln table_exists_action=truncate
[oracle@jy1 ~]$ impdp jy/jy directory=DUMP_DIR dumpfile=testoutln.dmp remap_schema=outln:outln table_exists_action=truncate Import: Release 11.2.0.4.0 - Production on Thu Jun 2 12:09:08 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Master table "JY"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "JY"."SYS_IMPORT_FULL_01": jy/******** directory=DUMP_DIR dumpfile=testoutln.dmp remap_schema=outln:outln table_exists_action=truncate Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"OUTLN" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Table "OUTLN"."OL$" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate Table "OUTLN"."OL$NODES" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate Table "OUTLN"."OL$HINTS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "OUTLN"."OL$HINTS" 13.49 KB 5 rows . . imported "OUTLN"."OL$" 10.32 KB 1 rows . . imported "OUTLN"."OL$NODES" 7.820 KB 1 rows Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE ORA-31684: Object type PROCEDURE:"OUTLN"."ORA$GRANT_SYS_SELECT" already exists Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION Job "JY"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Thu Jun 2 12:15:44 2016 elapsed 0 00:06:14