Queries against dba_free_space with locally managed tablespaces are slow - is there any way to speed them up ?

The Oracle (tm) Users' Co-Operative FAQ

Queries against dba_free_space with locally managed tablespaces are slow - is there any way to speed them up ?

Just because Oracle Corp. supplies a view definition, that doesn't mean you have to use it. You might create a new definition of dba_free_space that is much faster than the original one.

On one database used for BAAN a query on dba_free_space lasted 50 minutes, whereas the following new definition returned the same results within 50 seconds.Of course, as versions of Oracle change, you may have to keep updating the definitions of your views to cater for new features.

CREATE OR REPLACE VIEW DBA_FREE_SPACE_NEW ( 
	TABLESPACE_NAME,
	FILE_ID, 
	BLOCK_ID, 
	BYTES, 
	BLOCKS,
	RELATIVE_FNO 
) AS
select /*+use_hash (tsfi, fet2 ) */
	tsfi.tablespace_name, tsfi.file_id, fet2.block_id,
	tsfi.blocksize * fet2.blocks, fet2.blocks, tsfi.relfile#
from
	(
	  select /*+ use_hash ( ts, fi ) */
	     ts.name tablespace_name, fi.file# file_id, ts.BLOCKSIZE,
	     fi.relfile#, ts.ts#
	  from sys.ts$ ts, sys.file$ fi
	  where ts.ts# = fi.ts#
	  and ts.online$ in (1,4)
	) tsfi,
	(
	  select f.block# block_id, f.length blocks, f.file# file_id, f.ts#
	  from sys.fet$ f
	  union all
	  select f.ktfbfebno block_id, f.ktfbfeblks blocks, f.ktfbfefno, ktfbfetsn
	  from sys.x_$ktfbfe f
	) fet2         
where
   fet2.file_id = tsfi.relfile# and
   fet2.ts# = tsfi.ts#;     

Further reading: n/a