oracle spm使用1，oraclespm使用
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
可以看到当启动optimizer_user_sql_plan_baselines时走索引应该是最优的却没有使用
plan_name accecpt 为 no 影响了走索引计划
譬如如下数据
id value
1 2
1 3
2 3
3 5
3 6
可以写个语句统计value的分组
select id,sum(value) from table group by id having sum(value)>=5
这样的结果就是
1 5
3 11
其实这句的意思就是
select id,sum(value) from table where sum(value)>=5 group by id
但是oracle中，计算字段没法当作条件来用，所以就用having 来表示
你的例子是count(*)也是一个道理的，当然什么avg,max,min之类的聚合函数也同样
你想要找表就找关键字，如果说你不知道表名的关键字。那么在表的上面操作也无非就那么几个，drop和create是不可能在包里处理的。你就查select、 update 和 delete，之后跟的就是表名，
这个是查包里用了多少个表，都是哪些表。
如果反过来查，一个表被多少个包，存贮过程等使用了，那就更简单了，用Toad for Oracle，在编辑器里打上表名后F4，点那个used by就OK了。
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。