情景:
版本升级或者应用程序升级后的CBO策略会发生变化,改变了执行计划的稳定性
要保持CBO的稳定性,就是“存储大纲“
1,前提:
这两个参数在所有环境下都要一致,都为True
query_rewrite_enabled string TRUE
star_transformation_enabled string true
optimizer_features_enable string 10.2.0.1——要保证一致
2,创建:
允许在不同级别:数据库、会话、当前SQL语句级别
创建的存储大纲信息都保存在OL$表中,默认保存在system表空间中,最好指定别的表空间存放
注意事项:
(1)数据库级别
含义:对当前数据库上执行的每一个SQL都创建大纲,在OLTP上会有很多,所以要指定别的表空间存放
语法:SQL>alter system set create_stored_outlines = true;
(2)会话级别
语法:SQL> alter session set create_stored_outlines= true;
(3)为特定SQL语句
SQL> alter user outln identified by oracle account unlock;
User altered.
Connected.
SQL> create outline dept_outline
2 on
3 select * from scott.dept
4 tablespace oltbs;
SQL> conn outln/oracle
Connected.
SQL> select ol_name,sql_text,creator,timestamp
2 from ol$
3 where ol_name like 'DEPT%';
OL_NAME SQL_TEXT CREATOR TIMESTAMP
--------------- -------------------------------------------------- ------------------------------ ---------
DEPT_OUTLINE select * from scott.dept SYS 04-DEC-11
tablespace oltbs
注意:如果在数据库级别创建存储大纲,则大纲名由oracle自己创建,不需要用户干预;
SQL> show user;
USER is "OUTLN"
SQL> select ol_name,sql_text from ol$;
OL_NAME SQL_TEXT
--------------- --------------------------------------------------
SYS_OUTLINE_111 select sysdate + :"SYS_B_0" / (:"SYS_B_1" * :"SYS_
20408223360002 B_2") from dual
DEPT_OUTLINE select * from scott.dept
tablespace oltbs
SYS_OUTLINE_111 select count (*), state from "SYSMAN"."MGMT_NOTIFY
20408304826176 _QTABLE" where q_name = :1 gr
3,删除存储大纲
SQL> show user;
USER is "OUTLN"
SQL> drop outline DEPT_OUTLINE;
drop outline DEPT_OUTLINE
*
ERROR at line 1:
ORA-18006: DROP ANY OUTLINE privilege is required for this operation
SQL> conn / as sysdba
Connected.
SQL> show user;
USER is "SYS"
SQL> drop outline DEPT_OUTLINE;
Outline dropped.
4,启用存储大纲
oracle不会自动是哟你存储大纲,要设置use_stored_outlines,才会使用alter system或session创建的存储大纲
SQL> alter system set use_stored_outlines = true;
System altered.