DBA_FREE_SPACE查询慢的原因及解决方法

转自:http://www.cndba.cn/Expect-le/article/261

DBA_FREE_SPACE查询慢的原因及解决方法

MOS文档271169.1

1.当执行查询表空间使用空间，速度非常慢

    SELECT D.TABLESPACE_NAME,
      SPACE,
      (SPACE - NVL (FREE_SPACE, 0)),
      ROUND ((1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2),
      FREE_SPACE
      FROM (SELECT TABLESPACE_NAME,  
      ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,  
      SUM (BLOCKS) BLOCKS  
     FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME) D,  
       (  SELECT TABLESPACE_NAME,  
        ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE  
         FROM DBA_FREE_SPACE
        GROUP BY TABLESPACE_NAME) F  
       WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)

第一感觉是DBA_FREE_SPACE表查询慢，查看执行计划也是设计到很多内部表。一头雾水

查看DBA_FREE_SPACE视图的sql语句

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
  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 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# = 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
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

这里设计到了回收站的表sys.recyclebin$，这也是正常情况。在回收站里的对象，也是占用空间的。

sys.recyclebin$里的大量对象会降低DBA_FREE_SPACE查询速度

2.解决方法

#查看CDB中回收站对象数量

SQL> select count(1) from dba_recyclebin;
  COUNT(1)
----------
 123

#清空回收站

SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> select count(1) from dba_recyclebin;
  COUNT(1)
----------
 0

注意：在ORACLE 12C中，各个PDB和CDB之间回收站是私有的，所以对每个PDB回收站进行单独的清空。

#切换到PDB中再次查看

SQL> ALTER SESSION SET CONTAINER=ZHIXIN;
Session altered.
SQL> select count(1) from dba_recyclebin;
  COUNT(1)
----------
91

PDB中的回收站对象是没有清空的。需要单独情况回收站。

#再次查询速度就很快了。

注意：回收站数据清空前，要确认是否可以清除。ORACLE官方说明这是正常情况。