【OUTLINE】使用Oracle Outline技术暂时锁定SQL的执行计划

  Oracle的Outline技术可以在特殊情况下保证执行计划的稳定性。在极端情况下可以使用此项技术实现暂时锁定执行计划的目的。
  主要使用场景如下：
  ①短时间内无法完成SQL的优化任务，此时可以使用outline暂时锁定SQL执行计划；
  ②在CBO优化模式下，当统计信息出现问题时，会导致执行计划出现异常变化，此时可以使用outline暂时调整SQL执行计划；
  ③由于数据库的bug导致SQL的执行计划出现异常，使用outline锁定执行计划。

  记录一下关于outline的使用方法，供参考。

1.初始化环境
1）创建用户secooler，并授予适当权限，注意，用户需要具有create any outline权限
SYS@PROD> create user secooler identified by secooler;

User created.

SYS@PROD> grant connect,resource to secooler;

Grant succeeded.

SYS@PROD> grant create any outline,alter any outline to secooler;

Grant succeeded.

2）在secooler用户中创建表T
SYS@PROD> conn secooler/secooler
Connected.
SECOOLER@PROD> create table t as select * from all_objects;

Table created.

SECOOLER@PROD> select count(*) from t;

  COUNT(*)
----------
      4448

2.创建outline
1）解锁outln用户
SECOOLER@PROD> conn / as sysdba
Connected.
SYS@PROD> alter user outln identified by outln account unlock;

User altered.

2）创建一个outline，取名叫做t_outln1，指定它的category名字为CATEGORY_T
SYS@PROD> conn secooler/secooler
Connected.
SECOOLER@PROD> 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@PROD> conn outln/outln
Connected.
OUTLN@PROD> 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.1')
IGNORE_OPTIM_EMBEDDED_HINTS

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

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   128 |    19   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |   128 |    19   (0)| 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@PROD> conn / as sysdba
Connected.
SYS@PROD> 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字段创建索引
SYS@PROD> conn secooler/secooler
Connected.
SECOOLER@PROD> create index i_t on t(object_id);

Index created.

（2）查看此时SQL的执行计划
SECOOLER@PROD> set lines 200
SECOOLER@PROD> set autotrace traceonly explain;
SECOOLER@PROD> 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@PROD> alter session set use_stored_outlines=CATEGORY_T;

Session altered.

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

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    51 |  6528 |    19   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    51 |  6528 |    19   (0)| 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@PROD> alter session set use_stored_outlines=false;

Session altered.

SECOOLER@PROD> 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@PROD> alter session set use_stored_outlines=CATEGORY_T;

Session altered.

SECOOLER@PROD> select * from t where OBJECT_ID=258;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    51 |  6528 |    19   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |    51 |  6528 |    19   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=258)

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

SECOOLER@PROD> alter outline t_outln1 disable;

Outline altered.

SECOOLER@PROD> 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.清除outline
我们在10g环境下可以使用dbms_outln.drop_by_cat完成清空具体category的目的。
SECOOLER@PROD> conn outln/outln
Connected.
OUTLN@PROD> 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.1')
IGNORE_OPTIM_EMBEDDED_HINTS

OUTLN@PROD> exec dbms_outln.drop_by_cat('CATEGORY_T');

PL/SQL procedure successfully completed.

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

no rows selected

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

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

Good luck.

secooler
10.09.08

-- The End --

时间： 2024-10-23 09:54:30

【OUTLINE】使用Oracle Outline技术暂时锁定SQL的执行计划的相关文章

Oracle数据库如何搜集指定SQL的执行计划和解决过程中的ORA-00904错误

  Oracle 数据库如何搜集指定SQL的执行计划和解决过程中的ORA-00904错误 (版权声明,本人原创或者翻译的文章如需转载,如转载用于个人学习,请注明出处;否则请与本人联系,违者必究) 如何收集指定SQL的执行计划对开发人员来说非常重要的,这里记录下基础的收集方式,以便查阅和其他人参考. 1. 链接到sqlplus,如下图 2. 执行下面两个的命令之一 set autotrace on; (说明:打开自动分析统计,并显示SQL语句的运行结果) 3. 输入并执行要搜集执行计划的SQL语句

Oracle 历史SQL语句执行计划的对比与分析

    基于CBO优化器的环境中,SQL执行计划的生成依赖于统计信息的真实与完整.如列的离散度,列上的直方图,索引的可用性,索引上的聚簇因子.当这些信息是真实完整的情况下,CBO优化器通常都可以制定最优的执行计划.也正因此CBO优化器也灵活,难以控制,任一信息的不真实或缺失都可能导致执行计划发生变化而产生多个版本.经常碰到的情形是之前的某个SQL语句前阵子还不是TOP SQL,而最近变成了TOP SQL.或者说之前尽管是TOP SQL但,但最近尽然成了TOP 1.对于此情形,我们可以比对SQL语

ORACLE从共享池删除指定SQL的执行计划

Oracle 11g在DBMS_SHARED_POOL包中引入了一个名为PURGE的新存储过程,用于从对象库缓存中刷新特定对象,例如游标,包,序列,触发器等.也就是说可以删除.清理特定SQL的执行计划,这样在特殊情况下,就避免你要将整个SHARED POOL清空的危险情况.例如某个SQL语句由于优化器产生了错误的执行计划,我们希望优化器重新解析,生成新的执行计划,必须先将SQL的执行计划从共享池中刷出或将其置为无效,那么优化器才能将后续SQL进行硬解析.生成新的执行计划.这在以前只能使用清空共享

Oracle中基于hint的3种执行计划控制方法详细介绍_oracle

hint(提示)无疑是最基本的控制执行计划的方式了:通过在SQL语句中直接嵌入优化器指令,进而使优化器在语句执行时强制的选择hint指定的执行路径,这种使用方式最大的好处便是方便和快捷,定制度也很高,通常在对某些SQL语句执行计划进行微调的时候我会首选这种方式,不过尽管如此,hint在使用中仍然有很多不可忽视的问题: 使用hint过程中有一些值得注意的细则,首先便是要准确的识别对应的查询块,如果需要使用注释也可以hint中声明:对于使用别名的对象一律使用别名来引用,并且诸如"用户名.对象&quo

浅析SQL SERVER执行计划中的各类怪相

在查看执行计划或调优过程中,执行计划里面有些现象总会让人有些疑惑不解:     1:为什么同一条SQL语句有时候会走索引查找,有时候SQL脚本又不走索引查找,反而走全表扫描?     2:同一条SQL语句,查询条件的取值不同,它的执行计划会一致吗?     3: 同一条SQL语句,其执行计划会变化,为什么     4: 在查询条件的某个或几个字段上创建了索引,执行计划就一定会走该索引吗?     5:同时存在几个索引,SQL语句会走那个索引?      .....................

SQL Server执行计划的理解

原文:SQL Server执行计划的理解 要理解执行计划,怎么也得先理解,那各种各样的名词吧.鉴于自己还不是很了解.本文打算作为只写懂的,不懂的懂了才写. 在开头要先说明,第一次看执行计划要注意,SQL Server的执行计划是从右向左看的. 名词解析: 扫描:逐行遍历数据. 先建立一张表,并给大家看看大概是什么样子的. CREATE TABLE Person( Id int IDENTITY(1,1) NOT NULL, Name nvarchar(50) NULL, Age int NULL

使用 EXPLAIN PLAN 获取SQL语句执行计划

     SQL查询语句的性能从一定程度上影响整个数据库的性能.很多情况下,数据库性能的低下差不多都是不良SQL语句所引起.而SQL语句的执行 计划则决定了SQL语句将会采用何种方式从数据库提取数据并返回给客户端,本文描述的将是如何通过EXPLAIN PLAN 获取SQL语句执行计划来获 取SQL语句的执行计划. 一.获取SQL语句执行计划的方式     1. 使用explain plan 将执行计划加载到表plan_table,然后查询该表来获取预估的执行计划      2. 查询动态性能视图

【DBAplus】深入Oracle优化器：一条诡异执行计划的解决之道

深入Oracle优化器:一条诡异执行计划的解决之道 DBAplus社群 | 2016-05-05 19:51 CBO计算成本并选择最佳执行计划的至关重要输入物就是表和索引的统计信息,过旧或错误的统计信息则可能导致一个性能极差的执行计划被错误地选中.本文将以一个案例展示诡异的统计信息如何影响执行计划的生成. 1案例介绍 这是一个简单的sql,近两个月来对于告警明细表(分区)做月度汇总查询时,总是出现了异常缓慢的情况. 测试SQL: 字段NEALARM_TIME是固定条件,字段RELATED_EMS

引用:初探Sql Server 执行计划及Sql查询优化

原文:引用:初探Sql Server 执行计划及Sql查询优化 初探Sql Server 执行计划及Sql查询优化 收藏 MSSQL优化之----探索MSSQL执行计划   作者:no_mIss   最近总想整理下对MSSQL的一些理解与感悟,却一直没有心思和时间写,晚上无事便写了一篇探索MSSQL执行计划,本文讲执行计划但不仅限于讲执行计划.   网上的SQL优化的文章实在是很多,说实在的,我也曾经到处找这样的文章,什么不要使用IN了,什么OR了,什么AND了,很多很多,还有很多人拿出仅几S甚