oracle的stored outline的创建与维护

                author：skate
time：2011/09/17

 

执行计划的稳定

 

如何确定哪些sql的执行计划不稳定

如何固定执行计划

执行计划直接影响app的性能，有的时候执行计划固定是很有用的，有很多因素影响执行计划的不稳定，像optimizer statistics的改变，optimizer mode的改变，影响性能的内存参数的改变（SORT_AREA_SIZE and BITMAP_MERGE_AREA_SIZE），还有数据库的升级，大数据量迁移等，所以有的时候我就需要让sql的执行计划稳定。

sql执行计划的固定可以用oracle的hints，这种方式很灵活，可以控制每个sql的执行计划，但是需要应用对没有sql都需要修改，这就有个弊端，让APP修改不是很容易，如果想在改变的话， 不能又让app做修改吧？这个时候就有oracle outlines了，他可以在数据库端修改，应用无需任何改动。

是否固定执行计划也要事先考虑，因为数据库环境是在变化的，随着时间推移，数据量的增加，oracle为了更优化sql，可能会改变sql的执行计划，所以这就需要我们平衡是否固定执行计划或什么周期重新固定新的执行计划，在数据量总是突变的环境里不要固定执行计划。

 

stored outlines的使用

一个stored outlines可以理解为是一组sql执行计划的集合，这个集合的内容都是来源于oracle的优化器的结果，即当我们创建一个outline后，stored outlines仅仅在游标被解析的时候才能被创建与应用，如果游标被缓存了，我将会发现sql没有使用我们新创建的stored outline，就是这个原因。

我们使用stored outlines可以稳固数据库的所有sql的执行计划，这种情况用的还是比较少的；在实际环境中我们往往想优化
某些sql，让这些需要优化的sql的执行计划固定。

 

创建outlines

1.oracle自动为所有的sql创建outlines

例如我们测试环境中应用用户为skate

A.用sys用户给skate授权，这个权限是必须的

sql> grant CREATE ANY OUTLINE to skate;

sql> alter system set CREATE_STORED_OUTLINES = true;

或
sql> alter system set CREATE_STORED_OUTLINES = skate_outline;

stored outline是分category(类目)的，当CREATE_STORED_OUTLINES = true时，oracle使用默认的“default”category，
当CREATE_STORED_OUTLINES = skate_outline时，oracle将使用我们自定义的“skate_outline” category；当我们设置了
CREATE_STORED_OUTLINES参数时，oracle将自动为了后续所有编译的sql创建outline到CREATE_STORED_OUTLINES指定的category下。
当我们把CREATE_STORED_OUTLINES设置为false时，oracle就停止创建outline

sql> alter system set  USE_STORED_OUTLINES =true
或
sql> alter system set  USE_STORED_OUTLINES =skate_outline

检查otuline是否生效:
SELECT NAME, SQL_TEXT ,used
  FROM USER_OUTLINES
  WHERE CATEGORY='skate_outline';
和
SELECT OUTLINE_CATEGORY, OUTLINE_SID
  FROM V$SQL
  WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM tablename%';

 

停止使用outline
sql> alter system set  USE_STORED_OUTLINES =false;

 

2.我们为指定的sql创建outlines

A.用sys用户给skate授权，这个权限是必须的

sql> grant CREATE ANY OUTLINE to skate;

创建自定义的category(这里把两个sql的outpine创建在同一个category下)
sql> create or replace OUTLINE  test_outline1 FOR CATEGORY cate_outline ON select * from aa1 where tname ='5'
sql> create or replace OUTLINE  test_outline2 FOR CATEGORY cate_outline ON select * from aa1 where tname ='2'

让oracle使用outline
sql> alter system set  USE_STORED_OUTLINES =cate_outline;

检查otuline是否生效:
SELECT NAME, SQL_TEXT ,used
  FROM USER_OUTLINES
  WHERE CATEGORY='cate_outline';
和
SELECT OUTLINE_CATEGORY, OUTLINE_SID
  FROM V$SQL
  WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM tablename%';

停止使用outline
sql> alter system set  USE_STORED_OUTLINES =false;

如果是rac集群环境，那就要每个实例的都要配置

 

注意：
1.
Oracle creates the USER_OUTLINES and USER_OUTLINE_HINTS views in the SYS tablespace based on data in the OL$
and OL$HINTS tables, respectively， Direct manipulation of the OL$, OL$HINTS, and OL$NODES tables is prohibited

2.
在DBMS_OUTLN 和 DBMS_OUTLN_EDIT中很有用的procedures如下:
 CLEAR_USED - Clears specified outline
 DROP_BY_CAT - Drops outlines that belong to a specified category
 UPDATE_BY_CAT - Changes the category of outlines in one specified category to a new specified category
 EXACT_TEXT_SIGNATURES - Computes an outline signature according to an exact text matching scheme
 GENERATE_SIGNATURE - Generates a signature for the specified SQL text

3.
在使用CREATE_STORED_OUTLINES参数时，oracle会大量占用sys表空间，所以我们要把更改outline的默认表空间

A.Use the Oracle Export utility to export the OL$, OL$HINTS, and OL$NODES tables:

EXP OUTLN/outln_password
    FILE = exp_file TABLES = 'OL$' 'OL$HINTS' 'OL$NODES'
 
B.Start SQL*Plus and connect to the database.

CONNECT OUTLN/outln_password;

C.Remove the previous OL$, OL$HINTS, and OL$NODES tables:

DROP TABLE OL$;
DROP TABLE OL$HINTS;
DROP TABLE OL$NODES;

D.Create a new tablespace for the tables:

CONNECT SYSTEM/system_password;
CREATE TABLESPACE outln_ts
  DATAFILE 'tspace.dat' SIZE 2M
  DEFAULT STORAGE (INITIAL 10K NEXT 20K MINEXTENTS 1 MAXEXTENTS 999
                   PCTINCREASE 10)
  ONLINE;

E.Enter the following statement to change the default tablespace:

ALTER USER OUTLN DEFAULT TABLESPACE outln_ts;

 

F.To force the import into the OUTLN_TS tablespace, set quota for the SYSTEM tablespace to 0K for the OUTLN user.
You will also need to revoke the UNLIMITED TABLESPACE privilege and all roles, such as the RESOURCE role, that
have unlimited tablespace privileges or quotas. Set a quota for the OUTLN tablespace.

G.Import the OL$, OL$HINTS, and OL$NODES tables:

IMP OUTLN/outln_password
    FILE = exp_file TABLES = (OL$, OL$HINTS, OL$NODES)

 

常用命令：

 grant CREATE ANY OUTLINE to skate;
 alter system set CREATE_STORED_OUTLINES = true;
 alter system set CREATE_STORED_OUTLINES = skate_outline; 
 create or replace OUTLINE  test_outline1 FOR CATEGORY cate_outline ON select * from aa1 where tname ='5'
 alter system set  USE_STORED_OUTLINES =skate_outline;
 alter system set  USE_STORED_OUTLINES =false;
 select * from user_outlines
 select * from user_outline_hints
 select* from ol$
 select * from ol$hints
 select * from ol$nodes
 drop outline test_outline1
 
begin
dbms_outln.drop_by_cat(cat => 'TEST_OUTLINE');
end;
 
 SELECT NAME, SQL_TEXT ,used
  FROM USER_OUTLINES
  WHERE CATEGORY='cate_outline';

SELECT OUTLINE_CATEGORY, OUTLINE_SID
  FROM V$SQL
  WHERE SQL_TEXT LIKE 'SELECT COUNT(*) FROM tablename%';

 
参考文档：http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/outlines.htm

 

 

----end---