| 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: Healty ratio of index segment size vs table segment size?
Branimir
Beware of simple ratios.
The logic is seductive. It seems likely that an easy way to find unnecessary indexes is to look at a ratio such as you describe. And it shouldn't pose much load on a system to do a quick report on ratio. But what would it mean in practice? Just go around dropping indexes on tables that exceed their quota?
I haven't used the index monitoring feature, and a cautious DBA always makes a small test before widespread deployment, but from what I've been told, the monitoring feature is pretty low overhead.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Wednesday, January 21, 2004 9:39 AM
To: Multiple recipients of list ORACLE-L
Wondering if there is a "rule of thumb", quick'n fast but good enough to be used as an indicator, litmus paper so to speak, of overly indexed table(s)...
Can, better yet - should, sheer size comparison of index versus table segments be used as a reliable pointer to problematic table indexing?
If it can, what could be considered as average "healthy ratio" above which would be prudent to have a closer look and investigate?
Related to the above dilemma, how "expensive" is to monitor index usage, say if script is run against all few hundred indexes on app tables, would the additional load noticeably affect application performance or is it better/safer or may be required to monitor not more than just a few "most suspected" indexes at a time?
Thoughts, pointers, opinions - appreciated.
Branimir
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Branimir Petrovic
INET: BranimirP_at_cpas.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------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).
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------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 Thu Jan 22 2004 - 07:49:25 CST
 |
 |