oracle spm使用1
来源:互联网 发布:php和java哪个好 编辑:程序博客网 时间:2024/04/28 08:34
oracle11g new feature SPM
有助于保持sql的语句特性,只允许执行性能提高的执行计划。
它不同于stored outlines, spm在于稳定sql性能,而store outlines在于冻结sql执行计划
事列
1.启用optimizer_cature_sql_plan_baselines 捕获sql语句
SQL> alter session set optimizer_capture_sql_plan_baselines = true;
Session altered.
SQL> select * from objs where object_id = 2;
no rows selected
SQL> select * from objs where object_id = 2;
no rows selected
SQL> alter session set optimizer_capture_sql_plan_baselines = false;
Session altered.
2.查询dba_sql_plan_baselines,确定sql的spm状态
SQL> select plan_name, sql_handle, enabled, accepted, fixed,
2 module, sql_text
3 from dba_sql_plan_baselines;
PLAN_NAME SQL_HANDLE ENABLED ACCEPTED FIXED MODULE SQL_TEXT
------------------------------ -------------------- --------- --------- --------- -------------------- --------------------------------------------------
SQL_PLAN_4pzq3z6xcqkpwd0984253 SQL_4afec3f9bacb4abc YES YES NO SQL*Plus select * from objs where object_id = 2
3.增加索引改变环境再运行sql
SQL> alter session set optimizer_capture_sql_plan_baselines = true;
Session altered.
SQL> select * from objs where object_id = 2;
no rows selected
SQL> select * from objs where object_id = 2;
no rows selected
SQL> alter session set optimizer_capture_sql_plan_baselines = false;
Session altered.
4.查询dba_sql_plan_baselines
SQL> select plan_name, sql_handle, enabled, accepted, fixed, module, sql_text from dba_sql_plan_baselines;
PLAN_NAME SQL_HANDLE ENABLED ACCEPTED FIXED MODULE SQL_TEXT
------------------------------ -------------------- --------- --------- --------- -------------------- --------------------------------------------------
SQL_PLAN_4pzq3z6xcqkpwbe4c314c SQL_4afec3f9bacb4abc YES NO NO SQL*Plus select * from objs where object_id = 2
SQL_PLAN_4pzq3z6xcqkpwd0984253 SQL_4afec3f9bacb4abc YES YES NO SQL*Plus select * from objs where object_id = 2
SQL>
5.测试
SQL>
SQL> set autotrace on
SQL> select * from objs where object_id = 2;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 954894094
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 247 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| OBJS | 1 | 98 | 247 (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=2)
Note
-----
- SQL plan baseline "SQL_PLAN_4pzq3z6xcqkpwd0984253" used for this statement
Statistics
----------------------------------------------------------
255 recursive calls
0 db block gets
1044 consistent gets
879 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
28 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> alter session set optimizer_use_sql_plan_baselines = false;
Session altered.
SQL> select * from objs where object_id = 2;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 613004408
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| OBJS | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJSID_OBJS | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
4 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
有助于保持sql的语句特性,只允许执行性能提高的执行计划。
它不同于stored outlines, spm在于稳定sql性能,而store outlines在于冻结sql执行计划
事列
1.启用optimizer_cature_sql_plan_baselines 捕获sql语句
SQL> alter session set optimizer_capture_sql_plan_baselines = true;
Session altered.
SQL> select * from objs where object_id = 2;
no rows selected
SQL> select * from objs where object_id = 2;
no rows selected
SQL> alter session set optimizer_capture_sql_plan_baselines = false;
Session altered.
2.查询dba_sql_plan_baselines,确定sql的spm状态
SQL> select plan_name, sql_handle, enabled, accepted, fixed,
2 module, sql_text
3 from dba_sql_plan_baselines;
PLAN_NAME SQL_HANDLE ENABLED ACCEPTED FIXED MODULE SQL_TEXT
------------------------------ -------------------- --------- --------- --------- -------------------- --------------------------------------------------
SQL_PLAN_4pzq3z6xcqkpwd0984253 SQL_4afec3f9bacb4abc YES YES NO SQL*Plus select * from objs where object_id = 2
3.增加索引改变环境再运行sql
SQL> alter session set optimizer_capture_sql_plan_baselines = true;
Session altered.
SQL> select * from objs where object_id = 2;
no rows selected
SQL> select * from objs where object_id = 2;
no rows selected
SQL> alter session set optimizer_capture_sql_plan_baselines = false;
Session altered.
4.查询dba_sql_plan_baselines
SQL> select plan_name, sql_handle, enabled, accepted, fixed, module, sql_text from dba_sql_plan_baselines;
PLAN_NAME SQL_HANDLE ENABLED ACCEPTED FIXED MODULE SQL_TEXT
------------------------------ -------------------- --------- --------- --------- -------------------- --------------------------------------------------
SQL_PLAN_4pzq3z6xcqkpwbe4c314c SQL_4afec3f9bacb4abc YES NO NO SQL*Plus select * from objs where object_id = 2
SQL_PLAN_4pzq3z6xcqkpwd0984253 SQL_4afec3f9bacb4abc YES YES NO SQL*Plus select * from objs where object_id = 2
SQL>
5.测试
SQL>
SQL> set autotrace on
SQL> select * from objs where object_id = 2;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 954894094
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 247 (1)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| OBJS | 1 | 98 | 247 (1)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=2)
Note
-----
- SQL plan baseline "SQL_PLAN_4pzq3z6xcqkpwd0984253" used for this statement
Statistics
----------------------------------------------------------
255 recursive calls
0 db block gets
1044 consistent gets
879 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
28 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> alter session set optimizer_use_sql_plan_baselines = false;
Session altered.
SQL> select * from objs where object_id = 2;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 613004408
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| OBJS | 1 | 98 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_OBJSID_OBJS | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
4 physical reads
0 redo size
1343 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
可以看到当启动optimizer_user_sql_plan_baselines时走索引应该是最优的却没有使用
plan_name accecpt 为 no 影响了走索引计划
2 0
- oracle spm使用1
- 《基于ORACLE SQL优化》读书笔记-使用SPM稳定执行计划
- spm使用之三spm应用实例
- 使用 spm 和 SeaJS
- oracle SPM 执行计划管理
- seajs的spm使用摸索
- seajs的spm使用摸索
- seajs的spm使用摸索
- 使用spm构建seajs项目
- 使用SPM绑定执行计划
- spm使用之二兼谈spm的贱格
- spm,afni的安装和使用心得
- 使用spm固定sql执行计划
- seajs 和spm的使用简介
- spm使用之五修改spm自带文档主题模板
- spm使用之六安装别人写好的spm文档主题模板
- spm使用之八谈谈spm doc watch的调用过程
- Oracle SPM(SQL Plan Management)介绍及演示SQL
- 明升资料整理
- 火狐与IE的7个JavaScript差异
- 送给想转产品的人之一 哪些人适合做产品
- 德国国旗用代码实现
- 数据结构----栈(静态数组)
- oracle spm使用1
- WCF教程-讲的很详细
- adb devices error or offline
- MVC 路由反向排除
- Z变换与傅里叶变换
- bzoj2753: [SCOI2012]滑雪与时间胶囊 MST
- IBM Cognos BI 最佳实践: 定制 IBM Cognos 8 UI
- 简单的C/S聊天室
- Ora-14074 When Trying To Add Partition