Oracle 查询表空间使用率超慢问题一则
作者 | JiekeXu
来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT）
大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看 Oracle 查询表空间使用率超慢问题一则，欢迎点击上方蓝字关注我，标星或置顶，更多干货第一时间到达！
近期，在运维的数据库中有一套 11g 和 一套 19c 的环境，使用如下 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;下面我们来一起看看：
查看执行计划
记得以前遇到过一次也是查询很慢的情况，根据执行计划收集完一次数据字典统计信息就好了，那么这次到底是不是同样的问题呢？
15:12:51 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.6815:13:43 SYS@testogg> select * from table(dbms_xplan.display());DBA_FREE_SPACE 视图慢
DBA_FREE_SPACE 视图有 2454 行，看看这个到底有多少行。
不看不知道，一看吓一跳。
DBA_FREE_SPACE 只有 1391 行，count（*）花费了 1 分 41 秒。那么我们来看一下它的执行计划，看看都访问了哪些对象。
由上图看出，主要访问了这几个系统表 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)
如下图，收集统计信息没有任何改善，查询时间基本没变。
定位问题
在 Oracle 的 MOS 中，有一篇文章说明查看 DBA_FREE_SPACE 慢的原因。
Queries on DBA_FREE_SPACE are Slow (Doc ID 271169.1)
查询 DBA_FREE_SPACE 视图都有哪些表组成：
set long 99999 line 29999 pages 49999select text from dba_views where view_name='DBA_FREE_SPACE';
TEXT--------------------------------------------------------------------------------select ts.name, fi.file#, f.block#, f.length * ts.blocksize, f.length, f.file#from sys.ts$ ts, sys.fet$ f, sys.file$ fiwhere ts.ts# = f.ts# and f.ts# = fi.ts# and f.file# = fi.relfile# and ts.bitmapped = 0union allselect ts.name, fi.file#, f.ktfbfebno, f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefnofrom sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fiwhere 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 and bitand(ts.flags, 4503599627370496) <> 4503599627370496union allselect ts.name, fi.file#, u.ktfbuebno, u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefnofrom sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fiwhere ts.ts# = rb.ts# and rb.ts# = fi.ts# and u.ktfbuefno = fi.relfile# and u.ktfbuesegtsn = rb.ts# and u.ktfbuesegfno = rb.file# and u.ktfbuesegbno = rb.block# and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0 and bitand(ts.flags, 4503599627370496) <> 4503599627370496union allselect ts.name, fi.file#, u.block#, u.length * ts.blocksize, u.length, u.file#from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rbwhere ts.ts# = u.ts# and u.ts# = fi.ts# and u.segfile# = fi.relfile# and u.ts# = rb.ts# and u.segfile# = rb.file# and u.segblock# = rb.block# and ts.bitmapped = 0union allselect ts.name, fi.file#, f.extent_start, (f.extent_length_blocks_2K /(ts.blocksize/2048)) * ts.blocksize, (f.extent_length_blocks_2K / (ts.blocksize/2048)), fi.relfile#from sys.ts$ ts, sys.new_lost_write_extents$ f, sys.file$ fiwhere ts.ts# = f.extent_datafile_tsid and f.extent_datafile_tsid = fi.ts# and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0 and bitand(ts.flags, 4503599627370496) = 4503599627370496清理回收站
查看回收站对象数及对象名
select count(1) from dba_recyclebin;
COUNT(1)---------- 144
Elapsed: 00:00:00.07
--查看回收站对象select owner,object_name,original_name from dba_recyclebin;确认回收站对象可以清除后，我们来清理回收站，要注意的两点就是确认回收站的对象是否可以清理，以及各个 PDB 容器下的回收站都要清理，不单单是 CDB 容器。
purge dba_recyclebin;然后查看 DBA_FREE_SPACE 和 表空间使用率都可以秒出结果。
另一套 11g 测试库查询表空间使用率需要 1 分 23 秒，收集统计信息也是无果。
那么我们来看一下回收站大小 80.375M，对象有 948个，看来回收站对象还是挺多的了。
SQL> select sum(SPACE)*8/1024 Total_MB from dba_recyclebin;
TOTAL_MB----------80.375SQL> select count(*) from dba_recyclebin;
COUNT(*)---------- 948SQL> purge dba_recyclebin;
DBA Recyclebin purged.
Elapsed: 00:00:48.84那么我们确认可清理后，清理回收站完成之后，查询表空间使用率结果也是秒出了。
写在最后
总的来说，查询表空间使用率如果比较慢对于 DBA
管理人员而言还是比较不耐烦的，当需要快速查看某个表空间时需要等待一两分钟不太友好，如果监控时也是使用的这个 SQL 查看使用率，试想每 5
分钟执行一次，那么看到的慢 SQL 有 99%
的都是查询表空间的，这不是打脸么，那么遇到这样的问题第一反应则是查看执行计划，收集统计信息，如果无果，那就查看回收站对象，如果回收站对象过多，则会导致查看
dba_free_space 变慢，我们需要确认对象是否可清理，清理完成后便可以秒出结果。
全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，你关心谁就分享给谁，一起学习共同进步~~~
❤️ 欢迎关注我的公众号，来一起玩耍吧！！！
————————————————————————————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————