SQL优化案例 | 从Exadata迁移到国产沃趣一体机一般方法探究（四）

Original 姚崇·沃趣科技沃趣技术 2023-03-10

作者姚崇·沃趣科技高级数据库技术专家

出品沃趣科技

存储技术的发展，闪存的出现，大大提升了数据库一体机的性能，随着闪存成本的降低，我相信未来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