| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Determine if an index needs to be rebuilt... how do I?
Many databases are very heavy on the indexes and checking the depth and
number of deleted leafs on everyone is just not practical in a single job.
I have a PL/SQL jobs that runs for "N" minutes during low activity periods
every night against the indexes. It stores that last object id of the index
last checked so the next time it runs it will pick up where it left off. I
either put the SQL to rebuild the indexes in a table, run it then or write
it out to a file. This can then be run during a maintenance window or while
the db is operation depending on the version of Oracle. Once you have
rebuilt all the suspect indexes you can start target indexes are associated
with high activity tables. These can be identified by activating table
monitoring and using DBA_TAB_MODIFICATIONS. This can all pretty much be
automated.
-----Original Message-----
Sent: Wednesday, March 13, 2002 3:25 PM
To: Multiple recipients of list ORACLE-L
Steve Adams's site http://www.ixora.com.au has scripts which help with this. Also your indexes many only need coalescing.
Ian MacGregor
Stanford Linear Accelerator Center
ian_at_SLAC.Stanford.edu
-----Original Message-----
Sent: Wednesday, March 13, 2002 12:52 PM
To: Multiple recipients of list ORACLE-L
Hi,
I want to automate index rebuilds on some databases I administer. They are all Oracle 8.1.6 and 8.1.7 on Solaris 2.6 thru 2.8. I want to use "analyze index...validate structure" and then look at the index_stats view to determine what shape a given index is in.
My question... for people who use this method, what selection criteria do you use on the index_stats view to make the decision to rebuild... or not to rebuild the particular index???
My aim would be to use such a selection criteria and limit the rebuilds to just a few each night... until at some point they are all done. After that I would run say once a week just to pick on indexes as they become fragmented and skewed etc etc.
Thanks,
Ken
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Post, Ethan
INET: Ethan.Post_at_ps.net
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Mar 13 2002 - 16:25:10 CST
 |
 |