生产系统pl/sql调优案例 (88天)

昨天基本休息了一天,想着生产系统升级也会多多少少碰到些问题,肯定有一些心得或者是值得学习的东西,结果昨晚到现在生产系统升级一直为一个pl/sql的问题所困扰。在测试环境中只用了十多分钟, 在生产系统上跑了快5个小时。这个经历太痛苦了,大半夜还在考虑怎么优化真是痛苦。 这个也算是一个很深刻的学习经验,和大家分享一下。 pl/sql的大体功能是从用户订购的套餐根据指定的参数来取得所对应的产品编号,然后在订购表中去查询,生成动态的sql语句。看起来功能也不复杂。代码如下: 首先按照要求清除指定的数据,然后在两个循环中去动态的insert。这种实现可能是大家都会使用的一般方式。

delete /*+ parallel( HUGE_PARAMS,8)*/ HUGE_PARAMS where param_name in 
(
'PARAM1',
'PARAM2',
'PARAM3',
'PARAM4',
'PARAM5',
'PARAM6');
COMMIT;

declare
    seq_no number(9);
 
  begin
//根据条件取得相应的产品编号,输出大概有4000条左右。
  for params in (
                 select distinct param_name,offer_code
                 from  OFFER_PARAM where param_name in 
        (
        'PARAM1',
        'PARAM2',
        'PARAM3',
        'PARAM4',
        'PARAM5',
        'PARAM6');     
//在此基础上进行迭代循环,根据取得的产品编号,和一个大表关联,生成insert语句。    HUGE_DATA有大概2000万的数据,而且查询条件没有主键关联。               
  loop
    Dbms_Output.Put_Line ('Parameters:' || params.param_name );
    for subscriber in (
                      select xxxxxx
                        from HUGE_DATA
                       where offer_code = params.offer_code                       
                    )
    loop
      Dbms_Output.Put_Line ('Subscriber:' || .........);
 
    select  HUGE_PARAMS_sq.nextval into seq_no from dual;

//对于参数1,insert语句有一些变化,对于其他的参数,insert的格式都基本一致。HUGE_PARAMS里面有近2000万条记录。  
    IF params.param_name='PARAM1' 
    THEN
 
    INSERT /*+ parallel( HUGE_PARAMS,4) */INTO  HUGE_PARAMS 
        ( HUGE_PARAMS.AGR_LEVEL,  HUGE_PARAMS.EXP_ISSUE_DATE,  HUGE_PARAMS.PARAM_VALUES,  HUGE_PARAMS.EFFECTIVE_DATE, 
         HUGE_PARAMS.EFF_ISSUE_DATE,  HUGE_PARAMS.EXPIRATION_DATE,  HUGE_PARAMS.INS_TRX_ID,  HUGE_PARAMS.PARAM_NAME, 
         HUGE_PARAMS.AGREEMENT_NO,  HUGE_PARAMS.AGREEMENT_KEY,  HUGE_PARAMS.TRX_ID,  HUGE_PARAMS.OFFER_INSTANCE_ID, 
         HUGE_PARAMS.PARAM_SEQ_NO, OPERATOR_ID, APPLICATION_ID, DL_SERVICE_CODE, DL_UPDATE_STAMP, SYS_CREATION_DATE, SYS_UPDATE_DATE) 
    VALUES 
    ('S', subscriber.exp_issue_date, 'N', subscriber.effective_date, subscriber.eff_issue_date, subscriber.expiration_date,subscriber.ins_trx_id, params.param_name, 
           subscriber.agreement_no, subscriber.agreement_key, subscriber.trx_id, subscriber.soc_seq_no, seq_no, subscriber.operator_id, subscriber.application_id, 
           subscriber.dl_service_code, subscriber.dl_update_stamp, subscriber.sys_creation_date, NULL);  
 
    ELSE
 
    INSERT /*+ parallel( HUGE_PARAMS,4) */ INTO  HUGE_PARAMS 
        ( HUGE_PARAMS.AGR_LEVEL,  HUGE_PARAMS.EXP_ISSUE_DATE,  HUGE_PARAMS.PARAM_VALUES,  HUGE_PARAMS.EFFECTIVE_DATE, 
         HUGE_PARAMS.EFF_ISSUE_DATE,  HUGE_PARAMS.EXPIRATION_DATE,  HUGE_PARAMS.INS_TRX_ID,  HUGE_PARAMS.PARAM_NAME, 
         HUGE_PARAMS.AGREEMENT_NO,  HUGE_PARAMS.AGREEMENT_KEY,  HUGE_PARAMS.TRX_ID,  HUGE_PARAMS.OFFER_INSTANCE_ID, 
         HUGE_PARAMS.PARAM_SEQ_NO, OPERATOR_ID, APPLICATION_ID, DL_SERVICE_CODE, DL_UPDATE_STAMP, SYS_CREATION_DATE, SYS_UPDATE_DATE) 
    VALUES 
    ('S', subscriber.exp_issue_date, 0, subscriber.effective_date, subscriber.eff_issue_date, subscriber.expiration_date,subscriber.ins_trx_id, params.param_name, 
           subscriber.agreement_no, subscriber.agreement_key, subscriber.trx_id, subscriber.soc_seq_no, seq_no, subscriber.operator_id, subscriber.application_id, 
           subscriber.dl_service_code, subscriber.dl_update_stamp, subscriber.sys_creation_date, NULL);   
 
    END IF;             
    end loop;
//在子循环后,进行commit
    COMMIT;
  end loop;
 
end;
/

结果等了很久,开发和我们的压力都很大。 大家就试着想想做一个预备方案,看能不能优化一下。 首先的思路就是拆分,能尽量去除循环。 然后尝试把insert ,values的方式改造成insert select的形式。 这样不论需要生成几千几万的insert,values语句,insert,select的形式只需要几个单独的sql语句。 最后在一个临时的空表中进行测试,发现执行只需要不到一分钟。在开发进行了数据的检查后,和期望的一样,数据条数也丝毫不差。

//对于PARAM1的语句,标黄的部分就是有差别的地方。其余部分PARAM2,3,4,5,6都是类似的格式。
###PARAM1的改造
INSERT /*+ parallel(HUGE_PARAMS,4) */INTO HUGE_PARAMS
        (AGR_LEVEL, EXP_ISSUE_DATE, PARAM_VALUES, EFFECTIVE_DATE, 
        EFF_ISSUE_DATE, EXPIRATION_DATE, INS_TRX_ID, PARAM_NAME, 
        AGREEMENT_NO, AGREEMENT_KEY, TRX_ID, OFFER_INSTANCE_ID, 
        PARAM_SEQ_NO, OPERATOR_ID, APPLICATION_ID, DL_SERVICE_CODE, DL_UPDATE_STAMP, SYS_CREATION_DATE, SYS_UPDATE_DATE) 
    select 
    'S', subscriber.exp_issue_date, 'N', subscriber.effective_date, subscriber.eff_issue_date, subscriber.expiration_date,subscriber.ins_trx_id, ’PARAM1', 
           subscriber.agreement_no, mod(subscriber.agreement_no,100), subscriber.trx_id, subscriber.soc_seq_no, HUGE_PARAMS_sq.nextval,subscriber.operator_id, subscriber.application_id, 
           subscriber.dl_service_code, subscriber.dl_update_stamp, subscriber.sys_creation_date, NULL from service_agreement  subscriber
                       where soc in (select distinct soc_cd from OFFER_PARAM where param_name='');
###PARAM2,3,4,5,6的改造
INSERT /*+ parallel(HUGE_PARAMS,4) */INTO HUGE_PARAMS
        (AGR_LEVEL, EXP_ISSUE_DATE, PARAM_VALUES, EFFECTIVE_DATE, 
        EFF_ISSUE_DATE, EXPIRATION_DATE, INS_TRX_ID, PARAM_NAME, 
        AGREEMENT_NO, AGREEMENT_KEY, TRX_ID, OFFER_INSTANCE_ID, 
        PARAM_SEQ_NO, OPERATOR_ID, APPLICATION_ID, DL_SERVICE_CODE, DL_UPDATE_STAMP, SYS_CREATION_DATE, SYS_UPDATE_DATE) 
    select 
    'S', subscriber.exp_issue_date, 0, subscriber.effective_date, subscriber.eff_issue_date, subscriber.expiration_date,subscriber.ins_trx_id, 'PARAM2', 
           subscriber.agreement_no, mod(subscriber.agreement_no,100), subscriber.trx_id, subscriber.soc_seq_no, HUGE_PARAMS_sq.nextval,subscriber.operator_id, subscriber.application_id, 
           subscriber.dl_service_code, subscriber.dl_update_stamp, subscriber.sys_creation_date, NULL from service_agreement  subscriber
                       where soc in (select distinct soc_cd from OFFER_PARAM where param_name='Rolled ATB quota from ensemble');

从pl/sql改造成sql的方式也是根据业务来考虑的。欢迎拍砖。

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

原文发表时间:2014-05-30

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏函数式编程语言及工具

Akka(3): Actor监管 - 细述BackoffSupervisor

    在上一篇讨论中我们谈到了监管:在Akka中就是一种直属父子监管树结构,父级Actor负责处理直属子级Actor产生的异常。当时我们把BackoffSup...

2066
来自专栏AI研习社

如何利用微信监管你的TF训练?

之前回答问题【在机器学习模型的训练期间,大概几十分钟到几小时不等,大家都会在等实验的时候做什么?(http://t.cn/Rl8119m)】的时候,说到可以用微...

3354
来自专栏乐沙弥的世界

ORA-02409:超时:分布式事务处理等待锁定ORA-02063

ORA-02409:超时:分布式事务处理等待锁定ORA-02063 一、错误现象与环境     前端应用程序运行时出现下面的错误提示: 事件添加失败:O...

732
来自专栏函数式编程语言及工具

Akka(8): 分布式运算:Remoting-远程查找式

  Akka是一种消息驱动运算模式,它实现跨JVM程序运算的方式是通过能跨JVM的消息系统来调动分布在不同JVM上ActorSystem中的Actor进行运算,...

2999
来自专栏数据库新发现

Oracle诊断案例-Spfile案例一则

情况说明: 系统:SUN Solaris8 数据库版本:9203 问题描述:工程人员报告,数据库在重新启动时无法正常启动.检查发现UNDO表空间丢失. 问题诊断...

553
来自专栏PPV课数据科学社区

【学习】七天搞定SAS(三):基本模块调用

搞定基本的函数之后,开始鼓捣SAS里面的模型。也就是说,要开始写PROC了。说实话,越学SAS,越觉得SAS像Stata...无论是从输出的样式,还是语法。好不...

2785
来自专栏JackieZheng

Hadoop阅读笔记(三)——深入MapReduce排序和单表连接

  继上篇了解了使用MapReduce计算平均数以及去重后,我们再来一探MapReduce在排序以及单表关联上的处理方法。 在MapReduce系列的第一篇就有...

2077
来自专栏Flutter入门到实战

老司机带你重构Android的v4包的部分源码

版权声明:本文为博主原创文章,未经博主允许不得转载。https://www.jianshu.com/p/a08d754944c4

671
来自专栏哈雷彗星撞地球

RunLoop总结:RunLoop的应用场景(四)App卡顿监测

今天要介绍的RunLoop使用场景很有意思,在做长期项目,需要跟踪解决用户问题非常有用。 使用RunLoop 监测主线程的卡顿,并将卡顿时的线程堆栈信息保存下...

682
来自专栏linux驱动个人学习

SMBus与I2C的差别

782

扫描关注云+社区