标题: 19C DBA_FREE_SPACE查询慢的原因及解决方法 [打印本页] 作者: 郑全 时间: 2022-5-17 11:57 标题: 19C DBA_FREE_SPACE查询慢的原因及解决方法 1.当执行查询表空间使用空间，速度非常慢
select total.ts tablespace,
total.mb total_mb,
NVL(total.mb - free.mb,total.mb) used_mb,
NVL(free.mb,0) free_mb,
DECODE(total.mb,NULL,0,NVL(ROUND((total.mb - free.mb)/(total.mb)*100,2),100)) pct_used,
CASE WHEN (total.mb IS NULL) THEN '['||RPAD(LPAD('OFFLINE',13,'-'),20,'-')||']'
ELSE '['|| DECODE(free.mb,
null,'XXXXXXXXXXXXXXXXXXXX',
NVL(RPAD(LPAD('X',trunc((100-ROUND( (free.mb)/(total.mb) * 100, 2))/5),'X'),20,'-'),
'--------------------'))||']'
END as GRAPH
from
(select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_data_files group by tablespace_name) total,
(select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name) free
where total.ts=free.ts(+)
2.进一步查询，发现是dba_free_space慢
select tablespace_name ts, sum(bytes)/1024/1024 mb from dba_free_space group by tablespace_name；
3.查看dba_free_space定义 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
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
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#
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,
(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
这部分非常慢检查dba_recyclebin，
SQL> select count(*) from dba_recyclebin;
COUNT(*)
----------
1089254.