Donald K. Burleson Oracle Tips

How to count the number of blocks in each freelist group for a table

In highly active databases where multiple transactions insert rows into tables, the tables are defined with multiple freelists.  Multiple freelist groups allow multiple transactions to grab free blocks without causing buffer busy waits or segment header contention.

As most experienced Oracle people know, freelists cannot share free blocks, and there are often cases where the freelists become un-balanced.

This most commonly occurs when multiple tasks insert rows into the table, but a single task delete rows.  As the single task deletes the rows, the newly freed data block only participates on one freelist.  This is known as the “sparse table phenomenon”, and is evidenced by a table that continues to extend, even though the dba_tables view shows lot’s of free blocks.

The following script will walk the freelists chains on the tables and provide you with the relative number of free blocks on each freelist.  If you see a serious imbalance between multiple freelist groups, you can use the dbms_repair.rebuild_freelists procedure to coalesce the freelists.

set serveroutput on;

DECLARE

x number;

cursor c1 is

select

   substr(table_name,1,13) table_name,

   substr(a.owner,1,13)      owner

from

   dba_tables a, dba_segments b

where

   a.table_name = b.segment_name

and

   b.extents > 1

and

   a.freelist_groups > 1

and

   b.extents > a.min_extents

and

   empty_blocks > 3*(a.next_extent/4096)

and

   a.owner not in ('SYS','SYSTEM');

BEGIN

FOR c1rec in c1

LOOP

  dbms_space.free_blocks(c1rec.owner, c1rec.table_name, 'TABLE', 01, x );

  dbms_output.put('Number of free list group 1 entries for table ');

  dbms_output.put(c1rec.owner);

  dbms_output.put('.');

  dbms_output.put(c1rec.table_name);

  dbms_output.put(' is ');

  dbms_output.put_line(x);

  dbms_space.free_blocks(c1rec.owner, c1rec.table_name, 'TABLE', 02, x );

  dbms_output.put('Number of free list group 2 entries for table ');

  dbms_output.put(c1rec.owner);

  dbms_output.put('.');

  dbms_output.put(c1rec.table_name);

  dbms_output.put(' is ');

  dbms_output.put_line(x);

  dbms_output.put_line('.');

END LOOP;

END;

/

The procedure REBUILD_FREELISTS is used to rebuild the freelists of tables to evenly re-distribute the free blocks among the freelists. This procedure has four possible inputs, two of which have default values as shown below.

Amazingly enough Oracle put the defaults at the end of the variable list so positional naming isn't required, however, the developer who created all of these procedures has obviously never heard of the UPPER function so you must specify your arguments in upper case or an error will occur.

REBUILD_FREELISTS Procedure IN Parameters

An example run of this procedure is shown below.

Let's try lower case and see how user friendly this procedure is:

SQL> execute dbms_repair.rebuild_freelists('graphics_dba','internal_graphics');

BEGIN dbms_repair.rebuild_freelists('graphics_dba','internal_graphics'); END;

*

ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_REPAIR", line 278
ORA-06512: at line 1

I guess the answer is not very. Let's do it again with upper case inputs:

SQL> execute dbms_repair.rebuild_freelists('GRAPHICS_DBA','INTERNAL_GRAPHICS');

PL/SQL procedure successfully completed.

If there is only one freelist group, the master freelist is updated with all free blocks and the other freelists are zeroed.

If the object has multiple freelist groups then the master freelist in each freelist group is updated in a round-robin fashion and the rest of the freelists are zeroed.

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