POC测试中SQL改写的小技巧

1 概述

随着国产化趋势的来临，越来越多的系统需要迁移到国产环境来，这其中最重要的就是数据库的迁移，数据库本身的架构和容灾实现方式已经进行了大量的测试，sql优化的流程顺序也基本定型，但是具体业务sql的改造，脱离了源环境的复杂sql业务在新环境的运行效率就成了迁移成功与否的重要指标，甚至是可否迁移的决定性因素，怎么减轻大行数的存储过程改写中遇到的问题，现场很重视，但却容易被大家所忽略。结合多次POC测试现场的实际工作经历和经验，总结了几条。

2 输入输出参数的关注

输入输出作为存储过程执行前后参数带入和结果集展示往往是需要打印在日志中，方便与源环境对比的重要信息，经过并发测试还是单语句执行，都可以在日志中清晰的看到SQL完整的执行过程；测试中通过下面这个语句完成了大量日志方面改写的工作；

2.1 存储过程设计

create or replace ksdwrt(i_num number default 1,i_str varchar2 default null,i_path varchar2,i_filename varchar2 )
as
logpath       varchar2(128) := i_path;  
outfile       utl_file.file_type;
log_date      varchar2(32) := to_char(sysdate, 'yyyymmdd');
file_name     varchar2(64) := i_filename;
file_exists   boolean :=false;

begin
      if i_num = 1 then
           utl_file.fgetattr(logpath,file_name, file_exists, null, null);
          if file_exists then
              outfile:=utl_file.fopen(logpath,file_name,'a',32767); 
              utl_file.put_line(outfile,i_str);
              utl_file.fflush(outfile);
              utl_file.fclose(outfile);
          else
               outfile:=utl_file.fopen(logpath,file_name,'w'); 
               utl_file.put_line(outfile,i_str);
               utl_file.fflush(outfile);
               utl_file.fclose(outfile);
          end if;
     end if;
exception
 when others then
   print('err');
end ksdwrt;

2.2 测试结果

create directory "LOG_PATH" as '/dm/dmbak';

CREATE or REPLACE PROCEDURE  test_proc()
as
  start_time timestamp;
  end_time timestamp;
begin
select sysdate into start_time;
ksdwrt(1, '开始时间=['||start_time||']','LOG_PATH','test_proc.log');
select sysdate into end_time;
ksdwrt(1, '结束时间=['||end_time||']','LOG_PATH','test_proc.log');
ksdwrt(1, '耗时=['||timestampdiff(ss,start_time,end_time)||'秒]','LOG_PATH','test_proc.log');
EXCEPTION
  WHEN OTHERS THEN
  	ROLLBACK;
    RAISE;
end;

call test_proc();

3 每部分是否顺序执行

总体看一个完整的存储过程每个部分是否正常执行，执行中是否因为某些原因而跳出，添加必要的信息采集内容就可以清楚执行顺序；

3.1 存储过程设计

create table test_sxzx(id int ,name varchar2(100));
create or replace procedure test_sxzx_procedure(input varchar2(20))
as 
begin
insert into test_sxzx values(input,'name');
commit;
exception
when others then
print 'sql_code->'||sqlcode||'  err ->'||sqlerrm||' :test_sxzx err';
end;

call test_sxzx_procedure('test');

3.2 测试结果

DMSQL 程序提供了内置函数 SQLCODE 和 SQLERRM，程序员可以在异常处理部分通过这两个函数获取异常对应的错误码和描述信息。SQLCODE 返回错误码，为一个负数。SQLERRM 返回异常的描述信息，为字符串类型。
若异常为 DM 服务器错误，则 SQLERRM 返回该错误的描述信息，否则 SQLERRM 的返回值遵循以下规则：
如果错误码在-15000 至-19999 间，返回'User-Defined Exception'；
如果错误码在-20000 至-30000 之间，返回'DM-<错误码绝对值>'；
如果错误码大于 0 或小于-65535，返回'-<错误码绝对值 >: non-DMexception'；
否则，返回'DM-<错误码绝对值>: Message <错误码绝对值> not found;'。

4 批量参数读取和批量提交

大数据量的执行在测试中会出现执行效率低下，执行结果因为硬件上限的影响，导致整体测试出现严重的导向性：

4.1 存储过程设计

create table test_dqtj(id int ,name varchar2(100), bz varchar2(200));
declare
begin
for i in 1..1000 loop
insert into test_dqtj values(i, i||'test','');
commit;
end loop;
end;
create or replace procedure test_dqtj_procedure()
as
 p_query varchar2;
 test_dqtj_name varchar2;
 type type_test_dqtj is table of test_dqtj%rowtype index by binary_integer;
 v_test_dqtj type_test_dqtj;
 type type_test_dqtj_cursor is ref cursor;
 cur_test_dqtj type_test_dqtj_cursor;
 v_batchnum number:=100;
 v_cnt number :=0;
 v_commit_cnt number :=10;
begin
   p_query:='select * from test_dqtj';
   open cur_test_dqtj for p_query;
   loop
   	fetch cur_test_dqtj bulk collect into v_test_dqtj limit v_batchnum;
   	if v_test_dqtj.count = 0 then
   	    exit;
   	end if;
   	for i in v_test_dqtj.first .. v_test_dqtj.last loop
   	     v_cnt := v_cnt +1;
   	    test_dqtj_name:=v_test_dqtj(i).name;
   	    update test_dqtj set bz = test_dqtj_name;
   	    if v_cnt >= v_commit_cnt then
   	       commit;
   	       v_cnt :=0;
   	    end if;
  		end loop;	
  end loop;
exception
 when others then
 	rollback;
   raise;
end;

call test_dqtj_procedure();

批量提交大限度减少insert-select逻辑SQL，在读取原表时因undo空间回滚时间限制关系和大量数据占用临时空间造成的SQL执行缓慢，甚至无法执行而大量回滚。

POC测试中SQL改写的小技巧