Oracle等待事件:Data file init write

在给某一个大表加有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”等待。


Posted

in

by

Tags:

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *