ORACLE使用STORED OUTLINE固化執行計畫--私人和公有

利用儲存綱要可以固定sql執行計畫，儲存綱要有一系列與sql語句相關的optimizer hints構成。當儲存綱要可用是，oracle會自動根據這些hint產生對應sql語句的執行計畫。儲存綱要可以分為私人和公有，同時我們可以對儲存綱要劃分不同的類別（categories），並指定那一類儲存綱要發揮作用，這無疑會方便dba的管理工作。儲存綱要一旦儲存，就不會隨後續系統配置或者統計資訊的變化而變化。（oracle 11g雖然支援儲存綱要，但是oracle已經不建議使用儲存綱要，而是使用SPM).

公有和私人

oracle可以建立公有的儲存綱要，也可以建立私人的儲存綱要。具有的文法格式如下：

建立公有儲存綱要，相關資訊會存放在outln用中，建立私人綱要時，相關資訊會存放在目前使用者模式下，因此目前使用者需要具有

建立綱要需要具有CREATE ANY OUTLINE許可權
SQL> create public outline outline1 on select count(*) from t1 where object_id < 100;create public outline outline1 on select count(*) from t1 where object_id < 100                                                       *第 1 行出現錯誤:ORA-18005: 此操作需要 CREATE ANY OUTLINE 許可權SQL> create public outline outline1 on select count(*) from t1 where object_id < 100;大綱已建立。

要是儲存綱要發揮作用，需要在system或者session層級設定use_stored_outlines參數
SQL> set autotrace on explainSQL> select count(*) from t1 where object_id < 100;  COUNT(*)----------98執行計畫----------------------------------------------------------Plan hash value: 3724264953---------------------------------------------------------------------------| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |---------------------------------------------------------------------------|   0 | SELECT STATEMENT   |  |1 |    13 |   295   (1)| 00:00:04 ||   1 |  SORT AGGREGATE    |  |1 |    13 |       |  ||*  2 |   TABLE ACCESS FULL| T1   |    12 |   156 |   295   (1)| 00:00:04 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("OBJECT_ID"<100)Note-----   - dynamic sampling used for this statement (level=2)SQL> create index i1 on t1(object_id);索引已建立。SQL> select count(*) from t1 where object_id < 100;  COUNT(*)----------98執行計畫----------------------------------------------------------Plan hash value: 3900446664--------------------------------------------------------------------------| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  | |     1 |    13 |     2   (0)| 00:00:01 ||   1 |  SORT AGGREGATE   | |     1 |    13 |      |  ||*  2 |   INDEX RANGE SCAN| I1 |    98 |  1274 |     2   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OBJECT_ID"<100)Note-----   - dynamic sampling used for this statement (level=2)SQL> alter session set use_stored_outlines=true;會話已更改。SQL> select count(*) from t1 where object_id < 100;  COUNT(*)----------98執行計畫----------------------------------------------------------Plan hash value: 3724264953---------------------------------------------------------------------------| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |---------------------------------------------------------------------------|   0 | SELECT STATEMENT   |  |1 |    13 |   295   (1)| 00:00:04 ||   1 |  SORT AGGREGATE    |  |1 |    13 |       |  ||*  2 |   TABLE ACCESS FULL| T1   |  4411 | 57343 |   295   (1)| 00:00:04 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("OBJECT_ID"<100)Note-----   -- outline "OUTLINE1" used for this statement
在system層級設定use_stored_outlines為true後，
SQL> show userUSER 為 "EASY2"SQL> select count(*) from t1 where object_id < 100;  COUNT(*)----------98執行計畫----------------------------------------------------------Plan hash value: 3724264953---------------------------------------------------------------------------| Id  | Operation   | Name | Rows  | Bytes | Cost (%CPU)| Time  |---------------------------------------------------------------------------|   0 | SELECT STATEMENT   |  |1 |    13 |   295   (1)| 00:00:04 ||   1 |  SORT AGGREGATE    |  |1 |    13 |       |  ||*  2 |   TABLE ACCESS FULL| T1   |  4411 | 57343 |   295   (1)| 00:00:04 |---------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("OBJECT_ID"<100)Note-----   - outline "OUTLINE1" used for this statement

下面建立私人的儲存綱要，建立私人綱要前可以執行如下語句來在當前模式下建立相關對象
SQL> exec DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES;PL/SQL 過程已成功完成。SQL> select * from tab;TNAME       TABTYPECLUSTERID------------------------------ ------- ----------OL$       TABLEOL$HINTS       TABLEOL$NODES       TABLET1       TABLESQL> select count(*) from OL$;  COUNT(*)---------- 0
SQL> create private outline outline2 on select count(*) from t1 where object_id < 100;大綱已建立。SQL> select count(*) from OL$;  COUNT(*)---------- 0SQL> select count(*) from SYSTEM.OL$;  COUNT(*)---------- 1
SESSION 1
SQL> select count(*) from t1 where object_id < 100;  COUNT(*)----------98執行計畫----------------------------------------------------------Plan hash value: 3900446664--------------------------------------------------------------------------| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  | |     1 |    13 |     3   (0)| 00:00:01 ||   1 |  SORT AGGREGATE   | |     1 |    13 |      |  ||*  2 |   INDEX RANGE SCAN| I1 |  4411 | 57343 |     3   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OBJECT_ID"<100)Note-----   - outline "OUTLINE2" used for this statement
SESSION 2：
SQL> alter session set use_private_outlines=TRUE;會話已更改。SQL> select count(*) from t1 where object_id < 100;  COUNT(*)----------98執行計畫----------------------------------------------------------Plan hash value: 3900446664--------------------------------------------------------------------------| Id  | Operation  | Name | Rows  | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------|   0 | SELECT STATEMENT  | |     1 |    13 |     2   (0)| 00:00:01 ||   1 |  SORT AGGREGATE   | |     1 |    13 |      |  ||*  2 |   INDEX RANGE SCAN| I1 |    98 |  1274 |     2   (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("OBJECT_ID"<100)Note-----   - dynamic sampling used for this statement (level=2)
總結：oracle的儲存綱要可以分為公有和私人，公有綱要的相關資訊存放在OUTLN使用者中，私人公有的相關資訊存放在SYSTEM下的暫存資料表中；由於私人綱要存放在臨死表中，因此只能再當前session中使用，使用私人綱要需要設定USE_PRIVATE_OUTLINES（system或者session層級，主要這不是初始化參數），同樣使用公有綱要需要設定USE_STORED_OUTLINES；