使用zabbix客户端监控ORACLE可用表空间时, 发现有时候查询进程会越来越多, 导致系统越来越慢, 并且都在执行同一个SQL, 查询dba_free_space视图，网上资料都说和回收站recyclebin$有关， 实际上没有开启回收站。完整的查询语句为：

select t.tablespace_name, t.mb "TotalMB", 
t.mb - nvl(f.mb,0) "UsedMB", nvl(f.mb,0) "FreeMB" ,
lpad(ceil((1-nvl(f.mb,0)/decode(t.mb,0,1,t.mb))*100)||'%', 6) "% Used", t.ext "Ext", '|'||rpad(lpad('#',ceil((1-nvl(f.mb,0)/decode(t.mb,0,1,t.mb))*20),'#'),20,' ')||'|' "Used" 
from 
(select tablespace_name, trunc(sum(bytes)/1048576) MB 
from dba_free_space group by tablespace_name 
union all 
select tablespace_name, trunc(sum(bytes_free)/1048576) MB 
from v$temp_space_header group by tablespace_name 
) f, 
(select tablespace_name, trunc(sum(bytes)/1048576) MB, max(autoextensible) ext 
from dba_data_files group by tablespace_name 
union all 
select tablespace_name, trunc(sum(bytes)/1048576) MB, max(autoextensible) ext 
from dba_temp_files group by tablespace_name 
) t 
where t.tablespace_name = f.tablespace_name (+) 
order by t.tablespace_name

查看执行计划发现#93 占用了大部分的时间， 使用的是全表扫X$KTFBUE

============================================================================================================================================================================================================
| Id    |                Operation                 |           Name            |  Rows   | Cost |   Time    | Start  | Execs |   Rows   | Read | Read  | Mem | Activity |         Activity Detail          |
|       |                                          |                           | (Estim) |      | Active(s) | Active |       | (Actual) | Reqs | Bytes |     |   (%)    |           (# samples)            |
============================================================================================================================================================================================================
| -> 93 |            FIXED TABLE FULL              | X$KTFBUE                  |    100K |   20 |        86 |     +5 

而该基表没有收集统计信息：
SQL> select owner,table_name,object_type,NUM_ROWS,BLOCKS,LAST_ANALYZED from dba_tab_statistics where  table_name='X$KTFBUE';

OWNER                          TABLE_NAME                     OBJECT_TYPE    NUM_ROWS     BLOCKS LAST_ANAL
------------------------------ ------------------------------ ------------ ---------- ---------- ---------
SYS                            X$KTFBUE                       FIXED TABLE

可以看到X$ktfbue无统计信息，下面尝试使用GATHER_FIXED_OBJECTS_STATS。

SQL>  exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
PL/SQL procedure successfully completed.

发现还是有很多fixed table没有统计信息:

SQL> SELECT count(*),count(last_analyzed),sum(decode(last_analyzed,null,1,0)) FROM DBA_TAB_STATISTICS where OBJECT_TYPE='FIXED TABLE';

  COUNT(*) COUNT(LAST_ANALYZED) SUM(DECODE(LAST_ANALYZED,NULL,1,0))
---------- -------------------- -----------------------------------
      1335                 1180                                 155

单独收集fixed table统计信息:

SQL> EXEC DBMS_STATS.gather_table_stats('SYS','X$KTFBUE');

PL/SQL procedure successfully completed.

收集完X$KTFBUE的统计信息以后，， 执行计划有原来的FIXED FULL TABLE变成了FIXED TABLE FIXED INDEX，现在4秒钟就可以返回数据。问题得到解决