Validating and rebuilding large and fragmented indexes in 11i

You can have various methods to identify the large or fragmented indexes in 11i or you can just configure the Oracle Alert to receive a mail. This alert is scheduled to check the situation periodically say daily at mid night.

The alert ‘Indexes too large or fragmented‘ is pre-defined alert.
There are three possible ways to rebuild indexes :
i)        Using ALTER INDEX ..REBULD ONLINE command
ii)       Taking an export of database specifying ROWS=NO and then using the import with option INDEXFILE
iii)     Using bde_rebuild.sql
I would prefer the last method. You can download the bde_rebuild.sqlscript from metalink doc id 182699.1. Run this script in 11i instance using apps or system account 

           $SQLPLUS apps/apps @bde_rebuild.sql

 
This script will dynamically analyze and generates bde_rebuild_indexes.sql which rebuilds the required indexes.

Execution of this script have some optional parameters which script will ask when you run it:
—  Owner of table (schema) <optional> : applsys (for example)
—  Table name :
—  Index name or Index Suffix 
—  Threshold between 20 and 80% :          for this parameter default value is 50 which is sufficient for OLTP environment
I would prefer to run the script for required schema (identify required schema from alert) separately for every schema.

The only care to be taken while running this script is : This script blocks DML commands on indexes being analyzed, including SELECT statements.

Execute in Production during a low online user activity period. Blocking time lasts between few Sec to a few minutes, depending on index size.

Share This Post with Your Friends over Social Media!

About the Author Atul Kumar

Leave a Comment:

8 comments
Ramam says October 14, 2008

Hello Saurabh,

Very good post on Indexes. Can you also please let us know how to confirm what are the indexes needs to be rebuild.

Ramam

Reply
akula.venu says October 14, 2008

Hi surabh,

Please provide information on how to identify the large and fragmented indexes and also let us know how to configure oracle alert to get notifications.

Thanks,
Venu

Reply
Saurabh says October 14, 2008

Hi Ramam,

Thanks for you appreciation.
The script is capable to analyze and select the indexes which requires rebuild. These indexes are reported in stats file BDE_REBUILD_REPORT.TXT, which is generated when you run the script. The parameter ‘Threshold’ takes care of this. Threshold represents the Amount of unused space due to deletes. You can enter Threshold value between 20-80, for OLTP env 50 is ok.

Regards,
Saurabh

Reply
Saurabh says October 14, 2008

Hi Venu,

The script will automatically identify the indexes need to be rebuild. You only have to specify the Threshold parameter while running the script. If your env is OLTP then you can rely on default value of Threshold which is 50.

You can configure alert.
navigate to alert manager resp> define
query by name ‘Indexes too large or fragmented’.
check the enable box. Now click the Actions button. select ‘summary message to DBA’, then click action details. Specify your email id in ‘To’ box and save.
Now you can run this request from
alert manager > request > check
or you can schdule this alert. please note you have to configure mailer notification to recieve mails.

Reply
Sanjeev Nanda says July 10, 2009

hi Saurabh,
Very good post on Indexes,can you throw some light on large or fragmented table in 11i .

regards
sanjeev

Reply
Saurabh says July 10, 2009

Hi Sanjeev,

Thanks for your good words.
I ‘ll try to work on your suggestion as soon as possible.

Regards,
Saurabh

Reply
JpalaciosP says February 12, 2013

Hi, Can we use this script to run in R12 instance?
Thanks

Reply
Saurabh says February 14, 2013

I tried using the script on R12 and it worked well.

Thanks,
Saurabh

Reply
Add Your Reply