现场反应一个sql很慢，发来了sql的awr：

现场发来的10:00到11:00的sql的awr报告，报告如下：

sql：

SELECT A.ORG_NO,

A.RELEASE_DATE,

A.MR_SECT_NO,

A.PAY_MODE,

A.AMT_TYPE,

(SELECT NAME FROM R_SECT　C WHERE C.MR_SECT_NO = A.MR_SECT_NO) MR_SECT_NO_NAME,

(SELECT OPERATOR_NO

FROM R_OPER_ACTIVITY

WHERE MR_SECT_NO = A.MR_SECT_NO

AND OPERATOR_NO IS NOT NULL

AND ACT_CODE = '03'

AND EFFECT_FLAG = '1'

AND ROWNUM = 1) OPERATOR_NO,

COUNT(DISTINCT A.CONS_NO) TOTALCONS,

SUM(T_PQ) T_PQ,

SUM(RCVBL_AMT) RCVBL_AMT,

SUM(RCVBL_INPRICE_AMT) RCVBL_INPRICE_AMT,

SUM(RCVBL_PL_AMT) RCVBL_PL_AMT

FROM V_CA_RCVBL_FLOW A, C_CONS C

WHERE A.ORG_NO = :B7

AND A.CONS_NO = C.CONS_NO

AND (:B6 IS NULL OR C.ELEC_TYPE_CODE = :B6)

AND A.RCVBL_YM = :B5

AND A.RELEASE_DATE = :B4

AND ((:B3 IS NULL AND

A.AMT_TYPE NOT IN

(SELECT T.PARA_VALUE

FROM A_PAY_CFG T

WHERE PARA_TYPE = '欠费统计过滤电费类别')) OR A.AMT_TYPE = :B3)

AND (:B2 IS NULL OR A.PAY_MODE = :B2)

AND (:B1 IS NULL OR A.MR_SECT_NO = :B1)

GROUP BY A.ORG_NO, A.RELEASE_DATE, A.MR_SECT_NO, A.PAY_MODE, A.AMT_TYPE

查看这个报表我们发现三个疑点：

1、执行计划中出现c_cons的全表扫描，sql整体的cost达到127K，为什么sql的每次buffer gets只有4349，sql每次的执行时间是0.192秒？

2、为什么sql的执行次数达到了138859？

3、为什么sql的执行计划会采用c_cons的全表扫描，再进行merge join，而不采用nested loop,cons_no的索引（这个索引是唯一索引）？

关于1的疑点：

经过现场多次测试，发现如果 V_CA_RCVBL_FLOW的结果集为0，那么buffer gets在10以下（实际上c_cons根本就没有进行全表扫描），而 V_CA_RCVBL_FLOW的结果集不为0，buffer gets会在5000以上（可以推测如果选择的单位的数据越多，buffer gets会越多），多次的执行平均下来就只有4349。

关于2的疑点：

查看储存过程 pkg_ca_rcvbl. p_rcvbl_release 发现针对查询窗口的选择，储存过程是循环执行的，比如说窗口上查询时间是20130401 到 20130431，那么储存过程里是分成30次进行查询，加上单位的循环，每一次的窗口查询都会执行这个sql多次，双循环导致了执行sql频率偏高。

关于3的疑点：

通过加入提示让现场测试，测试如下：

SQL> SELECT A.ORG_NO,

2 A.RELEASE_DATE,

3 A.MR_SECT_NO,

4 A.PAY_MODE,

5 A.AMT_TYPE,

6 (SELECT NAME FROM R_SECT C WHERE C.MR_SECT_NO = A.MR_SECT_NO) MR_SECT_NO_NAME,

7 (SELECT OPERATOR_NO

8 FROM R_OPER_ACTIVITY

9 WHERE MR_SECT_NO = A.MR_SECT_NO

10 AND OPERATOR_NO IS NOT NULL

11 AND ACT_CODE = '03'

12 AND EFFECT_FLAG = '1'

13 AND ROWNUM = 1) OPERATOR_NO,

14 COUNT(DISTINCT A.CONS_NO) TOTALCONS,

15 SUM(T_PQ) T_PQ,

16 SUM(RCVBL_AMT) RCVBL_AMT,

17 SUM(RCVBL_INPRICE_AMT) RCVBL_INPRICE_AMT,

18 SUM(RCVBL_PL_AMT) RCVBL_PL_AMT

19 FROM V_CA_RCVBL_FLOW A, C_CONS C

20 WHERE A.ORG_NO ='634010701'

21 AND C.ORG_NO = '634010701'

22 AND A.CONS_NO = C.CONS_NO

23 AND (NULL IS NULL OR C.ELEC_TYPE_CODE = NULL)

24 AND A.RCVBL_YM = '201304'

25 AND A.RELEASE_DATE = '20130403'

26 AND ((NULL IS NULL AND

27 A.AMT_TYPE NOT IN

28 (SELECT T.PARA_VALUE

29 FROM A_PAY_CFG T

30 WHERE PARA_TYPE = '欠费统计过滤电费类别')) OR A.AMT_TYPE = NULL)

31 AND (NULL IS NULL OR A.PAY_MODE = NULL)

32 AND (NULL IS NULL OR A.MR_SECT_NO = NULL)

33 GROUP BY A.ORG_NO, A.RELEASE_DATE, A.MR_SECT_NO, A.PAY_MODE, A.AMT_TYPE;

已选择8行。

我们看到ID是9的部分，view部分的cost突然变成了267K，前面我们看到merge join的cost是127K，所有oracle认为 merge join的执行计划更好。为什么view部分的cost突然变成了267K? 我们看到刚才awr报告中的view这里的cost就只有1？这到底是怎么回事呢？ bug？

还是统计信息不准？还是其他原因？看来我们需要深入的了解一下oracle计算cost的过程，以下是现场发过来的10053事件的部分内容：

PARAMETERS USED BY THE OPTIMIZER

********************************

*************************************

PARAMETERS WITH ALTERED VALUES

******************************

_pga_max_size = 1677720 KB

optimizer_index_cost_adj = 25

_optimizer_push_pred_cost_based = false

_optimizer_push_pred_cost_based = false，难道是这个参数引起的？为什么现场会修改这个隐含参数呢？

在session中修改"_optimizer_push_pred_cost_based"=TRUE，通过现场测试：

set autotrace traceonly

set linesize 250

alter session set "_optimizer_push_pred_cost_based"=TRUE;

我们看到现在的执行计划才是正常正确的执行计划。

关于为什么这个参数会被修改的原因是另外一个故障造成的。

_optimizer_push_pred_cost_based 这个参数的作用基于成本的谓词前置特性，通俗的说是查询视图的时候进行谓词推入过程的成本计算，如果为false，可能导致cost异常，跟我们的这个案例是比较符合的。

知道了原因，解决起来就比较方便了，只要在这个sql中加入提示opt_param('_optimizer_push_pred_cost_based','TRUE') 就可以了。

隐含参数的修改要慎重，再慎重啊，特别是针对优化器的隐含参数。

ITPub博客

_optimizer_push_pred_cost_based被修改引起一个sql执行计划突变

最新文章