还是那套古老的8.1.7.4，在该系统上检查表空间使用情况的SQL运行缓慢，其SQL如下:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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中该视图的默认定义是:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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构造不同的视图来加快查询速度:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
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权限*/ ;