Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

Donald K. Burleson

Oracle Tips

Predict indexes for rebuilding

 

By John Palinski

 

One of the DBA's chores is to monitor tablespace sizes to ensure that adequate space is available for the objects that reside within the tablespace.  A second task is to monitor index sizes and rebuild the indexes when they get too large or fragmented.  See the latest consensus on index rebuilding.

 

Indexes have a tendency to grow as they are being updated in a different proportion to the referenced data. This is especially true of bitmap indexes that can grow very rapidly when updated.  Besides the space savings, smaller sized indexes increase performance by reducing the disk area needed to identify rows.  For these reasons it is a good habit to monitor the size of tablespaces and the indexes rebuilding them when necessary. 

 

Indexes are also often kept in their own tablespaces segregated from the tables they reference.  This allows the DBA to monitor growth separately from the data.  If you have tables that grow consistently, but at a slow steady space, you may want to keep a smaller amount of free space than you would with a tablespace populated by indexes.  Placing indexes into their own tablespace also allows you to target them based upon the object need. 

 

Normally, when I rebuild a tablespaces index I generate a DDL script using SQL*Plus and the following script:

 

select 'alter index '||owner||'.'||index_name||' rebuild;'

from dba_indexes

where tablespace_name = 'INDEX_TABLESPACE';

 

This statement produces a list of DDL commands that rebuild the indexes in place.  Normally, the generated script works exceedingly well. However, in some occasions the target index is larger that the tablespace free space.  When this occurs, you will get the following error:

 

ORA-1652: unable to extend temp segment by 3168 in tablespace

INDEX_TABLESPACE

 

This error occurs because Oracle must build the new index before the old one is removed and the tablespace does not have the free space for a new version and an old version.  The DBA then has two choices:

 

   1. Drop the existing index and recreate it.

   2. Rebuild the index in a different tablespace with adequate free space and rebuilding it again in the original tablespace after the original index version is dropped.

 

The former option of dropping the index may cause applications and users response problems.  It also requires the DBA to create a new DDL index statement.  Not a difficult task, but not one a busy DBA relishes.  For these two reasons I generally choose option 2. 

 

The only drawback to this option is that the index is created twice requiring extra server work.  Having the computer perform a little more work is far superior then me performing some work.  However, I don't like having to re-execute commands because they failed.  For this reason I developed a simple script for rebuilding all tablespaces indexes that eliminates the Ora-1652 error. 

 

It performs the following:

 

  1. Prompts the user for the target tablespace

  2. Determines the tablespace's free space available for the rebuilt index

  3. Determines the tablespace's indexes

  4. Determines the size of each index

  5. Compares the index size to the available free space.  If the index size is less than the tablespace size, the index is rebuilt in the same tablespace. If the index is larger than the available free space the index is first rebuilt in a development tablespace called Reorg_index_area.  The index is then rebuilt in the original tablespace. The Reorg_index_area tablespace is an empty tablespace created by the DBA to temporarily hold rebuilt indexes.

 

  6. Messages displaying the original tablespace free space, the indexes

rebuilt, and the final tablespace free space.

 

The script that rebuilds the indexes is displayed below.  It is an anonymous block procedure that will prompt you for the target tablespace name.

 

set serveroutput on

declare

cursor freespace_cursor is select sum(bytes)/1024/1024 freespace

                           from dba_free_space

                           where tablespace_name =

upper('&&target_tablespace')

                           group by tablespace_name;

  freespace_value     number; 

  q                   varchar2(60);      

begin

  open freespace_cursor;

  fetch freespace_cursor into freespace_value;

  close freespace_cursor;

  dbms_output.put_line ('Tablespace '||'&&target_tablespace'||'

freespace is now '||freespace_value); for z in (select a.owner,

index_name, segment_name, 'Index', index_type, uniqueness, compression,

                 a.tablespace_name, mbytes_used

          from dba_indexes a

            join (select owner, segment_name, sum(bytes)/1024/1024

mbytes_used

                  from dba_extents

            where tablespace_name = upper('&target_tablespace')

            group by owner, segment_name) b on (a.owner = b.owner

                                      and a.index_name = b.segment_name)

    order by mbytes_used)

loop

 if z.mbytes_used < freespace_value then

    execute immediate 'alter index '||z.owner||'.'||z.segment_name||'

rebuild ';  else

    execute immediate 'alter index '||z.owner||'.'||z.segment_name||'

rebuild tablespace reorg_index_area';

    dbms_output.put_line ('Rebuild of index in reorg_index_area

'||z.owner||'.'||z.segment_name||' is complete');

    execute immediate 'alter index '||z.owner||'.'||z.segment_name||'

rebuild tablespace '||z.tablespace_name;

    dbms_output.put_line ('Rebuild of index in original tablespace is

'||z.owner||'.'||z.segment_name||' is complete');

  end if;

  dbms_output.put_line ('Rebuild of index

'||z.owner||'.'||z.segment_name||' is complete');

  open freespace_cursor;

  fetch freespace_cursor into freespace_value;

  close freespace_cursor;

  dbms_output.put_line ('Tablespace '||'&&target_tablespace'||'

freespace is now '||freespace_value); end loop; end; / undefine

target_tablespace

 

The code block prompts the user for the name of the target tablespace, Index_tablespace.  Also notice that the script built several indexes directly into the Index_tablespace tablespace.  Two of the indexes were too large for the free space and were rebuilt into the Reorg_index_area tablespace and again into the Index_tablespace tablespace. 

 

The net effect is rebuilt indexes with no DBA intervention. The only thing the DBA needs to do is launch the script and identify the tablespace.  Everything else is automatic.

 

See these related notes on Oracle index rebuilding:

 

 

Oracle Index rebuilding - Why when how

 

Oracle index rebuilding

 

Important Oracle 10g bug in index rebuilding

 

Oracle myth busters index rebuild disk io wait event analysis

 

Identifying which Oracle Indexes to Rebuild

 

Predict Oracle table index benefit from rebuild reorganization

 

Oracle tuning: Blocksize and index tree structures

 

Oracle index rebuild command

 

Oracle indexes rebuild distinct keys

 

Identifying Which Indexes to Rebuild Table of Contents

 

 

 

 

Oracle Consulting

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 
   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.