本文是Oracle SPM技术的一个应用实例，分享给没了解过SPM或者没用过SPM的老铁们。通过本文，应该要了解什么是SPM，它的作用是什么，它的应用场景是什么。
这个应用实例总结就是：通过使用SPM技术，固定SQL的执行计划，从而实现调优的效果。它尤其适用于优化EBS系统的标准功能的场景，因为标准功能不建议也不可以直接修改SQL做性能调优。
SPM介绍：
Spm（SQL plan management）执行计划管理。
Spm可以管理执行计划，为sql创建baseline（基线），保证sql执行计划不会因为各种变化（优化器版本升级、系统统计信息变动、数据变动、测试环境到生产环境的迁移）产生大的变动。
简单理解就是使用spm可以：
1、固定一个sql的（一个或者多个）执行计划
2、将更好的执行计划加入（evolve）到基线中
注：如果一个sql有基线执行计划，那么优化器会优先考虑，如果基线中的某个执行计划fixed属性是yes，那么其他的计划将不会再被加入进来。
?
问题描述
最近发现EBS系统的库存组织参数设置功能有性能问题：新建的库存组织，默认组织参数都是无货位控制的。现在问题是，新建的库存组织要修改货位控制参数时候，都要卡掉渣，改一个库存组织卡至少30分钟以上。
问题分析
库存组织参数在修改货位控制的时候，需要发一个SQL查询发运明细表是否存在INV在接口未处理的数据。就是执行这个SQL的时候，特别慢。监控了一下，跑一次SQL需要约40分钟。
就是下面这条SQL，也是卡在这个SQL：
SELECT '1' FROM WSH_DELIVERY_DETAILS_OB_GRP_V?? WHERE ORGANIZATION_ID =? :b1? AND RELEASED_STATUS =? 'C'? AND INV_INTERFACED_FLAG IN ( 'N' , 'P' )? AND ROWNUM? <? 2接着，需要具体分析SQL的执行计划：
从执行计划可以看出来，主要用WSH_DELIVERY_DETAILS_N14索引，SQL执行时间需要2388秒。需要获取的buffer是2.13G。同时这条SQL的返回661K的数据量，明显是有性能问题的。因为新的库存组织是没什么数据量的才对。
根据上面的分析已经知道问题就是执行计划的问题，所以，总体的优化逻辑如下：
Stp1：先修改索引统计信息，通过这个方式让SQL走正确的执行计划。
由于是标准功能，无法直接修改SQL调整执行计划。所以只好用这个方式调整SQL的执行计划。
Stp2：接着，用Oracle的SPM技术，创建基线执行计划，让这个SQL固定执行计划即可。
如果索引更新统计信息，那优化器还是会用回优化之前的执行计划。为了让SQL能稳定调整之后的最优的执行计划，需要用到SPM技术固定执行计划。
具体步骤如下：
1、分析表的索引并确定SQL调优逻辑
先查看索引的情况：
WSH_DELIVERY_DETAILS_N14: INV_INTERFACED_FLAG 。目前SQL执行计划就走的这个索引。这个索引的数据量还是挺大的（因为是看所有库存组织的所有数据，不分库存组织）。
WSH_DELIVERY_DETAILS_N8: RELEASED_STATUS, ORGANIZATION_ID 。新的库存组织如果走这个索引查询，性能会极大提高。因为新库存组织没历史数据。
所以，结论是，正确的执行计划应该是走N8索引。
select idx.owner,idx.index_name,idx.DISTINCT_KEYS,idx.leaf_blocks,idx.blevel
,idx.clustering_factor,idx.NUM_ROWS,tab.BLOCKS
? from dba_indexes idx,all_tables tab
?where idx.TABLE_OWNER = tab.OWNER
?? and idx.TABLE_NAME = tab.TABLE_NAME
?? and idx.index_name IN ('WSH_DELIVERY_DETAILS_N14','WSH_DELIVERY_DETAILS_N8');
2、调整SQL的执行计划
要固定SQL执行计划，先决条件就是要数据库先用我们优化后的执行计划（执行SQL）。
所以，必须要调整索引的统计信息，引导CBO优化器，才可以让这个SQL能找到WSH_DELIVERY_DETAILS_N8这个索引执行。
这个才是很有技巧的一步骤。由于索引算cost的有自己的算法逻辑，我开始是直接调整WSH_DELIVERY_DETAILS_N8的索引统计信息，怎么调都调不低。
后来，只好调整WSH_DELIVERY_DETAILS_N14，将它cost调高，那SQL自然会选择N8来做执行计划了。
BEGIN
dbms_stats.set_index_stats(
?ownname => 'WSH'
,indname => 'WSH_DELIVERY_DETAILS_N14'
--,stattab => 'ZX_REC_NREC_DIST'
,numrows => 10?? --50539875
,numlblks => 10?? --94468
,numdist => 1?? --3
,NO_INVALIDATE => FALSE
,force => TRUE
);
END;然后，用之前的脚本看一下执行计划，发现已经走N8了：
? ? ? ? ? ? ? ? ? ?3、在ERP系统中执行一次这个SQL
注意：这个步骤需要到ERP中操作。不建议在toad里操作。因为SQL脚本只要有一点点差异的话，都是另外一个SQLID了。
然后查询：
SELECT * FROM XYG_ALD_SESS_SQL_V WHERE SQL_ID IN ('3mhdsuqgyscgr');
--SQL_TEXT = SELECT '1' FROM WSH_DELIVERY_DETAILS_OB_GRP_V?? WHERE ORGANIZATION_ID =? :b1? AND RELEASED_STATUS =? 'C'? AND INV_INTERFACED_FLAG IN ( 'N' , 'P' )? AND ROWNUM? <? 2??查看新的执行计划：
确实是用到理想中的：WSH_DELIVERY_DETAILS_N8 索引了。调整成功！
select * from table(dbms_xplan.display_cursor('3mhdsuqgyscgr','0','advanced -PROJECTION -bytes iostats,last'));
SQL_ID 3mhdsuqgyscgr, child number 0
-------------------------------------
SELECT '1' FROM WSH_DELIVERY_DETAILS_OB_GRP_V WHERE ORGANIZATION_ID =
:b1 AND RELEASED_STATUS = 'C' AND INV_INTERFACED_FLAG IN ( 'N' ,
'P' ) AND ROWNUM < 2
Plan hash value: 1278635995
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 14 (100)| |
|* 1 | COUNT STOPKEY | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| WSH_DELIVERY_DETAILS | 1 | 14 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | WSH_DELIVERY_DETAILS_N8 | 749K| 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
2 - SEL$F5BB74E1 / WSH_DELIVERY_DETAILS@SEL$2
3 - SEL$F5BB74E1 / WSH_DELIVERY_DETAILS@SEL$2
Outline Data
-------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 7212
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
2 - filter((INTERNAL_FUNCTION("INV_INTERFACED_FLAG") AND
(NVL("LINE_DIRECTION",'O')='O' OR NVL("LINE_DIRECTION",'O')='IO')))
3 - access("RELEASED_STATUS"='C' AND "ORGANIZATION_ID"=:B1)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
? ? ? ? ? ? ? ? ? ?4、用SPM技术固定SQL执行计划
执行下面脚本即可创建执行计划基线并固定。
DECLARE
?? my_plans PLS_INTEGER;
BEGIN
?? my_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '3mhdsuqgyscgr',fixed => 'YES');
END;---查看执行计划基线:
select sql_handle, plan_name, enabled, accepted, fixed from dba_sql_plan_baselines;
5、将索引的统计信息还原
现在，既然基线有了，那这时候可以将N14索引的统计信息改回来了。否则会影响别的SQL的执行计划。
6、最后要再确认执行计划的正确性
这时候再看执行计划，发现还是和之前的一样，走N8索引：
SELECT * FROM XYG_ALD_SESS_SQL_V WHERE SQL_ID IN ('3mhdsuqgyscgr');
注意！这时候可以看到执行计划后面多了这句：
SQL plan baseline SQL_PLAN_4b4bmstj2fqvuabe022c8 used for this statement
说明SPM起作用了。
select * from table(dbms_xplan.display_cursor('3mhdsuqgyscgr','0','advanced -PROJECTION -bytes iostats,last'));
?处理问题总结
一顿优化之后，我再到ERP操作，发现速度飞快，2~3秒就可以修改新的库存组织的货位控制参数了。
但是，值得注意的是，这个修改也并不是最完美的修改方式。
这个修改逻辑，对于新的库存组织，应该是会很有效果。因为新的库存组织实际上是没历史数据的，用N8（先搜索库存组织的发运数据）这个索引当然快了。
但是，如果是已经有一定数量的历史数据的库存组织，用这个索引，估计就会非常非常慢了。
缘由是，上面说到，这个N8索引本身会根据库存组织找RELEASED_STATUS='C'的数据来验证。如果历史数据数据量很多的话，查询肯定会很慢。例如SQB，CB这些库存组织。不过，反过来想，有历史数据的库存组织也不大可能会修改这个货位控制的设置，所以也还好。
那问题来了，有没有别的优化方式？
其实还是有的，只是有一定的成本和风险。就是新增一个库存组织+INV_INTERFACED_FLAG标志的索引即可。这样子基本上就可以直接优化这条SQL。不过，这个方式也并不完美。首先，增加索引，本身对数据库带来索引表空间的压力和基表的DML的额外性能消耗；另外，也可能会影响标准功能的性能（增加索引了，执行计划可能会改，可能因此走错执行计划而导致性能的问题）。
综上所述，并没有最完美的优化方式，只是需要从几个方案中找一个相对合理的，成本底风险低的优化方案。
本次优化完毕。