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

Search Discussions

  • Ryan.gaffuri_at_cox.net at Jan 21, 2004 at 5:24 pm
    www.ixora.com.au

    there is a script in there that will identify unnecessary redundant indexes.

    for the record, that is one of the best oracle websites out there. Lots of great stuff on it.
    From: Branimir Petrovic
    Date: 2004/01/21 Wed AM 10:39:25 EST
    To: Multiple recipients of list ORACLE-L
    Subject: Healty ratio of index segment size vs table segment size?

    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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: 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).
  • Goulet, Dick at Jan 21, 2004 at 6:19 pm
    They appear to be Tango Uniform today!!

    Dick Goulet
    Senior Oracle DBA
    Oracle Certified 8i DBA

    -----Original Message-----
    Sent: Wednesday, January 21, 2004 12:25 PM
    To: Multiple recipients of list ORACLE-L

    www.ixora.com.au

    there is a script in there that will identify unnecessary redundant indexes.

    for the record, that is one of the best oracle websites out there. Lots of great stuff on it.
    From: Branimir Petrovic
    Date: 2004/01/21 Wed AM 10:39:25 EST
    To: Multiple recipients of list ORACLE-L
    Subject: Healty ratio of index segment size vs table segment size?

    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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.net
    --
    Author: 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: Goulet, Dick
    INET: DGoulet_at_vicr.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).
  • DENNIS WILLIAMS at Jan 22, 2004 at 1:49 pm
    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).
    --
    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).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJan 21, '04 at 3:39p
activeJan 22, '04 at 1:49p
posts4
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase