并发的批量插入数据的应用,app,db层面的优化

      这里讲述了一个并发的批量插入数据的应用,应用,数据库层面的优化案例。其实是老白讲述的一个案例,我这里稍微整理了一下,其实如果大家的数据库概念清晰的话,思路还是蛮清晰的:

    1.首先,批量插入数据,必然带来大量的重做数据,这个是需要考虑的,所以需要考虑log_buffer的设置和联机日志文件组的设置.应该设置log_buffer为一个合适的大小,避免log buffer space等待(同时也就减小了redo buffer allocation retries系统统计值的持续增大).通过设置合适的日志文件组数和日志文件大小,尽量保证日志文件切换的频率为15-20分钟切换一次,不要因为设置的不合适导致频繁的日志切换，从而导致log file switch(归档未完成,checkpoint未完成)等待的频繁出现,从而影响了系统的性能。

    2.应用层面上来讲,设置一个合适的批量提交条数,避免因为过于频繁的提交导致的log file sync等待从而影响了插入的效率；另外频繁的提交,特别是在这样一个以插入为主的应用中,必然导致大量并发的小的IO操作,IOPS的增大很可能导致IO系统成为一个瓶颈,从而影响系统整体的吞吐量。当然就像上面提到的一样需要根据实际情况进行充分的测试从而找到一个自己系统的最佳的提交条数.
    一般来说,bulk insert(forall insert)相对于一般的insert来说会有性能上的大的提升，当然这需要应用层面做出调整修改了。

    3.物理对象层面的调整:并发插入,如果不同的进程使用同一个数据块来插入数据的话,必然导致数据块的buffer busy waits，从而影响插入性能。那如何解决呢?ASSM表空间是一个不错的选择,甚至可以说ASSM表空间就是针对提升这种大并发的插入数据提出的,它通过3级的BMB,不断的使用插入会话的PID做HASH算法,选择下一级的BMB或者是最终的插入数据块,从而在很大程度上保证不同的进程使用不同的数据块来插入数据,在很大程度上避免上面提到的buffer busy waits导致的插入效率问题。当然如果因为种种原因(比如说因为数据块版本问题)不能使用ASSM，只能使用一般的LMT的话,就可以使用多FREELISTS(FREELISTS的数量最好>=NOP并发插入进程数量)来缓解这个问题,ORACLE内部采用某种算法来选择某个FREELIST链表(可能也是根据PID做HASH算法)上的空闲块来插入数据,也可以在很大程度上保证不同的进程使用不同的FREELIST链表上的数据块在插入数据,从而很大程度上避免BUFFER BUSY WAITS.
    不断的插入数据,空间显然需要不断的扩展,而段是以extent为单位进行扩展的,为了避免频繁不断的扩展空间,需要设置一个比较大的extent大小(assm表空间下的uniform size,或者普通LMT表空间下的initial,next),这样也可以避免不断的提高HMW带来的争用。
    当然也可以考虑使用合适的分区方式来避免buffer busy waits,enq:HW-contention等待等。

**************************************************************************************************************

    这里提到了ASSM表空间,就附录上我去年做的一个实验,ASSM表空间和非ASSM表空间下并行插入大量数据时的性能对比,来说明一下ASSM表空间相对于非ASSM表空间下的对象,在并发插入数据时性能上的提升。

create tablespace tbs_zsj_assm datafile '/home1/oracle/oradata/btoc/tbs_zsj_assm.dbf' size 5120m
extent management local uniform size 1m segment space management auto;

create tablespace tbs_zsj_manual datafile '/home1/oracle/oradata/btoc/tbs_zsj_manual.dbf' size 5120m
extent management local uniform size 1m segment space management manual;
  
create sequence seq_zsj_assm start with 1 nomaxvalue cache 10000;
create sequence seq_zsj_manual start with 1 nomaxvalue cache 10000;
 
alter user btocuser quota unlimited on tbs_zsj_assm;
alter user btocuser quota unlimited on tbs_zsj_manual;

create table t_zsj_assm(id number,name varchar2(20)) tablespace tbs_zsj_assm;
create table t_zsj_manual(id number,name varchar2(20)) tablespace tbs_zsj_manual;
这里非ASSM表空间对象使用默认的物理存储参数,FREELISTS=1
 
create table t_zsj_test(type varchar2(20),pid number,dtime date default sysdate);
 
每个过程插入100W行数据
SQL> create or replace procedure p_zsj_test(v_type varchar2,v_job_no number)
  2  is
  3  begin
  4     insert into t_zsj_test(type,pid) values(v_type,v_job_no);
  5     commit;
  6  
  7     if(v_type='assm') then
  8         for i in 1..1000000
  9         loop
 10            insert into t_zsj_assm(id,name) values(seq_zsj_assm.nextval,'aaaaaaaaaa');
 11         end loop;
 12         commit;
 13     elsif(v_type='manual') then
 14         for i in 1..1000000
 15         loop
 16            insert into t_zsj_manual(id,name) values(seq_zsj_manual.nextval,'aaaaaaaaaa');
 17         end loop;
 18         commit;
 19     end if;
 20     insert into t_zsj_test(type,pid) values(v_type,v_job_no);
 21     commit;
 22  end;
 23  /
 
Procedure created
 
 
SQL> --先测试一下非ASSM表空间的,开10个并行的job来完成插入工作
SQL> declare
  2     v_null pls_integer;
  3     v_job_what varchar2(100);
  4  begin
  5     for i in 1..10
  6     loop
  7         v_job_what:='p_zsj_test(''manual'','||i||');';
  8         dbms_job.submit(job => v_null,what => v_job_what ,next_date => sysdate);
  9     end loop;
 10     commit;
 11  end;
 12  /
 
PL/SQL procedure successfully completed

在一个16核,几乎没有任何负载的机器上运行测试:开始时间:2010-8-19 15:25:06 ,结束时间:2010-8-19 15:32:07
运行了7分钟


查看这个时间段的ash报表,发现:
buffer busy waits      72.03%
CPU + Wait for CPU     18.76%
enq: HW - contention    2.32%


执行时,没有实时的查看v$session_wait,执行完后,很短的时间后立刻查看这个时间段的ash视图信息:
select p3,count(1) cnt 
from v$active_session_history 
where to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') between '2010-08-19 15:25:06' and '2010-08-19 15:32:07' 
      and event='buffer busy waits'
group by p3;


P3      CNT
1       3171
4       1


buffer busy waits等待:p1=file#,p2=block#,p3=class#
实际上这个p3也就是class#对应着v$waitstat里的rownum


select rownum class#,class,count,time from v$waitstat;
这里1对应data block,4对应segment header
也就是说这里的buffer busy waits主要等待的还是数据块,而不是段头块,也就是说不同的进程同时往同一个数据块里插入数据导致的缓冲区忙等待.


顺便提一下这里的enq: HW - contention等待:
无论是ash报表,还是ash视图,都报告p3=33554441(参数的含义是block,实际上这是个dba地址),实际上是段头块:
select *
from v$active_session_history 
where to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') between '2010-08-19 15:25:06' and '2010-08-19 15:32:07' 
      and event='enq: HW - contention';


select dbms_utility.data_block_address_file(33554441) file#,dbms_utility.data_block_address_block(33554441) block# from dual;
8      9


查看它对应的段:
select segment_name,segment_type,tablespace_name from dba_extents where file_id=8 and 9 between block_id and block_id+blocks-1;
T_ZSJ_MANUAL      TABLE     TBS_ZSJ_MANUAL


select header_file,header_block from dba_segments where segment_name='T_ZSJ_MANUAL' and segment_type='TABLE';
8      9


它刚好是t_zsj_manual这个段的段头,这很正常,因为段头存储着hwm,extent map,第一个L3 BMB的指针和L2 BMB地址列表
enq: HW - contention等待,这个等待是因为插入数据,需要提高HWM的时候,不断的修改段头的HWM造成的,
解决方案:
1.将这个段通过分区(比如hash分区)等方式分解为多个段,原来是一个段,现在是多个段,修改段头HWM造成的enq: HW - contention等待自然会减少.
2.对于ASSM表空间来说,使用一个较大的UNIFORM SIZE,也就是使用较大的extent,这样可以不必过于频繁的分配extent,从而减小enq: HW - contention等待。(这一点下一节的实验验证一下)

对于HHWM和LHWM，可以参见tanel的一段话
ASSM high-HWM extensions are not done over extent boundaries IIRC, thus with small extents you'd have lots of extensions to do,
each requiring HW-enqueue get. ASSM relieves HW-enqueue contention by increasing HHWM in large sizes 
(depending on extent and current segment size), so less HW operations have to be done.

The LHWM extensions can be done without having HW-enqueue as they involve only changing few records in a BMB block and the BMB 
is pinned for that exclusively anyway. The actual batch formatting of datablocks is done while having FB enqueue, allowing 
finer granularity of locking block ranges in case of multiple parallel inserts.

再看一下ASSM的表现:
SQL> --再测试一下ASSM表空间的,也是开10个并行的job来完成插入工作
SQL> declare
  2     v_null pls_integer;
  3     v_job_what varchar2(100);
  4  begin
  5     for i in 1..10
  6     loop
  7         v_job_what:='p_zsj_test(''assm'','||i||');';
  8         dbms_job.submit(job => v_null,what => v_job_what ,next_date => sysdate);
  9     end loop;
 10     commit;
 11  end;
 12  /
 
PL/SQL procedure successfully completed

相同机器相同环境下的ASSM运行:开始时间:2010-8-19 16:24:08 ,结束时间:2010-8-19 16:26:57
运行了2.8分钟

查看ash报表,不关注其它的:
buffer busy waits    3.30%
只占了3.3%

select p3,count(1) cnt
from v$active_session_history 
where to_char(sample_time,'yyyy-mm-dd hh24:mi:ss') between '2010-08-19 16:24:08' and '2010-08-19 16:26:57' and event='buffer busy waits'
group by p3;
P3      CNT
1       65

    使用非ASSM表空间,10个并发进程插入数据,总时间为7分钟;而使用ASSM表空间,总的插入时间为2.8分钟,可以看到ASSM表空间相对于非ASSM表空间下使用默认的物理存储参数(freelists=1)的对象来说,效率上的提升还是很明显的。
    ASSM表空间性能提升的原因何在呢?在ASSM中,从段头中寻找第一个L3 BMB,可用的L2 BMB,从这个L2位图管理块中根据插入会话的进程PID做HASH运算确定一个L1 BMB,在这个L1 BMB中根据会话进程PID做HASH运算确定使用哪个数据块来插入数据。这样就可以很大程度上避免不同的进程使用同一个数据块来插入数据带来的data block的buffer busy waits.从而大幅提升了并发插入的效率。