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.68
15: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 49999
select 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$ fi
where ts.ts# = f.ts#
  and f.ts# = fi.ts#
  and f.file# = fi.relfile#
  and ts.bitmapped = 0
union all
select
       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
  and bitand(ts.flags, 4503599627370496) <> 4503599627370496
union all
select
       ts.name, fi.file#, u.ktfbuebno,
       u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where 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) <> 4503599627370496
union all
select 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$ rb
where 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 = 0
union all
select
       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$ fi
where 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.375
SQL> select count(*) from dba_recyclebin;

  COUNT(*)
----------
       948

SQL> 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
————————————————————————————