Donald K. Burleson Oracle Tips

Finding tables with RAW columns

One little-known feature of Oracle is that Oracle will never re-use rows that are below the high water mark for any table that contains RAW or LONG RAW columns.

This can have important ramifications for efficient re-use of table storage.  Because Oracle will always grab free blocks from the freelist rather than re-use rows below the high-water mark, it is critical that you re-set the values of PCTUSED and PCTFREE to reasonable values.

Remember, for table with RAW columns, freelist re-link operations will NOT re-populate usable free blocks onto the freelist.  Hence, we always want to set PCTUSED to a very low value.  When PCTUSED is set to 5, Oracle will not re-link a data block onto the freelist chain until it is 5% used, 95% empty.  Since the data block cannot be reused anyway, this reduced operational overhead in the segment header.

In sum, table with RAW columns that have frequent deletes should be manually reorganized to coalesce all free space that was made by the deletes.

Here is the script I use to identify these tables.

-- raw.sql

set heading off;

set pages 9999;

set feedback off;

spool raw.lst

select distinct

   ' alter table '||owner||'.'||table_name||

   ' pctused 5'||

   ' pctfree 40'||

   ';'

from

   dba_tables

where

table_name in

 (select table_name from dba_tab_columns

   where

 data_type in ('RAW','LONG RAW')

 )

and

owner in ('SSSS')

and

pct_used <> 5

and

pct_free <> 40

;

spool off;

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95(I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

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