oracle数据库管理-insert导致event -dbfile sequential read

数据库中频繁出现会话等待（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

可以看到全都是索引。
尝试keep 索引
DB_KEEP_CACHE_SIZE是够大的，怎么样把分区索引keep起来？

SQL> show parameter DB_KEEP_CACHE_SIZE

NAME TYPE VALUE

---

db_keep_cache_size big integer 200M

把分区索引keep起来，IDX_01为分区
SQL> ALTER INDEX TUSER.IDX_01
MODIFY DEFAULT ATTRIBUTES
ALTER TABLE customer STORAGE (BUFFER_POOL KEEP);

ALTER INDEX cust_name_idx STORAGE (BUFFER_POOL KEEP);