Oracle Tips   Donald K. Burleson

Oracle Table free space waste

The problem of wasted space in a table and a corresponding misleading high water mark has already been discussed in this chapter.  Needless to say, tables that suffer from high levels of wasted space could definitely be causing your database to spin in ways you do not want.  The other problem that might exist in your tables is one of chained/migrated rows. 

Under normal circumstances, a row of data should fit completely inside one Oracle block.  Sometimes, however, this is not the case, and the table suddenly finds itself containing chained or migrated rows, which are rows that span more than one data block.

Chaining occurs when a row is initially too large to fit inside one block.  Two or more blocks are used by Oracle to hold the row.  Migration deals with rows that have grown so much that they can no longer be contained within their original block.  When this occurs, Oracle relocates the row out of its original block into another block, but leaves a pointer behind to indicate the relocation. 

Both chaining and migration force Oracle to perform more than one I/O to retrieve data that could normally be obtained with a single I/O operation. The end result is degraded performance. 

How can you determine the levels of wasted space in your tables, plus find out if they suffer from a chained/migrated row problem?  The scripts below will provide all the answers you should need.  They locate tables that contain 25% or more wasted space. 

As a bonus, the scripts also calculate the chained row ratio for a table, the percentage of used extents to maximum extents, and determine if the object can extend into its next block of free space. 

In other words, these are nice reorganization diagnostic scripts.  If you are using version 7 of Oracle, you can use the tabreorg7.sql script:

See Code depot for complete script

select  
         owner,  
         segment_name table_name,  
         segment_type,
         round(bytes/1024,2) table_kb,  
         num_rows,  
         blocks,  
         empty_blocks,
         hwm highwater_mark,
         avg_used_blocks,
         greatest(round(100 * (nvl(hwm - avg_used_blocks,0) /
         greatest(nvl(hwm,1),1) ),2),0) block_inefficiency,
. . .

(select 
         a.owner owner,  
         segment_name,
         segment_type,
         bytes,  
         num_rows,  
         a.blocks blocks,  
. . .    

         sys.dba_segments a,  
         sys.dba_tables b,
         sys.ts$ c
   where
         ( a.owner = b.owner ) and 
         ( segment_name = table_name ) and 
         ( ( segment_type = 'table' ) ) and
         b.tablespace_name = c.name),
(  select
         tablespace_name f_tablespace_name,
         max(bytes) max_free_space
    from
          sys.dba_free_space
. . . ;

 

If you using Oracle8 or higher, then use the tabreorg8.sql script:

See Code depot for complete script

select  

         /*+ RULE */
         owner,
         segment_name table_name,  
         segment_type,
         round(bytes/1024,2) table_kb,  
         num_rows,  
         blocks,  
         empty_blocks,
         hwm highwater_mark,
. . .

(select 
         a.owner owner,  
         segment_name,
         segment_type,
         bytes,  
         num_rows,  
 . . .   sys.dba_segments a,  
         sys.dba_all_tables b,
         sys.ts$ c
   where
         ( a.owner = b.owner ) and 
         ( segment_name = table_name ) and 
         ( ( segment_type = 'TABLE ) ) and
         b.tablespace_name = c.name
 union all
 select 
         a.owner owner,  
         segment_name || '.' || b.partition_name,
         segment_type,
         bytes,  
         b.num_rows,  
         a.blocks blocks,  
         b.empty_blocks empty_blocks,  
         . . .

by 10 desc, 1 asc,2 asc

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

It's only $19.95 and you can order it and get instant access to the Oracle scripts here:

http://www.rampant-books.com/book_2003_1_perf.htm

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.