問(wèn)題說(shuō)明：   

業(yè)務(wù)人員反饋系統跑批慢了，平時(shí)耗時(shí)5分鐘，現在需要跑3個(gè)多小時(shí)，而且是每月10日和每月15日都會(huì )變慢。

環(huán)境說(shuō)明：

DB:Oracle 11.2.0.4.0 RAC
OS:AIX 7.1

問(wèn)題分析:

抓取跑批對應的慢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'

手動(dòng)執行，查看速度：

返回前100條記錄很快，之后平均每10秒取出100行數據，最終取出全部結果集耗時(shí)很長(cháng)。

查看執行計劃：

   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也很低，預估的時(shí)間也很短。

難道是cjcaaaaaaa_info表統計信息不準確？

檢查后發(fā)現表統計信息是準確的，cjcaaaaaaa_info數據量很小。

cjcaaaaaaa_info和chen_balance表關(guān)聯(lián)關(guān)系很簡(jiǎn)單，where謂詞條件也不復雜，那么是什么原因導致的SQL執行慢呢？

顯然當前的cjcaaaaaaa_info和chen_balance關(guān)聯(lián)采用NESTED LOOPS已經(jīng)是最優(yōu)的，嘗試添加hint強制hash join速度更慢了。

既然表關(guān)聯(lián)方式?jīng)]問(wèn)題，表訪(fǎng)問(wèn)路徑?jīng)]問(wèn)題，還有可能哪塊有問(wèn)題呢？

仔細檢查了SQL，發(fā)現查詢(xún)的列有一處 可疑的地方：

......
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看上去像是一個(gè)function，查看function的定義：

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

函數部分由IF和ELSE兩部分組成，每部分包含多個(gè)SELECT查詢(xún)操作。

那么SQL執行慢，是否和cjc_fun_xxxxxxx函數有關(guān)呢？

注釋掉原SQL中包含cjc_fun_xxxxxxx函數部分，再次執行SQL，速度恢復正常，不超過(guò)5分鐘執行完成。

單獨進(jìn)行函數部分測試：

單獨執行函數，速度很慢，每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'

此時(shí)問(wèn)題比較清晰了，就是因為cjc_fun_xxxxxxx函數部分導致SQL查詢(xún)速度慢，那么為什么只有每月10號和每月15日速度慢呢？

主要是因為原SQL包含case when部分，當每月10號和每月15日時(shí)，cjc_fun_xxxxxxx函數部分執行的次數更多。

cjc_fun_xxxxxxx函數對性能究竟有多大的影響？

在滿(mǎn)足sAssetFlag = '1'條件時(shí)，函數會(huì )執行8條select語(yǔ)句，并將結果集進(jìn)行加和后返回。

在不滿(mǎn)足sAssetFlag = '1'條件時(shí)，函數會(huì )執行14條select語(yǔ)句，并將結果集進(jìn)行加和后返回。

并且除了執行的select次數不同外，執行的select語(yǔ)句也是不一樣的，也就是在sAssetFlag值不同時(shí)，即使執行相同次數cjc_fun_xxxxxxx函數，執行時(shí)間也不同。

  綜合以上兩點(diǎn)，SQL執行時(shí)間取決于函數執行次數，和單次函數執行的邏輯有關(guān)。

例如：

在最極端的情況下，查詢(xún)的每條語(yǔ)句都會(huì )調用4次函數，每次函數執行14個(gè)select語(yǔ)句，在查詢(xún)60000條數據時(shí)，后臺實(shí)際會(huì )執行 336萬(wàn)條select語(yǔ)句。

解決方案：

和業(yè)務(wù)人員溝通，cjc_fun_xxxxxxx函數不能在優(yōu)化了，但是可以使用中間表代替。
例如，跑批前提前單獨執行cjc_fun_xxxxxxx函數部分，并將結果插入到臨時(shí)表t1中，
在跑批時(shí)，不需要在執行cjc_fun_xxxxxxx函數，直接和臨時(shí)表t1進(jìn)行關(guān)聯(lián)即可，
經(jīng)測試，速度有明顯改善，平均耗時(shí)不超過(guò)5分鐘。

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