查询DBA_FREE_SPACE缓慢问题
mhr18 2024-12-24 12:27 20 浏览 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），新上线没几周的库，回收站也没有什么东西，还是触发了这个问题，如果遇到这个问题处理也比较简单，清空一下回收站即可。
相关推荐
- mysql一 基本操作（mysql基础操作命令）
-
先讲数据库--mysql、redis、MongoDB爬虫今天的内容：mysql一基本操作注意事项：1.安装：自己安装，有问题可以问老师、可以自己找办法解决2.上课：先讲知识点再回答问题3....
- 香港DM德馬景泰藍文科集團20250702期《生命的收獲》LIN JING DE
-
香港DM德馬景泰藍文科集團20250702期《生命的收獲》DMSIXSEVEN/LINJINGDE/1973主編供圖：香港DM德馬數字甲骨文集團Hongkong-basedDMDelm...
- 01.Java发展历史（java发展简史）
-
1.Java发展历史Java由SunMicrosystems公司（现为Oracle公司）的JamesGosling及其团队在1991年开发，最初命名为"Oak"，后改名为"...
- 月薪最高12000!看看这些宝山岗位有没有你心仪的~
-
招聘目录信息1猎上网络科技（上海）有限公司职位分析师职位分析师助理2上海祥腾金属材料有限公司商务车驾驶员3上海赋拓物联网技术有限公司软件测试工程师4上海集优标五高强度紧固件有限公司...
- 69岁的Java之父终于退休了，他竟然也经历过职场PUA
-
有些程序员不到35岁就要担心被优化，但有的程序员干到了69岁的高龄，才准备退休。就前几天，Java之父詹姆斯·高斯林，自个儿在领英上宣布以后要美美享受退休生活了。这一下子，就炸出了不少...
- ocm认证年薪多少?（ocm认证好考吗）
-
从业数据库方面的工程师待遇都很好，年薪30万都是比较平常的事。OCM认证的价值可以归纳成以下几点：1、可以全面掌握Oracle知识，专业知识完善；2、证书对于公司招投标有巨大作用，甚至可以不上班就拿钱...
- 自学java开发攻略，想做程序员的必备攻略?
-
背景现阶段，随着互联网的快速发展，程序员这个行业越来月吃香，不仅仅是因为科技先进，说出去很牛逼，让别人产生崇拜，主要原因是程序员行业薪资待遇好，相比国内其他行业，程序员的薪资待遇是羡慕了很多人呢！于是...
- SpringBoot WebFlux整合R2DBC实现数据库反应式编程
-
环境：Springboot2.4.12R2DBC简介SpringdataR2DBC是更大的Springdata系列的一部分，它使得实现基于R2DBC的存储库变得容易。R2DBC代表反应式关系数...
- AI编码工具未能代替码农:大模型的局限性揭秘
-
随着ChatGPT和GithubCopilot等AI编码工具的兴起，StackOverflow近日因流量减少宣布裁员近三分之一。这引发了一个争议的问题：ChatGPT这类AI编码工具，真的要颠覆整...
- 大模型无法替代码农!GPT-4解决GitHub编程问题成功率为0
-
编辑：编辑部【新智元导读】ChatGPT之类的AI编码工具来势汹汹，StackOverflow又裁员了！不过，普林斯顿和芝大竟发现，面对真实世界GitHub问题，GPT-4的解决率竟是0%。Stac...
- 机器人动作领域突破!UniVLA模型实现跨模态AI机器人动作统一
-
还记得《星际穿越》中那台能在无重力、尘暴甚至巨浪中切换形态、随时理解库珀指令的TARS多功能机器人吗？在银幕里，我们羡慕TARS能听懂人话、看懂环境、马上给出最合适的动作；而在现实世界的实验室里，“让...
- FEA-Bench:首个仓库级新功能实现基准，让大模型更懂软件开发
-
当前，大语言模型在代码生成领域已展现出惊人的能力，但能否胜任真实软件开发中的“新增功能实现”任务，仍是一个关键未解的问题。对此，微软亚洲研究院与北京大学联合发布了首个专注于仓库级新功能实现的基准测试...
- 基于Java的软件版本信息管理系统-2025计算机毕业设计
-
基于Java的软件版本信息管理系统-2025计算机毕业设计前言随着移动互联网的快速发展，APP已成为人们日常生活中不可或缺的一部分。为了高效地管理这些APP的信息，如版本更新、用户反馈、下载量统计等，...
- 马斯克在干嘛?抱着它现身推特 简介改为“推特老板”
-
马斯克收购推特的交易真是“没完没了”。据彭博社报道，当地时间26日，美国亿万富翁埃隆·马斯克抱着水槽走进了推特公司的旧金山总部，他还将自己在推特账号的公开自我介绍改为“推特老板”（ChiefTw...
- 福布斯公布7月全球十大富豪:马斯克成6月最大“输家”，身家缩水160亿美元
-
7月日，福布斯今日公布了2025年7月全球十大富豪榜，其中第二名迎来新面孔。软件巨头甲骨文（Oracle）联合创始人兼董事长拉里埃里森（LarryEllison）凭借公司股价飙升成功上位。由...
欢迎 你 发表评论:
- 一周热门
- 最近发表
- 标签列表
-
- oracle位图索引 (74)
- oracle批量插入数据 (65)
- oracle事务隔离级别 (59)
- oracle 空为0 (51)
- oracle主从同步 (56)
- oracle 乐观锁 (53)
- 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)