sqlplus scott/oracle


一、创建测试表、序列和脚本
create table tmp1(id varchar2(1000));
create index idx_tmp1 on tmp1(id)  ;

create sequence testseq
increment by 1 
start with 10001 
maxvalue 999999999 
cycle
cache 500000;

cat insert_date.sh

#!/bin/bash
for((i=1;i<=100;i++));
do
(
sqlplus -S /nolog <<EOF
conn scott/oracle
begin 
  for c_f in (
     select level as id
       from dual
      connect by level <=10000)
  loop
     insert into tmp1 values(testseq.NEXTVAL);
     commit;
  end loop;
end;
/
exit
EOF
)&
done
wait


二、模拟100并行插入

--1>场景一：未做任何优化，模拟100并行插入
sh insert_date.sh


col event for a30
set linesize  1000
select event,count(1)
      from gv$active_session_history a
        where sample_time >=to_date('20240820 16:44:35','yyyymmdd hh24:mi:ss')
         and sample_time <=to_date('20240820 16:48:03','yyyymmdd hh24:mi:ss')
		 and sql_id='gs8gm0c1gx8p2'
	group by event
	order by count(1);

EVENT                            COUNT(1)
------------------------------ ----------
cursor: pin S                           1
enq: TX - allocate ITL entry            2
latch: enqueue hash chains              7
latch: cache buffers chains             8
library cache: mutex X                 23
latch: In memory undo latch            50
                                      216
buffer deadlock                       224
enq: TX - index contention           4156
buffer busy waits                   12627

select count(1)
      from gv$active_session_history a
        where sample_time >=to_date('20240820 16:44:35','yyyymmdd hh24:mi:ss')
         and sample_time <=to_date('20240820 16:48:03','yyyymmdd hh24:mi:ss')
		 and sql_id='gs8gm0c1gx8p2';
  COUNT(1)
----------
      17314

--2>场景二：修改为反向索引，模拟100并行插入

--重建测试表
drop table  tmp1;
create table tmp1(id varchar2(1000));
create index idx_tmp1 on tmp1(id)  reverse;


--重建序列
drop sequence testseq;
create sequence testseq
increment by 1 
start with 10001 
maxvalue 999999999 
cycle
cache 500000;



select event,count(1)
      from gv$active_session_history a
        where sample_time >=to_date('20240820 16:54:29','yyyymmdd hh24:mi:ss')
         and sample_time <=to_date('20240820 16:56:30','yyyymmdd hh24:mi:ss')
		 and sql_id='gs8gm0c1gx8p2'
	group by event
	order by count(1);

EVENT                                      COUNT(1)
---------------------------------------- ----------
enq: TX - index contention                      252
latch free                                      368
library cache: mutex X                          504
                                                535
latch: In memory undo latch                     551
latch: redo copy                                748
buffer busy waits                              2371




select count(1)
      from gv$active_session_history a
        where sample_time >=to_date('20240820 16:54:29','yyyymmdd hh24:mi:ss')
         and sample_time <=to_date('20240820 16:56:30','yyyymmdd hh24:mi:ss')
		 and sql_id='gs8gm0c1gx8p2';

  COUNT(1)
----------
      5700		

--3>场景三：修改为全局hash分区索引，模拟100并行插入
--分区索引测试
--新建索引分区测试insert并行速度
drop table  tmp1;
create table tmp1(id varchar2(1000));
create index idx_tmp1 on tmp1(id)  global partition by hash(id) partitions 10;


--创建序列
drop sequence testseq;
create sequence testseq
increment by 1 
start with 10001 
maxvalue 999999999 
cycle
cache 500000;

select event,count(1)
      from gv$active_session_history a
        where sample_time >=to_date('20240820 17:04:31','yyyymmdd hh24:mi:ss')
         and sample_time <=to_date('20240820 17:07:32','yyyymmdd hh24:mi:ss')
		 and sql_id='gs8gm0c1gx8p2'
	group by event
	order by count(1);
EVENT                                      COUNT(1)
---------------------------------------- ----------
latch: In memory undo latch                     218
library cache: mutex X                          218
                                                260
buffer deadlock                                 656
enq: TX - index contention                     1929
buffer busy waits                              5544

	
	

select count(1)
      from gv$active_session_history a
        where sample_time >=to_date('20240820 17:04:31','yyyymmdd hh24:mi:ss')
         and sample_time <=to_date('20240820 17:07:32','yyyymmdd hh24:mi:ss')
		 and sql_id='gs8gm0c1gx8p2';
  COUNT(1)
----------
      9005		 
	 
--4>场景四：修改为全局hash分区索引+调整表的PCTFREE，模拟100并行插入
--消除 buffer busy waits 
--新增pctfree和反向索引


--新建索引分区测试insert并行速度
drop table  tmp1;
create table tmp1(id varchar2(1000));
ALTER  TABLE  tmp1   pctfree 50;
create index idx_tmp1 on tmp1(id)  reverse;

--创建序列
drop sequence testseq;
create sequence testseq
increment by 1 
start with 10001 
maxvalue 999999999 
cycle
cache 500000;

select event,count(1)
      from gv$active_session_history a
        where sample_time >=to_date('20240820 17:30:37','yyyymmdd hh24:mi:ss')
         and sample_time <= to_date('20240820 17:32:05','yyyymmdd hh24:mi:ss')
		 and sql_id='gs8gm0c1gx8p2'
	group by event
	order by count(1);
EVENT                                      COUNT(1)
---------------------------------------- ----------
enq: TX - index contention                      292
latch: In memory undo latch                     318
library cache: mutex X                          361
                                                451
latch: redo copy                                637
buffer busy waits                              1730

	
	

select count(1)
      from gv$active_session_history a
        where sample_time >=to_date('20240820 17:30:37','yyyymmdd hh24:mi:ss')
         and sample_time <= to_date('20240820 17:32:05','yyyymmdd hh24:mi:ss')
		 and sql_id='gs8gm0c1gx8p2';
  COUNT(1)
----------
      4400		
	  
--实验证明 调整 pctfree可以缓解	  buffer busy waits

三、总结:
 
   普通索引：      总等待17314秒，其中enq: TX - index contention 4156秒,buffer busy waits 12627秒
   反向索引：      总等待5700秒， 其中enq: TX - index contention  252秒,buffer busy waits  2371秒
   分区索引：      总等待9005秒， 其中enq: TX - index contention 1929秒,buffer busy waits  5544秒
   反向+pctfree  ：总等待4400秒， 其中enq: TX - index contention  292秒,buffer busy waits  1730秒
  
    总结：enq: TX - index contention 可以通过创建反向索引和分区索引来缓解，其中反向索引效果最好。