查询DBA_FREE_SPACE缓慢问题

mahan

环境：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$") AND "TS"."CONTENTS$"=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$") AND "TS"."CONTENTS$"=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$") 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$ 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；再次查询就恢复正常了