Re: RE: Healty ratio of index segment size vs table segment size?

comments in line... I may need correction from some of you on this.
> -----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)...

the only rule of thumb I have seen is the script on Steve Adams site at www.ixora.com.au that finds unnecessary indexes. The site appears to be down, so you can try google and the cache feature.

>
> Can, better yet - should, sheer size comparison of index versus table
> segments be used as a reliable pointer to problematic table indexing?

No. some people would argue that the size of an index is an indicator of needing a rebuild, but many of the big time tuners say this is not true, so I take their advice.

Disk space is cheap.

>
> If it can, what could be considered as average "healthy ratio" above
> which would be prudent to have a closer look and investigate?

no,no,no... I hate ratios.
>
> 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?

do all monitoring during off peak hours. Can you run it over the weekend? Benchmark it. You can do alot of good monitoring during off peak hours. I run all kinds of high load stuff on the weekends. You may want to run Steven Adams script once a month or so. Do it from a batch job and send yourself an email if something comes up.
>
> Thoughts, pointers, opinions - appreciated.
>
> Branimir
>

The key to a good index screen is database design and understanding the goal of your system. What are you users trying to do most frequently? What is most critical to the system? Design towards that. Can you make a small change to your data model so that you do not really need to add an extra index and what is the impact of it? (pros and cons to everything).

The big question is are your indexes affecting performance of your DML statements? do you foresee any possible problems in the future?

Could someone correct me here. Im willing to bet Im half right.. though not sure which half.

> --
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <ryan.gaffuri_at_cox.net
  INET: ryan.gaffuri_at_cox.net

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).