Oracle某行系統SQL優化(案例五)

問題說明：   

業務人員反饋系統跑批慢了，平時耗時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#####