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

相关文章

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

关于ORA-01555的问题分析(r5笔记第87天)

今天开发的同事发给我一个问题,在运行某一个Job的时候抛出了ORA错误,希望我们看看从数据库层面能不能发现什么。 错误日志如下: Function: Entit...

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

完美的执行计划导致的性能问题(r4笔记第17天)

今天现场的开发同事反馈有一个job处理数据的速度很慢,从半夜2点开始运行,结果到了早上8点还没有运行完,最后无奈kill掉了进程。等我刚到公司,他们想让我查查倒...

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

巧用外部表避免大量的insert (r4笔记第71天)

昨天开发咨询我一个问题,希望我对下面的语句进行调优。 语句类似下面的形式 SELECT subscriber_no FROM SUBSCRIBER S W...

3478
来自专栏乐沙弥的世界

Oracle 聚簇因子(Clustering factor)

    聚簇因子是 Oracle 统计信息中在CBO优化器模式下用于计算cost的参数之一,决定了当前的SQL语句是否走索引,还是全表扫描以及是否作为嵌套连接外...

1301
来自专栏数据和云

追本溯源:Oracle 只读表空间的探索实践

作者简介 ? 胡中豪 云和恩墨西区交付工程师,多年一线 DBA 经验,曾服务于运营商、电网、政府行业、银行等行业客户;擅长数据库故障处理、性能优化、实施升级 本...

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

CPU 100%负载的性能优化分析(r7笔记第40天)

今天收到报警邮件,提示在短时间内DB time有了很大的抖动。报警邮件如下: ZABBIX-监控系统: ------------------------...

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

物化视图相关的性能改进 (r7笔记第58天)

今天早上开发的一个同事找到我说他早上做了一个统计查询,但是感觉速度很慢,已经过了一个小时了还没有反应。想让我看看是什么情况。 我通过v$session查到有一个...

3355
来自专栏数据库新发现

关于dirty buffer

SQL> select VIEW_DEFINITION from v$fixed_view_definition where VIEW_NAME = 'GV$B...

983
来自专栏乐沙弥的世界

PL/SQL 包编译时hang住的处理

       最近PL/SQL包在编译时被hang住,起初以为是所依赖的对象被锁住。结果出乎意料之外。下面直接看代码演示。

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

一条运行了3天的"简单"的sql(r2笔记82天)

早上刚到公司,查看系统的负载,就马上看到一个进程的执行时间已经有3天了。 而且cpu的消耗极高。 Tasks: 2374 total, 19 running,...

3155

扫码关注云+社区