查询慢的现象 在查询数据文件的剩余空间或者查询表空间使用率时，可能会碰见查询很慢的情况，主要原因是在于查询dba_free_space视图的效率特别低。
1 2 3 4 5 6 7 8 SQL> set timing on SQL> select /*+ gather_plan_statistics */count(1) from dba_free_space; COUNT(1) ---------- 12493 已用时间: 00: 00: 03.54
查询一个12493行数据的视图花了大概4s，执行计划如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 select /*+ gather_plan_statistics */count(1) from dba_free_space Plan hash value: 1995021683 -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 41 (100)| 1 |00:00:03.54 | 682K| 166K| | | | | 1 | SORT AGGREGATE | | 1 | 1 | | | 1 |00:00:03.54 | 682K| 166K| | | | | 2 | VIEW | DBA_FREE_SPACE | 1 | 15 | | 41 (44)| 12493 |00:00:00.04 | 682K| 166K| | | | | 3 | UNION-ALL | | 1 | | | | 12493 |00:00:00.04 | 682K| 166K| | | | | 4 | NESTED LOOPS | | 1 | 1 | 40 | 5 (0)| 0 |00:00:00.01 | 33 | 0 | | | | | 5 | NESTED LOOPS | | 1 | 1 | 33 | 4 (0)| 0 |00:00:00.01 | 33 | 0 | | | | | 6 | TABLE ACCESS CLUSTER | FET$ | 1 | 1 | 26 | 3 (0)| 0 |00:00:00.01 | 33 | 0 | | | | | 7 | INDEX FULL SCAN | I_TS# | 1 | 1 | | 1 (0)| 32 |00:00:00.01 | 1 | 0 | | | | |* 8 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 7 | 1 (0)| 0 |00:00:00.01 | 0 | 0 | | | | |* 9 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | | 1 (0)| 0 |00:00:00.01 | 0 | 0 | | | | |* 10 | INDEX UNIQUE SCAN | I_FILE2 | 0 | 1 | 7 | 1 (0)| 0 |00:00:00.01 | 0 | 0 | | | | |* 11 | HASH JOIN | | 1 | 3 | 99 | 2 (0)| 5184 |00:00:00.04 | 952 | 0 | 2716K| 2716K| 1803K (0)| | 12 | NESTED LOOPS | | 1 | 3 | 45 | 1 (0)| 5184 |00:00:00.04 | 919 | 0 | | | | |* 13 | FIXED TABLE FULL | X$KTFBFE | 1 | 3 | 24 | 0 (0)| 5184 |00:00:00.03 | 915 | 0 | | | | |* 14 | INDEX UNIQUE SCAN | I_FILE2 | 5184 | 1 | 7 | 1 (0)| 5184 |00:00:00.01 | 4 | 0 | | | | |* 15 | TABLE ACCESS CLUSTER | TS$ | 1 | 1 | 18 | 1 (0)| 30 |00:00:00.01 | 33 | 0 | | | | | 16 | INDEX FULL SCAN | I_TS# | 1 | 1 | | 1 (0)| 32 |00:00:00.01 | 1 | 0 | | | | |* 17 | HASH JOIN | | 1 | 9 | 909 | 23 (79)| 7309 |00:00:03.50 | 681K| 166K| 2546K| 2546K| 1793K (0)| |* 18 | HASH JOIN | | 1 | 10 | 830 | 22 (82)| 7309 |00:00:00.04 | 681K| 166K| 1572K| 1572K| 1567K (0)| | 19 | MERGE JOIN | | 1 | 605 | 10890 | 5 (20)| 597 |00:00:00.01 | 38 | 0 | | | | | 20 | TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$ | 1 | 582 | 6402 | 3 (0)| 574 |00:00:00.01 | 37 | 0 | | | | | 21 | INDEX FULL SCAN | RECYCLEBIN$_TS | 1 | 582 | | 1 (0)| 574 |00:00:00.01 | 11 | 0 | | | | |* 22 | SORT JOIN | | 574 | 63 | 441 | 2 (50)| 597 |00:00:00.01 | 1 | 0 | 9216 | 9216 | 8192 (0)| | 23 | INDEX FULL SCAN | I_FILE2 | 1 | 63 | 441 | 1 (0)| 65 |00:00:00.01 | 1 | 0 | | | | |* 24 | FIXED TABLE FULL | X$KTFBUE | 1 | 1000 | 65000 | 17 (100)| 584K|00:00:03.35 | 681K| 166K| | | | |* 25 | TABLE ACCESS CLUSTER | TS$ | 1 | 1 | 18 | 1 (0)| 30 |00:00:00.01 | 33 | 0 | | | | | 26 | INDEX FULL SCAN | I_TS# | 1 | 1 | | 1 (0)| 32 |00:00:00.01 | 1 | 0 | | | | | 27 | NESTED LOOPS | | 1 | 1 | 64 | 6 (0)| 0 |00:00:00.01 | 33 | 0 | | | | | 28 | NESTED LOOPS | | 1 | 83 | 64 | 6 (0)| 0 |00:00:00.01 | 33 | 0 | | | | | 29 | NESTED LOOPS | | 1 | 1 | 53 | 5 (0)| 0 |00:00:00.01 | 33 | 0 | | | | | 30 | NESTED LOOPS | | 1 | 1 | 46 | 4 (0)| 0 |00:00:00.01 | 33 | 0 | | | | |* 31 | TABLE ACCESS CLUSTER | TS$ | 1 | 1 | 7 | 3 (0)| 0 |00:00:00.01 | 33 | 0 | | | | | 32 | INDEX FULL SCAN | I_TS# | 1 | 1 | | 1 (0)| 32 |00:00:00.01 | 1 | 0 | | | | | 33 | TABLE ACCESS CLUSTER | UET$ | 0 | 1 | 39 | 1 (0)| 0 |00:00:00.01 | 0 | 0 | | | | |* 34 | INDEX RANGE SCAN | I_FILE#_BLOCK# | 0 | 1 | | 1 (0)| 0 |00:00:00.01 | 0 | 0 | | | | |* 35 | INDEX UNIQUE SCAN | I_FILE2 | 0 | 1 | 7 | 1 (0)| 0 |00:00:00.01 | 0 | 0 | | | | |* 36 | INDEX RANGE SCAN | RECYCLEBIN$_TS | 0 | 83 | | 1 (0)| 0 |00:00:00.01 | 0 | 0 | | | | |* 37 | TABLE ACCESS BY INDEX ROWID | RECYCLEBIN$ | 0 | 1 | 11 | 1 (0)| 0 |00:00:00.01 | 0 | 0 | | | | | 38 | NESTED LOOPS | | 1 | 1 | 34 | 4 (0)| 0 |00:00:00.01 | 0 | 0 | | | | | 39 | NESTED LOOPS | | 1 | 1 | 31 | 3 (0)| 0 |00:00:00.01 | 0 | 0 | | | | | 40 | TABLE ACCESS FULL | NEW_LOST_WRITE_EXTENTS$ | 1 | 1 | 13 | 2 (0)| 0 |00:00:00.01 | 0 | 0 | | | | |* 41 | TABLE ACCESS CLUSTER | TS$ | 0 | 1 | 18 | 1 (0)| 0 |00:00:00.01 | 0 | 0 | | | | |* 42 | INDEX UNIQUE SCAN | I_TS# | 0 | 1 | | 1 (0)| 0 |00:00:00.01 | 0 | 0 | | | | |* 43 | INDEX RANGE SCAN | I_FILE2 | 0 | 1 | 3 | 1 (0)| 0 |00:00:00.01 | 0 | 0 | | | | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 8 - filter("TS"."BITMAPPED"=0) 9 - access("TS"."TS#"="F"."TS#") 10 - access("F"."TS#"="FI"."TS#" AND "F"."FILE#"="FI"."RELFILE#") 11 - access("TS"."TS#"="KTFBFETSN") 13 - filter(("CON_ID"=0 OR "CON_ID"=3)) 14 - access("KTFBFETSN"="FI"."TS#" AND "KTFBFEFNO"="FI"."RELFILE#") 15 - filter(("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND INTERNAL_FUNCTION("TS"."ONLINE$") AND BITAND("TS"."FLAGS",4503599627370496)<>4503599627370496)) 17 - access("TS"."TS#"="RB"."TS#") 18 - access("KTFBUEFNO"="FI"."RELFILE#" AND "KTFBUESEGTSN"="RB"."TS#" AND "KTFBUESEGFNO"="RB"."FILE#" AND "KTFBUESEGBNO"="RB"."BLOCK#") 22 - access("RB"."TS#"="FI"."TS#") filter("RB"."TS#"="FI"."TS#") 24 - filter(("CON_ID"=0 OR "CON_ID"=3)) 25 - filter(("TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND INTERNAL_FUNCTION("TS"."ONLINE$") AND BITAND("TS"."FLAGS",4503599627370496)<>4503599627370496)) 31 - filter("TS"."BITMAPPED"=0) 34 - access("TS"."TS#"="U"."TS#") 35 - access("U"."TS#"="FI"."TS#" AND "U"."SEGFILE#"="FI"."RELFILE#") 36 - access("U"."TS#"="RB"."TS#") 37 - filter(("U"."SEGBLOCK#"="RB"."BLOCK#" AND "U"."SEGFILE#"="RB"."FILE#")) 41 - filter((BITAND("TS"."FLAGS",4503599627370496)=4503599627370496 AND "TS"."CONTENTS$"=0 AND "TS"."BITMAPPED"<>0 AND INTERNAL_FUNCTION("TS"."ONLINE$"))) 42 - access("TS"."TS#"="F"."EXTENT_DATAFILE_TSID") 43 - access("F"."EXTENT_DATAFILE_TSID"="FI"."TS#") - this is an adaptive plan
从执行计划可以看到消耗性能的重点在于第18-24行过程中，通过先对RECYCLEBIN$索引全扫描，在对索引 I_FILE2 （FILE$表）全扫描，merge后得到的结果与X$KTFBUE表进行hash join，而这里X$KTFBUE 估算为1000行，实际为584K行。由于对X$KTFBUE的全表扫描，表示Oracle要读取数据库里每一个segment的header块（这里并不清楚这估算的1000行数据怎么来的，这个X$KTFBUE 表上并没有统计信息）
1 2 3 4 5 6 7 8 9 10 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- |* 18 | HASH JOIN | | 1 | 10 | 830 | 22 (82)| 7309 |00:00:00.04 | 681K| 166K| 1572K| 1572K| 1567K (0)| | 19 | MERGE JOIN | | 1 | 605 | 10890 | 5 (20)| 597 |00:00:00.01 | 38 | 0 | | | | | 20 | TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$ | 1 | 582 | 6402 | 3 (0)| 574 |00:00:00.01 | 37 | 0 | | | | | 21 | INDEX FULL SCAN | RECYCLEBIN$_TS | 1 | 582 | | 1 (0)| 574 |00:00:00.01 | 11 | 0 | | | | |* 22 | SORT JOIN | | 574 | 63 | 441 | 2 (50)| 597 |00:00:00.01 | 1 | 0 | 9216 | 9216 | 8192 (0)| | 23 | INDEX FULL SCAN | I_FILE2 | 1 | 63 | 441 | 1 (0)| 65 |00:00:00.01 | 1 | 0 | | | | |* 24 | FIXED TABLE FULL | X$KTFBUE | 1 | 1000 | 65000 | 17 (100)| 584K|00:00:03.35 | 681K| 166K| | | |
对查询的过程做trace，发现大部分的时间都在等待db file sequential read事件，也就是要读取每个segment的header块。
1 2 3 4 5 6 7 8 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SID, USERNAME , TYPE, STATISTIC , DELTA, HDELTA/SEC, %TIME, GRAPH , NUM_WAITS, WAITS/SEC, AVERAGES --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 279, SYS , WAIT, latch: cache buffers chains , 3525, 174.95us, .0%, [ ], 405, 20.1, 8.7us average wait 279, SYS , WAIT, read by other session , 3052684, 151.51ms, 15.2%, [WW ], 3530, 175.2, 864.78us average wait 279, SYS , WAIT, db file sequential read , 16622726, 825ms, 82.5%, [WWWWWWWWW ], 13254, 657.81, 1.25ms average wait -- End of Stats snap 1, end=2023-04-04 10:25:32, seconds=20.1
原因分析
如果回收站没有数据的话，那么这个hash join和table full也就不会发生，然而在这个环境当中recyclebin是启用的，里面有574行记录，所以导致全表扫描的发生。
第二点， 即使回收站里面有数据，如果访问X$KTFBUE 不是走全表扫描，而是走索引的话那么效率也会高的多，代价也没有这么高。
所以从两个方向来进行思考：
为什么查询剩余空间会查询到recyclebin$表，回收站里也会占据空间吗？那么是算可用还是不可用？
为什么X$KTFBUE的估算行数跟实际行数差别这么大？为什么没有统计信息？要如何收集它的统计信息呢？
Recyclebin的空间占用 表空间TS_XB 为我用来测试的例子，通过下面这个sql可以得出表空间TS_XB 的空间大小、剩余空间、已分配到extents的空间、已分配到segments的空间。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 select 'File space' What, nvl(sum(user_bytes)/1048576,0) MB from dba_data_files where tablespace_name = 'TS_XB' union all select 'Free space', nvl(sum(bytes/1048576),0) from dba_free_space where tablespace_name = 'TS_XB' union all select 'Extents', nvl(sum(bytes/1048576),0) from dba_extents where tablespace_name = 'TS_XB' union all select 'Segments', nvl(sum(bytes/1048576),0) from dba_segments where tablespace_name = 'TS_XB' ; WHAT MB ---------- ---------- File space 38 Free space 38 Extents 0 Segments 34
这里能观察到，File space和Free space大小基本是一致的，而分配到segments的空间大小则有34M，分配到extents的空间则为0。通过这几个数据你觉得这个表空间到底是空闲还是已满呢？
这里既然segments已经分配了34MB的大小，可以先查询到底是那些segments占用了
1 2 3 4 5 6 SQL> select segment_name from dba_segments where tablespace_name='TS_XB'; SEGMENT_NAME -------------------------------------------------------------------------------- BIN$+GjLhdmVcGbgVXcViOqQBQ==$0 BIN$+GjLhdmUcGbgVXcViOqQBQ==$0
可以看到几乎都是BIN$开头的对象，也就是处于回收站当中的对象。即使被drop掉以后仍然占据了segments空间，但是Oracle又认为这部分占用空间是可以回收利用的，所以也将其算在了dba_free_space其中。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 SQL> purge recyclebin; Recyclebin purged. SQL> select 'File space' What, nvl(sum(user_bytes)/1048576,0) MB from dba_data_files where tablespace_name = 'TS_XB' 2 union all 3 select 'Free space', nvl(sum(bytes/1048576),0) from dba_free_space where tablespace_name = 'TS_XB' 4 union all 5 select 'Extents', nvl(sum(bytes/1048576),0) from dba_extents where tablespace_name = 'TS_XB' 6 union all 7 select 'Segments', nvl(sum(bytes/1048576),0) from dba_segments where tablespace_name = 'TS_XB' 8 ; WHAT MB ---------- ---------- File space 38 Free space 38 Extents 0 Segments 0
清理完回收站后，可以看到segments的空间也被立即释放出来了。
收集Fixed Table STATS 现在的表是没有统计信息的，默认情况下就是1000行，那么很容易联想到导致全表扫描的原因是因为没有统计信息。
1 2 3 4 5 SQL> select owner,table_name,NUM_ROWS,BLOCKS,LAST_ANALYZED from dba_tab_statistics where table_name='X$KTFBUE'; OWNER TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED ------------------------------ ------------------------------ ---------- ---------- ------------------- SYS X$KTFBUE
通常对于fixed table收集统计信息都是通过存储过程DBMS_STATS.GATHER_FIXED_OBJECTS_STATS，但是这个也是要对所有fixed表进行收集，也会对性能有影响。
1 exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
然而你会发现，即使这样收集完以后，X$KTFBUE表上仍然是没有任何统计信息。
这是由于Oracle认为收集这些fixed表的统计信息：
会带来较大的性能消耗
不收集SQL性能会更好
这些表不支持收集统计信息
统计信息过期的太快
X$KTFBUE表就属于上面这些因素的其中一张表，这种表不止一个，还有很多其他的fixed表也是这样。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 select last_analyzed, count(1) from (SELECT case when last_analyzed is null then '无统计信息' else '包含统计信息' end last_analyzed FROM DBA_TAB_STATISTICS WHERE OBJECT_TYPE = 'FIXED TABLE') group by last_analyzed; LAST_ANALYZE COUNT(1) ------------ ---------- 无统计信息 157 包含统计信息 1178
直接对表进行统计信息收集
1 2 3 4 5 6 7 exec DBMS_STATS.GATHER_TABLE_STATS('SYS','X$KTFBUE'); SQL> select owner,table_name,NUM_ROWS,BLOCKS,LAST_ANALYZED from dba_tab_statistics where table_name='X$KTFBUE'; OWNER TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED ------------------------------ -------------------- ---------- ---------- ------------------- SYS X$KTFBUE 586359 2023-04-04 13:51:40
结果验证 经过清空回收站和收集统计信息以后，重新查询DBA_FREE_SPACE
1 2 3 4 5 6 7 SQL> select /*+ gather_plan_statistics */count(1) from dba_free_space; COUNT(1) ---------- 6636 已用时间: 00: 00: 00.03
执行计划由于回收站的数据为空，实际也就没有产生对X$KTFBUE的全表扫描
1 2 3 4 5 6 7 8 9 10 11 12 ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- |* 17 | HASH JOIN | | 1 | 9 | 909 | 23 (79)| 0 |00:00:00.01 | 11 | 2546K| 2546K| 522K (0)| |* 18 | HASH JOIN | | 1 | 10 | 830 | 22 (82)| 0 |00:00:00.01 | 11 | 1572K| 1572K| 480K (0)| | 19 | MERGE JOIN | | 1 | 605 | 10890 | 5 (20)| 0 |00:00:00.01 | 11 | | | | | 20 | TABLE ACCESS BY INDEX ROWID| RECYCLEBIN$ | 1 | 582 | 6402 | 3 (0)| 0 |00:00:00.01 | 11 | | | | | 21 | INDEX FULL SCAN | RECYCLEBIN$_TS | 1 | 582 | | 1 (0)| 0 |00:00:00.01 | 11 | | | | |* 22 | SORT JOIN | | 0 | 63 | 441 | 2 (50)| 0 |00:00:00.01 | 0 | 9216 | 9216 | 8192 (0)| | 23 | INDEX FULL SCAN | I_FILE2 | 0 | 63 | 441 | 1 (0)| 0 |00:00:00.01 | 0 | | | | |* 24 | FIXED TABLE FULL | X$KTFBUE | 0 | 1000 | 65000 | 17 (100)| 0 |00:00:00.01 | 0 | | | | -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
BTW
是根据LMT（locally managed tablespace）中使用的extents信息动态生成的表。可以看到X$KTFBUE的数据量几乎跟数据库里的extents数量接近
1 2 3 4 5 6 7 8 9 10 11 SQL> select count(1) from dba_extents; COUNT(1) ---------- 580315 SQL> select count(1) from X$KTFBUE; COUNT(1) ---------- 580328
1 2 3 4 5 6 7 8 9 10 11 12 13 ADDR : N/A INDX : N/A INST_ID : N/A KTFBUESEGTSN : ts# containing this segment KTFBUESEGFNO : Relative number of the file containing the segment header KTFBUESEGBNO : segment header block number KTFBUEEXTNO : Extent number KTFBUEFNO : Relative number of the file containing the extent KTFBUEBNO : Starting block number of the extent KTFBUEBLKS : Size of the extent in ORACLE blocks KTFBUECTM : commit_jtime,Commit Time of the undo in the extent expressed as Julian date KTFBUESTT :commit_wtime,Commit Time of the undo in the extent expressed as wall clock time KTFBUESTA : Transaction Status of the undo in the extent;1, 'ACTIVE', 2, 'EXPIRED', 3, 'UNEXPIRED', 0 for non‐undo