开发人员找到我说，报表系统中有一个存储过程最近总是报错ORA-02050:transaction … . rolled back,some remote DBs may be in-doubt。
根据错误信息可知，可能是由于远程数据库处理失败，导致事务失败而回滚。原因可能是网络不稳定，也可能是remote节点的连接超时，也有可能被kill了等。
系统室负责人说了，公司的网络绝对没问题，报错肯定是你们程序写的有问题，所以只能从代码上找原因了。
咨询了开发人员大概的情况，说是这个存储过程只操作了一张表，单纯的insert数据，每次的数据量基本固定在4500万行左右。我粗略看了下存储过程，
此存储过程的核心操作其实就是一个DML的大事务，估计一个小时执行不完。同时我向开发人员验证了，他们说不报错的时候都是在一个小时内执行完毕的，
执行时间超过一个小时就会报错ORA-02050。至此，可以推断出，不是网络问题，是程序太耗时，运行时间超过了remote端设置的超时时间(即1小时)。
因此，我向开发人员提议，改写存储过程，把大事务粒度化，从而提高执行时间，提高存储过程的执行性能。
源存储过程源码如下：
procedure SP_RF_CB_ACCT(kjrq in varchar2,e_errcode out char) is
--**定义存储过程使用的相关变量
...省略业务相关的变量...
--**删除目标表的数据
EXECUTE IMMEDIATE 'truncate table CB_ACCT';
--**把表的数据加载到目标表中
INSERT /*+ APPEND */ INTO CB_ACCT NOLOGGING
(
ACCT_NO ,
CURRENCY ,
GL_ACCT_NO ,
BRANCH_NO ,
CUSTOMER_NO ,
SYS_ID ,
ACCT_TYPE ,
OPEN_DATE ,
LST_MNT_DT ,
STATUS ,
CUSTOMER_NAME ,
CURR_VAL ,
CURR_VAL_TOCNY ,
SOUR_CODE ,
CUSTOMER_TYPE
)
select
trim(ACCT_NO) ,
trim(CURRENCY) ,
trim(GL_ACCT_NO) ,
trim(BRANCH_NO) ,
trim(CUSTOMER_NO),
trim(SYS_ID) ,
trim(ACCT_TYPE) ,
OPEN_DATE ,
LST_MNT_DT ,
trim(STATUS) ,
utl_raw.cast_to_varchar2(CUSTOMER_NAME) CUSTOMER_NAME ,
CURR_VAL ,
CURR_VAL_TOCNY ,
trim(SOUR_CODE) ,
trim(CUSTOMER_TYPE)
from v_CB_ACCT@REPORT_RIFM;
--**记录成功执行的记录数
v_success := sql%rowcount;
commit;
exception
--**总程序异常处理部分
when others then
begin
...省略一些业务处理逻辑....
end;
end SP_RF_CB_ACCT;
分析以上的存储过程代码：v_CB_ACCT@REPORT_RIFM是通过DBLINK从remote端获取4500行数据，一次性插入到本地的目标中，明显是个大事务。
使用嵌套表，改写后的存储过程如下：
procedure SP_RF_CB_ACCT(kjrq in varchar2,e_errcode out char) is
--**定义存储过程使用的相关变量
......此处省略......
--定义CUESOR,此处会获取到4500万行左右的记录
CURSOR cb_cur IS
select trim(ACCT_NO),
trim(CURRENCY),
trim(GL_ACCT_NO),
trim(BRANCH_NO),
trim(CUSTOMER_NO),
trim(SYS_ID),
trim(ACCT_TYPE),
OPEN_DATE,
LST_MNT_DT,
trim(STATUS),
utl_raw.cast_to_varchar2(CUSTOMER_NAME) CUSTOMER_NAME,
CURR_VAL,
CURR_VAL_TOCNY ,
trim(SOUR_CODE),
trim(CUSTOMER_TYPE)
from v_CB_ACCT@REPORT_RIFM;
--定义与表CB_ACCT中每个列的类型对应的类型
TYPE type_ACCT_NO IS TABLE OF CB_ACCT.ACCT_NO%TYPE;
TYPE type_CURRENCY IS TABLE OF CB_ACCT.CURRENCY%TYPE;
TYPE type_GL_ACCT_NO IS TABLE OF CB_ACCT.GL_ACCT_NO%TYPE;
TYPE type_BRANCH_NO IS TABLE OF CB_ACCT.BRANCH_NO%TYPE;
TYPE type_CUSTOMER_NO IS TABLE OF CB_ACCT.CUSTOMER_NO%TYPE;
TYPE type_SYS_ID IS TABLE OF CB_ACCT.SYS_ID%TYPE;
TYPE type_ACCT_TYPE IS TABLE OF CB_ACCT.ACCT_TYPE%TYPE;
TYPE type_OPEN_DATE IS TABLE OF CB_ACCT.OPEN_DATE%TYPE;
TYPE type_LST_MNT_DT IS TABLE OF CB_ACCT.LST_MNT_DT%TYPE;
TYPE type_STATUS IS TABLE OF CB_ACCT.STATUS%TYPE;
TYPE type_CUSTOMER_NAME IS TABLE OF CB_ACCT.CUSTOMER_NAME%TYPE;
TYPE type_CURR_VAL IS TABLE OF CB_ACCT.CURR_VAL%TYPE;
TYPE type_CURR_VAL_TOCNY IS TABLE OF CB_ACCT.CURR_VAL_TOCNY%TYPE;
TYPE type_SOUR_CODE IS TABLE OF CB_ACCT.SOUR_CODE%TYPE;
TYPE type_CUSTOMER_TYPE IS TABLE OF CB_ACCT.CUSTOMER_TYPE%TYPE;
--定义嵌套表
acct_no_tab type_ACCT_NO;
currency_tab type_CURRENCY;
gl_acct_no_tab type_GL_ACCT_NO;
branch_no_tab type_BRANCH_NO;
customer_no_tab type_CUSTOMER_NO;
sys_id_tab type_SYS_ID;
acct_type_tab type_ACCT_TYPE;
open_date_tab type_OPEN_DATE;
lst_mnt_dt_tab type_LST_MNT_DT;
status_tab type_STATUS;
customer_name_tab type_CUSTOMER_NAME;
curr_val_tab type_CURR_VAL;
curr_val_tocny_tab type_CURR_VAL_TOCNY;
sour_code_tab type_SOUR_CODE;
customer_type_tab type_CUSTOMER_TYPE;
--定义分批插入时，每次插入的最大数据条目,5百万行
v_limit pls_integer := 5000000;
begin
--**删除目标表的数据
EXECUTE IMMEDIATE 'truncate table CB_ACCT';
--**把表的数据加载到目标表中
OPEN cb_cur;
LOOP
FETCH cb_cur BULK COLLECT INTO acct_no_tab,
currency_tab,
gl_acct_no_tab,
branch_no_tab,
customer_no_tab,
sys_id_tab,
acct_type_tab,
open_date_tab,
lst_mnt_dt_tab,
status_tab,
customer_name_tab,
curr_val_tab,
curr_val_tocny_tab,
sour_code_tab,
customer_type_tab LIMIT v_limit;
EXIT WHEN acct_no_tab.COUNT=0;
FORALL i in 1..acct_no_tab.COUNT
INSERT /*+ APPEND */ INTO CB_ACCT NOLOGGING
(ACCT_NO,CURRENCY,GL_ACCT_NO,BRANCH_NO,CUSTOMER_NO,SYS_ID, ACCT_TYPE,
OPEN_DATE,LST_MNT_DT,STATUS,CUSTOMER_NAME,CURR_VAL,CURR_VAL_TOCNY,SOUR_CODE,CUSTOMER_TYPE
) values (
acct_no_tab(i),
currency_tab(i),
gl_acct_no_tab(i),
branch_no_tab(i),
customer_no_tab(i),
sys_id_tab(i),
acct_type_tab(i),
open_date_tab(i),
lst_mnt_dt_tab(i),
status_tab(i),
customer_name_tab(i),
curr_val_tab(i),
curr_val_tocny_tab(i),
sour_code_tab(i),
customer_type_tab(i)
);
--**记录成功执行的记录数
v_success := v_success + sql%rowcount;
commit;
END LOOP;
CLOSE cb_cur;
-----**调用日志存储过程写入日志数据
此处省略。。。
--------------------****** FDM 数据处理完成 *****------------------------------------
exception
--**总程序异常处理部分
when others then
IF cb_cur%ISOPEN
THEN CLOSE cb_cur;
END IF;
begin
--将错误信息插入错误日志表etl_errlog
insert into etl_errlog(......省略.....)
VALUES(......省略......);
commit;
end;
end SP_RF_CB_ACCT;
也可以使用记录RECORD类型，嵌套表来实现以上功能。
procedure SP_RF_CB_ACCT(kjrq in varchar2,e_errcode out char) is
--**定义存储过程使用的相关变量
......此处省略......
--定义CUESOR,此处会获取到4500万行左右的记录
CURSOR cb_cur IS
select trim(ACCT_NO),
trim(CURRENCY),
trim(GL_ACCT_NO),
trim(BRANCH_NO),
trim(CUSTOMER_NO),
trim(SYS_ID),
trim(ACCT_TYPE),
OPEN_DATE,
LST_MNT_DT,
trim(STATUS),
utl_raw.cast_to_varchar2(CUSTOMER_NAME) CUSTOMER_NAME,
CURR_VAL,
CURR_VAL_TOCNY ,
trim(SOUR_CODE),
trim(CUSTOMER_TYPE)
from v_CB_ACCT@REPORT_RIFM;
--定义基于表类型的变量
TYPE r_cb_acct IS RECORD(
ACCT_NO CB_ACCT.ACCT_NO%TYPE;
CURRENCY CB_ACCT.CURRENCY%TYPE;
GL_ACCT_NO CB_ACCT.GL_ACCT_NO%TYPE;
BRANCH_NO CB_ACCT.BRANCH_NO%TYPE;
CUSTOMER_NO CB_ACCT.CUSTOMER_NO%TYPE;
SYS_ID CB_ACCT.SYS_ID%TYPE;
ACCT_TYPE CB_ACCT.ACCT_TYPE%TYPE;
OPEN_DATE CB_ACCT.OPEN_DATE%TYPE;
LST_MNT_DT CB_ACCT.LST_MNT_DT%TYPE;
STATUS CB_ACCT.STATUS%TYPE;
CUSTOMER_NAME CB_ACCT.CUSTOMER_NAME%TYPE;
CURR_VAL CB_ACCT.CURR_VAL%TYPE;
CURR_VAL_TOCNY CB_ACCT.CURR_VAL_TOCNY%TYPE;
SOUR_CODE CB_ACCT.SOUR_CODE%TYPE;
CUSTOMER_TYPE CB_ACCT.CUSTOMER_TYPE%TYPE
);
--定义嵌套表
TYPE type_cb_acct IS TABLE OF r_cb_acct;
cb_acct_tab type_cb_acct;
--定义分批插入时，每次插入的最大数据条目,5百万行
v_limit pls_integer := 5000000;
begin
--**删除目标表的数据
EXECUTE IMMEDIATE 'truncate table CB_ACCT';
--**把表的数据加载到目标表中
OPEN cb_cur;
LOOP
FETCH cb_cur BULK COLLECT INTO cb_acct_tab LIMIT v_limit;
EXIT WHEN cb_acct_tab.COUNT=0;
FORALL i in cb_acct_tab.FIRST..cb_acct_tab.LAST
INSERT /*+ APPEND */ INTO CB_ACCT NOLOGGING
(ACCT_NO,CURRENCY,GL_ACCT_NO,BRANCH_NO,CUSTOMER_NO,SYS_ID, ACCT_TYPE,
OPEN_DATE,LST_MNT_DT,STATUS,CUSTOMER_NAME,CURR_VAL,CURR_VAL_TOCNY,SOUR_CODE,CUSTOMER_TYPE
) values (
cb_acct_tab(i).ACCT_NO,
cb_acct_tab(i).CURRENCY,
cb_acct_tab(i).GL_ACCT_NO,
cb_acct_tab(i).BRANCH_NO,
cb_acct_tab(i).CUSTOMER_NO,
cb_acct_tab(i).SYS_ID,
cb_acct_tab(i).ACCT_TYPE,
cb_acct_tab(i).OPEN_DATE,
cb_acct_tab(i).LST_MNT_DT,
cb_acct_tab(i).STATUS,
cb_acct_tab(i).CUSTOMER_NAME,
cb_acct_tab(i).CURR_VAL,
cb_acct_tab(i).CURR_VAL_TOCNY,
cb_acct_tab(i).SOUR_CODE,
cb_acct_tab(i).CUSTOMER_TYPE
);
--**记录成功执行的记录数
v_success := v_success + sql%rowcount;
commit;
END LOOP;
CLOSE cb_cur;
-----**调用日志存储过程写入日志数据
此处省略。。。
--------------------****** FDM 数据处理完成 *****------------------------------------
exception
--**总程序异常处理部分
when others then
IF cb_cur%ISOPEN
THEN CLOSE cb_cur;
END IF;
begin
--将错误信息插入错误日志表etl_errlog
insert into etl_errlog(......省略.....)
VALUES(......省略......);
commit;
end;
end SP_RF_CB_ACCT;
改写后存储过程的执行性能大有提升，从之前的耗时一个多小时，优化成了15分钟内完成，性能提升了75%以上。
优化该存储过程的关键点有：
①大事务改成粒度小的小事务，通过v_limit来限制每次操作的条目数。
②使用批量操作的功能BULK COLLECT … LIMIT。
③使用FORALL来避免SQL引擎和plsql引擎频繁切换的消耗。
1010
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
