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

chenoracle發表於2021-03-31
OracleSQL優化

問題說明：

資料庫遷移到新伺服器後，某一張檢視查詢緩慢，原庫查詢耗時3分鐘，新庫查詢5小時以上。

環境說明：

新庫：

DB:Oracle 11.2.0.4.0 RAC
OS:Redhat 7.6

原庫：

DB:Oracle 11.2.0.4.0 單機
OS:AIX 5.1

問題分析：

處理此類問題，常見思路如下：
1 分別對比原庫和新庫該SQL對應的執行計劃。
2 對比兩個執行計劃，找到差異部分或最耗時的部分。
3 分析執行計劃差異原因，根據原因嘗試解決問題。

本次案例檢視引用的SQL使用了28個union all組成，關聯了30多張表。

在執行如下SQL時，新庫會卡住很久，甚至無法返回結果。
select count(*) from v_cjc_01;

檢視內SQL類似如下：

create or replace view v_cjc_01 as
select col1,col2,col3,...col10
  from t1
 where xxx=yyy
union all
select col1,col2,col3,...col10
  from t2
 where zzz=yyy
union all
......
union all
select col1,col2,col3,...col10
  from t3
 where xxx=yyy and col2 not in (select col2 from t3 a where col1=aaa col3=bbb)
union all
select col1,col2,col3,...col10
  from t5
 where ttt=yyy;

檢視執行計劃，發現主要有兩處不同

一：

原執行計劃 

|* 40 |     HASH JOIN                  |                             | 72408 |  2616K|  2264K| 28026   (1)| 00:05:37 |
|  41 |      VIEW                      | VW_JF_SET$5EF83EA7          | 72408 |  1414K|       |  3028   (1)| 00:00:37 |
|  42 |       UNION-ALL                |                             |       |       |       |            |          |
|* 43 |        TABLE ACCESS FULL       | CJC_T1_00000000000000000    | 72407 |  2050K|       |  1514   (1)| 00:00:19 |
|* 44 |        TABLE ACCESS FULL       | CJC_T1_00000000000000000    |     1 |    29 |       |  1514   (1)| 00:00:19 |
|  45 |      INDEX FAST FULL SCAN      | IDX_MB_ACCINF_ACCNO         |  9179K|   148M|       | 12212   (1)| 00:02:27 |
----------------------------------------------------------------------------------------------------------------------

新執行計劃

|  13 |     NESTED LOOPS               |                             |   236 |  9676 |  2003   (1)| 00:00:25 |
|* 14 |      TABLE ACCESS FULL         | CJC_T1_00000000000000000    |   235 |  5640 |  1533   (1)| 00:00:19 |
|* 15 |      INDEX RANGE SCAN          | IDX_MB_ACCINF_ACCNO         |     1 |    17 |     2   (0)| 00:00:01 |
|  16 |     NESTED LOOPS               |                             |     1 |    41 |  1535   (1)| 00:00:19 |
|* 17 |      TABLE ACCESS FULL         | CJC_T1_00000000000000000    |     1 |    24 |  1533   (1)| 00:00:19 |
|* 18 |      INDEX RANGE SCAN          | IDX_MB_ACCINF_ACCNO         |     1 |    17 |     2   (0)| 00:00:01 |

二：

原執行計劃

|* 16 |     HASH JOIN RIGHT ANTI NA    |                             |  6647 |  1174K|    12M| 26593   (1)| 00:05:20 |
|* 17 |      TABLE ACCESS FULL         | CJC_T2_00000000000000000    |   130K|    10M|       | 11309   (1)| 00:02:16 |
|* 18 |      TABLE ACCESS FULL         | CJC_T2_00000000000000000    |   664K|    60M|       | 11309   (1)| 00:02:16 |

新執行計劃

|* 21 |     FILTER                     |                             |       |       |            |          |
|* 22 |      TABLE ACCESS FULL         | CJC_T2_00000000000000000    |  4928 |   202K| 11126   (1)| 00:02:14 |
|* 23 |      TABLE ACCESS FULL         | CJC_T2_00000000000000000    |     1 |    27 | 11124   (1)| 00:02:14 |

根據第一處不同，可以看到：

原執行計劃，出現了VW_JF_SET$5EF83EA7，說明原庫使用到了Join Factorization特性，
在該特性下，union all連線各個分支查詢SQL時，會將各個分支部分中公共部分單獨提出作為一個單獨的結果集，
然後在和原union all剩下的部分做關聯，從而減少了公共部分表重複掃描的問題，效能會有提升。

為什麼新庫沒有使用到Join Factorization特性呢？

對比兩個庫的_OPTIMIZER_JOIN_FACTORIZATION引數，都是使用的預設值true。
說明優化器認為新庫不適合使用Join Factorization特性。
但是從執行計劃看兩種表資料量都比較大，結果集也很大，新庫使用的NESTED LOOPS明顯比原庫HASH JOIN效能差。

可以通過10053事件對比檢視原庫和新庫對同一SQL是如何進行改寫，如何計算cost的？

原庫和新庫分別執行：

alter session set tracefile_identifier='10053';
ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';
select count(*) from v_cjc_01;
ALTER SESSION SET EVENTS '10053 trace name context off';
select value from v$diag_info where name='Default Trace File';

檢視生成的trace檔案：

搜尋關鍵字 Final query after transformations

新庫SQL改寫部分： 

UNION ALL
        (SELECT '0' "KERNAL_INTER4"
           FROM "CHEN"."CJC_T1_00000000000000000" "A",
                CHENPMB."MB_ACCINF"               "B"
          WHERE "B"."AIF_ACCNO" = "A"."CJC_T3_000000"
            AND "A"."MPB_BUSINESSTYPE" = '10'
            AND "A"."MPB_TRANTIME" IS NOT NULL
            AND LENGTH("A"."MPB_TRANTIME") = 14
            AND "A"."MPB_TRANTIME" > '20200501000000') UNION ALL
        (SELECT '0' "KERNAL_INTER4"
           FROM "CHEN"."CJC_T1_00000000000000000" "A",
                CHENPMB."MB_ACCINF"               "B"
          WHERE "B"."AIF_ACCNO" = "A"."CJC_T3_000000"
            AND "A"."MPB_BUSINESSTYPE" = '11'
            AND "A"."MPB_TRANTIME" IS NOT NULL
            AND LENGTH("A"."MPB_TRANTIME") = 14
            AND "A"."MPB_TRANTIME" > '20200501000000')

原庫SQL改寫部分：

UNION ALL
        (SELECT '0' "KERNAL_INTER4"
           FROM CHENPMB."EA_EACCT_INFO_S" "EA_EACCT_INFO_S"
          WHERE "EA_EACCT_INFO_S"."EA_ACCOUNT_OPEN_RESULT" = '1') UNION ALL
        (SELECT "VW_JF_SET$5EF83EA7"."ITEM_2" "KERNAL_INTER4"
           FROM ((SELECT "A"."CJC_T3_000000" "ITEM_1", '0' "ITEM_2"
                    FROM "CHEN"."CJC_T1_00000000000000000" "A"
                   WHERE "A"."MPB_TRANTIME" > '20200501000000'
                     AND LENGTH("A"."MPB_TRANTIME") = 14
                     AND "A"."MPB_TRANTIME" IS NOT NULL
                     AND "A"."MPB_BUSINESSTYPE" = '10') UNION ALL
                 (SELECT "A"."CJC_T3_000000" "ITEM_1", '0' "ITEM_2"
                    FROM "CHEN"."CJC_T1_00000000000000000" "A"
                   WHERE "A"."MPB_TRANTIME" > '20200501000000'
                     AND LENGTH("A"."MPB_TRANTIME") = 14
                     AND "A"."MPB_TRANTIME" IS NOT NULL
                     AND "A"."MPB_BUSINESSTYPE" = '11')) "VW_JF_SET$5EF83EA7",
                CHENPMB."MB_ACCINF" "B"
          WHERE "B"."AIF_ACCNO" = "VW_JF_SET$5EF83EA7"."ITEM_1")) "MB_YINGXIAO_COUNT1"

嘗試按照原庫10053轉換的SQL改寫新庫SQL後，執行速度恢復正常，但是SQL改動量較大。

根據第二處不同，可以看到：

改寫前原SQL部分：

select CUSTOMERID as CSTNO,
       TRANTIME as TRANTIME,
       '10' as TRANTYPE,
       to_date(TRANTIME, 'yyyymmddhh24miss') as TRANTIME1,
       createtime,
       1 as cnt,
       0 as amt,
       '0' as kernal_inter1,
       '0' as kernal_inter2,
       '0' as kernal_inter3,
       '0' as kernal_inter4
  from CJC_T2_00000000000000000 c
 where c.tranname = 'XX繫結'
   and c.retcode = '0000'
   and c.TRANTIME is not null
   and length(c.TRANTIME) = 14
   and c.TRANTIME > '20200501000000'
   and c.medicalcardno not in
       (select a.medicalcardno
          from CJC_T2_00000000000000000 a
         where a.tranname = 'XX解綁'
           and a.retcode = '0000')

新庫SQL改寫部分: 

SELECT '0' "KERNAL_INTER4"
           FROM CHENPMB."CJC_T2_00000000000000000" "A",
                CHENPMB."CJC_T2_00000000000000000" "C"
          WHERE "C"."TRANNAME" = 'XX繫結'
            AND "C"."RETCODE" = '0000'
            AND "C"."TRANTIME" IS NOT NULL
            AND LENGTH("C"."TRANTIME") = 14
            AND "C"."TRANTIME" > '20200501000000'
            AND "C"."MEDICALCARDNO" = "A"."MEDICALCARDNO"
            AND "A"."TRANNAME" = 'XX解綁'
            AND "A"."RETCODE" = '0000'

原庫SQL改寫部分:

(SELECT '0' "KERNAL_INTER4"
           FROM CHENPMB."CJC_T2_00000000000000000" "C"
          WHERE "C"."TRANNAME" = 'XX繫結'
            AND "C"."RETCODE" = '0000'
            AND "C"."TRANTIME" IS NOT NULL
            AND LENGTH("C"."TRANTIME") = 14
            AND "C"."TRANTIME" > '20200501000000'
            AND NOT EXISTS
          (SELECT 0
                   FROM CHENPMB."CJC_T2_00000000000000000" "A"
                  WHERE LNNVL("A"."MEDICALCARDNO" <> "C"."MEDICALCARDNO")
                    AND "A"."TRANNAME" = 'XX解綁'
                    AND "A"."RETCODE" = '0000'))

根據執行計劃可知，

原庫將not in改寫成NOT EXISTS，表關聯走的 HASH JOIN RIGHT ANTI NA，效率較高。
嘗試將新庫SQL中not in改寫成NOT EXISTS後，效率有明顯提升，不到5分鐘可以執行完成。

SQL是否還有優化空間呢？

可以看看Oracle給出的建議sql tuning advisor:

SQL>
DECLARE
  my_task_name VARCHAR2(30);
BEGIN
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id      => 'gzjy5nh7g0sfs',
                                                  scope       => 'COMPREHENSIVE',
                                                  time_limit  => 3600,
                                                  task_name   => 'cjc_sql_tuing_task',
                                                  description => 'SQL TUNE TEST');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'cjc_sql_tuing_task');
END;
/

SELECT status FROM  DBA_ADVISOR_TASKS WHERE  task_name = 'cjc_sql_tuing_task';
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 300
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('cjc_sql_tuing_task') FROM   DUAL;

建議如下：

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  為此語句找到了效能更好的執行計劃 2。選擇以下 SQL 概要檔案之一進行實施。
  Recommendation (estimated benefit: 95.14%)
  ------------------------------------------
  - 考慮接受推薦的 SQL 概要檔案。
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'cjc_sql_tuing_task', task_owner => 'CHENPMB', replace => TRUE);
  Recommendation (estimated benefit: 99.95%)
  ------------------------------------------
  - 考慮接受建議的 SQL 概要檔案, 以便對此語句使用並行執行。
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'cjc_sql_tuing_task', task_owner => 'CHENPMB', replace => TRUE,
            profile_type => DBMS_SQLTUNE.PX_PROFILE);
  與 DOP 128 並行執行此查詢會使 SQL 概要檔案計劃上的響應時間縮短 99.13%。但是, 啟用並行執行時要付出一些代價。它將增加語句的資源消耗
  (預計為 11.07%), 這會導致系統吞吐量降低。此外, 由於在非常短的持續時間內消耗了這些資源, 因此如果沒有足夠可用的硬體容量,
  併發語句的響應時間將受到負面影響。

2- Index Finding (see explain plans section below)

--------------------------------------------------
  通過建立一個或多個索引可以改進此語句的執行計劃。
  Recommendation (estimated benefit: 99.7%)
  -----------------------------------------
  - 考慮執行可以改進物理方案設計的訪問指導或者建立推薦的索引。
    create index CHENPMB.IDX$$_04000001 on
    CHENPMB.MB_PAY_LOG("MPL_TRANCODE","MPL_STT",LENGTH("MPL_TIME"),"MPL_TIME");
  - 考慮執行可以改進物理方案設計的訪問指導或者建立推薦的索引。
    create index CHENPMB.IDX$$_04000002 on
    CHENPMB.MB_FINANCE_TRANS_INCREMENT("MFT_TRAN_STATUS",LENGTH("MFT_TRAN_TIME"
    ),"MFT_TRAN_CODE","MFT_TRAN_TIME");
  - 考慮執行可以改進物理方案設計的訪問指導或者建立推薦的索引。
    create index CHEN.IDX$$_04000003 on CHEN.CJC_T1_00000000000000000("MPB_BUSI
    NESSTYPE",LENGTH("MPB_TRANTIME"),"MPB_TRANTIME","CJC_T3_000000");
  - 考慮執行可以改進物理方案設計的訪問指導或者建立推薦的索引。
    create index CHENPMB.IDX$$_04000004 on
    CHENPMB.MB_LOANBYFINANCE_RECORD("MLR_TRANCODE","MLR_LOANTYPEFLAG","MLR_STAT
    US",LENGTH("MLR_TRANTIME"),"MLR_TRANTIME");
  - 考慮執行可以改進物理方案設計的訪問指導或者建立推薦的索引。
    create index CHENPMB.IDX$$_04000006 on
    CHENPMB.MB_TRANFLOW_INCREMENT("TRF_BSNCODE","TRF_STT",LENGTH("TRF_SUBTIME")
    ,"TRF_SUBTIME","TRF_FLOWNO");
  Rationale
  ---------
    建立推薦的索引可以顯著地改進此語句的執行計劃。但是, 使用典型的 SQL 工作量執行 "訪問指導"
    可能比單個語句更可取。通過這種方法可以獲得全面的索引建議案, 包括計算索引維護的開銷和附加的空間消耗。

最終建議：

1 將SQL中not in部分改寫成not exists。
2 建立組合索引。

優化效果：

SQL由原來5小時查不出結果，優化到12秒完成查詢。

###2021-03-31 22:00 chenjuchao###

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2766034/，如需轉載，請註明出處，否則將追究法律責任。

相關文章