SQL最佳化案例-改變那些CBO無能為力的執行計劃（一）

沃趣科技發表於2018-05-07
SQL

使用者寫的sql，Oracle會進行等價改寫，即使是RBO最佳化模式，Oracle也會給你做一些轉換，這些轉化都是基於一種固定的演算法，oracle稱這種轉換是“啟發式”的。比如我們寫inner join時，並且只訪問單表資料，Oracle會自動降為半連線，然後用semi join的方式給你做join。transformation是Oracle必做的一個步驟，至少在8.05版本之後transformation都一直存在。

網上有很多最佳化法則，有的說exists比in效率高，有的說in比exists執行的快，那就要看SQL是如何寫的，CBO是如何轉換的，是否能轉換？當然這種轉換不是基於成本的而是“基於啟發的轉化”。




當Oracle沒辦法做transformation的時候，可能就是sql產生問題的時候，此時就要我們去找原因了，下面透過一些案例，說明這種最佳化器無能為力的情況（為了保護客戶的隱私，表名和部分列已經重新命名）。

| 用merge代替update


UPDATE關聯更新跑了將近40分鐘，SQL語句如下：

點選(此處)摺疊或開啟

  1. UPDATE PRO_S_ACCT A SET ACCT_SKID = (SELECT ACCT_SKID FROM ACCT_S_BK B WHERE A.ACCT_ID = B.ACCT_ID);
執行計劃如下：


檢視量表資料量，其中PRO_S_ACCT有1044227行資料，acct_s_bk有553554行資料。



UPDATE後面跟子查詢類似巢狀迴圈。pro_s_acct為巢狀迴圈的驅動表，acct_s_bk為被驅動表，那麼表acct_s_bk就會被掃描100多萬次，就會產生大量的邏輯讀，被驅動表走全表掃描，我們可以在其上面建立索引，但是此時索引會被掃描100多萬次。

下面我們建立索引看其執行計劃如下：

點選(此處)摺疊或開啟

  1. create index ind_id_skid on acct_s_bk (ACCT_ID,ACCT_SKID);



下面我們透過用merge into 等價改寫看其執行計劃：

點選(此處)摺疊或開啟

  1. merge into PRO_S_ACCT A
  2. using ACCT_S_BK B on (A.ACCT_ID = B.ACCT_ID)
  3. when matched
  4. then update
  5. set a.ACCT_SKID = B.ACCT_SKID;


MERGE INTO可以自由控制走巢狀迴圈或者走hash連線，並且當驅動表和被驅動表的使用資料超過1G時我們可以開啟相應大小的並行DML更新。 

點選(此處)摺疊或開啟

  1. merge /*+PARALLEL(8 )*/ into PRO_S_ACCT A
  2. using ACCT_S_BK B on (A.ACCT_ID = B.ACCT_ID)
  3. when matched
  4. then update
  5. set a.ACCT_SKID = B.ACCT_SKID;


實際執行中，2s完成。

下面透過sql改寫，來讓sql的執行計劃被我們所控制。


點選(此處)摺疊或開啟

  1. UPDATE INXX I
  2. SET (I.INT_FRM_DT,I.INT_TO_DT,I.ACCT_DESC) = (SELECT DBPP.CR_SOP_DATE,DBPP.EOP_DATE,DBPP.ACCT_DESC
  3.                                     FROM DBPP
  4.                                    WHERE DBPP.SYS_ID='INV'
  5.                                      AND DBPP.ACCT_TYPE = I.ACCT_TYPE
  6.                                      AND DBPP.INT_CAT = I.INT_CAT)
  7. WHERE I.EXTDATE = TO_DATE('2018-04-03','YYYY-MM-DD')
  8. AND EXISTS (SELECT DBPP.SYS_ID
  9.       FROM DBPP
  10.      WHERE DBPP.SYS_ID='INV'
  11.        AND DBPP.ACCT_TYPE = I.ACCT_TYPE
  12.        AND DBPP.INT_CAT = I.INT_CAT
  13.        AND DBPP.ACCT_DESC = 'S');


點選(此處)摺疊或開啟

  1. merge /*+parallel(10) use_hash(I,X) swap_join_inputs(X)*/ into INXX I
  2. using (SELECT DBPP.CR_SOP_DATE,DBPP.EOP_DATE,DBPP.ACCT_DESC,DBPP.ACCT_TYPE,DBPP.INT_CAT FROM DBPP WHERE DBPP.SYS_ID='INV' AND DBPP.ACCT_DESC = 'S') x
  3. on (x.ACCT_TYPE = I.ACCT_TYPE AND x.INT_CAT = I.INT_CAT)
  4. when matched
  5. then update set I.INT_FRM_DT=x.CR_SOP_DATE,I.INT_TO_DT=x.EOP_DATE,I.ACCT_DESC=x.ACCT_DESC
  6. WHERE I.EXTDATE = TO_DATE('2018-04-03','YYYY-MM-DD');



另一類似案例：

點選(此處)摺疊或開啟

  1. update WWW a
  2. set a.cny_bal=a.ll_bal*nvl((select b.hl from MMM b where b.startdate<=a.extedate and b.enddate > a.extdate and b.zb='CNY' and
  3. a.curr=b.yb),0)
  4. where a.extdate=to_date('2018-04-01','yyyy-mm-dd');
  5. 由於www表是按天分割槽，分割槽欄位是extdate，那麼可以起改寫成如下：
  6. merge /*+parallel(8)*/ into www a
  7. using (select b.hl from MMM b where b.zb='CNY' and b.enddate>date'2018-04-01' and b.startdate<=date'2018-04-01') c
  8. on (a.curr=c.yb)
  9. when matched
  10. then update
  11. set a.cny_bal=a.ll_bal*NVL(c.hl,0)
  12. where a.extdate=to_date('2018-04-01','yyyy-mm-dd');

| 有關外連結的其他改寫

點選(此處)摺疊或開啟

  1. SELECT
  2. CASE WHEN
  3.   NOT EXISTS (SELECT KHH FROM NB_XXXXXXXX B WHERE RQ>=ADD_MONTHS(TO_DATE('2018-04-27','YYYY-MM-DD'),-12) AND RQ<=TO_DATE('2018-04-27','YYYY-MM-DD') AND A.CUSTNO=B.KHH)
  4.                AND A.OPENCUPDATE+365=TO_DATE('2018-04-27','YYYY-MM-DD') THEN A.CUSTNO END BQXZ,
  5. CASE THEN
  6.   NOT EXISTS (SELECT KHH FROM NB_XXXXXXXX B WHERE RQ>=ADD_MONTHS(TO_DATE('2018-04-27','YYYY-MM-DD'),-12) AND RQ<=TO_DATE('2018-04-27','YYYY-MM-DD') AND A.CUSTNO=B.KHH)
  7.                AND A.OPENCUPDATE+365=TO_DATE('2018-04-27','YYYY-MM-DD') THEN A.CUSTNO END YE,
  8. '2' AS QD,
  9. SUBSTR(B.OPENBANKNO,1,4) JGM
  10. FROM NB_CCCCCCCCC A
  11. inner join DZZH_XXXXXXXXXXXXXXXXXX B
  12. ON A.CUSTNO = B.CUSTNO
  13. WHERE CUPCHECKSTT IN ('1','2');
  14. 685012 rows selected

由於環境是跑批業務，建立索引需要全面考慮，為了不改變當前環境我們儘量不建立索引，執行計劃如下：


實際執行時間37分鐘完成。

點選(此處)摺疊或開啟

  1. SELECT
  2. CASE WHEN c.khh is null then A.CUSTNO END BQXZ,
  3. CASE WHEN c.khh is null then A.CUSTNO END ye,
  4. '2' AS QD,
  5. SUBSTR(B.OPENBANKNO,1,4) JGM
  6. from NB_CCCCCCCCC A
  7. inner join DZZH_XXXXXXXXXXXXXXXXXX B
  8. ON A.CUSTNO = B.CUSTNO
  9. left join
  10. (SELECT KHH FROM NB_XXXXXXXX B WHERE RQ>=ADD_MONTHS(TO_DATE('2018-04-27','YYYY-MM-DD'),-12) AND RQ<=TO_DATE('2018-04-27','YYYY-MM-DD')) c
  11. on A.CUSTNO=c.KHH and A.OPENCUPDATE+365=TO_DATE('2018-04-27','YYYY-MM-DD')
  12. where CUPCHECKSTT IN ('1','2');
  13. 685012 rows selected
執行計劃如下，並且NB_XXXXXXXX表只掃描一次，邏輯讀由84M+18M降為126，執行時間也降為秒級（當然下面的資料因多次執行已經在buffer中）。




|  作者簡介

姚崇·沃趣科技高階資料庫技術專家

熟悉Oracle資料庫內部機制，豐富的資料庫及RAC叢集層故障診斷、效能調優、OWI、資料庫備份恢復及遷移經驗。
 

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

相關文章