在给某一个大表加有default值的clob列时出现了Data file init write等待事件，这个等待事件是10gR2中新加入的，恰恰10gr2的文档(乃至11g的文档)都没有列出该等待事件。该等待事件一般在Oracle自动扩展数据文件(auto extend datafile)并串行地格式化数据文件的新创建的空间时作为前台(foreground)等待事件出现:
SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi PL/SQL Release 10.2.0.4.0 - Production CORE 10.2.0.4.0 Production TNS for Linux: Version 10.2.0.4.0 - Production NLSRTL Version 10.2.0.4.0 - Production SQL> show parameter compatible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 10.2.0.3 /* 数据库版本10.2.0.4，compatible为10.2.0.3 */ SQL> alter table tv add spare6 clob default '........'; SQL> col event for a30; SQL> select event, time_waited, current_file#, current_block# 2 from V$active_Session_History 3 where event like 'Data file init write' 4 order by sample_time desc; EVENT TIME_WAITED CURRENT_FILE# CURRENT_BLOCK# ------------------------------ ----------- ------------- -------------- Data file init write 533810 4 54783 Data file init write 442042 4 54783 Data file init write 47286 4 54783 Data file init write 42932 4 54783 Data file init write 413477 4 54783 Data file init write 153503 4 54783 Data file init write 33520 4 54783 Data file init write 307616 4 54783 Data file init write 214404 4 54783 Data file init write 3400 4 54783 Data file init write 212386 4 54783 Data file init write 192631 4 54783 .............. Data file init write 611157 4 54775 Data file init write 1379 4 54775 Data file init write 223541 4 54775
伴随着数据文件扩展(Data file init write等待事件的直接触发原因)，Oracle需要通过以下递归dml语句维护相应的数据字典:
1.查询字典表file$确定表空间对应的数据文件号
select file# from file$ where ts#=:1
该操作可能伴随Data file init write等待事件发生:
PARSING IN CURSOR #3 len=36 dep=2 uid=0 oct=3 lid=0 tim=1261083587010014 hv=1570213724 ad='8f7d4210'
select file# from file$ where ts#=:1
END OF STMT
.............
WAIT #14: nam='Data file init write' ela= 1091 count=1 intr=256 timeout=4294967295 obj#=57314
WAIT #14: nam='Data file init write' ela= 1078 count=1 intr=256 timeout=4294967295 obj#=57314
WAIT #14: nam='Data file init write' ela= 1102 count=1 intr=256 timeout=4294967295 obj#=57314
WAIT #14: nam='Data file init write' ela= 1156 count=1 intr=256 timeout=4294967295 obj#=57314
WAIT #14: nam='Data file init write' ela= 1870 count=1 intr=256 timeout=4294967295 obj#=57314
WAIT #14: nam='Data file init write' ela= 37 count=1 intr=256 timeout=4294967295 obj#=57314
WAIT #14: nam='Data file init write' ela= 4 count=4294967295 intr=32 timeout=2147483647 obj#=57314
2.若使用bigfile tablespace则可能出现以下insert seg$流程
insert into seg$ (file#,block#,type#,ts#,blocks,extents,minexts,maxexts,
extsize,extpct,user#,iniexts,lists,groups,cachehint,bitmapranges,hwmincr,
spare1, scanhint) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,0,:16,
DECODE(:17,0,NULL,:17),:18)
PARSE #15:c=0,e=368,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=1261083586909081
BINDS #15:
kkscoacd
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=08 fl2=0001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2ba79a0e1330 bln=22 avl=03 flg=05
value=1024
SQL> select file#,block#,blocks from sys.seg$ where file#=1024;
FILE# BLOCK# BLOCKS
---------- ---------- ----------
1024 82 13434880
1024 13434962 16384
1024 13451346 16384
1024 13467730 16384
1024 13484114 16384
/* 会出现奇怪的1024号文件 */
3.维护用户表空间限额字典数据
update tsq$
set blocks = :3,
maxblocks = :4,
grantor# = :5,
priv1 = :6,
priv2 = :7,
priv3 = :8
where ts# = :1
and user# = :2
4.更新seg$数据字典基表以扩展数据段
update seg$
set type# = :4,
blocks = :5,
extents = :6,
minexts = :7,
maxexts = :8,
extsize = :9,
extpct = :10,
user# = :11,
iniexts = :12,
lists = decode(:13, 65535, NULL, :13),
groups = decode(:14, 6 5535, NULL, :14),
cachehint = :15,
hwmincr = :16,
spare1 = DECODE(:17, 0, NULL, :17),
scanhint = :18
where ts# = :1
and file# = :2
and block# = :3
以上数据文件空间扩展流程中只有查询语句”select file# from file$ where ts#=:1″伴随有”Data file init write”等待。
Leave a Reply