需要绑定SQL执行计划常见的几种情况：
SQL执行计划突变，导致数据库性能下降，从历史执行计划找一个合理的，进行绑定。
SQL无法使用更优的执行计划，且无历史执行计划，可通过hint手工构造的方式，进行绑定。
某些Bug引起优化器生成较差的执行计划。在bug修复前，进行绑定。
ORACLE固定执行计划的3种方式：
Oracle 9i使用outline （可跨版本10，11g均可使用）
Oracle 10g使用sql profile （11g也可使用）
Oracle 11g使用sql plan manage
接下来简述如何使用这3种方式进行执行计划的固定，并举例说明3种固定执行计划的优缺点，通过对比选择合适的固定执行计划来应对不同的业务场景。也就是什么场景下使用何种执行计划固定比较合适。
一、大纲（Stored Outline）
语法：(手动创建outline)
CREATE [ OR REPLACE ]
[ PUBLIC | PRIVATE ] OUTLINE [ outline ]
[ FROM [ PUBLIC | PRIVATE ] source_outline ]
[ FOR CATEGORY category ]
[ ON statement ] ;
1、当SQL执行计划因新版本变更，统计信息不准确，新建索引，参数改变等发生改变时，存储大纲可以使SQL语句的执行计划保持不变。在创建某条语句的大纲时，ORACLE会将SQL语句的文本，执行计划和语句使用的hints存储在一个系统默认用户OUTLN的3个表OL$，OL$HINTS，OL$NODES上。
2、使用大纲（outline）固定执行计划
--环境构建，建立测试表
SQL> create table zw as select * from dba_objects where object_id is not null;
Table created.
SQL> explain plan for select count(*) from zw;
Explained.
SQL> set lines 200
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 249608387
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 339 (1)| 00:00:05 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| ZW | 100K| 339 (1)| 00:00:05 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)
13 rows selected.
--查询数据字典dba_outlines：
SQL> select NAME,OWNER,CATEGORY,USED,SQL_TEXT,ENABLED,TIMESTAMP from dba_outlines;
no rows selected
--创建大纲(全表扫描)，默认是私有outline
SQL> create or replace outline zwoutline for category mycate on select count(*) from zw;
Outline created.
--再次查询dba_outlines;
col NAME for a10
col OWNER for a10
col CATEGORY for a10
col SQL_TEXT for a30
select NAME,OWNER,CATEGORY,USED,SQL_TEXT,ENABLED,TIMESTAMP from dba_outlines;
NAME OWNER CATEGORY USED SQL_TEXT ENABLED TIMESTAMP
---------- ---------- ---------- ------ ------------------------------ -------- -------------------
ZWOUTLINE SYS MYCATE UNUSED select count(*) from zw ENABLED 2017-09-02 15:36:33
--创建object_id列索引，将该列属性设置为非空
--索引不存储null值
SQL> alter table zw modify object_id not null;
Table altered.
SQL> create index idx_zw_obj_id on zw(object_id);
Index created.
SQL> analyze table zw compute statistics;
Table analyzed.
SQL> explain plan for select count(*) from zw;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1836624960
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 54 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_ZW_OBJ_ID | 100K| 54 (0)| 00:00:01 |
-------------------------------------------------------------------------------
--使用大纲固定执行计划：（alter system/session set use_stored_outlines=mycate;）系统级或会话级别；
SQL> alter system set use_stored_outlines=mycate;
System altered.
--查询dba_ouitlines(sql还未应用）
SQL> select NAME,OWNER,CATEGORY,USED,SQL_TEXT,ENABLED,TIMESTAMP from dba_outlines;
NAME OWNER CATEGORY USED SQL_TEXT ENABLED TIMESTAMP
---------- ---------- ---------- ------ ------------------------------ -------- -------------------
ZWOUTLINE SYS MYCATE UNUSED select count(*) from zw ENABLED 2017-09-02 15:36:33
--固定执行计划之后，就会按照创大纲时的执行计划去执行。
实际执行验证：（实际执行后就会应用outline）
SQL> select count(*) from zw;
COUNT(*)
----------
87036
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
SQL_ID 1f5n0rapts695, child number 0
-------------------------------------
select count(*) from zw
Plan hash value: 1836624960
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 54 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IDX_ZW_OBJ_ID | 87036 | 54 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / ZW@SEL$1
Outline Data
-------------
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
39 rows selected.
--查询dba_outlines
select NAME,OWNER,CATEGORY,USED,SQL_TEXT,ENABLED,TIMESTAMP from dba_outlines;
NAME OWNER CATEGORY USED SQL_TEXT ENABLED TIMESTAMP
---------- ---------- ---------- ------ ------------------------------ -------- -------------------
ZWOUTLINE SYS MYCATE UNUSED select count(*) from zw ENABLED 2017-09-02 15:54:31
上述的建立的大纲为公有大纲，为了不影响其它用户的使用，可以建立私有大纲如下：
create or replace private outline zwoutline2 for category mycate2 on select count(*) from zw;
思考：为什么我构建测试时，固定的是全表扫描，而不是比较优化的索引扫描？
其实这里我想说明的是outline的缺点是比较死板的，当创建新的索引，或者数据量大幅度变化时是无法做出相应改变的，也就是说它是固定死的。
关于outline具体参考如下链接：
http://blog.csdn.net/whiteoldbig/article/details/17210079
从10g起，可以通过引用共享池中已经存在的SQL语句来创建outline
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
软考中级精品资料免费领
历年真题答案解析
备考技巧名师总结
高频考点精准押题
- 资料下载
- 历年真题
193.9 KB下载数265
191.63 KB下载数245
143.91 KB下载数1148
183.71 KB下载数642
644.84 KB下载数2756
相关文章
发现更多好内容猜你喜欢
AI推送时光机Oracle执行计划绑定
数据库2024-04-02
oracle 11gSPM手动绑定执行计划
数据库2024-04-02
Oracle利用coe_load_sql_profile脚本绑定执行计划
数据库2024-04-02
oracle sqlprofile 固定执行计划，并迁移执行计划
数据库2024-04-02
oracle 固定执行计划
数据库2024-04-02
执行计划-1：获取执行计划
数据库2024-04-02
Oracle中怎么固定执行计划
数据库2024-04-02
Oracle DB 相关常用sql汇总7【手工绑定sql执行计划】
数据库2024-04-02
MySQL 5.7Explain执行计划
数据库2024-04-02
Oracle固定SQL的执行计划(二)---SPM
数据库2024-04-02
SQLServer的执行计划
数据库2023-05-16
【PG执行计划】Postgresql数据库执行计划统计信息简述
数据库2024-04-02
Oracle固定SQL的执行计划(一)---SQL Profile
数据库2024-04-02
MySQL执行计划详解
数据库2024-04-02
mongodb 执行计划说明
数据库2024-04-02
详解 MySQL 执行计划
数据库2022-05-10
mysql如何执行计划
数据库2024-04-02
Oracle查询执行计划
数据库2023-04-03
咦！没有更多了？去看看其它编程学习网 内容吧
