關於Oracle dba_free_space 檢視的研究

顧名思義，dba_free_space指的是Oracle還有多少表空間剩餘空間，其檢視結構也相當簡單：
    SQL> desc dba_free_space
    Name                                      Null?    Type
    —————————————– ——– —————————-
    TABLESPACE_NAME                                    VARCHAR2(30)
    FILE_ID                                            NUMBER
    BLOCK_ID                                           NUMBER
    BYTES                                              NUMBER
    BLOCKS                                             NUMBER
    RELATIVE_FNO                                       NUMBER
    但是我們查詢dba_free_space時，即表空間剩餘空間常常是離碎的，比如
    SQL> select * from dba_free_space where file_id=7;
    TABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
    —————————— ———- ———- ———- ———- ————
    ZHOUL                                   7      27145     983040        120            7
    ZHOUL                                   7      27905      65536          8            7
    ZHOUL                                   7      28937    7274496        888            7
    ZHOUL                                   7      36617     851968        104            7
    ZHOUL                                   7      60129     327680         40            7
    ZHOUL                                   7      63497     720896         88            7
    6 rows selected.
    這是為什麼呢？繼續檢視檢視dba_free_space的建立語句：
    create or replace view dba_free_space
    (tablespace_name, file_id, block_id, bytes, blocks, relative_fno)
    as
    select ts.name, fi.file#, f.block#,
           f.length * ts.blocksize, f.length, f.file#
    from sys.ts$ ts,  sys.fet$  f, sys.file$ fi
    where ts.ts# = f.ts#
      and f.ts# = fi.ts#
      and f.file# = fi.relfile#
      and ts.bitmapped = 0
    union all
    select /*+ ordered use_nl(f) use_nl(fi) */
           ts.name, fi.file#, f.ktfbfebno,
           f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
    from sys.ts$ ts,  sys.x$ktfbfe f , sys.file$ fi
    where ts.ts# = f.ktfbfetsn
      and f.ktfbfetsn = fi.ts#
      and f.ktfbfefno = fi.relfile#
      and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
    union all
    select /*+ ordered use_nl(u) use_nl(fi) */
           ts.name, fi.file#, u.ktfbuebno,
           u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
    from sys .recyclebin$  rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
    where ts.ts# = rb.ts#
      and rb.ts# = fi.ts#
      and rb.file# = fi.relfile#
      and u.ktfbuesegtsn = rb.ts#
      and u.ktfbuesegfno = rb.file#
      and u.ktfbuesegbno = rb.block#
      and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
    union all
    select ts.name, fi.file#, u.block#,
           u.length * ts.blocksize, u.length, u.file#
    from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
    where ts.ts# = u.ts#
      and u.ts# = fi.ts#
      and u.segfile# = fi.relfile#
      and u.ts# = rb.ts#
      and u.segfile# = rb.file#
      and u.segblock# = rb.block#
      and ts.bitmapped = 0;
    可以看到dba_free_space檢視有三部分組成：fet$，x$ktfbfe，x$ktfbue，recyclebin$。其中 fet$表格主要用於表空間extent管理是資料字典管理，x$ktfbue由前面的實驗得知主要用於對點陣圖塊的掃描，recyclebin$主要用於 管理回收站物件。
    那x$ktfbfe主要用於做什麼呢？
    View:   X$KTFBUE
             [k]ernel [t]ablespace [f]ile  itmapped
                sed [e]xtents
    Column          Type               Description
    ——–        —-               ——–
    ADDR            RAW(4|8)           address of this row/entry in the array or SGA
    INDX            NUMBER             index number of this row in the fixed table array
    INST_ID         NUMBER             oracle instance number
    KTFBUESEGTSN    NUMBER             tablespace number of segment
    KTFBUESEGFNO    NUMBER             segment relative file number
    KTFBUESEGBNO    NUMBER             segment block number
    KTFBUEEXTNO     NUMBER             extent number
    KTFBUEFNO       NUMBER             extent file number
    KTFBUEBNO       NUMBER             extent block number
    KTFBUEBLKS      NUMBER             extent length
    開啟10046事件跟蹤x$ktfbfe
    SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
    Session altered.
    SQL> select * from x$ktfbfe;
    ADDR           INDX    INST_ID  KTFBFETSN  KTFBFEFNO  KTFBFEBNO KTFBFEBLKS
    ——– ———- ———- ———- ———- ———- ———-
    B7F57A70          0          1          0          1      69769        632
    B7F57A70          1          1          1          2        233      18328
    B7F57A70          2          1          2          3      36953         80
    B7F57A70          3          1          2          3      37041         40
    B7F57A70          4          1          2          3      37121          8
    。。。
    SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
    Session altered.
    開啟跟蹤檔案，首先Oracle對x$ktfbfe進行解析
    PARSING IN CURSOR #1 len=22 dep=0 uid=0 oct=3 lid=0 tim=1273325024428885 hv=502180737 ad='2674fde8'
select * from x$ktfbfe
    END OF STMT
    PARSE #1:c=0,e=130,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1273325024428880
    BINDS #1:
    EXEC #1:c=0,e=78,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1273325024429032
    WAIT #1: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1273325024429077
    其次Oracle進一步解析ts$表，獲取滿足條件的ts#和flags
    PARSING IN CURSOR #2 len=100 dep=1 uid=0 oct=3 lid=0 tim=1273325024429259 hv=3768030067 ad='25b84394'
select ts#, flags from ts$ where bitmapped <> 0 and contents$ = 0   and (online$ = 1 or online$ = 4)
    END OF STMT
    PARSE #2:c=0,e=91,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1273325024429255
    BINDS #2:
    EXEC #2:c=1000,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1273325024429371
    WAIT #2: nam='db file sequential read' ela= 39 file#=1 block#=57 blocks=1 obj#=16 tim=1273325024442859
    WAIT #2: nam='db file scattered read' ela= 130 file#=1 block#=58 blocks=7 obj#=16 tim=1273325024443240
    FETCH #2:c=1000,e=13908,p=8,cr=4,cu=0,mis=0,r=1,dep=1,og=4,tim=1273325024443299
    最後Oracle解析file$，傳入繫結變數0,1,2…8
    PARSING IN CURSOR #3 len=36 dep=1 uid=0 oct=3 lid=0 tim=1273325024443480 hv=1570213724 ad='27af1440'
select file# from file$ where ts#=:1
    END OF STMT
    PARSE #3:c=0,e=80,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1273325024443476
    BINDS #3:
    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=b7f65180  bln=22  avl=01  flg=05
  value=0
    EXEC #3:c=0,e=119,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1273325024443693
    WAIT #3: nam='db file sequential read' ela= 14835 file#=1 block#=113 blocks=1 obj#=16 tim=1273325024458576
    WAIT #3: nam='db file sequential read' ela= 236 file#=1 block#=114 blocks=1 obj#=16 tim=1273325024458882
    FETCH #3:c=999,e=15195,p=2,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1273325024458914
    FETCH #3:c=0,e=8,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=1273325024458949
    STAT #3 id=1 cnt=1 pid=0 pos=1 obj=17 op='TABLE ACCESS FULL FILE$ (cr=4 pr=2 pw=0 time=15194 us)'
WAIT #1: nam='db file sequential read' ela= 16 file#=1 block#=2 blocks=1 obj#=-1 tim=1273325024459119
    WAIT #1: nam='db file sequential read' ela= 15 file#=1 block#=3 blocks=1 obj#=-1 tim=1273325024459190
    FETCH #1:c=2999,e=30138,p=12,cr=8,cu=2,mis=0,r=1,dep=0,og=1,tim=1273325024459250
    WAIT #1: nam='SQL*Net message from client' ela= 244 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1273325024459537
    FETCH #2:c=0,e=9,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=1273325024459584
    。。。
    PARSING IN CURSOR #3 len=36 dep=1 uid=0 oct=3 lid=0 tim=1273325024482416 hv=1570213724 ad='27af1440'
    select file# from file$ where ts#=:1
    END OF STMT
    PARSE #3:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1273325024482412
    BINDS #3:
    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=b7f65134  bln=22  avl=02  flg=05
      value=7
    EXEC #3:c=0,e=92,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,tim=1273325024482587
    FETCH #3:c=0,e=22,p=0,cr=3,cu=0,mis=0,r=1,dep=1,og=4,tim=1273325024482634
    FETCH #3:c=0,e=5,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,tim=1273325024482666
    STAT #3 id=1 cnt=1 pid=0 pos=1 obj=17 op='TABLE ACCESS FULL FILE$ (cr=4 pr=0 pw=0 time=19 us)'
WAIT #1: nam='db file sequential read' ela= 17 file#=6 block#=2 blocks=1 obj#=-1 tim=1273325024482802
    WAIT #1: nam='db file sequential read' ela= 15 file#=6 block#=3 blocks=1 obj#=-1 tim=1273325024482875
    FETCH #2:c=0,e=7,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=4,tim=1273325024482917
    透過檢視跟蹤檔案，我們可以看到Oracle對x$ktfbfe表格的查詢，最終會轉換成對ts$的查詢，透過條件過濾定位file$，然後從檔案的2號block和3號block去取得資料。
    我們知道每個資料檔案的2號至-8號block是關於extent map的block。
    從block type為1d可以知道這個block型別為KTFB Bitmapped File Space Header
    BBED> dump block 2 offset 0 count 32
    File: /oradata/mcstar/zhoul01.dbf (0)
    Block: 2                Offsets:    0 to   31           Dba:0×00000000
    ————————————————————————
1da20000 0200c001 1fb3840e 000a0304 15fd0000 07000000 08000000 60f80000
    <32 bytes per line>
    從block type為12可以知道這個block型別為KTFB Bitmapped File Space Bitmap
    BBED> dump block 3 offset 0 count 32
    File: /oradata/mcstar/zhoul01.dbf (0)
    Block: 3                Offsets:    0 to   31           Dba:0×00000000
    ————————————————————————
1ea20000 0300c001 1fb3840e 000a0104 35cd0000 07000000 09000000 00000000
    <32 bytes per line>
    從以上分析中我們推斷出，Oracle檢視x$ktfbfe，其實就是對Oracle 資料檔案的block 2至block 8掃描（本例block 3-8為空，則跳過不掃描）。
    從dba_free_space檢視建立指令碼中，我們還看到了表格recyclebin$內容的選取
    繼續測試：
    在資料庫中刪除一張表格RBOTEST，其資料量有52567
    SQL> select count(*) from RBOTEST;
      COUNT(*)
    ———-
         52567
    SQL> drop table RBOTEST;
    Table dropped.
    刷記憶體，保證髒塊刷出至資料檔案
    SQL>  alter system flush buffer_cache;
    System altered.
    在recyclebin中我們看到了刪除表格
    SQL> show recyclebin
    ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
    —————- —————————— ———— ——————-
    RBOTEST          BIN$oeDriA+aATTgQBCsowQS+Q==$0 TABLE        2011-04-27:14:55:03
    在基表中也存在
    SQL> select OBJ#,OWNER#,ORIGINAL_NAME,FILE#,BLOCK# ,FLAGS,SPACE from recyclebin$;
          OBJ#     OWNER# ORIGINAL_NAME                         FILE#     BLOCK#      FLAGS      SPACE
    ———- ———- ——————————– ———- ———- ———- ———-
        246366         60 RBOTEST_OBJ                               7      29579         18        128
        246367         60 RBOTEST_OWNER#                            7      29707         18        112
        246365         60 RBOTEST                                   7      27147         30        768
    但是在x$ktfbfe顯示依然是刪除前的狀態
    SQL> select * from x$ktfbfe
      2  where ktfbfefno=7;
    ADDR           INDX    INST_ID  KTFBFETSN  KTFBFEFNO  KTFBFEBNO KTFBFEBLKS
    ——– ———- ———- ———- ———- ———- ———-
    B7F57A70         50          1          8          7      29817          8
    B7F57A70         51          1          8          7      36617        104
    B7F57A70         52          1          8          7      60129         40
    B7F57A70         53          1          8          7      63497         88
    透過bbed檢視block狀態，發現checkval值未變，這說明Oracle在Oracle 10g中drop 表格時extent map並未發生變化
    BBED> dump block 2 offset 0 count 32
    File: /oradata/mcstar/zhoul01.dbf (0)
    Block: 2                Offsets:    0 to   31           Dba:0×00000000
    ————————————————————————
    1da20000 0200c001 1fb3840e 000a0304  15fd0000 07000000 08000000 60f80000
    <32 bytes per line>
    BBED> dump block 3 offset 0 count 32
    File: /oradata/mcstar/zhoul01.dbf (0)
    Block: 3                Offsets:    0 to   31           Dba:0×00000000
    ————————————————————————
    1ea20000 0300c001 1fb3840e 000a0104  35cd0000 07000000 09000000 00000000
    <32 bytes per line>
    清空回收站
    SQL> purge recyclebin;
    Recyclebin purged.
    SQL> select OBJ#,OWNER#,ORIGINAL_NAME,FILE#,BLOCK# ,FLAGS,SPACE from recyclebin$;
    no rows selected
    繼續檢視x$ktfbfe和物理上block狀態，發現未變，繼續刷記憶體。
    SQL> alter system flush buffer_cache;
    System altered.
    SQL> select * from x$ktfbfe
      2  where ktfbfefno=7;
    ADDR           INDX    INST_ID  KTFBFETSN  KTFBFEFNO  KTFBFEBNO KTFBFEBLKS
    ——– ———- ———- ———- ———- ———- ———-
    B7F26A58         47          1          8          7      27145        120
    B7F26A58         48          1          8          7      27905          8
    B7F26A58         49          1          8          7      28937        888
    B7F26A58         50          1          8          7      36617        104
    B7F26A58         51          1          8          7      60129         40
    B7F26A58         52          1          8          7      63497         88
    6 rows selected.
    BBED> dump block 2 offset 0 count 32
    File: /oradata/mcstar/zhoul01.dbf (0)
    Block: 2                Offsets:    0 to   31           Dba:0×00000000
    ————————————————————————
    1da20000 0200c001 5879930e 000a0104  955b0000 07000000 08000000 60f80000
    <32 bytes per line>
    BBED> dump block 3 offset 0 count 32
    File: /oradata/mcstar/zhoul01.dbf (0)
    Block: 3                Offsets:    0 to   31           Dba:0×00000000
    ————————————————————————
    1ea20000 0300c001 5879930e 000a0104  6e0e0000 07000000 09000000 00000000
    <32 bytes per line>
    可以看到x$ktfbfe和block均發生了變化。
    透過對dba_free_space的研究我們可以得出以下結論：
    1、對x$ktfbfe的掃描，其實是物理上對資料檔案2-8號block的掃描
    2、在Oracle 10g，在不帶引數purge，drop表格時，並不會對資料檔案頭extent map更新，透過這種方式減少了Oracle對extent map爭用的可能性，這也是dba_free_space檢視建立指令碼中需要對基表recyclebin$選擇的原因之一。曾經碰到過一案例，回收站物件 太多導致執行dba_free_space時間很長。
    3、在對回收站清空後，會更新資料檔案頭extent map，但命令purge recyclebin並不會引起物件基表的checkpoint。