执行计划绑定

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

http://www.mamicode.com/info-detail-1943333.html

需要绑定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
阅读原文内容投诉

执行计划绑定
下载Word文档到电脑，方便收藏和打印～

下载Word文档