oracle实验记录 （管理outlines） 转

http://space.itpub.net/12020513/viewspace-615564

使用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/7656893/viewspace-695858/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7656893/viewspace-695858/