首页 > 数据库 >使用Oracle Outline锁定SQL执行计划

使用Oracle Outline锁定SQL执行计划

时间：2023-03-05 15:32:15浏览次数：41  
标签：00 outline HR 258 orcl150 SQL Oracle id Outline 

文档课题：使用Oracle Outline锁定SQL执行计划.
1、相关概念
Oracle的Outline技术可在某些情况下保证执行计划的稳定性.
应用场景：
A、短时间内无法完成SQL的优化任务，此时可使用outline暂时锁定SQL执行计划；
B、CBO优化模式下，当统计信息出现问题时会导致执行计划出现异常变化，此时可使用outline暂时调整SQL执行计划；
C、数据库bug导致SQL执行计划出现异常，使用outline锁定执行计划.
2、实验测试
2.1、数据准备
--授予hr用户create any outline等权限
SYS@orcl150> grant create any outline,alter any outline to hr;

Grant succeeded.

SYS@orcl150> conn hr/hr 
Connected.
--hr用户创建测试表T
HR@orcl150> create table t as select * from all_objects;

Table created.

HR@orcl150> select count(*) from t;

  COUNT(*)
----------
     68352
2.2、创建outline
--解锁outln用户
HR@orcl150> conn / as sysdba
Connected.
SYS@orcl150> alter user outln identified by outln account unlock;

User altered.
--创建名为t_outln1的outline，指定category名称为category_t.
SYS@orcl150> conn hr/hr
Connected.
HR@orcl150> create outline t_outln1 for category category_t on select * from t where object_id=258;

Outline created.
2.3、outline确认
--此时outln用户中的三张表OL$、OL$HINTS、OL$NODES会记录此次操作的相关信息，执行计划记录在OL$HINTS中.
HR@orcl150> conn outln/outln
Connected.
OUTLN@orcl150> 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
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS

6 rows selected.
--也可以通过dba_outlines视图查询outline基本信息
SYS@orcl150> col name for a10
SYS@orcl150> col owner for a10
SYS@orcl150> col category for a15
SYS@orcl150> select name,owner,category,sql_text from dba_outlines;

NAME       OWNER      CATEGORY        SQL_TEXT
---------- ---------- --------------- --------------------------------------------------
T_OUTLN1   HR         CATEGORY_T      select * from t where object_id=258
2.4、原始执行计划
OUTLN@orcl150> conn hr/hr;
Connected.
HR@orcl150> set autotrace traceonly explain;
HR@orcl150> select * from t where object_id=258;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    11 |  1738 |   277   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |    11 |  1738 |   277   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=258)

Note
-----
   - dynamic sampling used for this statement (level=2)
说明：此处记录了一个全表扫描的执行计划.
2.5、测试outline
2.5.1、改变执行计划
--在T表的object_id字段创建索引，改变sql语句的执行计划
SYS@orcl150> conn hr/hr
Connected.
HR@orcl150> create index idx_t on t(object_id);

Index created.
--查看此时的sql执行计划
HR@orcl150> set autotrace traceonly explain
HR@orcl150> select * from t where object_id=258;

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   158 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |   158 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_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 (level=2)

说明：可以看到该SQL没有走全表扫描，使用了索引.
2.5.2、使用outline
--设置会话使用category为category_t的outline，强制SQL使用outline中记录的执行计划
HR@orcl150> alter session set use_stored_outlines=CATEGORY_T;

Session altered.
--再次查看sql语句的执行计划
HR@orcl150> select * from t where object_id=258;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   827 |   127K|   277   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |   827 |   127K|   277   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=258)

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

说明：可以看到此时sql语句获取数据的时候走的是全表扫描，使用的是outln中记录的执行计划.
2.5.3、消除outline对sql的影响
2.5.3.1、方法1
--调整use_stored_outlines参数为false，消除outline对sql语句的影响
HR@orcl150> alter session set use_stored_outlines=false;

Session altered.

HR@orcl150> select * from t where object_id=258;

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   158 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |   158 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_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 (level=2)
说明：use_stored_outlines被设置为false后，sql执行计划走索引.
2.5.3.2、方法2
--在use_stored_outlines参数起作用的前提下停用具体的outline.
--设置会话使用category为category_t的outline，强制SQL使用outline中记录的执行计划
HR@orcl150> alter session set use_stored_outlines=CATEGORY_T;

Session altered.

HR@orcl150> select * from t where object_id=258;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   827 |   127K|   277   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |   827 |   127K|   277   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=258)

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

--disable category为category_t的outline.
HR@orcl150> alter outline t_outln1 disable;

Outline altered.

HR@orcl150> select * from t where object_id=258;

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   158 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |   158 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_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 (level=2)
说明：outline t_outln1被disable后，sql执行计划走索引.
2.5.3.3、方法3
--可以使用dbms_outln.drop_by_cat清空具体category的目的.
--没清除前执行计划是全表扫描.
HR@orcl150> alter outline t_outln1 enable;

Outline altered.
HR@orcl150> alter session set use_stored_outlines=CATEGORY_T;

Session altered.
HR@orcl150> set autotrace traceonly explain
HR@orcl150> select * from t where object_id=258;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   827 |   127K|   277   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T    |   827 |   127K|   277   (1)| 00:00:04 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID"=258)

Note
-----
   - outline "T_OUTLN1" used for this statement
HR@orcl150> conn outln/outln
Connected.
OUTLN@orcl150> 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
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS

6 rows selected.

--清除outline
OUTLN@orcl150> exec dbms_outln.drop_by_cat('CATEGORY_T');

PL/SQL procedure successfully completed.

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

no rows selected
OUTLN@orcl150> conn hr/hr;
Connected.
HR@orcl150> select * from t where object_id=258;

no rows selected

HR@orcl150> set autotrace traceonly explain
HR@orcl150> select * from t where object_id=258;

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |   158 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |   158 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_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 (level=2)
小结：如上所示，outline没清除前执行计划是全表扫描，清除后执行计划为索引范围扫描.
3、use_stored_outlines参数说明
USE_STORED_OUTLINES参数不像一般的参数可以在参数文件中设定，但可以使用常规方法对其进行修改.
ALTER SESSION SET USE_STORED_OUTLINES = TRUE | FALSE | category;
ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE | FALSE | category;

特别说明：
文章转载：http://blog.itpub.net/26736162/viewspace-2102180/，测试过程中与原文存在部分差异，建议查看原博文.

标签：00,outline,HR,258,orcl150,SQL,Oracle,id,Outline
From： https://blog.51cto.com/u_12991611/6101403

相关文章

  • SQL语句顺序
          查询的结构  SELECT执行顺序 ......
  • MySQL基本命令操作
    目录创建数据库删除数据库数据表的创建与管理删除数据表修改字段数据类型添加字段字段更名删除字段表数据管理插入数据查询数据模糊匹配排序与分组HAVING子句查询聚合函数......
  • SQL-排序与分页
    1.排序（ORDERBY）使用：ORDERBY对查询到的数据进行排序操作升序：ASC（ascend）默认不写升序降序：DESC（descend）SELECTemployee_id,salaryFROMemployeesORDERBYsalaryDES......
  • P8大佬的 62条SQL优化策略，太牛X了！！ 收藏起来有大用！！！
    背景说明：Mysql调优，是大家日常常见的调优工作。所以Mysql调优是一个非常、非常核心的面试知识点。在40岁老架构师尼恩的读者交流群(50+)中，其相关面试题是一个非常、非常......
  • SQLAlchemy
    SQLAlchemy简单介绍ORM较全的框架，可以使用ORM操作表，比较方便ORMObjectRelationMApping对象关系映射通过操作对象的方式来操纵表之间的关系一.单表操作......
  • ORACLE数据库高水位线(high water mark).
    来源：ORACLE数据库高水位线(highwatermark)-墨天轮(modb.pro) 文档课题：ORACLE数据库高水位线(highwatermark).1、相关概念ORACLE数据库逻辑结构包括：数据库块(bloc......
  • ruby postgresql 模糊搜索
    客户需求，通过name,email两列中的字段进行搜索过滤这项目之前用的​​pg_search​​​email的addresses是不能过滤的，例如[email protected],只能搜martin，搜163.com是搜不到的......
  • php 检测mysql表是否存在
    pdo:<?php$dsn='mysql:dbname=test;host=127.0.0.1';$user='root';$password='';try{$pdo=newPDO($dsn,$user,$password);}catch(PDOException$e){......
  • SQL笔记--MySQL高级操作
    MySQL高级SQL操作数据新增多数据插入蠕虫复制主键冲突查询数据查询选项别名数据源where子句groupby子句having子句orderby子句limit子句更新数据限制更新删除数据限制......
  • MySQL行列互转
    sudoservicemysqlstart--启动mysql服务mysql-uroot--使用root用户登录quit/exit--退出setnamesutf8;--设置传输编码,避免中文乱码sourced:/xxx.sql;--sou......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99