oracle實驗記錄（使用outlines）

使用outlines 固定執行計劃
在oracle升級版本時有用

分2種
儲存outline
專用outline

實驗簡單的使用儲存outline

CREATE_STORED_OUTLINESProperty Description
Parameter type String
Syntax CREATE_STORED_OUTLINES = { true | false | category_name }
Default value There is no default value.
Modifiable ALTER SESSION, ALTER SYSTEM
Basic No

CREATE_STORED_OUTLINES determines whether Oracle automatically creates and stores an outline for each query submitted during the session.

Values:

true

Enables automatic outline creation for subsequent queries in the same session. These outlines receive a unique system-generated name and are stored in the DEFAULT category. If a particular query already has an outline defined for it in the DEFAULT category, then that outline will remain and a new outline will not be created.

false

Disables automatic outline creation during the session. This is the default.

category_name

Enables the same behavior. as true except that any outline created during the session is stored in the category_name category.

使用過程
Ensure that schemas in which outlines are to be created have the CREATE ANY OUTLINE privilege. For example, from SYS:

GRANT CREATE ANY OUTLINE TO user-name
Execute syntax similar to the following to designate; for example, the RBOCAT outline category.

ALTER SESSION SET CREATE_STORED_OUTLINES = rbocat;
Run the application long enough to capture stored outlines for all important SQL statements.

Suspend outline generation:

ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;
Gather statistics with the DBMS_STATS package.

Alter the parameter OPTIMIZER_MODE to CHOOSE.

Enter the following syntax to make Oracle use the outlines in category RBOCAT:

ALTER SESSION SET USE_STORED_OUTLINES = rbocat;
Run the application.

Subject to the limitations of plan stability, access paths for this application's SQL statements should be unchanged.

SQL> show user
USER 為 "XH"
SQL> drop table t1;

表已刪除。

建立一個table ,最早FTS 加個INDEX後 INDEX SCAN ,用outline固定FTS 的PLAN
SQL> create table t1 (a int,b int);

表已建立。

SQL> declare
2 begin
3 for i in 1..10000 loop
4 insert into t1 values(i,i+1);
5 end loop;
6 commit;
7 end;
8 /

PL/SQL 過程已成功完成。

SQL> execute dbms_stats.gather_table_stats('xh','t1');

PL/SQL 過程已成功完成。

SQL> set autot trace exp
SQL> alter session set create_stored_outlines=xhtr(category_name);~~~~~~~~~SESSION級建立, create_stored_outlines 為TURE 則建立 CATEGORY為defalut的 outline

會話已更改。例中為建立自己的category 不屬於default 使用時候也要針對這個建立的category才行 (use_stored_outlines=category_name)

SQL> select * from t1 where a=1;

執行計劃
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     7 |     6   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1   |     1 |     7 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=1)

SQL> alter session set create_stored_outlines=false;

會話已更改。

SQL> create index t1_ind on t1(a);

索引已建立。

SQL> select * from t1 where a=1;

執行計劃
----------------------------------------------------------
Plan hash value: 2059591622

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

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

| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:0
0:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 7 | 2 (0)| 00:0
0:01 |

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

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

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"=1)

SQL> alter session set use_stored_outlines=xhtr;

會話已更改。

SQL> select * from t1 where a=1;

執行計劃
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     7 |     6   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1   |     1 |     7 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=1)

Note
-----
- outline "SYS_OUTLINE_09092415225564012" used for this statement~~~~~~~~~~~~~~~~使用outline 固定了plan

SQL>
SQL> desc dba_outlines;
名稱                                      是否為空? 型別
----------------------------------------- -------- ----------------------------
NAME                                               VARCHAR2(30)
OWNER                                              VARCHAR2(30)
CATEGORY                                           VARCHAR2(30)
USED                                               VARCHAR2(6)
TIMESTAMP                                          DATE
VERSION                                            VARCHAR2(64)
SQL_TEXT                                           LONG
SIGNATURE                                          RAW(16)
COMPATIBLE                                         VARCHAR2(12)
ENABLED                                            VARCHAR2(8)
FORMAT                                             VARCHAR2(6)

SQL> select sql_text,owner,CATEGORY from dba_outlines where name='SYS_OUTLINE_09092
415225564012';

SQL_TEXT
--------------------------------------------------------------------------------
OWNER CATEGORY
------------------------------ ------------------------------
select * from t1 where a=1
XH XHTR

另一種方法

SQL> set autot trace exp
SQL> select * from t2 where a=2;

執行計劃
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     7 |     6   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2   |     1 |     7 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=2)

SQL> create or replace outline myot on select * from t2 where a=2;~~~~~~~~~~~建立outline (CATEGORY 屬於default)

另外可以加到特定的category中,例如
CREATE OUTLINE salaries FOR CATEGORY special
ON SELECT last_name, salary FROM employees;

大綱已建立。

SQL> create index ind_t2 on t2(a);

索引已建立。

SQL> select * from t2 where a=2;

執行計劃
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     7 |     6   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2   |     1 |     7 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=2)

Note
-----
- outline "MYOT" used for this statement

SQL> alter session set use_stored_outlines=false;~~~關閉 true時使用CATEGORY default的 outline

會話已更改。

SQL> select * from t2 where a=2;

執行計劃
----------------------------------------------------------
Plan hash value: 591771271

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

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

| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:0
0:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 7 | 2 (0)| 00:0
0:01 |

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

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

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"=2)

SQL>

SQL> alter session set use_stored_outlines=true;

會話已更改。

SQL> alter session set events '10053 trace name context forever';

會話已更改。

SQL> select * from t2 where a=2;

執行計劃
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     7 |     6   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2   |     1 |     7 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=2)

Note
-----
- outline "MYOT" used for this statement

SQL>

通過10053 trace可以看到 oracle 有INDEX STATS但並沒有嘗試計算index scan成本直接走了固定計劃

QUERY BLOCK SIGNATURE
*********************
qb name was generated
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 bjn=56286 hint_alias="T2"@"SEL$1"*************
*****************************
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
Table: T2 Alias: T2
    #Rows: 10000 #Blks: 20 AvgRowLen: 7.00
Index Stats::
Index: IND_T2 Col#: 1
    LVLS: 1 #LB: 21 #DK: 10000 LB/K: 1.00 DB/K: 1.00 CLUF: 18.00
***************************************
SINGLE TABLE ACCESS PATH
Column (#1): A(NUMBER)
    AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
Table: T2 Alias: T2
    Card: Original: 10000 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
Access Path: TableScan
    Cost: 6.37 Resp: 6.37 Degree: 0
      Cost_io: 6.00 Cost_cpu: 2142449
      Resp_io: 6.00 Resp_cpu: 2142449
Best:: AccessPath: TableScan
         Cost: 6.37 Degree: 1 Resp: 6.37 Card: 1.00 Bytes: 0

SELECT /*+ FULL ("T2") */ "T2"."A" "A","T2"."B" "B" FROM "XH"."T2" "T2" WHERE "T2"."A"=2

***********outline實際以hint 來控制plan

Outline Data:
/*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T2"@"SEL$1")
    END_OUTLINE_DATA
*/

SQL> select hint from dba_outline_hints where name='MYOT';

HINT
--------------------------------------------------------------------------------
FULL(@"SEL$1" "T2"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS

SQL> select sql_text,owner,CATEGORY from dba_outlines where name='MYOT';

SQL_TEXT
--------------------------------------------------------------------------------
OWNER CATEGORY
------------------------------ ------------------------------
select * from t2 where a=2
XH DEFAULT

專用 outline
The USE_PRIVATE_OUTLINES parameter lets you control the use of private outlines. A private outline is an outline seen only in the current session and whose data resides in the current parsing schema. Any changes made to such an outline are not seen by any other session on the system, and applying a private outline to the compilation of a statement can only be done in the current session with the USE_PRIVATE_OUTLINES parameter. Only when you explicitly choose to save your edits back to the public area are they seen by the rest of the users.

While the optimizer usually chooses optimal plans for queries, there are times when users know things about the execution environment that are inconsistent with the heuristics that the optimizer follows. By editing outlines directly, you can tune the SQL query without having to alter the application.

When the USE_PRIVATE_OUTLINES parameter is enabled and an outlined SQL statement is issued, the optimizer retrieves the outline from the session private area rather than the public area used when USE_STORED_OUTLINES is enabled. If no outline exists in the session private area, then the optimizer will not use an outline to compile the statement.

Any CREATE OUTLINE statement requires the CREATE ANY OUTLINE privilege. Specification of the FROM clause also requires the SELECT privilege. This privilege should be granted only to those users who would have the authority to view SQL text and hint text associated with the outlined statements. This role is required for the CREATE OUTLINE FROM command unless the issuer of the command is also the owner of the outline.

When creating or editing a private outline, the outline data is written to global temporary tables in the SYSTEM schema. These tables are accessible with the OL$, OL$HINTS, and OL$NODES synonyms.

使用 USE_PRIVATE_OUTLINES parameter 控制,只對當前 SESSION , 當啟用 USE_STORED_OUTLINES 發出SQL語句後優化器檢視 session private area(PGA內), 不是PUBILC AREA
如果session private area 沒有outline 優化器不會用outline編譯語句
建立專用outline時,如果本地schema中不存在儲存outline資料的預先outline表.則返回錯誤用dbms_outln_edit.create_edit_tables來建這些表

建立專用outline

CREATE OR REPLACE PRIVATE OUTLINE命令以公共方式儲存；公共綱要可以使用CREATE OR REPLACE OUTLINE…FROM PRIVATE…命令來由專用綱要建立。這個過程又叫做編輯，可以把一個已有的專用綱要複製成公有綱要。只要適合，Oracle會自動使用這些STORED OUTLINES，而不用向執行該命令的使用者顯示。

Creating a Private Clone Outline: Example
The following statement creates a stored private outline my_salaries based on the public category salaries created in the preceding example. In order to create a private outline, the user creating the private outline must have the EXECUTE object privilege on the DBMS_OUTLN_EDIT package and must execute the CREATE_EDIT_TABLES procedure of that package.

EXECUTE DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES;

CREATE OR REPLACE PRIVATE OUTLINE my_salaries 用public outline(stored outline) 生成private outline
FROM salaries;
Publicizing a Private Outline to the Public Area: Example
The following statement copies back (publicizes) a private outline to the public area after private editing:

CREATE OR REPLACE OUTLINE public_salaries
FROM PRIVATE my_salaries; 用private outline) 生成public outline(stored outline

SQL> show user
USER 為 "XH"
SQL> create table t3 (a int,b int);

表已建立。

SQL> ed
已寫入 file afiedt.buf

1 declare
2 begin
3 for i in 1..10000 loop
4 insert into t3 values(i,i+1);
5 end loop;
6 commit;
7* end;
SQL> /

PL/SQL 過程已成功完成。

SQL> execute dbms_stats.gather_table_stats('xh','t3');

PL/SQL 過程已成功完成。

SQL> set autot trace exp
SQL> select count(*) from t3 where a=2;

SQL> select * from t3 where a=2;

執行計劃
----------------------------------------------------------
Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     7 |     6   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T3   |     1 |     7 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=2)

SQL> EXECUTE DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES;

PL/SQL 過程已成功完成。

SQL> create or replace private outline my_t3 on select * from t3 where a=2
2 ;

大綱已建立。

SQL> create index t3_ind on t3(a);

索引已建立。

SQL> select * from t3 where a=2;

執行計劃
----------------------------------------------------------
Plan hash value: 2495931480

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

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

| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:0
0:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 7 | 2 (0)| 00:0
0:01 |

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

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

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"=2)

SQL> alter session set USE_PRIVATE_OUTLINES=true;

會話已更改。

SQL> select * from t3 where a=2;

執行計劃
----------------------------------------------------------
Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     7 |     6   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T3   |     1 |     7 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=2)

Note
-----
- outline "MY_T3" used for this statement~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> select * from dba_outlines where name='MY_T3'
2 ;

未選定行

SQL> select * from user_outlines where name='MY_T3'
2 ;

未選定行 ~~~~~~~~~~~~~~~~~~~~~~~~~~查不到

SQL> conn xh/a123~~~~~~~~~~~~~~~~同樣的使用者登陸執行沒有使用專用outline可以看出是 ,只對current SESSION
已連線。
SQL> select distinct sid from v$mystat;

SID
----------
154

SQL> set autot trace exp
SQL> select * from t3 where a=2;

執行計劃
----------------------------------------------------------
Plan hash value: 2495931480

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

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

| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:
0:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 7 | 2 (0)| 00:
0:01 |

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

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

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"=2)

SQL> alter session set USE_PRIVATE_OUTLINES=true;

會話已更改。

SQL> select * from t3 where a=2;

執行計劃
----------------------------------------------------------
Plan hash value: 2495931480

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

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

| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:
0:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 7 | 2 (0)| 00:
0:01 |

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

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

Predicate Information (identified by operation id):~~~~~~~~~~~~~~~~~~~~~沒有使用outlines
---------------------------------------------------

2 - access("A"=2)

SQL> create or replace outline pubilc_my_t3 from private my_t3
2 ;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~轉成公有的

大綱已建立。

SQL> select * from user_outlines where name='PUBILC_MY_T3'
2 ;

NAME                           CATEGORY                       USED
------------------------------ ------------------------------ ------
TIMESTAMP      VERSION
-------------- ----------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------
SIGNATURE                        COMPATIBLE   ENABLED FORMAT
-------------------------------- ------------ -------- ------
PUBILC_MY_T3                   DEFAULT                        UNUSED
25-9月 -09     10.2.0.1.0
select * from t3 where a=2
E36CE15099D7A752697194AF7F146095 COMPATIBLE   ENABLED NORMAL

SQL> conn xh/a123
已連線。
SQL> select distinct sid from v$mystat;

SID
----------
154

SQL> set autot trace exp
SQL> select * from t3 where a=2;

執行計劃
----------------------------------------------------------
Plan hash value: 2495931480

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

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

| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:0
0:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T3 | 1 | 7 | 2 (0)| 00:0
0:01 |

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

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

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"=2)

SQL> alter session set use_stored_outlines=true;

會話已更改。

SQL> select * from t3 where a=2;

執行計劃
----------------------------------------------------------
Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     7 |     6   (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T3   |     1 |     7 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=2)

Note
-----
- outline "PUBILC_MY_T3" used for this statement~~~~~~~~~~~~~~~~~~~~~~~stored outlines

與outline相關的view
USER_OUTLINES

USER_OUTLINE_HINTS

ALL_OUTLINES

ALL_OUTLINE_HINTS

DBA_OUTLINES

DBA_OUTLINE_HINTS

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

oracle實驗記錄 （使用outlines）

相關文章

oracle實驗記錄（使用outlines）