百倍性能的PL/SQL优化案例(r11笔记第13天)

我相信你是被百倍性能的字样吸引了,不过我所想侧重的是优化的思路,这个比优化技巧更重要,而结果嘛,其实我不希望说成是百倍提升,“”自黑“”一下。

有一个真实想法和大家讨论一下,就是一条SQL语句如果原本运行20秒,优化到了1秒,性能提升该说是20倍还是提高了95%。当然还见过一种说法,一条SQL语句每次运行20秒,每天运行100次,优化后每次运行1秒,运行还是100次,那么性能提升是说成优化累计时间为100*20-100=1990秒?

好了,我们来看看PL/SQL的优化,前期自己分析了一些信息,可以参考闪回区报警引发的性能问题分析(r11笔记第11天)

总体来说就是数据库层面的闪回区暴增,很快就接近报警阈值。

发现其中的一个重要因素就是一个update操作时间极长,大概是4个小时,而且资源消耗巨大。

SQL_FULLTEXT ---------------------------------------------------------------------------------------------------- UPDATE CARDINFO A SET A.MAX_LEVEL = NVL((SELECT USER_CLASS FROM ROLE_CLASS_INFO B WHERE A.GROUPID = B.GROUP_ID AND B.CN_GUID = A.ROLE_GUID), A.MAX_LEVEL) WHERE DRAWED = 'Y'而经过数据分析发现这是一个规律性的变化,是在每周二会触发一次。经过确认这是一个scheduler JOB运行导致。而其中的关键就是调用的存储过程。

好了,重点就是存储过程,当然里面的逻辑还是有一些复杂。我简化一下。

简单解释一下,数据库a的表card_new中会存储一些礼包卡的数据,用户激活卡信息之后就会插入一条记录。而数据库b则是一个统计数据库,会从数据库a中基于规则表tasklist抽取这些数据,然后在统计端基于业务需求做信息的变更校准,信息都在cardinfo这个表里。规则表tasklist简单补充一下,就好像我们的手机卡号,比如152xxxx001-152xxxx999是一个号段,里面定义的就是这些信息,从源库按照这个规则抽取。

SQL> select count(*)from card_new where cardid between 'j23450010000' and 'j23500009999';  
  COUNT(*)
----------  
      5000 

存储过程的信息大体如下

CREATE  or replace PROCEDURE        "PROC_UPDATE_CARDINFO"
AS
BEGIN
  for cur in (select * from tasklist where is_droped = 'N') loop
    MERGE INTO cardinfo a
    USING (SELECT *
             FROM card_new@tmp_link t
            WHERE t.cardid >= cur.t_start
              AND t.cardid <= cur.t_end 
              ) b
    ON (a.cardid = b.cardid)
    WHEN MATCHED THEN
      UPDATE
         SET a.groupid   = b.GROUPID,
             a.role_guid = b.role_guid,
             a.drawed    = b.drawed,
             a.max_level = b.max_level
    WHEN NOT MATCHED THEN
      insert
        (cardid, groupid, role_guid, drawed, max_level)
      values
        (b.cardid, b.groupid, b.role_guid, b.drawed, b.max_level);
    COMMIT;
  end loop;

  /** 做字段1的映射变更*/
  UPDATE cardinfo a
     SET a.used_jewel = (SELECT jewel_total
                           FROM role_costs_info b
                          WHERE b.GROUP_ID = a.groupid
                            AND b.cn_guid = a.role_guid)
   WHERE drawed = 'Y' and cardid in(select cardid from tmp_cardinfo);
  COMMIT;

  /**  做字段2的映射变更**/
  UPDATE cardinfo a
     SET a.max_level = nvl((SELECT user_class
                             FROM role_class_info b
                            WHERE a.groupid = b.GROUP_ID
                              AND b.cn_guid = a.role_guid),
                           a.max_level)
   WHERE drawed = 'Y' and cardid in(select cardid from tmp_cardinfo);
  COMMIT;
END;
/

上面的表,除了规则表tasklist是不到1万条数据库(类似号段的数据),其它的数据量都在亿级,所以优化空间很大,优化难度不小。

和开发同学简单了解了需求之后,我的初步结论是update的部分有待提高,因为update的部分变更都是全表更新,这个影响面较大,没法确定增量的数据,基本上按照1周的频率来说,增量数据应该会在百万以内。而查看后面几个update的部分,发现变更的数据量都在千万级别,性能极差。

不过在优化的过程中,感觉我似乎偏离了方向,因为目标端按照现有的条件和补充条件发现始终变更的数据量太大,都是千万级别,和预期相去甚远,简单来说,按照目前的条件得到的数据不是增量数据,所以我的注意力就关注在了源头的数据抽取上。

因为源库的配置较好,使用了PCIE-SSD,查询亿级大表也蛮给力,我在备库查询了一下数据的情况。

SQL> SELECT count(t.cardid)
  2  FROM card_new t ,tasklist cur
  3  WHERE t.cardid >= cur.t_start
  4  AND t.cardid <= cur.t_end;
COUNT(T.CARDID)
---------------
      599845975

一看结果有5亿多条数据,当然大家仔细看,其实语句本身也是有问题的。

其实按照逻辑抽取的数据有2亿,也就是源库表中所有的数据。

如此一来,下游的数据变更都会直接影响,导致了现在的状况。

所以瓶颈很明显,在两个地方 1.抽取的时候对线上业务有性能压力,是全量抽取 2.更新的时候是全量更新,字段匹配数据范围太大

改进思路相对就很简单了。

  1. 明确增量的数据
  2. 使用临时表或者是在cardinfo中标记增量数据进行增量数据变更
  3. 进行完整的数据测试,保证性能改进真实有效。

我们来逐个说一下。

  1. 增量的数据,我查看了源表的字段,里面有一个基于时间的字段,看字段的名字应该是礼品卡的激活时间。和开发同事进行了确认,这个地方明确下来。

我们按照这样的思路来看,增量数据大概在7万左右。

SQL> select count(*)from card_new where DRAWDATE>sysdate-10; COUNT(*) ---------- 78174

如此一来就抓住了问题的本质,后面的更新部分就可以限制条件,避免全量更新。我就创建建了一个临时表来处理。得到从源库抽取所得的增量数据。

2.增量数据变更优化

原本的更新是这样的逻辑,

UPDATE cardinfo a SET a.used_jewel = (SELECT jewel_total FROM role_costs_info b WHERE b.GROUP_ID = a.groupid AND b.cn_guid = a.role_guid) WHERE drawed = 'Y' ;

改进之后,限制了条件,就是下面的形式

UPDATE cardinfo a SET a.used_jewel = (SELECT jewel_total FROM role_costs_info b WHERE b.GROUP_ID = a.groupid AND b.cn_guid = a.role_guid) WHERE drawed = 'Y' and cardid in(select cardid from tmp_cardinfo);

当然还有一些小细节处做了改进,再次先不赘述。

3.性能测试

接下来就是性能测试了,如何真实的模拟测试这个问题,11g中要充分利用Sapshot Standby的福利。

备库切换为Snapshot Standby的方法 dgbroker中把当前的备库设置为disable 然后使用sqlplus在备库操作:

recover managed standby database cancel; --取消日志应用 alter database convert to snapshot standby; --切换为Snapshot Standby alter database open; --切换后打开数据库 select database_role,open_mode from v$database; --检查变更是否生效

然后开始性能测试,我把数据源指向了源库对应的备库,这样对线上就没有直接的压力。在目标数据库中修改存储过程,运行测试。

SQL> exec PROC_UPDATE_CARDINFO1; PL/SQL procedure successfully completed. Elapsed: 00:01:04.38

原本执行至少4个小时的存储过程现在1分钟即可搞定。

完成测试,开始恢复备库为Physical Standby: sqlplus备库: shutdown immediate startup mount alter database convert to physical standby; --切换数据库为physical standby shutdown immediate --修改后数据库为nomount,重新启动 startup mount select database_role,open_mode from v$database; alter database open; 然后在主库使用DG Broker来enable原来的备库即可。

小结

整个一个流程走下来,让我对这个问题的认知,从原本的闪回区报警逐步发掘,扩展到PL/SQL的存储过程实现,当然这个部分还是花了些时间熟悉了下业务,为了更好的满足优化需求,优化中尤其需要牢牢把握性能瓶颈,抓住本质,然后逐个击破即可。而对于性能问题的测试,Snapshot Standby就是一个很不错的补充。评估运行时间等都会更加真实有效。

最后的性能提升,从4个小时提升为1分钟。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2016-12-15

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

一条报警信息的快速处理和分析(r9笔记第99天)

下午的时候收到这么一条报警。 ZABBIX-监控系统: ------------------------------------ 报警内容: Too many...

3416
来自专栏全栈工程师成长之路

深入浅出后端开发(MySQL篇)

38118
来自专栏lonelydawn的前端猿区

MySQL应用优化

一、基本语句优化原则 (1).尽量避免在索引列上进行运算或函数操作,这样会导致索引失效 如: select * from t where Year(d)>=20...

1955
来自专栏数据和云

SQL审核:OR展开与子查询优化案例详解

黄廷忠(网名:认真就输) 云和恩墨技术专家 个人博客:http://www.htz.pw/ 本篇整理内容是黄廷忠在“云和恩墨大讲堂”微信分享中的讲解案例,S...

3684
来自专栏前端黑板报

MySQL之什么是MySQL

这篇教程是帮助你回答:什么是MySQL?以及告诉你为何 MySQL 是世界上最流行的开源数据库的原因。

2684
来自专栏数据和云

巧用复合索引,有效降低系统IO

我们知道索引至关重要,合理的索引使用能够在很大程度上改善数据库的性能。然而很多人都会走入这样一个误区:走索引的SQL语句的性能一定比全表扫描好。真的是这样吗?今...

2979
来自专栏决胜机器学习

数据库专题(二) ——数据库设计

数据库专题(二)——数据库设计 (原创内容,转载请注明来源,谢谢) 一、数据库设计规范——范式 数据库设计,需要遵循设计原则,最主要的设计原则是范式。范式是...

3577
来自专栏杨建荣的学习笔记

MySQL中的反连接(r12笔记第45天)

关于Oracle的半连接,反连接,我一直认为这是一个能讲很长时间的话题,所以在我的新书《Oracle DBA工作笔记》中讲性能优化的时候,我花了不少的笔墨做...

2715
来自专栏数据和云

郑保卫 - 索引优化策略及实战

本文中将要介绍的索引战略方案是以尽可能少的索引来满足尽可能多的数据读取类型的索引构建方法。这个策略方案要求在构建索引时,尽可能多地搜集当前正在使用的未来将要出...

3185
来自专栏文渊之博

列存储索引1:初识列存储索引

     2012以后提供了一种不同于传统B树结构的索引类型,就是内存列存储索引。这种索引应用了一种基于列的存储模式,也是一种新的查询执行的批处理模式,并且为特...

2245

扫码关注云+社区

领取腾讯云代金券