查询DBA_FREE_SPACE缓慢问题
mhr18 2024-12-24 12:27 13 浏览 0 评论
这个是一个常见的问题，理论上应该也算是一个bug，在oracle10g，到19c，我都曾经遇到过；今天在给两套新建的19C RAC添加监控脚本时，又发现了这个问题，在这里记录一下。
Symptoms
环境：Centos 7.9，Oracle 19.24 RAC
在给两套rac添加tablespace监控脚本，在确认脚本是否正常，其中一套执行查询tablespace使用情况，可以秒出结果，但是另外一套执行查询确要耗时接近1分钟。
检查tablespace 使用情况脚本sql如下
SELECT t.*
FROM (SELECT a.tablespace_name
,a.unalloc_size
,nvl(f.free_size, 0) free_size
,a.used_size - nvl(f.free_size, 0) used_size
,round((a.used_size - nvl(f.free_size, 0)) /
(a.unalloc_size + a.used_size)
,2) capacity
FROM (SELECT tablespace_name
,round(SUM(bytes) / 1024 / 1024) free_size
FROM dba_free_space
GROUP BY tablespace_name) f
,(SELECT tablespace_name
,round(SUM(user_bytes) / 1024 / 1024) used_size
,round(SUM(decode(autoextensible
,'YES'
,decode(sign(maxbytes - user_bytes)
,-1
,0
,maxbytes - user_bytes)
,0)) / 1024 / 1024) unalloc_size
FROM dba_data_files
GROUP BY tablespace_name) a
WHERE 1 = 1
AND a.tablespace_name = f.tablespace_name(+)) t
WHERE capacity >= 0.85
AND (unalloc_size + free_size) < 4000
AND (unalloc_size + free_size) < used_size / 2
ORDER BY capacity DESC;异常查询耗时接近1分钟
考虑到前面的sql只使用到了DBA_FREE_SPACE和DBA_DATA_FILES两个视图，分别单独查询这两个视图，发现单独查询DBA_FREE_SPACE时有问题，也就是问题出在这个视图上
查看DBA_FREE_SPACE具体的sql语句 如下 ，看到这里的视图使用sys.recyclebin$，这样我想起了之前曾经处理过这个问题，就是和recyclebin有关！
SQL> set long 10000
SQL> 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
TEXT
--------------------------------------------------------------------------------
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
TEXT
--------------------------------------------------------------------------------
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#
TEXT
--------------------------------------------------------------------------------
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,
TEXT
--------------------------------------------------------------------------------
(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
再检查sql的执行计划
SQL> set serverout off
SQL> select /*+ gather_plan_statistics */ tablespace_name,
2 sum(bytes) tot_free,
3 count(*) chunks
4 from dba_free_space
5 group by tablespace_name ;
TABLESPACE_NAME TOT_FREE CHUNKS
-------------------- ---------- ----------
SYSTEM 3866624 2
DEMO 103809024 1
SYSAUX 103677952 1
UNDOTBS1 2698706944 270
ASKTOM 122945536 102
USERS 198705152 38
LARGETS 1.3993E+10 8
7 rows selected.
SQL_ID db1x4q4n8kgrs, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ tablespace_name, sum(bytes)
tot_free, count(*) chunks from dba_free_space group by
tablespace_name
Plan hash value: 190806552
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 7 |00:00:47.22 | 699K| 244K| | | |
| 1 | HASH GROUP BY | | 1 | 9 | 7 |00:00:47.22 | 699K| 244K| 1056K| 1056K| 1013K (0)|
| 2 | VIEW | DBA_FREE_SPACE | 1 | 14 | 422 |00:00:00.01 | 699K| 244K| | | |
| 3 | UNION-ALL | | 1 | | 422 |00:00:00.01 | 699K| 244K| | | |
| 4 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 12 | 0 | | | |
| 5 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 12 | 0 | | | |
|* 6 | INDEX FULL SCAN | I_FILE2 | 1 | 3 | 7 |00:00:00.01 | 1 | 0 | | | |
|* 7 | TABLE ACCESS CLUSTER | FET$ | 7 | 1 | 0 |00:00:00.01 | 11 | 0 | | | |
|* 8 | INDEX UNIQUE SCAN | I_TS# | 7 | 1 | 7 |00:00:00.01 | 4 | 0 | | | |
|* 9 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 10 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 11 | HASH JOIN | | 1 | 1 | 307 |00:00:00.01 | 67 | 0 | 1797K| 1797K| 1188K (0)|
| 12 | NESTED LOOPS | | 1 | 1 | 307 |00:00:00.01 | 50 | 0 | | | |
|* 13 | FIXED TABLE FULL | X$KTFBFE | 1 | 8 | 307 |00:00:00.01 | 46 | 0 | | | |
|* 14 | INDEX UNIQUE SCAN | I_FILE2 | 307 | 1 | 307 |00:00:00.01 | 4 | 0 | | | |
|* 15 | TABLE ACCESS FULL | TS$ | 1 | 1 | 7 |00:00:00.01 | 17 | 0 | | | |
| 16 | NESTED LOOPS | | 1 | 10 | 115 |00:01:55.93 | 699K| 244K| | | |
|* 17 | HASH JOIN | | 1 | 17 | 46 |00:00:00.01 | 22 | 0 | 1316K| 1316K| 778K (0)|
| 18 | NESTED LOOPS | | 1 | 19 | 46 |00:00:00.01 | 5 | 0 | | | |
| 19 | NESTED LOOPS | | 1 | 72 | 46 |00:00:00.01 | 4 | 0 | | | |
|* 20 | INDEX FULL SCAN | I_FILE2 | 1 | 3 | 7 |00:00:00.01 | 1 | 0 | | | |
|* 21 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 7 | 24 | 46 |00:00:00.01 | 3 | 0 | | | |
| 22 | TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$ | 46 | 7 | 46 |00:00:00.01 | 1 | 0 | | | |
|* 23 | TABLE ACCESS FULL | TS$ | 1 | 9 | 7 |00:00:00.01 | 17 | 0 | | | |
|* 24 | FIXED TABLE FULL | X$KTFBUE | 46 | 1 | 115 |00:00:47.14 | 699K| 244K| | | |
| 25 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 284 | 0 | | | |
| 26 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 284 | 0 | | | |
| 27 | MERGE JOIN CARTESIAN | | 1 | 138 | 336 |00:00:00.01 | 3 | 0 | | | |
|* 28 | INDEX FULL SCAN | I_FILE2 | 1 | 3 | 7 |00:00:00.01 | 1 | 0 | | | |
| 29 | BUFFER SORT | | 7 | 48 | 336 |00:00:00.01 | 2 | 0 | 2048 | 2048 | 2048 (0)|
| 30 | TABLE ACCESS FULL | RECYCLEBIN$ | 1 | 48 | 48 |00:00:00.01 | 2 | 0 | | | |
| 31 | TABLE ACCESS CLUSTER | UET$ | 336 | 1 | 0 |00:00:00.01 | 281 | 0 | | | |
|* 32 | INDEX UNIQUE SCAN | I_FILE#_BLOCK# | 336 | 1 | 41 |00:00:00.01 | 240 | 0 | | | |
|* 33 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 34 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 35 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 36 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
| 37 | TABLE ACCESS FULL | NEW_LOST_WRITE_EXTENTS$ | 1 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 38 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 39 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
|* 40 | INDEX RANGE SCAN | I_FILE2 | 0 | 1 | 0 |00:00:00.01 | 0 | 0 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(("FI"."TS#" IS NOT NULL AND "FI"."RELFILE#" IS NOT NULL))
7 - filter("F"."FILE#"="FI"."RELFILE#")
8 - access("F"."TS#"="FI"."TS#")
9 - filter("TS"."BITMAPPED"=0)
10 - access("TS"."TS#"="F"."TS#")
11 - access("TS"."TS#"="KTFBFETSN")
13 - filter(("CON_ID"=0 OR "CON_ID"=3))
14 - access("KTFBFETSN"="FI"."TS#" AND "KTFBFEFNO"="FI"."RELFILE#")
filter(("FI"."TS#" IS NOT NULL AND "FI"."RELFILE#" IS NOT NULL))
15 - filter((INTERNAL_FUNCTION("TS"."ONLINE#34;) AND "TS"."CONTENTS#34;=0 AND "TS"."BITMAPPED"<>0 AND
BITAND("TS"."FLAGS",4503599627370496)<>4503599627370496))
17 - access("TS"."TS#"="RB"."TS#")
20 - filter(("FI"."TS#" IS NOT NULL AND "FI"."RELFILE#" IS NOT NULL))
21 - access("RB"."TS#"="FI"."TS#")
23 - filter((INTERNAL_FUNCTION("TS"."ONLINE#34;) AND "TS"."CONTENTS#34;=0 AND "TS"."BITMAPPED"<>0 AND
BITAND("TS"."FLAGS",4503599627370496)<>4503599627370496))
24 - filter((INTERNAL_FUNCTION("CON_ID") AND "KTFBUESEGBNO"="RB"."BLOCK#" AND "KTFBUEFNO"="FI"."RELFILE#" AND "KTFBUESEGFNO"="RB"."FILE#" AND
"KTFBUESEGTSN"="RB"."TS#"))
28 - filter(("FI"."TS#" IS NOT NULL AND "FI"."RELFILE#" IS NOT NULL))
32 - access("U"."TS#"="RB"."TS#" AND "U"."SEGFILE#"="RB"."FILE#" AND "U"."SEGBLOCK#"="RB"."BLOCK#")
filter(("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#"))
33 - filter("TS"."BITMAPPED"=0)
34 - access("TS"."TS#"="U"."TS#")
38 - filter((INTERNAL_FUNCTION("TS"."ONLINE#34;) AND BITAND("TS"."FLAGS",4503599627370496)=4503599627370496 AND "TS"."CONTENTS#34;=0 AND
"TS"."BITMAPPED"<>0))
39 - access("TS"."TS#"="F"."EXTENT_DATAFILE_TSID")
40 - access("F"."EXTENT_DATAFILE_TSID"="FI"."TS#")
filter("FI"."TS#" IS NOT NULL)注意，第16行是一个嵌套LOOP，它有244K读取的累积总数。然后，我将其跟踪到第24行，这是对X$KTFBUE结构的完整扫描，但关键是该扫描执行了46次。 第22行，您可以看到这46次执行是来自我的RECYCLEBIN$。以上基本可以看出是和?回收站有关了。
Cause
查询MOS 检索DBA_FREE_SPACE select slow就可以找到如下文档
参考文档Queries on DBA_FREE_SPACE are Slow (Doc ID 271169.1)
1) In release 10g, the view dba_free_space is modified to access sys.recyclebin$ also.
SQL> 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 /*+ ordered use_nl(f) use_nl(fi) */
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
union all
select /*+ ordered use_nl(u) use_nl(fi) */
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# = u.ktfbuesegtsn
and u.ktfbuesegtsn = fi.ts#
and u.ktfbuesegfno = 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
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
2) Large number of entries in sys.recyclebin$ can slow down the select on dba_free_space.
3) This is a normal behavior.
Solution
Note for 11g database: the dba_free_space view doesn't contain hint which could also affected by the issue. In case there is only few entries in recyclebin, you may want to gather underlying stats of tables/dictionary to get better performance.
Purge the recyclebin.
For example:
SQL> purge recyclebin;
Recyclebin purged.
Or, as SYSDBA for system wide purging.
SQL> purge dba_recyclebin;
Recyclebin purged.虽然oracle10g和19c 视图DBA_FREE_SPACE稍有变化，但是都是通过recyclebin来实现的，理论上出现的问题也是一样的，我在出问题的环境中执行了 purge recyclebin?；再次查询就恢复正常了
?后记：
这应该算是一个bug，而且贯穿了oracle 10-19这个几个大版本，我这套rac还是打了几乎最新的19.24的补丁（最新为19.25），新上线没几周的库，回收站也没有什么东西，还是触发了这个问题，如果遇到这个问题处理也比较简单，清空一下回收站即可。
相关推荐
- Docker集群管理之Docker Compose
-
前言：在上一篇《Docker集群管理之DockerMachine》中，我们通过源码分析了解了DockerMachine的工作原理，使用者可以通过DockerMachine的一条命令在任意支持的平...
- 使用Dockerfile build镜像
-
Docker映像可以看作是Docker容器的压缩包，包含了应用程序以及运行应用程序所需的依赖，容器是映像的运行时实例。一般构建镜像都是使用dockerfile进行构建而不是dockercommit，...
- 自建私有云相册:Docker一键部署Immich，照片视频备份利器
-
自建私有云相册：Docker一键部署Immich，照片视频备份利器前言随着人们手机、PC、平板等电子产品多样，我们拍摄和保存的照片和视频数量也在不断增加。如何高效地管理和备份这些珍贵的记忆成为了一个重...
- docker容器的使用以及部署mysql
-
首先什么是docker官方：翻译：Docker是一个用于开发、发布和运行应用程序的开放平台。Docker使您能够将应用程序与基础架构分离，以便您可以快速交付软件。使用Docker，您可以像管理应...
-
- 自建Docker镜像加速服务，免费且简单，服务器VPS、NAS皆可用
-
写在前面：流程十分简单，有手就行，还请耐心看完。本文的实例仅做演示用，不久后将会删除，有需要的各位请自行搭建。免费实例如果15分钟内未收到入站流量，Render会关闭实例的网络服务。Render会在下次收到处理请求时重新启动该服务。Ren...
-
2025-05-24 15:40 mhr18
- 用了8年的方式-用 Docker 瞬间搭建本地开发环境
-
有些时候我们需要在本地搭开发环境，比如平时学习新技术的时候。或者有时候公司的项目需要在本地建一套类似的，方便调试修改。开发环境可能包括MySQL、Redis、Nginx、MQ、Elasticsea...
- 使用dockerfile构建docker镜像
-
准备工作购买vps使用ssh工具连接上1、更新系统aptupdate-y2、一键安装Dockercurl-fsSLhttps://get.docker.com-oget-docker.sh...
- 快速搭建 SpringCloud 微服务开发环境的脚手架
-
本文适合有SpringBoot和SpringCloud基础知识的人群，跟着本文可使用和快速搭建SpringCloud项目。本文作者：HelloGitHub-秦人HelloGitHub推出...
- Docker Hub最全详解(图文全面总结)
-
DockerHubDockerHub是一个由Docker公司负责维护的公共注册中心，它包含了超过15000多个可用来下载和构建容器的Docker镜像。DockerHub作用Docker好比一个代...
- Docker 命令详解
-
dockerimages—查看本地镜像命令dockerimages说明列出本地已下载的所有镜像及其标签、ID、大小等信息。适用场景查看本地镜像资源、准备删除或管理镜像时。常见用法docker...
- Kylin安装Dify
-
cd/mntgitclonehttps://github.com/langgenius/dify.gitcp/mnt/dify/docker/.env.example/mnt/dif...
- kali下对Docker的详细安装
-
Docker是渗透测试中必学不可的一个容器工具，在其中，我们能够快速创建、运行、测试以及部署应用程序。如，我们对一些漏洞进行本地复现时，可以使用Docker快速搭建漏洞环境，完成复现学习。注：本教程仅...
- 银河麒麟V10使用Docker方式部署应用
-
现在越来越多的企业级应用需要运行在国产化环境中，而银河麒麟V10是目前我碰到的最常用的服务器，在银河麒麟上部署应用有两种方式：使用二进制文件编译部署和使用Docker。关于使用二进制文件的方式...
- Docker入门到精通超详细教程，Docker全家桶实战攻略
-
大家好，我是各位双生的武魂、随身老爷爷。从看到这篇内容开始，你就是被选定的天命骚年，将承担起学完docker教程的使命，本使命为单向契约，你可选择YES或者选择YES。正式学习之前，我先给大家做一下d...
- 【Docker 新手入门指南】第一章:前言
-
一、基本介绍Docker介绍Docker是基于Go语言开发的开源容器化平台，旨在实现“一次镜像，处处运行”。它通过将应用程序及其依赖环境（代码、运行时、系统工具、系统库等）打包成一个轻量级、可移...
欢迎 你 发表评论:
- 一周热门
- 最近发表
- 标签列表
-
- oracle位图索引 (63)
- oracle批量插入数据 (62)
- oracle事务隔离级别 (53)
- oracle 空为0 (50)
- oracle主从同步 (55)
- oracle 乐观锁 (51)
- redis 命令 (78)
- php redis (88)
- redis 存储 (66)
- redis 锁 (69)
- 启动 redis (66)
- redis 时间 (56)
- redis 删除 (67)
- redis内存 (57)
- redis并发 (52)
- redis 主从 (69)
- redis 订阅 (51)
- redis 登录 (54)
- redis 面试 (58)
- 阿里 redis (59)
- redis 搭建 (53)
- redis的缓存 (55)
- lua redis (58)
- redis 连接池 (61)
- redis 限流 (51)