关于执行计划稳定性（二）

最新推荐文章于 2021-04-11 04:24:56 发布

congzhan1377

最新推荐文章于 2021-04-11 04:24:56 发布

阅读量117

点赞数

关于执行计划稳定性（一）：http://ningoo.itpub.net/post/2149/247475

关于执行计划稳定性（三）：http://ningoo.itpub.net/post/2149/247479

除了使用create or replace outline来创建outline保存执行计划外，也可以通过设置参数create_stored_oulines来保存session级的执行计划。

create_stored_outlines共有三个取值

true:自动创建outlines保存session中各查询的执行计划，并且保存在默认的名为default 的category中。
false:不自动创建outlines
categore_name:自动创建outlines保存session中各查询的执行计划，保存在名为category_name的category中。

1.试验环境

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

2.创建测试表

SQL> create table t as select * from all_objects;

表已创建。

3.设置create_stored_outlines参数

SQL> alter session set create_stored_outlines=mycategory;

会话已更改。

4.执行查询，其执行计划为全表扫描

SQL> set autot on exp
SQL> select count(*) from t;

COUNT(*)
----------
9789

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

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 8953 | 31 (0)| 00:00:01 |
-------------------------------------------------------------------

5.重新登陆

SQL> conn ning/ning
已连接。

6.创建index

SQL> create index ix_t on t(object_id);

索引已创建。

SQL> set autot on exp
SQL> select count(*) from t;

COUNT(*)
----------
9789

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

----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| IX_T | 8953 | 7 (0)| 00:00:01 |
----------------------------------------------------------------------

7.使用预先创建的mycategory，执行计划变回全表扫描

SQL> alter session set use_stored_outlines=mycategory;

会话已更改。

SQL> select count(*) from t;

COUNT(*)
----------
9789

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

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 11027 | 31 (0)| 00:00:01 |
-------------------------------------------------------------------

8.通过视图dba_outlines / all_outlines / user_outlines可以查看outlines的信息

SQL> select name,category,used from dba_outlines;

NAME CATEGORY USED
------------------------------ ------------------------------ ------
SYS_OUTLINE_07010322071753105 MYCATEGORY UNUSED
SYS_OUTLINE_07010322071743702 MYCATEGORY UNUSED
SYS_OUTLINE_07010322071743701 MYCATEGORY USED
SYS_OUTLINE_07010322071753103 MYCATEGORY UNUSED
SYS_OUTLINE_07010322071754606 MYCATEGORY UNUSED
SYS_OUTLINE_07010322071756208 MYCATEGORY UNUSED
SYS_OUTLINE_07010322071756207 MYCATEGORY UNUSED
SYS_OUTLINE_07010322071757811 MYCATEGORY UNUSED
SYS_OUTLINE_07010322071757809 MYCATEGORY UNUSED

已选择9行。

9.outlines实际上是通过一系列的hint来实现的，视图dba_outline_hints / all_outline_hints / user_outline_hints保存了相关信息。

SQL> select name,hint from dba_outline_hints;

NAME HINT
------------------------------ --------------------------------------------------------------------
SYS_OUTLINE_07010322071743701 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_07010322071743701 ALL_ROWS
SYS_OUTLINE_07010322071743701 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_07010322071743701 IGNORE_OPTIM_EMBEDDED_HINTS
SYS_OUTLINE_07010322071753105 FULL(@"SEL$F5BB74E1" "PLAN_TABLE"@"SEL$2")
SYS_OUTLINE_07010322071743702 FULL(@"SEL$F5BB74E1" "T"@"SEL$2")
SYS_OUTLINE_07010322071743702 OUTLINE(@"SEL$2")
SYS_OUTLINE_07010322071743702 OUTLINE(@"SEL$1")
SYS_OUTLINE_07010322071743702 MERGE(@"SEL$2")
SYS_OUTLINE_07010322071743702 OUTLINE_LEAF(@"SEL$F5BB74E1")
SYS_OUTLINE_07010322071743702 ALL_ROWS
SYS_OUTLINE_07010322071743702 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_07010322071743702 IGNORE_OPTIM_EMBEDDED_HINTS
SYS_OUTLINE_07010322071743701 FULL(@"SEL$1" "T"@"SEL$1")
SYS_OUTLINE_07010322071753105 OUTLINE(@"SEL$2")
SYS_OUTLINE_07010322071753105 OUTLINE(@"SEL$1")
SYS_OUTLINE_07010322071753105 MERGE(@"SEL$2")
SYS_OUTLINE_07010322071753105 OUTLINE_LEAF(@"SEL$F5BB74E1")
SYS_OUTLINE_07010322071753105 ALL_ROWS
SYS_OUTLINE_07010322071753105 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_07010322071753105 IGNORE_OPTIM_EMBEDDED_HINTS
SYS_OUTLINE_07010322071753103 FULL(@"DEL$1" "PLAN_TABLE"@"DEL$1")
SYS_OUTLINE_07010322071753103 OUTLINE_LEAF(@"DEL$1")
SYS_OUTLINE_07010322071753103 ALL_ROWS
SYS_OUTLINE_07010322071753103 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_07010322071753103 IGNORE_OPTIM_EMBEDDED_HINTS
SYS_OUTLINE_07010322071754606 FULL(@"SEL$4A78348A" "KOKBF$"@"SEL$E112F6F0")
SYS_OUTLINE_07010322071754606 OUTLINE(@"SEL$E112F6F0")
SYS_OUTLINE_07010322071754606 OUTLINE(@"SEL$1")
SYS_OUTLINE_07010322071754606 MERGE(@"SEL$E112F6F0")
SYS_OUTLINE_07010322071754606 OUTLINE_LEAF(@"SEL$4A78348A")
SYS_OUTLINE_07010322071754606 ALL_ROWS
SYS_OUTLINE_07010322071754606 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_07010322071754606 IGNORE_OPTIM_EMBEDDED_HINTS
SYS_OUTLINE_07010322071756208 FULL(@"SEL$F5BB74E1" "PLAN_TABLE"@"SEL$2")
SYS_OUTLINE_07010322071756208 OUTLINE(@"SEL$2")
SYS_OUTLINE_07010322071756208 OUTLINE(@"SEL$1")
SYS_OUTLINE_07010322071756208 MERGE(@"SEL$2")
SYS_OUTLINE_07010322071756208 OUTLINE_LEAF(@"SEL$F5BB74E1")
SYS_OUTLINE_07010322071756208 ALL_ROWS
SYS_OUTLINE_07010322071756208 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_07010322071756208 IGNORE_OPTIM_EMBEDDED_HINTS
SYS_OUTLINE_07010322071756207 FULL(@"SEL$1" "PLAN_TABLE"@"SEL$1")
SYS_OUTLINE_07010322071756207 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_07010322071756207 ALL_ROWS
SYS_OUTLINE_07010322071756207 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_07010322071756207 IGNORE_OPTIM_EMBEDDED_HINTS
SYS_OUTLINE_07010322071757811 FULL(@"SEL$F5BB74E1" "PLAN_TABLE"@"SEL$2")
SYS_OUTLINE_07010322071757811 OUTLINE(@"SEL$2")
SYS_OUTLINE_07010322071757811 OUTLINE(@"SEL$1")
SYS_OUTLINE_07010322071757811 MERGE(@"SEL$2")
SYS_OUTLINE_07010322071757811 OUTLINE_LEAF(@"SEL$F5BB74E1")
SYS_OUTLINE_07010322071757811 ALL_ROWS
SYS_OUTLINE_07010322071757811 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_07010322071757811 IGNORE_OPTIM_EMBEDDED_HINTS
SYS_OUTLINE_07010322071757809 FULL(@"SEL$2" "PLAN_TABLE"@"SEL$2")
SYS_OUTLINE_07010322071757809 FULL(@"SEL$1" "PLAN_TABLE"@"SEL$1")
SYS_OUTLINE_07010322071757809 OUTLINE(@"SEL$1")
SYS_OUTLINE_07010322071757809 OUTLINE(@"SEL$2")
SYS_OUTLINE_07010322071757809 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_07010322071757809 OUTLINE_LEAF(@"SEL$2")
SYS_OUTLINE_07010322071757809 ALL_ROWS
SYS_OUTLINE_07010322071757809 OPTIMIZER_FEATURES_ENABLE('10.2.0.1')
SYS_OUTLINE_07010322071757809 IGNORE_OPTIM_EMBEDDED_HINTS