查询DBA_FREE_SPACE缓慢问题
这个是一个常见的问题，理论上应该也算是一个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) capacityFROM (SELECT tablespace_name,round(SUM(bytes) / 1024 / 1024) free_sizeFROM dba_free_spaceGROUP 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_sizeFROM dba_data_filesGROUP BY tablespace_name) aWHERE 1 = 1AND a.tablespace_name = f.tablespace_name(+)) tWHERE capacity >= 0.85AND (unalloc_size + free_size) < 4000AND (unalloc_size + free_size) < used_size / 2ORDER BY capacity DESC;
异常查询耗时接近1分钟
考虑到前面的sql只使用到了DBA_FREE_SPACE和DBA_DATA_FILES两个视图，分别单独查询这两个视图，发现单独查询DBA_FREE_SPACE时有问题，也就是问题出在这个视图上
查看DBA_FREE_SPACE具体的sql语句 如下 ，看到这里的视图使用sys.recyclebin$，这样我想起了之前曾经处理过这个问题，就是和recyclebin有关！
SQL> set long 10000SQL> 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$ fiwhere ts.ts# = f.ts#and f.ts# = fi.ts#and f.file# = fi.relfile#and ts.bitmapped = 0union allselectts.name, fi.file#, f.ktfbfebno,f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefnoTEXT--------------------------------------------------------------------------------from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fiwhere ts.ts# = f.ktfbfetsnand f.ktfbfetsn = fi.ts#and f.ktfbfefno = fi.relfile#and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0and bitand(ts.flags, 4503599627370496) <> 4503599627370496union allselectts.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$ fiTEXT--------------------------------------------------------------------------------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$ = 0and bitand(ts.flags, 4503599627370496) <> 4503599627370496union allselect 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$ 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 allselectts.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$ fiwhere ts.ts# = f.extent_datafile_tsidand f.extent_datafile_tsid = fi.ts#and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0and bitand(ts.flags, 4503599627370496) = 4503599627370496
再检查sql的执行计划
SQL> set serverout offSQL> select /*+ gather_plan_statistics */ tablespace_name,2 sum(bytes) tot_free,3 count(*) chunks4 from dba_free_space5 group by tablespace_name ;TABLESPACE_NAME TOT_FREE CHUNKS-------------------- ---------- ----------SYSTEM 3866624 2DEMO 103809024 1SYSAUX 103677952 1UNDOTBS1 2698706944 270ASKTOM 122945536 102USERS 198705152 38LARGETS 1.3993E+10 87 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 bytablespace_namePlan 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$") AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 ANDBITAND("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$") AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 ANDBITAND("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$") AND BITAND("TS"."FLAGS",4503599627370496)=4503599627370496 AND "TS"."CONTENTS$"=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$ fiwhere ts.ts# = f.ts#and f.ts# = fi.ts#and f.file# = fi.relfile#and ts.bitmapped = 0union allselect /*+ ordered use_nl(f) use_nl(fi) */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.ktfbfetsnand f.ktfbfetsn = fi.ts#and f.ktfbfefno = fi.relfile#and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0union allselect /*+ ordered use_nl(u) use_nl(fi) */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# = u.ktfbuesegtsnand 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$ = 0union 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 = 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），新上线没几周的库，回收站也没有什么东西，还是触发了这个问题，如果遇到这个问题处理也比较简单，清空一下回收站即可。