记一个Enq: ss - contention性能问题处理

最近在忙上海南京广州深圳为ACS客户做技术分享，没太多时间更新公众号。接下来我会把这次技术分享里面一些案例写在公众号。今天是第一篇。

客户报业务处理突然变得非常慢，大量业务堆积，需要紧急处理。AWR显示主要等待事件为Enq: ss - contention：

根据Oracle 文档对该事件的描述：

When SMON is cleaning up very large dead transactions, it may not service other work like cleaning up temporary space, handling sort-segment requests or performing instance recovery.

检查数据库，果然存在一个大的update事务在回滚，而且预计还有很长一段时间才能回滚完成。

这种情况下，如何恢复当前业务的执行效率呢？

1、加速回滚，事务回滚完SMON就能正常管理sort segment了；

2、手工清理临时表空间：

alter session set events 'immediate trace name drop_segments level <TS number + 1>;

这两种方法是一个正常DBA的思维，非常正确。不过下次再遇到大事务回滚，还是会出现相同问题。

客户的运维DBA同时提供了一个受影响业务的SQL，并收集了sqlhc（SSC的客户都养成了这个好习惯），经过简单分析，发现这个问题从SQL优化的角度来解决效果会更佳。

对应的SQL，代码如下：

WITH TMP_RESULT AS

(SELECT A.ACCT_ID,

A.PRD_INST_ID,A.OFR_ID,

A.FEE_TYPE,A.ACCT_ITEM_TYPE_ID,

B.ACCT_ITEM_TYPE_NAME,A.FLUX,

A.RATE_DURATION,A.CHARGE

FROM V_DAT_M_21 A,

TB_BIL_ACCT_ITEM_TYPE B,

TB_PRD_PRD_INST_21 D

WHERE D.SERVICE_NBR = '888'

AND D.PRD_INST_ID = A.PRD_INST_ID

AND A.ACCT_ITEM_TYPE_ID = B.ACCT_ITEM_TYPE_ID

)

SELECT C.ACCT_ID,C.PRD_INST_ID,

C.OFR_ID,E.OFR_NAME,

C.FEE_TYPE,C.ACCT_ITEM_TYPE_ID,

C.ACCT_ITEM_TYPE_NAME,C.FLUX,

C.RATE_DURATION,C.CHARGE

FROM TMP_RESULT C, TB_PRD_OFR E

WHERE C.OFR_ID = E.OFR_ID AND C.OFR_ID <> 0

UNION

SELECT C.ACCT_ID,C.PRD_INST_ID,

C.OFR_ID,NULL OFR_NAME,

C.FEE_TYPE,C.ACCT_ITEM_TYPE_ID,

C.ACCT_ITEM_TYPE_NAME,C.FLUX,

C.RATE_DURATION,C.CHARGE

FROM TMP_RESULT C

WHERE C.OFR_ID = 0;

执行计划如下：

这种使用with的CTE（Common Table Expression）写法的SQL，在with对象被引用两次以上时，会先对命名对象生成一张临时表（如果包含字段包含lob字段则不会）写到临时表空间，这个过程叫materialize，后续使用该对象的时候直接从临时表中读取数据，不需要重复执行这段SQL。

这个SQL频繁执行，因此在SMON繁忙的时候产生了大量的Enq: ss - contention等待事件，执行效率由原来的1秒多，变成了当前的20秒，性能下降将近20倍。而其他SQL则几乎没有受到影响。

我们来分析一下这个SQL为什么会使用CTE：因为SQL用到的一个view：V_DAT_M_21，这个view是从db link获取数据，这个步骤消耗的时间最长，使用CTE的materialize就是为了避免这个view的两次调用。本来一个为了提高效率的优化操作，遇到了大事务回滚反而性能下降了很多。

分析到这里，我们又多了一个解决方案：用/*+ inline */hint ，告诉优化器不要做materialize，这样就避免了临时表的生成和临时表空间的使用，执行时间可能由1秒提高到2秒，但是也远比20秒要好很多。这个操作可以通过sql profile实现，不需要修改SQL代码。

我们再来看一下SQL逻辑，发现这个SQL可以通过一个外关联的等价改写来实现：

WITH TMP_RESULT AS

(SELECT A.ACCT_ID,A.PRD_INST_ID,

A.OFR_ID,A.FEE_TYPE,A.ACCT_ITEM_TYPE_ID,

B.ACCT_ITEM_TYPE_NAME,A.FLUX,

A.RATE_DURATION,A.CHARGE

FROM

V_DAT_M_21 A,

TB_BIL_ACCT_ITEM_TYPE B,

TB_PRD_PRD_INST_21 D

WHERE

D.SERVICE_NBR = '888'

AND D.PRD_INST_ID = A.PRD_INST_ID

AND A.ACCT_ITEM_TYPE_ID = B.ACCT_ITEM_TYPE_ID

)

SELECT distinct C.ACCT_ID,C.PRD_INST_ID,C.OFR_ID,

DECODE(C.OFR_ID ,0 , null , E.OFR_NAME ) as OFR_NAME,

C.FEE_TYPE,C.ACCT_ITEM_TYPE_ID,

C.ACCT_ITEM_TYPE_NAME,C.FLUX,

C.RATE_DURATION,

C.CHARGE

FROM TMP_RESULT C, TB_PRD_OFR E

WHERE C.OFR_ID = E.OFR_ID(+) ;

经过改写后，之前的几个方法就显得多余了。这个SQL既提升了执行效率，又完全不使用temp表空间，再也不需要担心大事务回滚的影响了。

总结：

一个问题可能有多个解决方法，深入分析，多思考一下，可能会发现更好的解决方法。SQL优化，带来的收益往往会超出预期。