昨天基本休息了一天，想着生产系统升级也会多多少少碰到些问题，肯定有一些心得或者是值得学习的东西，结果昨晚到现在生产系统升级一直为一个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的方式也是根据业务来考虑的。欢迎拍砖。
