17.Oracle查询表空间大小很慢一则

使用如下 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)

 ....

参考：https://mp.weixin.qq.com/s/HsDlc17OOCJc4JxEpcAJ2A