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

相关文章

来自专栏数据和云

Thinking in SQL系列之数据挖掘C4.5决策树算法

作者简介:牛超 ? 10多年数据库技术积累,长期从事ORACLE数据库管理与开发工作。精通企业级数据库应用设计、SQL、算法实现、异常分析、性能优化。目前...

3536
来自专栏软件测试经验与教训

测试工程师SQL面试题

测试人员工作在工作中会用到SQL来辅助测试,求职时也常常会在笔试环节遇到各种各样的sql设计题目,张老师整理了一些工作中常用的sql知识点,希望对大家有所帮助。

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

Oracle BIEE (Business Intelligence) 11g 11.1.1.6.0 学习(4)创建多维钻取分析

  在上一节时,我们创建了一个基于部门号的工资分类汇总。 这里就引出了一个概念:维度   专业的解释大家自行百度,这里就不班门弄斧了。从数据的使用角度看,维度可...

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

关于索引扫描的极速调优实战(第一篇) (r3笔记第81天)

一般在生产环境中,如果某个查询中涉及一个大表,走索引扫描是显然是最值得推荐的方式,但是索引扫描有unique index scan, range scan,sk...

2224
来自专栏乐沙弥的世界

NULL 值与索引(二)

    在NULL值与索引(一)中讲述了null值与索引的一些基本情况。其主要的内容为,基于允许存在null值的索引列,其索引值不会被存储;其次 是由于这个特...

522
来自专栏happyJared

爬虫进阶:Scrapy抓取慕课网

  完整的爬虫流程大致是这样的:分析页面结构 -> 确定提取信息 -> 设计相应表结构 -> 编写爬虫脚本 -> 数据保存入库;入库可以选择mongo这样的文档...

1244
来自专栏文渊之博

如何根据日志查看删除的数据(转译)

原文地址:https://raresql.com/2011/10/22/how-to-recover-deleted-data-from-sql-sever/ ...

18010
来自专栏乐沙弥的世界

Oracle 索引质量分析

      索引质量的高低对数据库整体性能有着直接的影响。良好高质量的索引使得数据库性能得以数量级别的提升,而低效冗余的索引则使得数据库性能缓慢如牛,即便是使用...

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

无限级分类(非递归算法/存储过程版/GUID主键)完整数据库示例_(1)表结构

无限分类是一个老生常谈的话题了,网上有很多解决方案,可以分成二个流派,一种利用递归,一种利用非递归(当然需要其它一些辅助手段判断节点层次),但核心表结构都差不多...

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

生产sql调优之统计信息分析(89天)

今天凌晨,又被电话叫醒了,说是有1个sql,现在跑的很慢。问题已经挺严重了,想让我看看,能不能做点什么。 首先就是和他们确认最近有什么改动,他们说这个是用了很...

2516

扫码关注云+社区