【PLAN STABILITY】 STORED-OUTLINE

renjixinchina發表於2014-07-18

 

stored outline用來儲存標準的sql執行計劃,無論環境或統計資訊的變化，都將不影響sql的效能。 11g以後stored outline被SQL plan management 代理,oracle建議11g以後將stored outline複製到SQL Plan Baselines

1.       建立outline

需要的許可權 

CREATE ANY OUTLINE 
EXECUTE_CATALOG_ROLE

建立outline的兩種方式 

CREATE OUTLINE emp_dept FOR CATEGORY scott_outlines
ON SELECT e.empno, e.ename, d.dname FROM emp e;
--v$sql 
BEGIN
  DBMS_OUTLN.create_outline(
    hash_value    => SQL hash_value ,
    child_number  => 0,
    category      => 'SCOTT_OUTLINES');
END;
/

2.       查詢outline

Outline儲存於outln.OL$;outln.OL$HINTS; outln.OL$NODES;

也可以使用 dba_outlines檢視查詢

3.       使用outline 

ALTER SESSION SET query_rewrite_enabled=TRUE;
ALTER SESSION SET use_stored_outlines=SCOTT_OUTLINES;
use_stored_outlines設定為true 使用DEFAULT目錄

查詢outline 是否被使用 

SELECT name, category, used FROM user_outlines;

4.       刪除outline 

BEGIN
  DBMS_OUTLN.drop_by_cat (cat => 'SCOTT_OUTLINES');
END;
/

5.       自動建立outline

啟動該選型oracle 將自動對執行的sql建立outline

ALTER SYSTEM SET create_stored_outlines=TRUE| category_name ;

ALTER SESSION SET create_stored_outlines=TRUE| category_name ;

6.       調整outline的執行計劃

How to Edit a Stored Outline to Use the Plan from Another Stored Outline (Doc ID 730062.1)

7.       Outline 注意事項

Outline是SQL文字全匹配, 不支援FORCE_MATCHING,對變數繫結的sql 必須要保證變數值一致

8.       示例

SQL> conn / as sysdba

Connected.

SQL> grant connect,resource to test;

 

Grant succeeded.

 

SQL> grant create any outline,alter any outline to test;

 

Grant succeeded.

 

SQL> alter user outln identified by outln account unlock;

 

User altered.

 

SQL> conn test/oracle

Connected.

SQL> create table t as select * from all_objects;

 

Table created.

 

SQL> set autotrace traceonly explain;

SQL> select * from t where OBJECT_ID=888;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |    27 |  3456 |   439   (2)| 00:00:06 |

|*  1 |  TABLE ACCESS FULL| T    |    27 |  3456 |   439   (2)| 00:00:06 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("OBJECT_ID"=888)

 

Note

-----

   - dynamic sampling used for this statement

 

SQL> set autotrace off

SQL> create outline t_outln1 for category CATEGORY_T on select * from t where OBJECT_ID=888;

 

Outline created.

 

SQL> create index i_t on t(object_id);

 

Index created.

 

SQL> set autotrace traceonly explain;

SQL> select * from t where OBJECT_ID=258;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2928007915

 

--------------------------------------------------------------------------------

----

 

| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time

   |

 

--------------------------------------------------------------------------------

----

 

|   0 | SELECT STATEMENT            |      |     1 |   128 |     2   (0)| 00:00:

01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |   128 |     2   (0)| 00:00:

01 |

 

|*  2 |   INDEX RANGE SCAN          | I_T  |     1 |       |     1   (0)| 00:00:

01 |

 

--------------------------------------------------------------------------------

----

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("OBJECT_ID"=258)

 

Note

-----

   - dynamic sampling used for this statement

 

SQL> select * from t where OBJECT_ID=888;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2928007915

 

--------------------------------------------------------------------------------

----

 

| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time

   |

 

--------------------------------------------------------------------------------

----

 

|   0 | SELECT STATEMENT            |      |     1 |   128 |     2   (0)| 00:00:

01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |   128 |     2   (0)| 00:00:

01 |

 

|*  2 |   INDEX RANGE SCAN          | I_T  |     1 |       |     1   (0)| 00:00:

01 |

 

--------------------------------------------------------------------------------

----

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("OBJECT_ID"=888)

 

Note

-----

   - dynamic sampling used for this statement

 

SQL> alter session set use_stored_outlines=CATEGORY_T;

 

Session altered.

 

SQL> select * from t where OBJECT_ID=888;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

 

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |  2027 |   253K|   439   (2)| 00:00:06 |

|*  1 |  TABLE ACCESS FULL| T    |  2027 |   253K|   439   (2)| 00:00:06 |

--------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("OBJECT_ID"=888)

 

Note

-----

   - outline "T_OUTLN1" used for this statement

 

SQL> select * from t where OBJECT_ID=258;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 2928007915

 

--------------------------------------------------------------------------------

----

 

| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time

   |

 

--------------------------------------------------------------------------------

----

 

|   0 | SELECT STATEMENT            |      |     1 |   128 |     2   (0)| 00:00:

01 |

 

|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |   128 |     2   (0)| 00:00:

01 |

 

|*  2 |   INDEX RANGE SCAN          | I_T  |     1 |       |     1   (0)| 00:00:

01 |

 

--------------------------------------------------------------------------------

----

 

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("OBJECT_ID"=258)

 

Note

-----

   - dynamic sampling used for this statement

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15747463/viewspace-1221950/，如需轉載，請註明出處，否則將追究法律責任。

相關文章