The Oracle (tm) Users' Co-operative FAQ

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 ?

Author's name: Hans-Peter Sloot Author's Email:Hans-Peter.Sloot@atosorigin.com	Date written: 9th August 2003 Oracle version(s): 8i
When Oracle introduced Locally managed tablespaces, they had to change their definition of the view dba_free_space to report free space in locally managed tablespaces. As a result, some systems have very poor response times when reporting free space. Is there anything you can do.

Back to index of questions

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