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. [EMAIL PROTECTED] -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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Healty ratio of index segment size vs table segment size?
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 [EMAIL PROTECTED] Date: 2004/01/21 Wed AM 10:39:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Healty ratio of index segment size vs table segment size?
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 [EMAIL PROTECTED] Date: 2004/01/21 Wed AM 10:39:25 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
