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#####
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29785807/viewspace-2788183/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle某行系統SQL優化案例(三)OracleSQL優化
- Oracle某行系統SQL優化案例(二)OracleSQL優化
- Oracle 某行系統SQL優化案例(一)OracleSQL優化
- Oracle某行系統SQL最佳化(案例四)OracleSQL
- Oracle效能優化-SQL優化(案例一)Oracle優化SQL
- Oracle效能優化-SQL優化(案例二)Oracle優化SQL
- Oracle效能優化-SQL優化(案例三)Oracle優化SQL
- Oracle效能優化-SQL優化(案例四)Oracle優化SQL
- Oracle某X系統SQL最佳化(案例六)OracleSQL
- SQL優化案例-自定義函式索引(五)SQL優化函式索引
- Oracle優化案例-自定義函式索引(五)Oracle優化函式索引
- SQL優化案例-定位系統中大量的rollback(十八)SQL優化
- Oracle優化案例-復現SQL ordered by Parse Calls(三十二)Oracle優化SQL
- Oracle優化案例-教你線上搞定top cpu的sql(十二)Oracle優化SQL
- MySQL SQL優化案例(一)MySql優化
- Oracle優化案例-(三十四)Oracle優化
- Oracle SQL優化之sql tuning advisorOracleSQL優化
- SQL優化案例-使用with as優化Subquery Unnesting(七)SQL優化
- Oracle優化案例-使用with as優化Subquery Unnesting(七)Oracle優化
- Oracle優化案例-從執行計劃定位SQL問題(三)Oracle優化SQL
- Oracle優化案例-關閉auto space advisor和sql tuning advisor(十九)Oracle優化SQL
- SQL優化案例-union代替or(九)SQL優化
- Oracle優化案例-系統切換引起的enq: SQ - contention(二十八)Oracle優化ENQ
- Oracle優化案例-union代替or(九)Oracle優化
- Oracle優化案例-擴充套件統計資訊(十四)Oracle優化套件
- 【雲趣科技】Oracle優化案例-教你線上搞定top cpu的sql(十三)Oracle優化SQL
- Oracle優化案例-又見union代替or(二十)Oracle優化
- SQL優化案例-單表分頁語句的優化(八)SQL優化
- Oracle優化案例-單表分頁語句的優化(八)Oracle優化
- SQL最佳化案例-自定義函式索引(五)SQL函式索引
- Oracle優化案例-view merge與coe_load_sql_profile固定執行計劃(十五)Oracle優化ViewSQL
- Oracle優化案例-join列索引缺失導致的sql效能問題(二十六)Oracle優化索引SQL
- SQL優化案例-正確的使用索引(二)SQL優化索引
- Oracle案例10——HWM(高水位線)效能優化Oracle優化
- Oracle優化案例-正確的使用索引(二)Oracle優化索引
- Oracle優化案例-儲存過程的優化思路(二十三)Oracle優化儲存過程
- Oracle SQL效能優化的40條軍規OracleSQL優化
- SQL優化案例-從執行計劃定位SQL問題(三)SQL優化