8i查询DBA_FREE_SPACE视图极慢的问题

还是那套古老的8.1.7.4，在该系统上检查表空间使用情况的SQL运行缓慢，其SQL如下:

SELECT D.TABLESPACE_NAME,
       SPACE "SUM_SPACE(M)",
       SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
       ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) "USED_RATE(%)",
       FREE_SPACE "FREE_SPACE(M)"
  FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE
          FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) D,
       (SELECT TABLESPACE_NAME,
               ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE
          FROM DBA_FREE_SPACE
         GROUP BY TABLESPACE_NAME) F
 where d.tablespace_name = f.tablespace_name(+)
 order by "USED_RATE(%)" desc;
/*很面熟的DBA常用脚本吧？*/

经确认其中对DBA_FREE_SPACE视图的查询耗费了大量时间，8i中该视图的默认定义是:

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
/*以上查询DMT表空间上的FREE EXTENT*/
union all
/*以下查询LMT表空间上的FREE EXTENT*/
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

/*也许你感到奇怪，实际上8i中就有了本地管理模式的表空间了，只是很少有人用。("In Oracle 8i the EXTENT MANAGEMENT clause was introduced into the CREATE TABLESPACE statement allowing extent management to be LOCAL or DICTIONARY. Locally Manages Tablespaces (LMT) have a bitmap of the blocks, or groups of blocks, they contain allowing them to track extent allocation without reference to the data dictionary.")*/

/*因字典管理模式下FET$基表往往较大，导致UNION ALL以上部分在连接操作时会产生大量的逻辑读，最终导致了对DBA_FREE_SPACE视图的查询十分缓慢。*/

Oracle 提供了官方的视图并不意味着我们非它不可用，可以通过修改DBA_FREE_SPACE的定义，或另建一个具有相同功能但查询SQL构造不同的视图来加快查询速度:

explain plan for

select /*+use_hash (tsfi, fet2 ) */

 tsfi.tablespace_name,

 tsfi.file_id,

 fet2.block_id,

 tsfi.blocksize * fet2.blocks,

 fet2.blocks,

 tsfi.relfile#

  from (select /*+ use_hash ( ts, fi ) */

         ts.name      tablespace_name,

         fi.file#     file_id,

         ts.BLOCKSIZE,

         fi.relfile#,

         ts.ts#

          from sys.ts$ ts, sys.file$ fi

         where ts.ts# = fi.ts#

           and ts.online$ in (1, 4)) tsfi,

       (select f.block# block_id, f.length blocks, f.file# file_id, f.ts#

          from sys.fet$ f

        union all

        select f.ktfbfebno  block_id,

               f.ktfbfeblks blocks,

               f.ktfbfefno,

               ktfbfetsn

          from sys.x$ktfbfe f) fet2

 where fet2.file_id = tsfi.relfile#

   and fet2.ts# = tsfi.ts# /*此查询需SYSDBA权限*/ ;

Explained

select * from  table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 717737944

---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |    20 |  1560 |     9  (12)| 00:00:01 |
|*  1 |  HASH JOIN           |          |    20 |  1560 |     9  (12)| 00:00:01 |
|*  2 |   HASH JOIN          |          |     4 |   104 |     6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS FULL | FILE$    |     4 |    36 |     2   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL | TS$      |     5 |    85 |     3   (0)| 00:00:01 |
|   5 |   VIEW               |          |   101 |  5252 |     3   (0)| 00:00:01 |
|   6 |    UNION-ALL         |          |       |       |            |          |
|   7 |     TABLE ACCESS FULL| FET$     |     1 |    52 |     3   (0)| 00:00:01 |
|   8 |     FIXED TABLE FULL | X$KTFBFE |   100 |  5200 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("FET2"."FILE_ID"="FI"."RELFILE#" AND
              "FET2"."TS#"="TS"."TS#")
   2 - access("TS"."TS#"="FI"."TS#")
   4 - filter("TS"."ONLINE$"=1 OR "TS"."ONLINE$"=4)

/*改写后可以大幅减少逻辑读从而提高性能*/

/*可以建立DBA_FREE_SPACE功能相同的替代品，并代入到表空间使用率的脚本中*/
CREATE OR REPLACE VIEW DBA_FREE_SPACE_NEW (
  TABLESPACE_NAME,
  FILE_ID,
  BLOCK_ID,
  BYTES,
  BLOCKS,
  RELATIVE_FNO
) AS
select /*+use_hash (tsfi, fet2 ) */
 tsfi.tablespace_name,
 tsfi.file_id,
 fet2.block_id,
 tsfi.blocksize * fet2.blocks,
 fet2.blocks,
 tsfi.relfile#
  from (select /*+ use_hash ( ts, fi ) */
         ts.name      tablespace_name,
         fi.file#     file_id,
         ts.BLOCKSIZE,
         fi.relfile#,
         ts.ts#
          from sys.ts$ ts, sys.file$ fi
         where ts.ts# = fi.ts#
           and ts.online$ in (1, 4)) tsfi,
       (select f.block# block_id, f.length blocks, f.file# file_id, f.ts#
          from sys.fet$ f
        union all
        select f.ktfbfebno  block_id,
               f.ktfbfeblks blocks,
               f.ktfbfefno,
               ktfbfetsn
          from sys.x$ktfbfe f) fet2
 where fet2.file_id = tsfi.relfile#
   and fet2.ts# = tsfi.ts#  /*建此视图需SYSDBA权限*/ ;