参见昨天Roger的文章,exchange outline可以同样实现这种需求
参考JL的文章：
Stored Outlines in Oracle 8
Stored Outlines in Oracle 9
测试环境：rhel 5.8 oracle 11.2.0.3 64bit
[root@db-2-15 ~]# su - oracle -bash-3.2$ sqlplus 'liu/liu' SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 14 10:28:51 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> drop table liu; Table dropped. SQL> create table liu (id int); Table created. SQL> begin 2 for 3 i in 1..1000000 loop 4 insert into liu values (i); 5 commit; 6 end loop; 7 end; 8 / PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> create index idx_test on liu (id); Index created. SQL> commit; Commit complete. SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'LIU',TABNAME=>'LIU', CASCADE=>TRUE); PL/SQL procedure successfully completed.
SQL> EXPLAIN PLAN FOR SELECT * FROM LIU WHERE ID =7000;
Explained.
SQL> select plan_table_output from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1128569081
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_TEST | 1 | 5 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - access("ID"=7000)
13 rows selected.
SQL> EXPLAIN PLAN FOR SELECT /*+full(a) */ * FROM LIU a WHERE ID =7000;
Explained.
SQL> select plan_table_output from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3882242470
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 459 (4)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| LIU | 1 | 5 | 459 (4)| 00:00:06 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - filter("ID"=7000)
13 rows selected.
创建两个outline(nohint,withhint) -Category TEST
SQL> CREATE OR REPLACE OUTLINE nohint for category test ON SELECT * FROM LIU a WHERE ID =7000; Outline created. SQL> create or replace outline withhint for category test on SELECT /*+full(a) */ * FROM LIU a WHERE ID =7000; Outline created. NAME CATEGORY USED SQL_TEXT ------------------------------ ------------------------------ ------------------ -------------------------------------------------------------------------------- NOHINT TEST UNUSED SELECT * FROM LIU a WHERE ID =7000 WITHHINT TEST UNUSED SELECT /*+full(a) */ * FROM LIU a WHERE ID =7000 SQL> alter session set use_stored_outlines = TEST; Session altered.
进入outln Schema 的outln相关表, 调换两个存储概要的实际内容.
SQL> update outln.ol$ ol1
set hintcount = (
select count(*)
from outln.ol$hints ol2
where ol2.ol_name = ol1.ol_name
)
where
ol1.ol_name in ('NOHINT','WITHHINT') 2 3 4 5 6 7 8
9 ;
2 rows updated.
SQL> commit;
Commit complete.
SQL> UPDATE OUTLN.OL$HINTS
SET OL_NAME=DECODE(OL_NAME, 'NOHINT', 'WITHHINT','WITHHINT','NOHINT')
WHERE OL_NAME IN ('NOHINT','WITHHINT'); 2 3
12 rows updated.
SQL> commit;
Commit complete.
SQL> conn liu/liu
Connected.
SQL> alter session set use_stored_outlines = TEST;
Session altered.
SQL> alter outline NOHINT disable;
alter outline NOHINT enable;
alter outline WITHHINT disable;
alter outline WITHHINT enable;
Outline altered.
SQL>
Outline altered.
SQL>
Outline altered.
SQL>
Outline altered.
SQL> EXPLAIN PLAN FOR SELECT /*+full(a) */ * FROM LIU a WHERE ID =7000;
Explained.
SQL> select plan_table_output from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1128569081
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_TEST | 1 | 5 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - access("ID"=7000)
Note
-----
- outline "WITHHINT" used for this statement
17 rows selected.
SQL> EXPLAIN PLAN FOR SELECT * FROM LIU a WHERE ID =7000;
Explained.
SQL> select plan_table_output from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3882242470
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 459 (4)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| LIU | 1 | 5 | 459 (4)| 00:00:06 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - filter("ID"=7000)
Note
-----
- outline "NOHINT" used for this statement
17 rows selected.
此时已经发生了交换
同理如果没有使用绑定变量同样可以交换,我们假设两个不同的值 这里使用Enkitec提供的脚本
SQL> CREATE OR REPLACE OUTLINE nohint1 for category test ON SELECT * FROM LIU a WHERE ID =6000;
Outline created.
SQL> CREATE OR REPLACE OUTLINE withhint1 for category test ON SELECT /*+full(a) */ * FROM LIU a WHERE ID =500;
Outline created.
SQL> alter session set use_stored_outlines = TEST;
SQL> @exchange_ol
Enter value for from_name: NOHINT1
old 6: fromname := '&from_name';
new 6: fromname := 'NOHINT1';
Enter value for to_name: WITHHINT1
old 7: toname := '&to_name';
new 7: toname := 'WITHHINT1';
PL/SQL procedure successfully completed.
Commit complete.
SQL>
SQL>
SQL>
SQL> EXPLAIN PLAN FOR SELECT /*+full(a) */ * FROM LIU a WHERE ID =500;
Explained.
SQL> select plan_table_output from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1128569081
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_TEST | 1 | 5 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - access("ID"=500)
Note
-----
- outline "WITHHINT1" used for this statement
17 rows selected.
SQL> EXPLAIN PLAN FOR SELECT * FROM LIU a WHERE ID =6000;
Explained.
SQL> select plan_table_output from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3882242470
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 459 (4)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| LIU | 1 | 5 | 459 (4)| 00:00:06 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 - filter("ID"=6000)
Note
-----
- outline "NOHINT1" used for this statement
17 rows selected.
此时同样发生了交换