資料檔案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也是正常的。