回收站与表空间利用率

今天休假结束，刚来到公司上班就遇到一个问题。
有个同事在监控测试环境表空间使用率时，发现效率很低。但同样的SQL在生产上却很快。生产的数据量远远大于测试。

SQL如下：
 SELECT upper(f.tablespace_name) "表空间名",      
       d.Tot_grootte_Mb "表空间大小(M)",      
       d.Tot_grootte_Mb - f.total_bytes "已使用空间(M)",      
       to_char(round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,
                     2),
               '990.99') "使用比",      
       f.total_bytes "空闲空间(M)",     
       f.max_bytes "最大块(M)"
  FROM (SELECT tablespace_name,              
               round(SUM(bytes) / (1024 * 1024), 2) total_bytes,              
               round(MAX(bytes) / (1024 * 1024), 2) max_bytes       
          FROM sys.dba_free_space       
         GROUP BY tablespace_name) f,      
       (SELECT dd.tablespace_name,
               round(SUM(dd.bytes) / (1024 * 1024), 2) Tot_grootte_Mb       
          FROM sys.dba_data_files dd       
         GROUP BY dd.tablespace_name) d
 WHERE d.tablespace_name = f.tablespace_name
 ORDER BY 4 DESC

检查dba_free_space视图的定义发现在执行计划中看到了对recyclebin$做了TABLE ACCESS FULL.
于是怀疑是不是回收站里有太多的垃圾。

SQL> SELECT COUNT(1) FROM DBA_RECYCLEBIN;

  COUNT(1)
----------
     10245
由于是测试环境，回收站中果然有很多被drop掉的对象。尝试清空回收站后，检查表空间的SQL运行正常。
SQL> purge dba_recyclebin;
Done        

以下是dba_free_space视图的定义：
从最后两个union all中看到访问了sys.recyclebin$，难怪全表扫描recyclebin$效率低呢。

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 u.ktfbuefno = 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