ORACLE使用STORED OUTLINE固化执行计划--私有和公有

最新推荐文章于 2023-09-21 15:33:09 发布
最新推荐文章于 2023-09-21 15:33:09 发布
阅读量2.3k 收藏 3
点赞数 1
分类专栏： Oracle Tuning 文章标签： oracle stored outline
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yidian815/article/details/17781293
版权

利用存储纲要可以固定sql执行计划，存储纲要有一系列与sql语句相关的optimizer hints构成。当存储纲要可用是，oracle会自动根据这些hint生成对应sql语句的执行计划。存储纲要可以分为私有和公有，同时我们可以对存储纲要划分不同的类别（categories），并指定那一类存储纲要发挥作用，这无疑会方便dba的管理工作。存储纲要一旦存储，就不会随后续系统配置或者统计信息的变化而变化。（oracle 11g虽然支持存储纲要，但是oracle已经不建议使用存储纲要，而是使用SPM).

公有和私有

oracle可以创建公有的存储纲要，也可以创建私有的存储纲要。具有的语法格式如下：

创建公有存储纲要，相关信息会存放在outln用中，创建私有纲要时，相关信息会存放在当前用户模式下，因此当前用户需要具有DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES的执行权限，以便创建相关对象（这不是必须的因为从10.1之后，会在system下创建临时表对象来存储私有的存储纲要）。

SQL> show user
USER 为 "OUTLN"
SQL> l
  1* select object_name,object_type from user_objects order by object_type
SQL> /

OBJECT_NAME		       OBJECT_TYPE
------------------------------ -------------------
OL$NAME 		       INDEX
OL$NODE_OL_NAME 	       INDEX
OL$SIGNATURE		       INDEX
OL$HNT_NUM		       INDEX
SYS_IL0000000451C00021$$       INDEX
SYS_LOB0000000451C00021$$      LOB
ORA$GRANT_SYS_SELECT	       PROCEDURE
OL$HINTS		       TABLE
OL$NODES		       TABLE
OL$			       TABLE

已选择10行。
从这里可以看出，公共存储纲要信息主要存放在OL$,OL$HINTS,OL$NODES三张表中。

首先，我们来创建一个公共纲要，创建纲要需要具有CREATE ANY OUTLINE权限

SQL> create public outline outline1 on select count(*) from t1 where object_id < 100;
create public outline outline1 on select count(*) from t1 where object_id < 100
                                                       *
第 1 行出现错误:
ORA-18005: 此操作需要 CREATE ANY OUTLINE 权限


SQL> create public outline outline1 on select count(*) from t1 where object_id < 100;

大纲已创建。

要是存储纲要发挥作用，需要在system或者session级别设置use_stored_outlines参数 

SQL> set autotrace on explain
SQL> select count(*) from t1 where object_id < 100;

  COUNT(*)
----------
	98


执行计划
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |    13 |   295   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |	  |	1 |    13 |	       |	  |
|*  2 |   TABLE ACCESS FULL| T1   |    12 |   156 |   295   (1)| 00:00:04 |
---------------------------------------------------------------------------

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

   2 - filter("OBJECT_ID"<100)

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> create index i1 on t1(object_id);

索引已创建。

SQL> select count(*) from t1 where object_id < 100;

  COUNT(*)
----------
	98


执行计划
----------------------------------------------------------
Plan hash value: 3900446664

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |	 |     1 |    13 |	      | 	 |
|*  2 |   INDEX RANGE SCAN| I1	 |    98 |  1274 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"<100)

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> alter session set use_stored_outlines=true;

会话已更改。

SQL> select count(*) from t1 where object_id < 100;

  COUNT(*)
----------
	98


执行计划
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |    13 |   295   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |	  |	1 |    13 |	       |	  |
|*  2 |   TABLE ACCESS FULL| T1   |  4411 | 57343 |   295   (1)| 00:00:04 |
---------------------------------------------------------------------------

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

   2 - filter("OBJECT_ID"<100)

Note
-----
   -- outline "OUTLINE1" used for this statement
在system级别设置use_stored_outlines为true后， 

SQL> show user
USER 为 "EASY2"
SQL> select count(*) from t1 where object_id < 100;

  COUNT(*)
----------
	98


执行计划
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation	   | Name | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |	  |	1 |    13 |   295   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |	  |	1 |    13 |	       |	  |
|*  2 |   TABLE ACCESS FULL| T1   |  4411 | 57343 |   295   (1)| 00:00:04 |
---------------------------------------------------------------------------

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

   2 - filter("OBJECT_ID"<100)

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

下面创建私有的存储纲要，创建私有纲要前可以执行如下语句来在当前模式下创建相关对象 

SQL> exec DBMS_OUTLN_EDIT.CREATE_EDIT_TABLES;

PL/SQL 过程已成功完成。

SQL> select * from tab;

TNAME			       TABTYPE	CLUSTERID
------------------------------ ------- ----------
OL$			       TABLE
OL$HINTS		       TABLE
OL$NODES		       TABLE
T1			       TABLE
SQL> select count(*) from OL$;

  COUNT(*)
----------
	 0
SQL> create private outline outline2 on select count(*) from t1 where object_id < 100;

大纲已创建。

SQL> select count(*) from OL$;

  COUNT(*)
----------
	 0

SQL> select count(*) from SYSTEM.OL$;

  COUNT(*)
----------
	 1
SESSION 1 

SQL> select count(*) from t1 where object_id < 100;

  COUNT(*)
----------
	98


执行计划
----------------------------------------------------------
Plan hash value: 3900446664

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |     1 |    13 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |	 |     1 |    13 |	      | 	 |
|*  2 |   INDEX RANGE SCAN| I1	 |  4411 | 57343 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"<100)

Note
-----
   - outline "OUTLINE2" used for this statement
SESSION 2： 

SQL> alter session set use_private_outlines=TRUE;

会话已更改。

SQL> select count(*) from t1 where object_id < 100;

  COUNT(*)
----------
	98


执行计划
----------------------------------------------------------
Plan hash value: 3900446664

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |	 |     1 |    13 |	      | 	 |
|*  2 |   INDEX RANGE SCAN| I1	 |    98 |  1274 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   2 - access("OBJECT_ID"<100)

Note
-----
   - dynamic sampling used for this statement (level=2)


总结：oracle的存储纲要可以分为公有和私有，公有纲要的相关信息存放在OUTLN用户中，私有公有的相关信息存放在SYSTEM下的临时表中；由于私有纲要存放在临死表中，因此只能再当前session中使用，使用私有纲要需要设置USE_PRIVATE_OUTLINES（system或者session级别，主要这不是初始化参数），同样使用公有纲要需要设置USE_STORED_OUTLINES；











ezbit
关注
  • 1
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
第四节 存储过程（stored procedures）2---马克-to-win java视频
06-11
第四节 存储过程（stored procedures）2---马克-to-win java视频
oracle如何更改执行计划扫描,如何替换/固化执行计划？
weixin_42615743的博客
04-03 304
没见效果，请帮忙看看哪里有问题？--1这是使用了提示的执行计划，可以看到，对　T_BD_LOTMASTER　走分区裁剪扫描KDSA918@clouddb_1>explain plan for2SELECT * FROM (SELECT /*+ no_index(T0) */t0.FNUMBER FNUMBER, t0_L.FNAME FNAME, t0.FLOTID FLOTIDFR...
oracle存储提纲（stored outline）
数据库天地
06-28 118
oracle存储提纲（stored outline）用来提供稳定的执行计划。从oracle 11g开始，逐渐被sql计划继续取代。下面是存储提纲的具体过程，结果在oracle 11g r2版本测试--创建测试表和索引 create table oln_test as select * from dba_tables; set autotrace on; SQL&gt; create inde...
执行计划绑定方法
Du.的博客
01-27 3651
固化执行计划的方法包括profile、baseline、ouline的方式，常用的方式baseline、outline，可以使用sqlt工具中包含的sql脚本来绑定（coe_load_sql_profile.sql、coe_xfr_sql_profile.sql、coe_load_sql_baseline.sql） 1、情景1--不跨库绑定profile 同实例里不同/相同的sql_id绑定同一个执行计划（plan_hash_value），使用coe_load_sql_profile.sql脚本实现
Oracle数据库固定执行计划的方法总结
rishairu1的博客
03-24 2384
Oracle数据库中执行sql的时候，优化器会根据优化器统计信息和一些参数来生成“它认为最好的“执行计划。 但是并不能够保证每一次都会选择最优的哪个执行计划。如果遇到了sql在一定时间段执行的时候性能变差。 并且发现它的执行计划发生了变化，那么可以考虑固定哪个”好的”执行计划。 当然，必须保证sql语句没有被修改，是同一个sql。 1. SQL Plan Management （SQL计划管理，简称SPM） 这是Oracle推荐使用的一种方法。使用起来也很简单。以下是一个手动固定的例子。 cr
第四节 存储过程（stored procedures）3---马克-to-win java视频
06-11
第四节 存储过程（stored procedures）3---马克-to-win java视频
spring-oracle-stored-proc:SpringBoot Oracle存储过程演示项目
05-12
用于执行Oracle存储过程的SpringBoot演示项目前提条件这需要Oracle DB。 请按照以旋转Oracle docker映像。 或者，您可以使用 ... 必须明确设置以下JPA属性spring : application : name : spring-oracle-stored-proc
商业编程-源码-使用ADO处理存储过程源代码 stored_proc_using_ado.zip
06-23
商业编程-源码-使用ADO处理存储过程源代码 stored_proc_using_ado.zip
what-stored-in-a-mobile-device-server
07-06
移动设备服务器中存储的内容 开发服务器在，在大学的服务器上。 执照 This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as ...
oracle11g执行计划管理-(讲解如何固定sql的执行计划)
06-21
摘要：本文描述了11g的新特性之一：执行计划管理，介绍了引入该新特性的原因，以及该新特性的相关特点、工作原理等。最后通过引入一个测试案例来介绍如何使用该新特性。 分析了如何固定优化过的执行计划，避免被sql优化器CBO覆盖
oracel 固化索引
Brady74的博客
12-04 604
有一张联系人表,名字叫做：contact_info 表里面有个索引名字叫做：IDX_ACC_MOBILE 根据电话号码来查询人员信息： 使用固化索引： select /* index(tIDX_ACC_MOBILE) */ t.name, t.age, t.mobile fromcontact_info t where t.mobile = '1234596' 如果删除： ...
Oracle 固定SQL执行计划的方法
loveLAxin的博客
05-30 1422
分为两种：自动(Automatic)类型的SQL Profile、手动(Manual)类型的SQL Profile。
Oracle 固定执行计划
最新发布
App20134833944的博客
09-21 129
Oracle数据库中执行sql的时候，优化器会根据优化器统计信息和一些参数来生成“它认为最好的“执行计划。但是并不能够保证每一次都会选择最优的哪个执行计划。如果遇到了sql语句在一定时间段执行时性能变差，这是Oracle推荐使用的一种方法。以下是一个手动固定的例子。并且发现它的执行计划发生了变化，那么可以考虑固定哪个”好的”时候的执行计划。当然，必须保证sql语句没有被修改，是同一个sql。
ORACLE 执行计划绑定
m0_49929446的博客
03-01 1777
Oracle 绑定 执行计划
oracle稳定执行计划
lltfjsy的博客
08-08 822
oracle稳定执行计划
oracle sql调优 执行计划固化 排序等 SQL Plan Baseline
yingmu3630的专栏
11-14 934
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id...
【杂】sql学习
yyins的博客
08-23 212
sql相关
【Oracle优化】通过SQL_PROFILE固化执行计划
个人公众号：Alen的数据库学习笔记
05-04 2191
在生产环境中，随着数据量的变化、查询条件的不同，有时执行计划发生了突变而导致SQL性能急剧下降。此时如果调整代码，或者修改业务逻辑，一方面时间上可能来不及，另一方面可能成本较高，那么有没有办法，可以在不调整代码或业务逻辑的情况下，让SQL语句使用计划突变之前的执行计划呢？本篇将介绍通过SQL_PROFILE来固化执行计划的三种方法。 一 绑定已存在的执行计划 DECLARE v_ar_pr...
ros noetic moveit中的Warehouse - Persistent Scenes and States功能和使用方法
06-09
在ROS Noetic的MoveIt中，Warehouse是一个用于存储和检索MoveIt相关数据的工具。其中，Persistent Scenes and States功能允许用户将场景和机器人状态保存到Warehouse中，并在需要时检索它们。 使用Persistent ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值