- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: How to find out which indexes want to rebuild...
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-10-2007 02:58 AM
тАО05-10-2007 02:58 AM
Is any datadictionary view or dynamic performance view are there to find out which indexes are want to rebuild in the database.
Help apperciated..
Thanks,
Nirmal.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-10-2007 03:20 AM
тАО05-10-2007 03:20 AM
Solutionas far as i know doesn't exist a view like you're looking for but
try this "ir.sql" (in attch) as start point.
I have never used neither mainteined neither created;
so use it in test environment.
(again : this seems to work with Oracle Server 7.3.x to 9.x).
note :
Rebuild the index when:
- deleted entries represent 20% or more of the current entries.
- the index depth is more then 4 levels.
Possible candidate for bitmap index:
- when distinctiveness is more than 99%
hth
regards
pg
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-10-2007 03:45 AM
тАО05-10-2007 03:45 AM
Re: How to find out which indexes want to rebuild in the database
If you can afford the time, your best bet is to rebuild every index in your user tablespace(s). I've scheduled the attached script to run every weekend on my production database server (please note that it's not my script, although I may have modified it a bit). It loops through all the indexes in the target tablespace and generates a dynamic script (rebuild_indexes_dynamic.sql) which does an online rebuild of each one.
If rebuilding every index isn't an option for you, see this article for tips on how to identify which indexes should be rebuilt:
http://www.dba-oracle.com/art_index1.htm
PCS
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-10-2007 08:34 AM
тАО05-10-2007 08:34 AM
Re: How to find out which indexes want to rebuild in the database
Please leave feedback you have left feeback only for 3 out of 31 responses, pleas read the forum guidelines, leaving a feedback is a nice way to say thanks for the people who have been helping you.
Rgds
HGN
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-10-2007 08:14 PM
тАО05-10-2007 08:14 PM
Re: How to find out which indexes want to rebuild in the database
you can use the attached script.
HTH,
Art
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-10-2007 09:24 PM
тАО05-10-2007 09:24 PM
Re: How to find out which indexes want to rebuild in the database
If you are using Locally managed tablespaces at the backend, this operation should be be arising.
In fact, in normal database operations rebuild of indexes should not be really done.
what version of database are you using?
kind regards
yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-11-2007 12:39 AM
тАО05-11-2007 12:39 AM
Re: How to find out which indexes want to rebuild in the database
Thanks 2 all for providing the scripts.
yogeraaj- we are using oracle 9i version database.
Thanks,
Nirmal.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-11-2007 12:45 AM
тАО05-11-2007 12:45 AM
Re: How to find out which indexes want to rebuild in the database
If your tablespaces are Locally managed and the indexes have been created on them, then there is nothing to worry about!
To check your tablespaces, run:
select TABLESPACE_NAME, EXTENT_MANAGEMENT
from dba_tablespaces;
hope this helps too!
kind regards
yogeeraj
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-16-2007 05:16 AM
тАО05-16-2007 05:16 AM
Re: How to find out which indexes want to rebuild in the database
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2913600659112
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО05-16-2007 05:48 AM
тАО05-16-2007 05:48 AM
Re: How to find out which indexes want to rebuild in the database
Rebuilding indexes as scheduled job is just a waste of time and resource.
And Switching to LMT does not automatically fix all the problems. LMT with uniform extent size could avoid Extent level fragments, but still there are some holes in block level which you cannot avoid in most cases.