生产系统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 条评论
登录 后参与评论

相关文章

来自专栏转载gongluck的CSDN博客

cocos2dx 打灰机

#include "GamePlane.h" #include "PlaneSprite.h" #include "BulletNode.h" #include...

5656
来自专栏张善友的专栏

Mix 10 上的asp.net mvc 2的相关Session

Beyond File | New Company: From Cheesy Sample to Social Platform Scott Hansel...

2617
来自专栏杨龙飞前端

scrollto 到指定位置

2544
来自专栏一个爱瞎折腾的程序猿

sqlserver使用存储过程跟踪SQL

USE [master] GO /****** Object: StoredProcedure [dbo].[sp_perfworkload_trace_s...

2110
来自专栏张善友的专栏

Miguel de Icaza 细说 Mix 07大会上的Silverlight和DLR

Mono之父Miguel de Icaza 详细报道微软Mix 07大会上的Silverlight和DLR ,上面还谈到了Mono and Silverligh...

2717
来自专栏菩提树下的杨过

Flash/Flex学习笔记(23):运动学原理

先写一个公用的小球类Ball: package{ import flash.display.Sprite; //小球 类 public class B...

25410
来自专栏芋道源码1024

熔断器 Hystrix 源码解析 —— 断路器 HystrixCircuitBreaker

本文主要基于 Hystrix 1.5.X 版本 1. 概述 2. HystrixCircuitBreaker 3. HystrixCircuitBreaker....

5377
来自专栏大内老A

The .NET of Tomorrow

Ed Charbeneau(http://developer.telerik.com/featured/the-net-of-tomorrow/) Exciti...

32210
来自专栏Ceph对象存储方案

Luminous版本PG 分布调优

Luminous版本开始新增的balancer模块在PG分布优化方面效果非常明显,操作也非常简便,强烈推荐各位在集群上线之前进行这一操作,能够极大的提升整个集群...

3225
来自专栏魂祭心

原 canvas绘制clock

4204

扫码关注云+社区