Oracle 批量插入sql优化一例

概念

大多数程序员在进行大数据量任务的加工处理时，因考虑可能没有足够的undo空间，或者认为频繁地提交大量小事务 会比处理和 提交一个大事务 更快，也更高效，因此总是力图找出一种过程性的方法，通过循环来完成这个任务。

这是一种不好的存在误导性的开发习惯，最好的办法是按业务的要求以适当的频度提交，并且相应地设置undo大小。

通常频繁的提交并不会更快，一般地在一个sql语句完成工作几乎总是更快一些，应该充分利用sql的批量处理能力。

我们尝试添加 APPEND 和 打开强制并行（这里 并行度 指定为4是参考了测试环境cpu核数）：

CREATE OR REPLACE PROCEDURE P_PERM_TEST03
/*  过程功能: 加工全量储蓄主表           
	输入参数:批处理日期 I_TRANDATE       
	输出参数:无  */
( I_REGCOD       VARCHAR2,   --日期格式为YYYYMMDD
  I_TRANDATE    VARCHAR2   --日期格式为YYYYMMDD
) IS
 
BEGIN
 
EXECUTE IMMEDIATE ' ALTER SESSION FORCE PARALLEL QUERY PARALLEL 4 ';
EXECUTE IMMEDIATE ' ALTER SESSION FORCE PARALLEL DML PARALLEL 4 ';
EXECUTE IMMEDIATE ' TRUNCATE TABLE TAB_PERM_TEST03 ';
 
  INSERT /*+ append nologging */  
  INTO TAB_PERM_TEST03
      SELECT   A.APPG_DATE,     A.REGCOD,      A.MEMB_CUST_AC,    A.CUSTOMER_NO,
                   A.BRANCH_NO,   A.CURRENCY_IND,   A.CURR_BAL
      FROM A_BOCS_INVM1 A
      WHERE A.REGCOD = I_REGCOD
      AND NOT EXISTS ( SELECT 1 FROM A_BOCS_INVM2 B  WHERE A.REGCOD = B.REGCOD
                                      AND A.MEMB_CUST_AC = B.MEMB_CUST_AC   )
      UNION ALL
      SELECT A.APPG_DATE,    A.REGCOD,   A.MEMB_CUST_AC,     A.CUSTOMER_NO,
                A.BRANCH_NO,      A.CURRENCY_IND,    A.CURR_BAL
      FROM A_BOCS_INVM2 A
      WHERE A.REGCOD = I_REGCOD  AND  A.APPG_DATE = I_TRANDATE ;
  COMMIT ;
  
EXECUTE IMMEDIATE ' ALTER SESSION enable parallel query ' ;
EXECUTE IMMEDIATE ' ALTER SESSION disable parallel dml ' ;
 
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
  
END;

先清空 shared_pool 和 buffer_cached；

SQL> alter system flush shared_pool ; 
System altered.
Elapsed: 00:00:00.42

SQL> alter system flush buffer_cache ;
System altered.
Elapsed: 00:00:00.11

SQL> begin P_PERM_TEST03 ( I_REGCOD => ' ',  I_TRANDATE => '20160831' ) ; end ;  
  2  /

PL/SQL procedure successfully completed.
Elapsed: 00:00:24.16
SQL> select count(1) from TAB_PERM_TEST03 t; 

COUNT(1)
----------
  38963190

我们观察到在打开4个并行的情况下（当前这个机器只有我在用），插入4kw数据仅用了24秒，插入速度再次提升了近6倍，平均每秒达到了166w。

最后完成生产改造后，生产上近12亿的数据量，插入耗时40分钟左右，平均每秒将近50w条插入的速度，虽然相比测试性能有较大的差距，但也比改造前提升了一个数量级。

优化总结
1.应该充分利用sql的批量处理能力，减少面向过程性的加工方式，不要频繁的提交；
2.当加工的任务量非常大时，应该充分利用数据库的并行性能。