SQL優化|從Exadata遷移到沃趣一體機一般方法探究 4

存儲技術的發展，快閃記憶體的出現，大大提升了資料庫一體機的性能，隨著快閃記憶體成本的降低，我相信未來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_AMOUNTFROM JR01_01 JRLEFT JOIN ED1P ED ON ED.TRAN_CODE=JR.TRAN_CODEINNER JOIN CB_EDP RE ON RE.TRAN_CODE=JR.TRAN_CODE AND RE.CA_FLAG = 0WHERE 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_timefrom 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>r1 select2 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_time7* from gv$sql s where sql_id in (4gfsgnxzpjp90) order by 1,2,3SQL_ID CHILD_NUMBER OFF IO_saved% AVG_TIME------------- ------------ --- ---------- ----------4gfsgnxzpjp90 0 No 0 1504.55472

添加如下索引：

添加索引後的執行計劃：

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

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

| 作者簡介

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

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