用SPM技术固定EBS标准功能的SQL执行计划

Introduction介绍

本文是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，那么其他的计划将不会再被加入进来。

（取自：Oracle使用SPM固定执行计划v1.pptx）

 

问题描述

最近发现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的额外性能消耗；另外，也可能会影响标准功能的性能（增加索引了，执行计划可能会改，可能因此走错执行计划而导致性能的问题）。

综上所述，并没有最完美的优化方式，只是需要从几个方案中找一个相对合理的，成本底风险低的优化方案。

本次优化完毕。