ITPub博客

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

原创 Oracle 作者:沃趣科技 时间:2018-07-19 16:33:08 0 删除 编辑

存储技术的发展,闪存的出现,大大提升了数据库一体机的性能,随着闪存成本的降低,我相信未来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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
杭州沃趣科技股份有限公司创建于2012年(股票代码:839849),是一家专注为企业用户提供基于高性能、高可用、可扩展的开放数据库云平台解决方案的国产厂商。公司创始团队为原阿里巴巴数据库技术团队核心骨干,凭借丰富的研发及运维经验,为行业客户提供数据库云产品及软硬件一体化解决方案。

注册时间:2016-07-18

  • 博文量
    169
  • 访问量
    610668