使用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
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 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
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 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
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 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
-------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
|
-------------------------------------------------------------------------------
------
| 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
-------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
|
-------------------------------------------------------------------------------
------
| 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
--------------------------------------------------------------------------------
------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
------
| 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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12020513/viewspace-615549/
817
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
