ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于执行计划稳定性(二)

关于执行计划稳定性(二)

原创 Linux操作系统 作者:NinGoo 时间:2019-07-05 22:27:04 0 删除 编辑

关于执行计划稳定性(一):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

已选择64行。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/193161/viewspace-50267/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2004-12-07

  • 博文量
    200
  • 访问量
    129156