使用pkg or alter outline
先看看alter outline
alter outline pubilc|private XX rubuild(重新建立)
alter outline pubilc|private XX rename to NEW_NAME
alter outline pubilc|private XX CHANGE CATEGORY TO xxx 改变类别
alter outline pubilc|private XX enable 启用
alter outline pubilc|private XX disable 启用
REBUILD
Specify REBUILD to regenerate the execution plan for outline using current conditions.
RENAME TO Clause
Use the RENAME TO clause to specify an outline name to replace outline.
CHANGE CATEGORY TO Clause
Use the CHANGE CATEGORY TO clause to specify the name of the category into which the outline will be moved.
ENABLE | DISABLE
Use this clause to selectively enable or disable this outline. Outlines are enabled by default. The DISABLE keyword lets you disable one outline without
affecting the use of other outlines.
SQL> select name,category ,used,sql_text from user_outlines;
NAME CATEGORY USED SQL_TEXT
---------- ---------- ------ ----------------------------------------
SYS_OUTLIN T1TEST UNUSED select * from t1 where a>9000
E_09092414
244437502
NAME CATEGORY USED SQL_TEXT
---------- ---------- ------ ----------------------------------------
MYOUTLINE DEFAULT UNUSED select * from t1 where a>9000
O1 DEFAULT USED select * from t1 where c1=1
SYS_OUTLIN XHTR USED select * from t1 where a=1
E_09092415
225564012
NAME CATEGORY USED SQL_TEXT
---------- ---------- ------ ----------------------------------------
MYOT DEFAULT USED select * from t2 where a=2
PUBILC_MY_ DEFAULT USED select * from t3 where a=2
T3
SQL> alter outline SYS_OUTLINE_09092415225564012 rename to MYOT2;
大纲已变更。 改名字
MYOT2 XHTR USED select * from t1 where a=1
SQL> alter outline myot change category to xhtr; 改变category
大纲已变更。
MYOT XHTR USED select * from t2 where a=2
使用PKG
CLEAR_USED Procedure
Clears the outline 'used' flag
CREATE_OUTLINE Procedure
Generates outlines from the shared cursor identified by hash value and child number
DROP_BY_CAT Procedure
Drops outlines that belong to a specified category
DROP_UNUSED Procedure
Drops outlines that have never been applied in the compilation of a SQL statement
EXACT_TEXT_SIGNATURES Procedure
Updates outline signatures to those that compute based on exact text matching
UPDATE_BY_CAT Procedure
Changes the category of outlines in one category to a new category
UPDATE_SIGNATURES Procedure
Updates outline signatures to the current version's signature
CLEAR_USED 清除指定 outline
SQL> execute DBMS_OUTLN.CLEAR_USED('MYOUTLINE');
PL/SQL 过程已成功完成。
DROP_BY_CAT Procedure 删除特定 category 的outline
SQL> execute DBMS_OUTLN.drop_by_cat('XHTR');
PL/SQL 过程已成功完成。
UPDATE_BY_CAT Procedure(将一个类别里所有outline给另一个类别)
This procedure changes the category of all outlines in one category to a new category.
SQL> execute DBMS_OUTLN.update_by_cat('MYOUTLINE','DEFAULT');
PL/SQL 过程已成功完成。
SQL> execute DBMS_OUTLN.drop_unused; 删除所有状态为unused(dba(all,user)_outlines中可查)的outline
PL/SQL 过程已成功完成。
CREATE_OUTLINE Procedure
This procedure generates an outline from the shared cursor identified by hash value and child number.
Syntax
DBMS_OUTLN.CREATE_OUTLINE (
hash_value IN NUMBER,
child_number IN NUMBER,
category IN VARCHAR2 DEFAULT 'DEFAULT');
根据shared pool里共享游标来建立outline(stored_outline)
SQL> select distinct sid from v$mystat;
SID
----------
135
SQL> create table test (a int,b int);
表已创建。
SQL> declare
2 begin
3 for i in 1..10000 loop
4 insert into test values(i,i+1);
5 end loop;
6 commit;
7 end;
8 /
PL/SQL 过程已成功完成。
SQL> select * from test where a=1;~~~~~~~~这时候没INDEX 是FTS
A B
---------- ----------
1 2
SQL> conn / as sysdba
已连接。
SQL> select prev_hash_value,sql_hash_value from v$session where sid=135;
PREV_HASH_VALUE SQL_HASH_VALUE
--------------- --------------
1310077156
SQL> col sql_text format a30
SQL> col outline_category format a30
SQL> select SQL_TEXT,child_number,OUTLINE_CATEGORY from v$sql where hash_value=1310
077156;
SQL_TEXT CHILD_NUMBER OUTLINE_CATEGORY
------------------------------ ------------ ------------------------------
select * from test where a=1 0
SQL> execute dbms_outln.create_outline(1310077156,0);
PL/SQL 过程已成功完成。
SQL> select sql_text from dba_outlines;
SQL_TEXT
----------------------------------------
select * from test where a=1~~~~~~~~~~~~~~~~~~~~~~有了这个outline 属于default
select * from xh.t4
select * from t1 where a>9000
DELETE FROM PLAN_TABLE WHERE STATEMENT_I
D=:1
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS
_XPLAN.DISPLAY('PLAN_TABLE', :1))
select /*+ opt_param('parallel_execution
_enabled',
SQL_TEXT
----------------------------------------
SELECT /*+ opt_param('parallel_execution
_enabled', 'false') */
select * from t1 where c1=1
select * from t3 where a=2
已选择9行。
SQL> conn xh/a123
已连接。
SQL> create index test_ind on test(a);
索引已创建。
SQL> set autotrace trace exp
SQL> select * from test where a=1;
执行计划
----------------------------------------------------------
Plan hash value: 2216933833
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST |~~~~~~~~~~~使用index
|* 2 | INDEX RANGE SCAN | TEST_IND |
------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"=1)
Note
-----
- rule based optimizer used (consider using cbo)
SQL> alter system set use_stored_outlines=true;~~~~~用category为default的outline
系统已更改。
SQL> select * from test where a=1;
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 26 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=1)
Note
-----
- outline "SYS_OUTLINE_09092517292451515" used for this statement*******使用了(FTS)
SQL> select SQL_TEXT,child_number,OUTLINE_CATEGORY from v$sql where hash_value=1310
077156;
SQL_TEXT CHILD_NUMBER OUTLINE_CATEGORY
------------------------------ ------------ ------------------------------
select * from test where a=1 0
select * from test where a=1 1 DEFAULT~~~~~产生一个子游标(outline 当HINT 加语句中 所以不能共享 child_number 0 的子游标) 使用的是outline计划
另外对于 outline
可以
UPDATE OUTLN.OL$HINTS 来交换outline
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12020513/viewspace-615564/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12020513/viewspace-615564/