oracle實驗記錄 （管理outlines）

使用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/，如需轉載，請註明出處，否則將追究法律責任。