Oracle：SQL大纲OutLine

hanjian861202

浏览: 159680 次
性别:
来自: 南京

最近访客更多访客>>

bruce_ma

saw

wang_eye

fmbobin

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (122)

社区版块

存档分类

Oracle的Outline技术可以在特殊情况下保证执行计划的稳定性，将执行计划写死。在极端情况下可以使用这个技术实现暂时锁定执行计划的目的。

主要使用场景如下：
短时间内无法完成SQL的优化任务，此时可以使用outline暂时锁定SQL执行计划；
在CBO优化模式下，当统计信息出现问题时，会导致执行计划出现异常变化，此时可以使用outline暂时调整SQL执行计划；
由于数据库的bug导致SQL的执行计划出现异常，使用outline锁定执行计划。
不改变源代码的情况下，修改执行计划(通过增加提示)。

记录一下关于outline的使用方法，供参考。
1.环境准备
1）创建用户secooler，并授予适当权限，注意，用户需要具有create any outline权限
sys@ora10g> create user secooler identified by secooler default tablespace TBS_SECOOLER_D;

User created.

sys@ora10g> grant connect,resource to secooler;

Grant succeeded.

sys@ora10g> grant create any outline to secooler;

Grant succeeded.

2）在secooler用户中创建表T
secooler@ora10g> create table t as select * from all_objects;

Table created.

secooler@ora10g> select count(*) from t;

COUNT(*)
----------
      4548

2.创建outline
1）解锁outln用户
sys@asd> alter user outln identified by outln account unlock;

User altered.

2）创建一个outline，取名叫做t_outln1，指定它的category名字为CATEGORY_T
outln@ora10g> conn secooler/secooler
Connected.
secooler@ora10g> create outline t_outln1 for category CATEGORY_T on select * from t where OBJECT_ID=258;

Outline created.

3）此时outln用户下的三张表OL$、OL$HINTS和OL$NODES中便会记录与此次操作的相关信息。执行计划会记录在OL$HINTS中。
secooler@ora10g> conn outln/outln
Connected.
outln@ora10g> select hint_text from ol$hints where ol_name = 'T_OUTLN1' order by hint#;

HINT_TEXT
--------------------------------------------------
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS

4）比对一下对应的执行计划
outln@ora10g> conn secooler/secooler
Connected.
secooler@ora10g> set autotrace traceonly explain;
secooler@ora10g> select * from t where OBJECT_ID=258;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     2 |   256 |    36   (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T    |     2 |   256 |    36   (3)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=258)

Note
-----
   - dynamic sampling used for this statement

这里记录了一个全表扫描的的执行计划。

5）关于创建的outline基本信息也可以通过dba_outlines视图进行查询
secooler@ora10g> select NAME,OWNER,CATEGORY,SQL_TEXT from dba_outlines;

NAME                           OWNER         CATEGORY
------------------------------ ------------- -------------------
SQL_TEXT
----------------------------------------------------------------
T_OUTLN1                       SECOOLER      CATEGORY_T
select * from t where OBJECT_ID=258

3.使用outline
1）为了对比，我们创建索引，改变SQL语句的执行计划
（1）在T表的X字段创建索引
secooler@ora10g> create index i_t on t(object_id);

Index created.

（2）查看此时SQL的执行计划
secooler@ora10g> set autotrace traceonly explain;
secooler@ora10g> select * from t where OBJECT_ID=258;

Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915

------------------------------------------------------------------------------------
| Id | Operation                   | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |   128 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| T    |     1 |   128 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | I_T |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=258)

Note
-----
   - dynamic sampling used for this statement

从执行计划上可以看到此时该SQL使用到了索引，没有进行全表扫面。

2）强制SQL使用ontline中记录的执行计划
（1）设置系统使用category为CATEGORY_T的outline
secooler@ora10g> alter system set use_stored_outlines=CATEGORY_T;

System altered.

（2）再次查看SQL语句的执行计划
secooler@ora10g> select * from t where OBJECT_ID=258;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |   124 | 15872 |    36   (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T    |   124 | 15872 |    36   (3)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=258)

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

从最后的注释上我们已经可以看到，此时SQL语句执行过程中使用的是outln中记录的执行计划。SQL在获取数据的时候走的是全表扫描。

3）消除ontline对SQL语句的影响
（1）第一种方法是调整use_stored_outlines参数为false
secooler@ora10g> alter system set use_stored_outlines=false;

System altered.

secooler@ora10g> select * from t where OBJECT_ID=258;

Execution Plan
----------------------------------------------------------
Plan hash value: 2928007915

------------------------------------------------------------------------------------
| Id | Operation                   | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |   128 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| T    |     1 |   128 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | I_T |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=258)

Note
-----
   - dynamic sampling used for this statement

（2）第二种方法是停用具体的outline
这种方法可以在use_stored_outlines参数起作用的前提下停用具体的outline。
secooler@ora10g> alter system set use_stored_outlines=CATEGORY_T;

System altered.

secooler@ora10g> select * from t where OBJECT_ID=258;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |   124 | 15872 |    36   (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T    |   124 | 15872 |    36   (3)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=258)

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

secooler@ora10g> alter outline t_outln1 disable;

Outline altered.

secooler@ora10g> select * from t where OBJECT_ID=258;

Execution Plan
--------------------------------------------------
Plan hash value: 2928007915

------------------------------------------------------------------------------------
| Id | Operation                   | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |   128 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| T    |     1 |   128 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | I_T |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"=258)

Note
-----
   - dynamic sampling used for this statement

此时ontline T_OUTLN1已经被停用。

4.更换大纲名字，从而不改变源代码的情况下，修改执行计划(通过增加提示)
outln@ora10g>select * from ol$hints;

outln@ora10g>update ol$hints set ol_name=decode(ol_name,'OL','OL2','OL2','OL')

where ol_name in('OL','Ol2');

5.清除outline
我们在10g环境下可以使用dbms_outln.drop_by_cat完成清空具体category的目的。
secooler@ora10g> conn outln/outln
Connected.
outln@ora10g> select hint_text from ol$hints where ol_name = 'T_OUTLN1' order by hint#;

HINT_TEXT
--------------------------------------------------
FULL(@"SEL$1" "T"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
IGNORE_OPTIM_EMBEDDED_HINTS

outln@ora10g> exec dbms_outln.drop_by_cat('CATEGORY_T');

PL/SQL procedure successfully completed.

outln@ora10g> select hint_text from ol$hints where ol_name = 'T_OUTLN1' order by hint#;

no rows selected

6.关于USE_STORED_OUTLINES参数的说明
USE_STORED_OUTLINES参数不像一般的参数可以在参数文件中进行设定，但我们可以使用常规的方法对其进行修改。
ALTER SESSION SET USE_STORED_OUTLINES = TRUE | FALSE | category;
ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE | FALSE | category;

7.小结
通过文中的描述，大家应该对Oracle Outline技术有一个比较详细的了解。在CBO优化模式下，很有可能遇到执行计划不稳定及不准确的情况。在这种场景下我们可以考虑使用这项技术暂时规避执行计划变化带来的性能问题。