Donald K. Burleson Oracle Tips

Locating sparse Oracle tables in RAC

Why will a table extend even when it has lot’s of free space?  This problem commonly arises when multiple freelists are defined for a table.

The unbalanced freelist issue dates back to the early days of Oracle HA solutions (OPS), and was problematic for large batch jobs.  Even though freelist blocks are shared across nodes, the node that a batch job attaches to will be the one that gets freed blocks from delete activity.  Conversely, insert jobs attach to only one node, and it’s that node that issues the data block addresses for insert activity.

For example, consider a batch job that inserts 2m rows.  The job attaches to a specific node in the RAC cluster, and uses the freelists.  Mellissa Holman on Metalink notes that the primary cost with using multiple process free lists or multiple free list groups is increased space usage, and this space allocation can become unbalanced.

Metalink note 220970.1, says that freelist contention is an issue in RAC for those shops not using Automatic Segment Space Management (ASSM).  “a shortage of freelists and freelist groups can cause contention with header blocks of tables and indexes as multiple instances vie for the same block.  This may cause a performance problem and require data partitioning.”
 

If the table has been loaded with parallel insert statements and then purged with a single process, only one of the freelist gets loaded with the free blocks.  This freelist imbalance cases the empty table to extend, so that the empty freelist can get a free block.  Remember, Oracle does not share free blocks between free lists, and the only solution is to parallelize the delete processes or reorganize the table.

The following script can be used to detect conditions when a table has unbalanced freelists.

column c1  heading "Tablespace";

column c2  heading "Owner";

column c3  heading "Table";

column c4  heading "Bytes M" format 9,999;

column c5  heading "Extents" format 999;

column c7  heading "Empty M" format 9,999;

column c6  heading "Blocks M" format 9,999;

column c8  heading "NEXT M" format 999;

column c9  heading "Row space M" format 9,999;

column c10  heading "Pct Full" format .99;

column db_block_size new_value blksz noprint

select value db_block_size from v$parameter where name = 'db_block_size';

select

        substr(dt.table_name,1,10) c3,

        ds.extents c5,

        ds.bytes/1048576 c4,

        dt.next_extent/1048576 c8,

       (dt.empty_blocks*4096)/1048576 c7,

       (avg_row_len*num_rows)/1048576 c9,

       (ds.blocks*&blksize)/1048576 c6,

       (avg_row_len*num_rows)/(ds.blocks*&blksize) c10

from    sys.dba_segments ds,

        sys.dba_tables dt

where   ds.tablespace_name = dt.tablespace_name

and   ds.owner = dt.owner

and   ds.segment_name = dt.table_name

and dt.freelists > 1

and ds.extents > 1

and dt.owner not in ('SYS','SYSTEM')

and (avg_row_len*num_rows)/1048576 > 50

and ds.bytes/1048576 > 20

order by c10;
 

This report provides a complete picture of the space consumption within an Oracle table.  Also note that there are several filters in the WHERE clause to only report on table with more than one freelist, more than one extent, where there is more than 50 meg of row space used and where the table is more than 20 megabytes.

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: