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官方说明这是正常情况。
版权声明:本文为博主原创文章,未经博主允许不得转载。
DBA_FREE_SPACE 查询慢