jump to navigation

Reuse Of Empty Index Leaf Blocks (Free Four) August 1, 2013

Posted by Richard Foote in DBMS_SPACE, Leaf Blocks, Oracle Indexes.
add a comment

A recent question by Stalin Subbiah has prompted me to write a quick post on the reuse of empty leaf blocks. In part, the question asked:

“Is there anyway I could monitor the effectiveness of empty blocks being reused from freelist of an index resulting from purge process that we are planning to start soon?”

I’ve previously discussed how Oracle can recycle index blocks that contain nothing but deleted index entries as such blocks are effectively added to the index freelist to be reused by subsequent index block splits. In my “Index Internals – Rebuilding The Truth” presentation, I mention a number of methods of how to see this reuse in operation, such as via block dumps, tree dumps and INDEX_STATS.

However, another simple method which I don’t think I’ve discussed here before is the use of the DBMS_SPACE package. So to help answer Stalin’s question, a simple demo.

Let’s start by creating and populating a table/index in a non-ASSM tablespace:

SQL> create table radiohead (id number, name varchar2(30)) tablespace bowie_stuff;

Table created.

SQL> insert into radiohead select rownum, 'DAVID BOWIE' from dual connect by level <= 1000000;

1000000 rows created.

SQL> commit;

Commit complete.

SQL> create index radiohead_id_i on radiohead(id) tablespace bowie_stuff;

Index created.

If we use DBMS_SPACE.FREE_BLOCKS to take a look at the number of free blocks currently in the index:

SQL> var free_blocks number
SQL> exec dbms_space.free_blocks(segment_owner=> user, segment_name=> 'RADIOHEAD_ID_I', segment_type=> 'INDEX', freelist_group_id=> 0, free_blks=> :free_blocks)

PL/SQL procedure successfully completed.

SQL> print free_blocks

FREE_BLOCKS
-----------
          0

We can see there are currently no free blocks.

OK, lets now delete a whole bunch of rows from the table/index:

SQL> delete from radiohead where id between 1 and 900000;

900000 rows deleted.

SQL> commit;

Commit complete.

If we now look at the number of free blocks:

SQL> exec dbms_space.free_blocks(segment_owner=> user, segment_name=> 'RADIOHEAD_ID_I', segment_type=> 'INDEX', freelist_group_id=> 0, free_blks=> :free_blocks)

PL/SQL procedure successfully completed.

SQL> print free_blocks

FREE_BLOCKS
-----------
       2003

We can see we now have some 2003 free blocks. Index blocks that are totally empty or contain nothing but deleted index entries are considered free blocks, which can potentially be reused/recycled by subsequent index block split operations.

We’ll now insert a whole bunch of new rows into the table, about 1/2 the number I deleted. Notice these new rows have ID values that are greater than all the current ID values within the table. As we’re effectively inserting monotonically increasing values, Oracle will perform 90-10 block splits, but these new index blocks as required will simply reuse the empty blocks that previously contained the deleted (lower range) ID values:

SQL> insert into radiohead select rownum+1000000, 'ZIGGY STARDUST'
from dual connect by level <= 500000;

500000 rows created.

SQL> commit;

Commit complete.

We can confirm this by seeing how the number of free blocks has now reduced since the rows have been inserted:

SQL> var free_blocks number
SQL> exec dbms_space.free_blocks(segment_owner=> user, segment_name=> 'RADIOHEAD_ID_I',
segment_type=> 'INDEX', freelist_group_id=> 0, free_blks=> :free_blocks)

PL/SQL procedure successfully completed.

SQL> print free_blocks

FREE_BLOCKS
-----------
        938

We can see that the free blocks has now reduced to just 938 blocks, down from 2003.

So if you’ve previously deleted a batch of rows in a similar manner and you want to keep track of how many index blocks are still currently free (remembering they remain in the index structure in their original logical location until recycled or reused), you can simply use the DBMS_SPACE.FREE_SPACE package.

If your index resides in an Automatic Segment Space Management (ASSM) tablespace, DBMS_SPACE.UNUSED_SPACE provides similar data.

Next, back to Oracle Database 12c and Asynchronous Global Index Maintenance …

Follow

Get every new post delivered to your Inbox.

Join 1,821 other followers