数据文件RESIZE导致查询DBA_DATA_FILES被锁
在客户的服务器上发现一个现象，执行对DBA_DATA_FILES的查询被挂起。检查发现是由于这时数据文件正在执行RESIZE操作。
这个现象在9i、10g、11g中都能重现，下面是9i一个测试环境下的问题重现，这个数据库很简单，只包括了两个数据文件：
SQL> select file_name, file_id, tablespace_name, bytes, blocks
2 from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS
----------------------------------- ---------- --------------- ---------- ----------
/data/oradata/test9/system01.dbf 1 SYSTEM 1.0737E+10 1048576
/data/oradata/test9/test01.dbf 2 TEST 2147491840 209716
操作分为三个会话进行，会话1准备执行表空间的RESIZE操作：
SQL> set timing on
SQL> alter database datafile '/data/oradata/test9/test01.dbf' resize 10240m;
数据库已更改。
已用时间: 00: 00: 55.78
随后会话2先运行对V$DATAFILE视图的查询，然后对DBA_DATA_FILES视图进行查询：
SQL> set sqlp 'SQL2> '
SQL2> select sid from v$mystat where rownum = 1;
SID
----------
10
SQL2> set timing on
SQL2> select file#, ts#, rfile#, name, bytes, blocks
2 from v$datafile;
FILE# TS# RFILE# NAME BYTES BLOCKS
---------- ---------- ---------- ----------------------------------- ---------- ----------
1 0 1 /data/oradata/test9/system01.dbf 1.0737E+10 1048576
2 2 2 /data/oradata/test9/test01.dbf 2147491840 209716
已用时间: 00: 00: 00.00
SQL2> select file_name, file_id, tablespace_name, bytes, blocks
2 from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS
----------------------------------- ---------- --------------- ---------- ----------
/data/oradata/test9/system01.dbf 1 SYSTEM 1.0737E+10 1048576
/data/oradata/test9/test01.dbf 2 TEST 1.0737E+10 1048576
已用时间: 00: 00: 53.36
等会话2出现等待后，会话3检查会话2的等待事件：
SQL> set sqlp 'SQL3> '
SQL3> select seq#, event, p1, p2, p3, seconds_in_wait
2 from v$session_wait
3 where sid = 10;
SEQ# EVENT P1 P2 P3 SECONDS_IN_WAIT
---------- ------------------------------ ---------- ---------- ---------- ---------------
90 row cache lock 6 0 3 13
显然会话2一直等待到会话1结束还完成查询操作，而一般来说查询很少会被锁住。而且奇怪的是查询V$DATAFILE并不会被锁，而查询DBA_DATA_FILES视图则被锁住。根据会话3的运行结果，会话2的查询等待事件为row cache lock。
显然问题很简单，只需要分析V$DATAFILE和DBA_DATA_FILES两个视图的区别就可以找到原因所在：
SQL> select text from dba_views where view_name = 'DBA_DATA_FILES';
TEXT
--------------------------------------------------------------------------------
select v.name, f.file#, ts.name,
ts.blocksize * f.blocks, f.blocks,
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#, decode(f.inc, 0, 'NO', 'YES'),
ts.blocksize * f.maxextend, f.maxextend, f.inc,
ts.blocksize * (f.blocks - 1), f.blocks - 1
from sys.file$ f, sys.ts$ ts, sys.v$dbfile v
where v.file# = f.file#
and f.spare1 is NULL
and f.ts# = ts.ts#
union all
select
v.name,f.file#, ts.name,
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL),
decode(f.status$, 1, 'INVALID', 2, 'AVAILABLE', 'UNDEFINED'),
f.relfile#,
decode(hc.ktfbhccval, 0, decode(hc.ktfbhcinc, 0, 'NO', 'YES'), NULL),
decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcmaxsz, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcinc, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz * ts.blocksize, NULL),
decode(hc.ktfbhccval, 0, hc.ktfbhcusz, NULL)
from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts
where v.file# = f.file#
and f.spare1 is NOT NULL
and v.file# = hc.ktfbhcafno
and hc.ktfbhctsn = ts.ts#
SQL> select view_definition from v$fixed_view_definition where view_name = 'GV$DATAFILE';
VIEW_DEFINITION
--------------------------------------------------------------------------------------------
select fe.inst_id,fe.fenum,to_number(fe.fecrc_scn), to_date(fe.fecrc_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian')
, fe.fetsn,fe.ferfn, decode(fe.fetsn,0,decode(bitand(fe.festa,2),0,'SYSOFF','SYSTEM'), decode(bitand(fe.festa,18),0,'O
FFLINE',2,'ONLINE','RECOVER')), decode(fe.fedor,2,'READ ONLY', decode(bitand(fe.festa, 12), 0,'DISABLED',4,'READ O
NLY',12,'READ WRITE','UNKNOWN')), to_number(fe.fecps), to_date(fe.fecpt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
to_number(fe.feurs), to_date(fe.feurt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), to_number(fe.fests), decode(fe.f
ests,NULL,to_date(NULL), to_date(fe.festt,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian')), to_number(fe.feofs),to_numb
er(fe.feonc_scn), to_date(fe.feonc_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), fh.fhfsz*fe.febsz,fh.fhfsz,fe.fe
csz*fe.febsz,fe.febsz,fn.fnnam, fe.fefdb,fn.fnbof, decode(fe.fepax, 0 ,
'UNKNOWN' , 65535, 'NONE'
, fnaux.fnnam) from x$kccfe fe, x$kccfn fn, x$kccfn fnaux, x$kcvfh fh where
((fe.fepax!=65535 and fe.fepax!=0 and fe.fepax=fnaux.fnnum)
or ((fe.fepax=65535 or fe.fepax=0) and fe.fenum=fnaux.fnfno and fnaux.fntyp=4 and fn
aux.fnnam is not null and fnaux.fnfno=fh.hxfil and fe.fefnh=fnaux.fnnum))
and fn.fnfno=fe.fenum and fn.fnfno=fh.hxfil and fe.fefnh=fn.fnnum and fe.fedup!=0 and fn.fntyp=4 and fn.f
nnam is not null
可以看到，V$DATAFILE视图由4个X$开头内部表构成：x$kccfe、x$kccfn、x$kccfn和x$kcvfh。访问这个视图可以马上返回结果。
而DBA_DATA_FILES视图由两部分构成，第一部分包括file$、ts$和v$dbfile，而第二部分也包括这三个cluster表，同时还包括了一个X$开头的内部表：x$ktfbhc。
通过进一步的分解测试，发现问题出现在x$ktfbhc这个对象上。通过分析DBA_DATA_FILES视图的定义，发现UNION ALL的两部分查询分别对应数据字典管理表空间和本地管理表空间，测试发现对数据字典管理的表空间执行RESIZE并不会导致查询视图被挂起：
SQL> select tablespace_name, extent_management
2 from dba_tablespaces;
TABLESPACE_NAME EXTENT_MAN
------------------------------ ----------
SYSTEM DICTIONARY
TEMP LOCAL
TEST LOCAL
SQL> set timing on
SQL> alter database datafile '/data/oradata/test9/system01.dbf' resize 1024m;
数据库已更改。
已用时间: 00: 00: 06.48
在会话2随后执行查询：
SQL2> select file_name, file_id, tablespace_name, bytes, blocks
2 from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS
----------------------------------- ---------- --------------- ---------- ----------
/data/oradata/test9/system01.dbf 1 SYSTEM 1073745920 104858
/data/oradata/test9/test01.dbf 2 TEST 1.0737E+10 1048576
已用时间: 00: 00: 00.01
RESIZE操作运行事件超过了6秒，但是查询瞬间就完成了。
看来问题和本地管理表空间的特性有关，对比查询file$和x$ktfbhc的结果：
SQL> select file#, blocks, blocks * (select blocksize from ts$ where ts# = a.ts#) bytes
2 from file$ a;
FILE# BLOCKS BYTES
---------- ---------- ----------
1 104858 1073745920
2 209716 2147491840
已用时间: 00: 00: 00.00
SQL> select ktfbhcafno,
2 decode(hc.ktfbhccval, 0, hc.ktfbhcsz, NULL) blocks,
3 decode(hc.ktfbhccval, 0, ts.blocksize * hc.ktfbhcsz, NULL) bytes
4 from sys.x$ktfbhc hc, sys.ts$ ts
5 where hc.ktfbhctsn = ts.ts#;
KTFBHCAFNO BLOCKS BYTES
---------- ---------- ----------
2 1048576 1.0737E+10
SQL> select file_name, file_id, tablespace_name, bytes, blocks
2 from dba_data_files;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS
----------------------------------- ---------- --------------- ---------- ----------
/data/oradata/test9/system01.dbf 1 SYSTEM 1073745920 104858
/data/oradata/test9/test01.dbf 2 TEST 1.0737E+10 1048576
已用时间: 00: 00: 00.00
显然file$中记录的信息对于本地管理表空间是不正确的，事实上也不是结果不正常，而是这个视图记录的是文件创建时的大小。而一旦数据文件大小发生变化，就只能从x$ktfbhc中获取文件真正的大小了。
查询了一下这个内部视图的含义，这个对象是Kernel Tablespace File Bitmap Header Control，也就是说记录了本地表空间的Bitmap头控制信息，那么Oracle在修改数据文件的大小时，肯定要保护住这张表中的记录不被修改，这就是row cache lock产生的原因。
至于为什么对于普通的表被修改时，查询不会被阻塞，而这张表的查询却导致row cache lock，个人认为有两个原因，首先数据字典表的缓存和用户数据的缓存是不一样的，一个是ROW CACHE一个是DB_BUFFER，这是这里产生row cache lock的原因之一，更重要的一点是X$KTFBHC视图并不是真正Oracle数据库中的对象，而是Oracle内部程序实现的一个动态接口，虽然可以实现SQL的直接访问，但是显然与普通的Oracle对象不同，而不是数据库中的对象就意味着这个对象不具备原子性、事务性、一致性的特点，那么Oracle内部实现的时候很可能就是一个读写互斥的方式，因此产生row cache lock也是正常的。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/4227/viewspace-682071/ ，如需转载，请注明出处，否则将追究法律责任。
