关于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。