数据库中频繁出现会话等待（dbfile sequential read），一张大表135G上面创建有四个单列索引，经查询确认此等待（dbfile sequential read）是由于一条insert语句引起，按照常理来说 insert和db filesequential read等待是两个完全不搭边的，经分析是由于表上的索引引起，表在进行insert 时，索引也在同时进行维护更新，需要查询索引块确认索引更新位置，当并发量很大时，将会发生此等待。以下是在测试环境中所做的测试。

创建测试表t2

create table t2(c1 number,c2number,c3 number);

创建多个索引，以方便模拟等待事件的发生,同时制定pctfree值为0 ，这样索引更容易发生分裂

create index ind_t2_c1 on t2(c1) pctfree 0;

create index ind_t2_c2 on t2(c2)pctfree 0;

create index ind_t2_c3 on t2(c3)pctfree 0;

create index ind_t2_c1c2 on t2(c1,c2)pctfree 0;

create index ind_t2_c1c2c3 on t2(c1,c2,c3)pctfree 0;

初始化数据插入

begin

for i in 1..1000000  

loop

insert into t1 values(i,i+1,i+2);

end loop;

commit;

end;

/

然后我们打开10046 会话跟踪，

alter session set events '10046 trace namecontext forever,level 12';

往表中插入大量数据

begin

for i in 50000..200000  

loop

insert into t1 values(i,i+1,i+2);

end loop;

commit;

end;

/


待插入完成后，关闭10046

alter session setevents '10046 trace name context  off';


查看trace文件

PARSING IN CURSOR #6len=97 dep=0 uid=90 oct=47 lid=90 tim=1387231767091421 hv=2257850176ad='36ebcc78' sqlid='3h55s3f3983u0'

begin

for i in 50000..200000

loop

insert into t1 values(i,i+1,i+2);

end loop;

commit;

end;

END OF STMT

PARSE#6:c=7000,e=6832,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1387231767091408

BINDS #6:

=====================

PARSING IN CURSOR #5len=41 dep=1 uid=90 oct=2 lid=90 tim=1387231767092758 hv=3483989809ad='3ce9466c' sqlid='d4pp1yg7ukxtj'

INSERT INTO T1 VALUES(:B1,:B1 +1,:B1 +2)

END OF STMT

PARSE#5:c=0,e=687,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1387231767092747

BINDS #5:

Bind#0

 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00pre=00

 oacflg=03 fl2=206001 frm=00 csi=00 siz=72off=0

 kxsbbbfp=0057ffb8  bln=22 avl=02  flg=05

 value=50000

Bind#1

 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00pre=00

 oacflg=03 fl2=206001 frm=00 csi=00 siz=0off=24

 kxsbbbfp=0057ffd0  bln=22 avl=02  flg=01

 value=50000

Bind#2

 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00pre=00

 oacflg=03 fl2=206001 frm=00 csi=00 siz=0off=48

 kxsbbbfp=0057ffe8  bln=22 avl=02  flg=01

 value=50000

WAIT #5:nam='db file sequential read' ela= 23409 file#=4 block#=1799 blocks=1obj#=72576 tim=1387231767131489

WAIT #5:nam='db file sequential read' ela= 1103 file#=4 block#=1800 blocks=1 obj#=72576tim=1387231767134290

WAIT #5:nam='db file sequential read' ela= 21198 file#=14 block#=2689 blocks=1obj#=72583 tim=1387231767155970

WAIT #5:nam='db file sequential read' ela= 14040 file#=4 block#=1900 blocks=1obj#=72583 tim=1387231767170334

WAIT #5:nam='db file sequential read' ela= 19055 file#=14 block#=3280 blocks=1obj#=72584 tim=1387231767189656

WAIT #5:nam='db file sequential read' ela= 20841 file#=4 block#=2328 blocks=1obj#=72584 tim=1387231767210672

WAIT #5:nam='db file sequential read' ela= 17894 file#=14 block#=5377 blocks=1obj#=72585 tim=1387231767228877


从以上信息可以明显看到'dbfile sequential read'等待，等待的信息为：

file#=4 block#=1799blocks=1 obj#=72576

我们确认下obj#=72576,是什么

orcl@ SCOTT>select  object_name,object_id fromdba_objects where object_id=72576;


OBJECT_NAME                     OBJECT_ID

----------------------------------------

IND_T1_C3                           72576


Elapsed: 00:00:00.12

orcl@ SCOTT>select  object_name,object_id fromdba_objects where object_id=72583;


OBJECT_NAME                     OBJECT_ID

----------------------------------------

IND_T1_C1C2                         72583


Elapsed: 00:00:00.03

orcl@ SCOTT>select  object_name,object_id fromdba_objects where object_id=72584;


OBJECT_NAME                     OBJECT_ID

----------------------------------------

IND_T1_C1C2C3                       72584


可以看到全都是索引。