Oracle Tips   Donald K. Burleson

Oracle data file fragmentation

If you want to drill down a little further and find out how badly fragmented each datafile in your database is, you can use the dffrag.sql script:

See Code depot for complete script

select
. . . .

largest_chunk
  from
       sys.dba_free_space a,
       sys.dba_data_files b,
       sys.filext$ c
 where
. . .   

       by 5 desc, 1;

 

One last thing to remember in detecting tablespace fragmentation  is that even if you find numerous free chunk counts in locally-managed tablespaces, it really is not an issue.

Since every object placed in the tablespace will have the same extent size, sooner or later the pockets of free space will be reused, whether new objects are placed into the tablespace or existing objects extend. 

Oracle tablespace map & mapping

If you indeed find fragmentationin your tablespaces, you should identify whether it is of the honeycomb or bubble variety. 

To answer this question, you need to produce a tablespace 'map' that plots the entire tablespace in datafile/block id order.  Doing so will show you a number of interesting things, including where the actual objects in the tablespace reside, along with where the pockets of free space are located. 

A clean tablespace will normally show one large segment of free space at the end.  A badly fragmented tablespace will show bubbles of free space interspersed throughout.  Two free space segments that reside next to one another can identify honeycombs. 

A good tablespace map script for version 7 of Oracle is the tsmap7.sql :

 See Code depot for complete script

select
. . .

   bytes / 1024 size_kb,
       blocks
from  
       sys.dba_free_space
where 
       tablespace_name = <:tablespace name>
union all
select
       owner,
       segment_type,
       segment_name,
       file_id,
       block_id,
       bytes / 1024,
       blocks
from  
       sys.dba_extents
where 
       tablespace_name = <:tablespace name>
order by

      4,5;

 

If version 8 or higher of Oracle is being used, then the tsmap8.sqlscript should be used:

select
. . .

   bytes / 1024 size_kb,
       blocks
from  
       sys.dba_free_space
where 
       tablespace_name = <:tablespace name>
union all
select
       owner,
       segment_type,
. . .    http://www.oracle-scripts.com
       sys.dba_extents
where 
       tablespace_name = <:tablespace name>
order by

       4,5;

 

The above is an excerpt from Oracle Performance Troubleshooting by Robin Schumacher.

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.