问题说明：   

业务人员反馈系统跑批慢了，平时耗时5分钟，现在需要跑3个多小时，而且是每月10日和每月15日都会变慢。

环境说明：

DB:Oracle 11.2.0.4.0 RAC
OS:AIX 7.1

问题分析:

抓取跑批对应的慢SQL,查看SQL文本如下：

select  '2021/08/15',
         RelativeDeductaccno,
         RelativeDeductaccno,
         LB.Deductaccno,
         lb.putoutno,
         LB.Customerid,
         LB.Customername,
         SI.ManageOrgID,
         0,
         LB.Normalbalance + LB.Overduebalance + LB.Waitoverduebalance AS balance,
         LB.Normalbalance + LB.Overduebalance + LB.Waitoverduebalance AS Actualbalance,
         SaveBeginSum * 10000,
         LoanBeginSum * 10000,
         SaveStandardSum * 10000,
         LoanStandardSum * 10000,
         nvl(ImpawnRatio1, 0),
         nvl(ImpawnRatio2, 0),
         nvl(ImpawnRatio3, 0),
         nvl(ImpawnRatio4, 0),
         IncomeBase * 10000,
         LB.Executerate / (30 * 1000),
         LB.Loanrate / (30 * 1000),
         case
           when LB.Executerate < LB.Loanrate then
            LB.Executerate / (30 * 1000)
           else
            LB.Loanrate / (30 * 1000)
         end,
         SI.SaveRate / 1000,
         LB.maturitydate,
         IncomeReturnDay,
         0,
         '0',
         '1',
         MainSaveToLoanFlag,
         case
           when LB.LoanStatus <= '1' then
            1
           else
            0
         end as LoanStatus,
         lb.assetflag,
         lb.businesstype,
         nvl(case
               when cjc_fun_xxxxxxx(LB.putoutno, '2021/06/15') > 0 then
                cjc_fun_xxxxxxx(LB.putoutno, '2021/06/15')
               else
                0
             end,
             0),
         case
           when nvl(LB.assetflag, 0) = '1' and
                cjc_fun_xxxxxxx(LB.putoutno, '2021/06/15') > 0 then
            nvl(cjc_fun_xxxxxxx(LB.putoutno, '2021/06/15'), 0)
           else
            0
         end
    from cjcaaaaaaa_info SI, chen_balance LB
   where SI.putoutno = LB.putoutno
     and SI.Validdate <= '2021/08/15'
     and Status = '1'
     and ACCOUNTFLAG = '1'

手动执行，查看速度：

返回前100条记录很快，之后平均每10秒取出100行数据，最终取出全部结果集耗时很长。

查看执行计划：

   PLAN_TABLE_OUTPUT
......
20 
21------------------------------------------------------------------------------------------------
22| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
23------------------------------------------------------------------------------------------------
24|   0 | SELECT STATEMENT             |                 |       |       | 14354 (100)|          |
25|   1 |  NESTED LOOPS                |                 |  6966 |  1367K| 14354   (1)| 00:00:01 |
26|   2 |   NESTED LOOPS               |                 |  6966 |  1367K| 14354   (1)| 00:00:01 |
27|*  3 |    TABLE ACCESS FULL         | cjcaaaaaaa_info |  6966 |   666K|   418   (1)| 00:00:01 |
28|*  4 |    INDEX UNIQUE SCAN         | chen_balance_PK |     1 |       |     1   (0)| 00:00:01 |
29|   5 |   TABLE ACCESS BY INDEX ROWID| chen_balance    |     1 |   103 |     2   (0)| 00:00:01 |
30------------------------------------------------------------------------------------------------
31 
32Predicate Information (identified by operation id):
33---------------------------------------------------
34 
35   3 - filter(("ACCOUNTFLAG"='1' AND "STATUS"='1' AND "SI"."VALIDDATE"<='2021/08/15'))
36   4 - access("SI"."PUTOUTNO"="LB"."PUTOUTNO")
37

查看执行计划，可以看到，即使cjcaaaaaaa_info走了全表扫描，cost也很低，预估的时间也很短。

难道是cjcaaaaaaa_info表统计信息不准确？

检查后发现表统计信息是准确的，cjcaaaaaaa_info数据量很小。

cjcaaaaaaa_info和chen_balance表关联关系很简单，where谓词条件也不复杂，那么是什么原因导致的SQL执行慢呢？

显然当前的cjcaaaaaaa_info和chen_balance关联采用NESTED LOOPS已经是最优的，尝试添加hint强制hash join速度更慢了。

既然表关联方式没问题，表访问路径没问题，还有可能哪块有问题呢？

仔细检查了SQL，发现查询的列有一处 可疑的地方：

......
nvl(case
               when cjc_fun_xxxxxxx(LB.putoutno, '2021/08/15') > 0 then
                cjc_fun_xxxxxxx(LB.putoutno, '2021/08/15')
               else
                0
             end,
             0),
         case
           when nvl(LB.assetflag, 0) = '1' and
                cjc_fun_xxxxxxx(LB.putoutno, '2021/08/15') > 0 then
            nvl(cjc_fun_xxxxxxx(LB.putoutno, '2021/08/15'), 0)
......

此处的cjc_fun_xxxxxxx看上去像是一个function，查看function的定义：

select dbms_metadata.get_ddl('FUNCTION','cjc_fun_xxxxxxx','CHENJ3') from dual;

函数部分由IF和ELSE两部分组成，每部分包含多个SELECT查询操作。

那么SQL执行慢，是否和cjc_fun_xxxxxxx函数有关呢？

注释掉原SQL中包含cjc_fun_xxxxxxx函数部分，再次执行SQL，速度恢复正常，不超过5分钟执行完成。

单独进行函数部分测试：

单独执行函数，速度很慢，每10秒返回100条记录

select case
         when nvl(LB.assetflag, 0) = '1' and
              cjc_fun_xxxxxxx(LB.putoutno, '2021/08/15') > 0 then
          nvl(cjc_fun_xxxxxxx(LB.putoutno, '2021/08/15'), 0)
         else
          0
       end
  from cjcaaaaaaa_info SI, chen_balance LB
 where SI.putoutno = LB.putoutno
   and SI.Validdate <= '2021/08/15'
   and Status = '1'
   and ACCOUNTFLAG = '1'

此时问题比较清晰了，就是因为cjc_fun_xxxxxxx函数部分导致SQL查询速度慢，那么为什么只有每月10号和每月15日速度慢呢？

主要是因为原SQL包含case when部分，当每月10号和每月15日时，cjc_fun_xxxxxxx函数部分执行的次数更多。

cjc_fun_xxxxxxx函数对性能究竟有多大的影响？

在满足sAssetFlag = '1'条件时，函数会执行8条select语句，并将结果集进行加和后返回。

在不满足sAssetFlag = '1'条件时，函数会执行14条select语句，并将结果集进行加和后返回。

并且除了执行的select次数不同外，执行的select语句也是不一样的，也就是在sAssetFlag值不同时，即使执行相同次数cjc_fun_xxxxxxx函数，执行时间也不同。

  综合以上两点，SQL执行时间取决于函数执行次数，和单次函数执行的逻辑有关。

例如：

在最极端的情况下，查询的每条语句都会调用4次函数，每次函数执行14个select语句，在查询60000条数据时，后台实际会执行 336万条select语句。

解决方案：

和业务人员沟通，cjc_fun_xxxxxxx函数不能在优化了，但是可以使用中间表代替。
例如，跑批前提前单独执行cjc_fun_xxxxxxx函数部分，并将结果插入到临时表t1中，
在跑批时，不需要在执行cjc_fun_xxxxxxx函数，直接和临时表t1进行关联即可，
经测试，速度有明显改善，平均耗时不超过5分钟。

#####chenjuchao 2021-08-22 14:30#####