使用如下 SQL 查看表空间使用率时竟然需要 1~2 分钟才可以查看结果，两套数据库数据库也就百 GB 级别，为何会这么慢呢？
SELECT a.tablespace_name,round(total/1024/1024/1024) "Total g", round(free/1024/1024/1024) "Free g",ROUND((total-free)/total,4)*100 "USED%" FROM (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name ) a, (SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name) b WHERE a.tablespace_name=b.tablespace_name ORDER BY 4;
查看执行计划：
SYS@testogg> explain plan for SELECT a.tablespace_name,round(total/1024/1024/1024) "Total g", round(free/1024/1024/1024) "Free g",ROUND((total-free)/total,4)*100 "USED%" FROM (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name ) a, (SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name) b WHERE a.tablespace_name=b.tablespace_name ORDER BY 4; Explained. Elapsed: 00:00:00.68 SYS@testogg> select * from table(dbms_xplan.display());
DBA_FREE_SPACE 视图慢
set auto on seleclt count(*) from dba_free_space;
由上图看出，主要访问了这几个系统表 FET$、TS$、RECYCLEBIN$、X$KTFBUE、UET$ 以及 NEW_LOST_wRITE_EXTENTS$，每一个都是有可能引起慢的原因，我们来收集一下统计信息看看
收集统计信息
收集系统统计信息：
exec dbms_stats.GATHER_SYSTEM_STATS;
收集动态性能视图基表的统计信息:
exec dbms_stats.GATHER_FIXED_OBJECTS_STATS;
收集数据字典的统计信息:
exec dbms_stats.GATHER_DICTIONARY_STATS;
收集用户的统计信息:
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => ‘SYS’)
收集表统计信息:
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => ‘SYS’,taname=>‘TS$’,CASCADE=>true)
....