SQL優化案例 | 從Exadata遷移到國產沃趣一體機一般方法探究（四）

儲存技術的發展，快閃記憶體的出現，大大提升了資料庫一體機的效能，隨著快閃記憶體成本的降低，我相信未來Exadata也不會再有flashcache、flashlog相關的特性。

目前有大量的基於Exadata資料庫的業務逐漸遷移到國產沃趣一體機當中，那麼我們看一下，遷移過程中，SQL優化的一般思路：

資料庫效能提升的一個標誌就是IO效能提升或者減少IO訪問次數（不管是申請的IO是在buffer中還是在磁碟中），Exadata一體機Smart Scan特性是資料Offloading的一個統稱，包含的子特性比較多，比如：行過濾、列過濾、儲存索引、布隆過濾、壓縮和解壓縮等等。但都離不開減少IO訪問的本質。

首先擷取了部分資料庫中使用解除安裝儲存特性的SQL（這些SQL是必須要優先處理的）：

SELECT SQL_ID,SUBSTR(SQL_TEXT,0,150), IO_CELL_OFFLOAD_ELIGIBLE_BYTES/1024/1024/1024 G FROM V$SQL WHERE IO_CELL_OFFLOAD_ELIGIBLE_BYTES<>0;

可以看到SQL中使用hint full(N) 或者直接路徑載入方式引導SQL走智慧掃描，那麼就需要對這些SQL進行精細化調整。

 

舉例，原SQL如下：

SELECT A.QSRQ,A.JZRQ,A.BZ,A.TELLER,SUM(A.A_AMOUNT) AS A_AMOUNT,SUM(A.B_AMOUNT) AS B_AMOUNT FROM
(SELECT /*+full(JR)*/ '2018-04-01' AS QSRQ, 
     '2018-06-30' AS JZRQ,
     JR.TELLER_NO AS TELLER,
     DECODE(ED.SIGN,'+',JR.FROM_CURR_CODE,JR.TO_CURRENCY_CODE) AS BZ,
     SUM(DECODE(ED.SIGN,'+',JR.JNRST_AMOUNT,0)) AS A_AMOUNT,
     SUM(DECODE(ED.SIGN,'-',JR.JNRST_AMOUNT,0)) AS B_AMOUNT
FROM JR01_01 JR
LEFT JOIN ED1P ED ON ED.TRAN_CODE=JR.TRAN_CODE
INNER JOIN CB_EDP RE ON RE.TRAN_CODE=JR.TRAN_CODE AND RE.CA_FLAG = 0
WHERE JR.POST_DATE >= TO_DATE('2018-04-01','YYYY-MM-DD')
  AND JR.POST_DATE <= TO_DATE('2018-06-30','YYYY-MM-DD')
      AND EXISTS (SELECT JGM FROM JGDY G WHERE G.JGM=JR.TRAN_BRANCH AND G.JGM='4051'
                  UNION
                  SELECT JGM FROM JGDY G WHERE G.JGM=JR.TRAN_BRANCH AND G.SJJGM='4051')
      AND EXISTS (SELECT '1' FROM TELM PARTITION("TELM_2018-06-30") WHERE EXTDATE =TO_DATE('2018-06-30','YYYY-MM-DD')
      AND TELLER_NO = JR.TELLER_NO AND TERM_TYPE='0' )
GROUP BY JR.TELLER_NO,JR.FROM_CURR_CODE,JR.TO_CURRENCY_CODE,ED.SIGN) A GROUP BY  A.BZ,A.TELLER,A.QSRQ,A.JZRQ ORDER BY A.TELLER;

SQL跑了將近3分鐘，執行計劃：

通過如下得知，智慧掃描特性為本SQL節約了86.25%的IO開銷，但平行遷移到普通資料庫中不做任何處理效率是下降的。

select 
sql_id ,
child_number,
decode(io_cell_offload_eligible_bytes,0,'No','Yes') OFFLOAD,
decode(io_cell_offload_eligible_bytes,0,0,100*(io_cell_offload_eligible_bytes-IO_interconnect_bytes))/decode(io_cell_offload_eligible_bytes,0,1,io_cell_offload_eligible_bytes) "IO_saved%",
(ELAPSED_TIME/1000000/DECODE(NVL(EXECUTIONS,0),0,1,EXECUTIONS)) avg_time
from gv$sql s where sql_id in ('4gfsgnxzpjp90') order by 1,2,3;
SQL_ID        CHILD_NUMBER OFF  IO_saved%   AVG_TIME
------------- ------------ --- ---------- ----------
4gfsgnxzpjp90            0 Yes 86.2519623  216.99365

下面是不做任何調整跑到QData一體機中效果，SQL執行效果如下(SQL還沒有執行完，時間肯定大於1500秒)：

13:21:46 report.QData>r
1  select
2  sql_id ,
3  child_number,
4  decode(io_cell_offload_eligible_bytes,0,'No','Yes') OFFLOAD,
5  decode(io_cell_offload_eligible_bytes,0,0,100*(io_cell_offload_eligible_bytes-IO_interconnect_bytes))/decode(io_cell_offload_eligible_bytes,0,1,io_cell_offload_eligible_bytes) "IO_saved%",
6  (ELAPSED_TIME/1000000/DECODE(NVL(EXECUTIONS,0),0,1,EXECUTIONS)) avg_time
7* from gv$sql s where sql_id in ('4gfsgnxzpjp90') order by 1,2,3
SQL_ID        CHILD_NUMBER OFF  IO_saved%   AVG_TIME
------------- ------------ --- ---------- ----------
4gfsgnxzpjp90            0 No           0 1504.55472

新增如下索引：

新增索引後的執行計劃：

執行變快的一個標準就是取得JR01_01表資料的邏輯讀從1942K變為19152。

 

隨著硬體技術發展，企業不斷追求低成本，必將有大量的國產一體機取代Exadata，同樣的也有對應技術來幫助實現這份工作。

|  作者簡介

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

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

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