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