RE: rebuilding indexes - sure to cause a ruckus
not to mention running 48 batch jobs on a 8CPU box with all of them committing after every record and using the table to generate keys (Cary would love this one) ;) They wanted to find other reasons and he conveniently ignored the real problem. Beautiful... Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta - SQL Optimization 101: 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Denny Koovakattu Sent: Friday, December 12, 2003 3:20 PM To: Multiple recipients of list ORACLE-L If it's from Oracle, I would believe it, i.e., I would believe somebody did actually say that ;) But it does not make it right. Now only if management knew/believed that. Some more from Oracle, - Oracle writes to one log member and then the other. So you need both log members for recovery. Volunteered to help us use _allow_resetlogs_corruption when we had one intact log member. (Took a lot of effort not to tell him to read the concepts manual. Was from a Sev1 problem that happened a few years ago.) - Increasing hit ratio, OS swap size to 3 times the OS memory and improving data proximity in an index (never really understood this one) among other bizarre ones to improve performance. This from an Oracle consultant who was called onsite by Development Management because we claimed the real reason was because the application was committing after every record to avoid locking issues on a table generating sequences, not to mention running 48 batch jobs on a 8CPU box with all of them committing after every record and using the table to generate keys (Cary would love this one) ;) They wanted to find other reasons and he conveniently ignored the real problem. BTW, I personally don't like having a zillion extents for an object (more so when you have multiple DBA Replacement Tools querying dba_extents constantly and showing flashing red lights) and would expect the development team NOT to give me a deer in the headlights look when asked for table sizing info. Response most often heard is Why do you need that. Oracle will be able to take care of it or can't Oracle take care of it or some variation thereof What I really want to say is if you don't have any idea about your data, then please don't write any SQL. That should take care of most performance issues. Barbara Baker wrote: You probably think you're joking. Unfortunately . . . We've been fighting with Oracle for several months about SEVERE performance degradation on an OpenVMS application after we upgraded the database to 8.1.7.4 One of Oracle's recommendations taken directly from our TAR just 2 weeks ago: o Ensure tables and indexes have as few extents as possible. sigh... Barb --- Bobak, Mark [EMAIL PROTECTED] wrote: I think this subject has been done to death. We should talk about less contentious issues such as: - The buffer cache hit ratio, your friend in expert Oracle tuning! - Rebuild your tables regularly to reduce the number of extents and improve performance! - Disk access is at least 10,000x slower than memory, to tune your database, eliminate physical I/O! Anyone else got and good ones? ;-) -Mark -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu 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: Cary Millsap 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: rebuilding indexes - sure to cause a ruckus
You probably think you're joking. Unfortunately . . . We've been fighting with Oracle for several months about SEVERE performance degradation on an OpenVMS application after we upgraded the database to 8.1.7.4 One of Oracle's recommendations taken directly from our TAR just 2 weeks ago: o Ensure tables and indexes have as few extents as possible. sigh... Barb --- Bobak, Mark [EMAIL PROTECTED] wrote: I think this subject has been done to death. We should talk about less contentious issues such as: - The buffer cache hit ratio, your friend in expert Oracle tuning! - Rebuild your tables regularly to reduce the number of extents and improve performance! - Disk access is at least 10,000x slower than memory, to tune your database, eliminate physical I/O! Anyone else got and good ones? ;-) -Mark -Original Message- Sent: Wednesday, December 10, 2003 5:24 PM To: Multiple recipients of list ORACLE-L BTW, does anyone know what a rocket scientist refers to when they say Hey, this is all quite easy, it sure ain't ? ? Cheers ;) Richard Surely the Rocket Scientist version must be Hey, this is all quite easy, it sure ain't index rebuilding very evil grin Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen 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: Bobak, Mark 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). __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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: rebuilding indexes - sure to cause a ruckus
Ouch. Suddenly, it seems not so funny -Original Message- Sent: Friday, December 12, 2003 11:25 AM To: Multiple recipients of list ORACLE-L You probably think you're joking. Unfortunately . . . We've been fighting with Oracle for several months about SEVERE performance degradation on an OpenVMS application after we upgraded the database to 8.1.7.4 One of Oracle's recommendations taken directly from our TAR just 2 weeks ago: o Ensure tables and indexes have as few extents as possible. sigh... Barb --- Bobak, Mark [EMAIL PROTECTED] wrote: I think this subject has been done to death. We should talk about less contentious issues such as: - The buffer cache hit ratio, your friend in expert Oracle tuning! - Rebuild your tables regularly to reduce the number of extents and improve performance! - Disk access is at least 10,000x slower than memory, to tune your database, eliminate physical I/O! Anyone else got and good ones? ;-) -Mark -Original Message- Sent: Wednesday, December 10, 2003 5:24 PM To: Multiple recipients of list ORACLE-L BTW, does anyone know what a rocket scientist refers to when they say Hey, this is all quite easy, it sure ain't ? ? Cheers ;) Richard Surely the Rocket Scientist version must be Hey, this is all quite easy, it sure ain't index rebuilding very evil grin Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen 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: Bobak, Mark 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). __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Barbara Baker 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: Bobak, Mark 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: rebuilding indexes - sure to cause a ruckus
If it's from Oracle, I would believe it, i.e., I would believe somebody did actually say that ;) But it does not make it right. Now only if management knew/believed that. Some more from Oracle, - Oracle writes to one log member and then the other. So you need both log members for recovery. Volunteered to help us use _allow_resetlogs_corruption when we had one intact log member. (Took a lot of effort not to tell him to read the concepts manual. Was from a Sev1 problem that happened a few years ago.) - Increasing hit ratio, OS swap size to 3 times the OS memory and improving data proximity in an index (never really understood this one) among other bizarre ones to improve performance. This from an Oracle consultant who was called onsite by Development Management because we claimed the real reason was because the application was committing after every record to avoid locking issues on a table generating sequences, not to mention running 48 batch jobs on a 8CPU box with all of them committing after every record and using the table to generate keys (Cary would love this one) ;) They wanted to find other reasons and he conveniently ignored the real problem. BTW, I personally don't like having a zillion extents for an object (more so when you have multiple DBA Replacement Tools querying dba_extents constantly and showing flashing red lights) and would expect the development team NOT to give me a deer in the headlights look when asked for table sizing info. Response most often heard is Why do you need that. Oracle will be able to take care of it or can't Oracle take care of it or some variation thereof What I really want to say is if you don't have any idea about your data, then please don't write any SQL. That should take care of most performance issues. Barbara Baker wrote: You probably think you're joking. Unfortunately . . . We've been fighting with Oracle for several months about SEVERE performance degradation on an OpenVMS application after we upgraded the database to 8.1.7.4 One of Oracle's recommendations taken directly from our TAR just 2 weeks ago: o Ensure tables and indexes have as few extents as possible. sigh... Barb --- Bobak, Mark [EMAIL PROTECTED] wrote: I think this subject has been done to death. We should talk about less contentious issues such as: - The buffer cache hit ratio, your friend in expert Oracle tuning! - Rebuild your tables regularly to reduce the number of extents and improve performance! - Disk access is at least 10,000x slower than memory, to tune your database, eliminate physical I/O! Anyone else got and good ones? ;-) -Mark -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu 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: rebuilding indexes - sure to cause a ruckus
Can we, please, change terminology and use the term log file instead of log member. I distinctly remember backup recovery class in NYC when a guy with a heavy accent popped the following question: Can I recover the database if I lose my member? It was the time after lunch while we were all drinking sodas in the lobby. I distinctly remember the feeling of diet coke coming out of my nostrils. It wasn't pleasant. On 12/12/2003 04:19:33 PM, Denny Koovakattu wrote: If it's from Oracle, I would believe it, i.e., I would believe somebody did actually say that ;) But it does not make it right. Now only if management knew/believed that. Some more from Oracle, - Oracle writes to one log member and then the other. So you need both log members for recovery. Volunteered to help us use _allow_resetlogs_corruption when we had one intact log member. (Took a lot of effort not to tell him to read the concepts manual. Was from a Sev1 problem that happened a few years ago.) - Increasing hit ratio, OS swap size to 3 times the OS memory and improving data proximity in an index (never really understood this one) among other bizarre ones to improve performance. This from an Oracle consultant who was called onsite by Development Management because we claimed the real reason was because the application was committing after every record to avoid locking issues on a table generating sequences, not to mention running 48 batch jobs on a 8CPU box with all of them committing after every record and using the table to generate keys (Cary would love this one) ;) They wanted to find other reasons and he conveniently ignored the real problem. BTW, I personally don't like having a zillion extents for an object (more so when you have multiple DBA Replacement Tools querying dba_extents constantly and showing flashing red lights) and would expect the development team NOT to give me a deer in the headlights look when asked for table sizing info. Response most often heard is Why do you need that. Oracle will be able to take care of it or can't Oracle take care of it or some variation thereof What I really want to say is if you don't have any idea about your data, then please don't write any SQL. That should take care of most performance issues. Barbara Baker wrote: You probably think you're joking. Unfortunately . . . We've been fighting with Oracle for several months about SEVERE performance degradation on an OpenVMS application after we upgraded the database to 8.1.7.4 One of Oracle's recommendations taken directly from our TAR just 2 weeks ago: o Ensure tables and indexes have as few extents as possible. sigh... Barb --- Bobak, Mark [EMAIL PROTECTED] wrote: I think this subject has been done to death. We should talk about less contentious issues such as: - The buffer cache hit ratio, your friend in expert Oracle tuning! - Rebuild your tables regularly to reduce the number of extents and improve performance! - Disk access is at least 10,000x slower than memory, to tune your database, eliminate physical I/O! Anyone else got and good ones? ;-) -Mark -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Denny Koovakattu 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). Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: rebuilding indexes - sure to cause a ruckus
Mark, I just met Elvis behind local Wal-Mart parking lot, we discussed the same topics ... funny you mention them. Oh ans we did talk about investing in Enron too (got a hot tip, the stock is going to go up). Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Wednesday, December 10, 2003 5:39 PM To: Multiple recipients of list ORACLE-L I think this subject has been done to death. We should talk about less contentious issues such as: - The buffer cache hit ratio, your friend in expert Oracle tuning! - Rebuild your tables regularly to reduce the number of extents and improve performance! - Disk access is at least 10,000x slower than memory, to tune your database, eliminate physical I/O! Anyone else got and good ones? ;-) -Mark ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: rebuilding indexes - sure to cause a ruckus
Thanks Raj, Unfortunately, in my rush to get the kids to school in time, I stuffed the formatting when my cut 'n' pasting got converted to plain text. Hope you found it all useful. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 10, 2003 2:49 AM Richard's explanation and example from c.d.o.s now has a permanent tinyurl link ... http://tinyurl.com/yflq if anyone is interested ... this might be better for bookmarks. Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 08, 2003 4:29 PM To: Multiple recipients of list ORACLE-L Hi Yong, Saying there are a few errors is being a little kind to Don's Inside Oracle Indexing article. [ rest snipped ] ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: Richard Foote 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: rebuilding indexes - sure to cause a ruckus
Hi Yong, One thing I should have mentioned when I posted my epic is that it not only attempts to correct the numerous technical errors in the article but also attempts to answer the various questions the article raises but totally fails to address. What I find most astonishing about the article is that the author confesses at the conclusion he has no idea when and why an index rebuild is beneficial. And as the author doesn't know, then surely it must all be so difficult, a scientific-less phenomenon. If I can convince anyone who makes it through my email that this isn't rocket science, then it's been worth the bandwidth. BTW, does anyone know what a rocket scientist refers to when they say Hey, this is all quite easy, it sure ain't ? ? Cheers ;) Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 10, 2003 5:44 AM Thanks, Richard. I'll read your long message more carefully later. I like your statement that rebuilding an index or not is not rocket science. One needs to measure the performance before and after the rebuild and make a conclusion himself. Many times we discuss performance issues and get very technical and sophisticated, without showing experimental results! Having been a science researcher before, I'd like to emphasize that facts speak louder than theories. There may be 10,000 24x7 databases in the world that don't easily allow even testing an index rebuild. But there may be 100 times more production databases in the world that are not 24x7. The individual DBA needs to do his control study and conclude, using experts' opinions as reference. Yong Huang __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang 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: Richard Foote 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: rebuilding indexes - sure to cause a ruckus
Richard: I think that is the simple way of questioning other person's capacity. Remember this statment (borrowed from some one !!) If you are telling something is simple, you are questioning the other person's intelligence !!' KG -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan 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: rebuilding indexes - sure to cause a ruckus
Richard, I found it immensely useful, that's why I created the tinyurl and went to c.d.o.s and read the whole thread, from first to last post. (man those people need to learn to weed out old comments in the replies). This is what I love about this forum, it comes with huge amount of knowledge, eager members who are ready to educate and learn, add a dash of rhetoric and a right amount of fun. In other words a perfect combination. Thanks for your explanation. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Wednesday, December 10, 2003 4:15 AM To: Multiple recipients of list ORACLE-L Thanks Raj, Unfortunately, in my rush to get the kids to school in time, I stuffed the formatting when my cut 'n' pasting got converted to plain text. Hope you found it all useful. Cheers Richard ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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).
OT Re: Re: rebuilding indexes - sure to cause a ruckus
Hi Richard, I think, there are 2 candidates for an answer. 1life (nothing is more difficult) 2...love (ever tried to read your madam's thoughts?) ;-) Corrections welcome (as always). Cheers, Guido [EMAIL PROTECTED] 10.12.2003 11.39 Uhr (snip) If I can convince anyone who makes it through my email that this isn't rocket science, then it's been worth the bandwidth. BTW, does anyone know what a rocket scientist refers to when they say Hey, this is all quite easy, it sure ain't ? ? Cheers ;) Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guido Konsolke 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: rebuilding indexes - sure to cause a ruckus
Terse ? You haven't heard me terse until youhear me trying to get the kids to sleep at night. Don got it easy ;) - Original Message - From: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L Sent: Wednesday, December 10, 2003 8:14 AM Subject: Re: rebuilding indexes - sure to cause a ruckus And in case you miss it in Richard's terse message, one of the big reasons that it is not 'rocket science' is that you can perform operations that modify the index(es), and perform block dumps of the index as you go. You can see exactly what Oracle is doing with the index. Jared Yong Huang [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/09/2003 11:44 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: rebuilding indexes - sure to cause a ruckusThanks, Richard. I'll read your long message more carefully later. I like yourstatement that rebuilding an index or not is not rocket science. One needs tomeasure the performance before and after the rebuild and make a conclusionhimself. Many times we discuss performance issues and get very technical andsophisticated, without showing experimental results! Having been a scienceresearcher before, I'd like to emphasize that facts speak louder than theories.There may be 10,000 24x7 databases in the world that don't easily allow eventesting an index rebuild. But there may be 100 times more production databasesin the world that are not 24x7. The individual DBA needs to do his controlstudy and conclude, using experts' opinions as reference.Yong Huang__Do you Yahoo!?New Yahoo! Photos - easier uploading and sharing.http://photos.yahoo.com/-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Yong HuangINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: rebuilding indexes - sure to cause a ruckus
Hi KG, O, you've got me thinking here !! I'm not too sure that I've really questioned anyone's intelligence. I've always measured someone's intelligence by: 1. How quickly the can learn and absorb new information 2. How much they know and appreciate the work of David Bowie A quick check of the Oxford Dictionary describes the word intelligence as mental ability to learn and understand things (although interestingly, there's no mention of DB). I guess the issue I have is that if intelligent people are told and feed incorrect information (and Don's article has it's share of incorrect information) then fundamentally it's one's knowledge that I begin questioning. Unfortunately, I believe there are a lot of intelligent people in the Oracle community who have a questionable knowledge of Oracle (or aspects of Oracle) as a direct result of the poor quality of information that people absorb (be it books, training courses, web-articles, etc..). And undoubtedly many of these people that write substandard materials in turn have picked up flawed knowledge due to the quality of their readings, education and lack of proper research. As I mentioned Knowledge is the key that unlocks the door of doubt. If you have no doubts about something, it by definition becomes simple !! Unfortunately, if you're presented with the wrong information, you get access to the wrong key ;) Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 10, 2003 10:09 PM Richard: I think that is the simple way of questioning other person's capacity. Remember this statment (borrowed from some one !!) If you are telling something is simple, you are questioning the other person's intelligence !!' KG -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: K Gopalakrishnan 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: Richard Foote 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: rebuilding indexes - sure to cause a ruckus
Richard Foote scribbled on the wall in glitter crayon: BTW, does anyone know what a rocket scientist refers to when they say Hey, this is all quite easy, it sure ain't ? ? the only two i know use theoretical physics.;-) -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] Nothing can move faster than light. - Albert Einstein -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William 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: rebuilding indexes - sure to cause a ruckus
BTW, does anyone know what a rocket scientist refers to when they say Hey, this is all quite easy, it sure ain't ? ? Cheers ;) Richard Surely the Rocket Scientist version must be Hey, this is all quite easy, it sure ain't index rebuilding very evil grin Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen 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: rebuilding indexes - sure to cause a ruckus
LOL, This made me think of the Simpson's Halloween episode where Monty Burns says, 'Smithers, this isn't rocket science, its brain surgery'. - Alan Davey Senior Analyst/Project Leader Oracle 9i OCA; 3/4 OCP w) 973.267.5990 x458 w) 212.295.3458 -Original Message- Sent: Wednesday, December 10, 2003 5:24 PM To: Multiple recipients of list ORACLE-L BTW, does anyone know what a rocket scientist refers to when they say Hey, this is all quite easy, it sure ain't ? ? Cheers ;) Richard Surely the Rocket Scientist version must be Hey, this is all quite easy, it sure ain't index rebuilding very evil grin Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen 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). This information in this e-mail is intended solely for the addressee and may contain information which is confidential or privileged. Access to this e-mail by anyone else is unauthorized. If you are not the intended recipient, or believe that you have received this communication in error, please do not print, copy, retransmit, disseminate, or otherwise use the information. Also, please notify the sender that you have received this e-mail in error, and delete the copy you received. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Davey, Alan 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: rebuilding indexes - sure to cause a ruckus
I think this subject has been done to death. We should talk about less contentious issues such as: - The buffer cache hit ratio, your friend in expert Oracle tuning! - Rebuild your tables regularly to reduce the number of extents and improve performance! - Disk access is at least 10,000x slower than memory, to tune your database, eliminate physical I/O! Anyone else got and good ones? ;-) -Mark -Original Message- Sent: Wednesday, December 10, 2003 5:24 PM To: Multiple recipients of list ORACLE-L BTW, does anyone know what a rocket scientist refers to when they say Hey, this is all quite easy, it sure ain't ? ? Cheers ;) Richard Surely the Rocket Scientist version must be Hey, this is all quite easy, it sure ain't index rebuilding very evil grin Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen 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: Bobak, Mark 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: rebuilding indexes - sure to cause a ruckus
How about: Keep re-analyzing your tables and indexes. Run gather_statistics (or whatever) all the time. Bobak, Mark wrote: I think this subject has been done to death. We should talk about less contentious issues such as: - The buffer cache hit ratio, your friend in expert Oracle tuning! - Rebuild your tables regularly to reduce the number of extents and improve performance! - Disk access is at least 10,000x slower than memory, to tune your database, eliminate physical I/O! Anyone else got and good ones? ;-) -Mark -Original Message- Sent: Wednesday, December 10, 2003 5:24 PM To: Multiple recipients of list ORACLE-L BTW, does anyone know what a rocket scientist refers to when they say Hey, this is all quite easy, it sure ain't ? ? Cheers ;) Richard Surely the Rocket Scientist version must be Hey, this is all quite easy, it sure ain't index rebuilding very evil grin Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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: rebuilding indexes - sure to cause a ruckus
Hi Tanel, I recommend a strong cup of coffee and a small nap 1/2 way through ;) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 7:44 AM Ouch, I gotta take a day off to read this one ;) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: rebuilding indexes - sure to cause a ruckus
Hi Paul, The long one includes a discussion on why you should generally coalesce rather than rebuild indexes ;) Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 8:44 AM somewhat on the longish side??? I'd hate to see a long article! ;-) --- Richard Foote [EMAIL PROTECTED] wrote: Hi Yong, Saying there are a few errors is being a little kind to Don's Inside Oracle Indexing article. In part, these are some of the issues I raised directly with Don in a number of emails (warning somewhat on the longish side ;): __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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: Richard Foote 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: rebuilding indexes - sure to cause a ruckus
Hi Steve, I agree completely, but the question is would you rebuild it afterwards ? Cheers ;-) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 9:29 AM I think it needs an index. ;-) -Original Message- Paul Baumgartel Sent: Monday, December 08, 2003 3:44 PM To: Multiple recipients of list ORACLE-L somewhat on the longish side??? I'd hate to see a long article! ;-) --- Richard Foote [EMAIL PROTECTED] wrote: Hi Yong, Saying there are a few errors is being a little kind to Don's Inside Oracle Indexing article. In part, these are some of the issues I raised directly with Don in a number of emails (warning somewhat on the longish side ;): -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Orr, Steve 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: Richard Foote 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: rebuilding indexes - sure to cause a ruckus
This message's a keeper! Thanks! :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Monday, December 08, 2003 3:29 PM To: Multiple recipients of list ORACLE-L Hi Yong, Saying there are a few errors is being a little kind to Don's Inside Oracle Indexing article. [truncate drop storage] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: rebuilding indexes - sure to cause a ruckus
Richard's explanation and example from c.d.o.s now has a permanent tinyurl link ... http://tinyurl.com/yflq if anyone is interested ... this might be better for bookmarks. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Monday, December 08, 2003 4:29 PM To: Multiple recipients of list ORACLE-L Hi Yong, Saying there are a few errors is being a little kind to Don's Inside Oracle Indexing article. [ rest snipped ] ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **5 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: rebuilding indexes - sure to cause a ruckus
What sort of Oak Table member are you, Richard? Any Oak Table member worth their weight in toilet paper would certainly execute DBMS_POWERNAP part way through, but the strong cup of coffee definitely needs a large shot of whiskey substituted for it! :) Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Richard Foote Sent: Tuesday, December 09, 2003 7:35 PM To: Multiple recipients of list ORACLE-L Hi Tanel, I recommend a strong cup of coffee and a small nap 1/2 way through ;) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 09, 2003 7:44 AM Ouch, I gotta take a day off to read this one ;) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: Pete Sharman 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: rebuilding indexes - sure to cause a ruckus
Thanks, Richard. I'll read your long message more carefully later. I like your statement that rebuilding an index or not is not rocket science. One needs to measure the performance before and after the rebuild and make a conclusion himself. Many times we discuss performance issues and get very technical and sophisticated, without showing experimental results! Having been a science researcher before, I'd like to emphasize that facts speak louder than theories. There may be 10,000 24x7 databases in the world that don't easily allow even testing an index rebuild. But there may be 100 times more production databases in the world that are not 24x7. The individual DBA needs to do his control study and conclude, using experts' opinions as reference. Yong Huang __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang 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: rebuilding indexes - sure to cause a ruckus
And in case you miss it in Richard's terse message, one of the big reasons that it is not 'rocket science' is that you can perform operations that modify the index(es), and perform block dumps of the index as you go. You can see exactly what Oracle is doing with the index. Jared Yong Huang [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/09/2003 11:44 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: rebuilding indexes - sure to cause a ruckus Thanks, Richard. I'll read your long message more carefully later. I like your statement that rebuilding an index or not is not rocket science. One needs to measure the performance before and after the rebuild and make a conclusion himself. Many times we discuss performance issues and get very technical and sophisticated, without showing experimental results! Having been a science researcher before, I'd like to emphasize that facts speak louder than theories. There may be 10,000 24x7 databases in the world that don't easily allow even testing an index rebuild. But there may be 100 times more production databases in the world that are not 24x7. The individual DBA needs to do his control study and conclude, using experts' opinions as reference. Yong Huang __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang 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: rebuilding indexes - sure to cause a ruckus
Science is the belief in the ignorance of experts. --Richard Feynman Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Yong Huang Sent: Tuesday, December 09, 2003 1:45 PM To: Multiple recipients of list ORACLE-L Thanks, Richard. I'll read your long message more carefully later. I like your statement that rebuilding an index or not is not rocket science. One needs to measure the performance before and after the rebuild and make a conclusion himself. Many times we discuss performance issues and get very technical and sophisticated, without showing experimental results! Having been a science researcher before, I'd like to emphasize that facts speak louder than theories. There may be 10,000 24x7 databases in the world that don't easily allow even testing an index rebuild. But there may be 100 times more production databases in the world that are not 24x7. The individual DBA needs to do his control study and conclude, using experts' opinions as reference. Yong Huang __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang 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: Cary Millsap 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: rebuilding indexes - sure to cause a ruckus
Shh!! Nonsense! It's all black magic and conjecture! How else are we going to be highly paid Oracle consultants, if everyone knows all this stuff is provable and demonstrable?? ;-) -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Tuesday, December 09, 2003 5:15 PMTo: Multiple recipients of list ORACLE-LSubject: Re: rebuilding indexes - sure to cause a ruckusAnd in case you miss it in Richard's terse message, one of the big reasons that it is not 'rocket science' is that you can perform operations that modify the index(es), and perform block dumps of the index as you go. You can see exactly what Oracle is doing with the index. Jared Yong Huang [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/09/2003 11:44 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: rebuilding indexes - sure to cause a ruckusThanks, Richard. I'll read your long message more carefully later. I like yourstatement that rebuilding an index or not is not rocket science. One needs tomeasure the performance before and after the rebuild and make a conclusionhimself. Many times we discuss performance issues and get very technical andsophisticated, without showing experimental results! Having been a scienceresearcher before, I'd like to emphasize that facts speak louder than theories.There may be 10,000 24x7 databases in the world that don't easily allow eventesting an index rebuild. But there may be 100 times more production databasesin the world that are not 24x7. The individual DBA needs to do his controlstudy and conclude, using experts' opinions as reference.Yong Huang__Do you Yahoo!?New Yahoo! Photos - easier uploading and sharing.http://photos.yahoo.com/-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: Yong HuangINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
Re: rebuilding indexes - sure to cause a ruckus
Hi! Yes Yong I agree with you, that rebuilding may be beneficial in some cases, especially some tables/indexes become either logically or physically read only (btw, I was not speaking about coalescing in my post, it's a different story anyway). But the point I wanted to make is that single query's speed (LIO amount) right after rebuilding index doesn't usually give us enough information to determine whether overall system speed will go better in long term. When you have rebuilt an index, it becomes more compact, causing more recursive operations for block splits and perhaps index height changes in the future. Also, in heavily loaded 24x7 environments with no real low-peak time, the additional CPU, IO and brief exclusive lock usage may cause more harm than it gives benefit. And often this benefit is only short term in regular OLTP systems. But in some cases, as when you've deleted a number of rows from your table or done a lot of updates and you never expect these keys to be back in the index, a rebuild can be justified. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 05, 2003 10:29 PM Tanel, I think you're saying a query almost always runs faster right after the index rebuild and there's no point in finding the criterion whether to rebuild an index. (What is 42?) Some time ago I posted a message somewhere else showing a case where rebuilding or coalescing an index may be benefitial. A data warehouse is found to have some data errors. Deletes and updates are done. Then the database goes to mostly read-only again, and will last for a month or quarter. Then shrinking frequently used B*Tree indexes is a good idea. Now I'd like to add one more criterion as a result of reading Jonathan Lewis' dbazine article and email with him (errors are mine): the index is full scanned, or if range scanned or unique scanned, the index selectivity has to be fairly low (but not too low for the index to be ignored by CBO). In a typical working environment, a data warehouse does have plenty of relatively quiet period. I worked on a monthly data load project at an insurance company. I remember we rebuilt a partitioned IOT (one partition at a time) and fast full index scan (certain partitions) did run faster. There're some errors in Don Burleson's dbazine article (e.g. pct_used in dba_indexes) and Mike Hordila's Oramag article (structurally unbalanced index). But one thing alluded to in there is important: study Oracle performance problems as scientific research. You said setting _wait_for_sync to false improves performance. That's a fact. We can only explain and analyze it but not deny it. Similarly, when Mike says queries run 10 to 50% faster after index rebuild, we can't deny unless we find his measurement is wrong. Wouldn't it be nice if Oracle researchers write articles with sections like Abstract - Experimental - Results - Discussion in that order? Yong Huang Tanel Poder wrote: There's no point of arguing about whether a query ran faster right after you rebuilt your index. Nor there is no point in finding some ultimate algorithm for finding the point of index rebuilding, we all know the answer - it's 42. Instead, a long stress test has to be done, e.g. running 10 millions of continous transactions and queries (simulating real life). Do one 10M without rebuilding indexes in the meantime, measure total execution time, IO amount, CPU usage, segment sizes etc. Then restore your database back to starting point and do the same test again with regular index rebuilds during the operations (online or taking users offline, depending on environment type). And then measure the same statistics, especially total execution time. Note, that statistics and time also for rebuilding indexes should be accounted in totals, because in real life they don't just disappear somewhere as in some simple-minded tests. Tanel. __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang 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: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web
Re: rebuilding indexes - sure to cause a ruckus
Ouch, I gotta take a day off to read this one ;) Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, December 08, 2003 11:29 PM Hi Yong, Saying there are a few errors is being a little kind to Don's Inside Oracle Indexing article. In part, these are some of the issues I raised directly with Don in a number of emails (warning somewhat on the longish side ;): a.. There are no such things as star indexes. Star joins, yes, star transformations yes, but not star indexes ? b.. I still disagree with your description of b-tree indexes being complex and difficult to understand, but then again this could just be my personal perception (check out http://groups.google.com/groups?q=g:thl3498916429ddq=hl=enlr=ie=UTF-8se lm=ant%259.39604%24jM5.100537%40newsfeeds.bigpond.comrnum=47 where I have a sample demo on how to investigate the workings of b-tree indexes.) However, by understanding them and a how they function, the question of whether or not they need rebuilding no longer needs to be debated. It becomes easily apparent under what conditions indexes could benefit from a rebuild. I'll expand on this later but I would suggest those that debate, those that really don't know when a rebuild is justified and just rebuild in the hope it might do some good are those that really don't understand how indexes function. Knowledge is the key that unlocks the door of doubt and those without the key fumble aimlessly and prod around in hope... c.. Your subsequent quote There is enough anecdotal evidence that index rebuilding has helped some systems perform better, and I also have no doubt that there is no scientific basis for the claim is a nonsense. Of course one explain in scientific terms such performance improvements, I can only suggest that you unfortunately can't. Oracle is not some magic piece of software and it doesn't run on some magical pieces of hardware. Any suggestions to the contrary are not helpful to anyone. d.. I still disagree with the double the block size, halving the logical reads must be a good thing argument. It's a path that could lead to a very disappointing conclusion (read cliff edge). Indexes prefer large block sizes true but if the underlining storage file-system is not tuned to read (or write) these larger block sizes efficiently, then the whole thing is counter productive. You've been warned ... e.. Your description of PCTUSED is still wrong. There is no PCTUSED for indexes so it really shouldn't be misleading to confuse a non-existing index attribute with the amount of used space as documented in INDEX_STATS... f.. Including in your criteria for rebuilding an index btree_space being greater than a block is redundant when listed with the other criteria. It is fundamentally impossible for an index with 4 levels or more to consist of a single block, so why mention it. It just adds confusion and is silly. The DBA who swears by this criteria (which I noticed has changed in this draft ;), how do they make such a claim? It's one thing to swear, it's quite another to prove. Your table that lists average rows and blocks per different index levels shows that those indexes with a leaf row length of 500,000,000 and with 100,000 blocks require 4 levels. How does rebuilding such indexes with no subsequent change in index level improve performance ? I mean, large indexes need more levels right, so rebuilding them all the time and keeping the levels unchanged only to rebuild them again because they're still 4 or more levels seems like a pointless, never-ending exercise in futility. To rebuild an index that actually results in a reduction in it's level generally requires a drastic reduction in it's data volume due to the orders of sizing magnitude that a new level represents. More on this and the other so-called rebuild criteria later but the current level of an index is not a criteria for a rebuild. A level 3 index could conceivably be rebuilt to just a level 1 (if there were heaps and heaps of deletions) and a level 5 index could be rebuilt to stay at level 5. Which index has benefited . g.. Criteria for a rebuild: or the total length of deleted is 1 block makes no sense whatsoever. Nearly all indexes would have a total length of deleted than 1 block meaning nearly all indexes need rebuilding. I don't think so ... h.. Your discussion on the clustering factor affecting the likelihood of requiring an index rebuild is still flawed, however interestingly, you've now given an example on why this is the case. However, you've still come to the wrong conclusion !! Firstly, you're incorrect in your example to say that a 1,000,000 row table with a clustering factor of 1,000,000 has it's rows in the same order as it's index although I guess this could be a typo. Regardless, if you delete all last_name beginning with a K, you are going to delete consecutive leaf nodes
Re: rebuilding indexes - sure to cause a ruckus
Hi Yong, Saying there are a few errors is being a little kind to Don's Inside Oracle Indexing article. In part, these are some of the issues I raised directly with Don in a number of emails (warning somewhat on the longish side ;): a.. There are no such things as star indexes. Star joins, yes, star transformations yes, but not star indexes ? b.. I still disagree with your description of b-tree indexes being complex and difficult to understand, but then again this could just be my personal perception (check out http://groups.google.com/groups?q=g:thl3498916429ddq=hl=enlr=ie=UTF-8se lm=ant%259.39604%24jM5.100537%40newsfeeds.bigpond.comrnum=47 where I have a sample demo on how to investigate the workings of b-tree indexes.) However, by understanding them and a how they function, the question of whether or not they need rebuilding no longer needs to be debated. It becomes easily apparent under what conditions indexes could benefit from a rebuild. I'll expand on this later but I would suggest those that debate, those that really don't know when a rebuild is justified and just rebuild in the hope it might do some good are those that really don't understand how indexes function. Knowledge is the key that unlocks the door of doubt and those without the key fumble aimlessly and prod around in hope... c.. Your subsequent quote There is enough anecdotal evidence that index rebuilding has helped some systems perform better, and I also have no doubt that there is no scientific basis for the claim is a nonsense. Of course one explain in scientific terms such performance improvements, I can only suggest that you unfortunately can't. Oracle is not some magic piece of software and it doesn't run on some magical pieces of hardware. Any suggestions to the contrary are not helpful to anyone. d.. I still disagree with the double the block size, halving the logical reads must be a good thing argument. It's a path that could lead to a very disappointing conclusion (read cliff edge). Indexes prefer large block sizes true but if the underlining storage file-system is not tuned to read (or write) these larger block sizes efficiently, then the whole thing is counter productive. You've been warned ... e.. Your description of PCTUSED is still wrong. There is no PCTUSED for indexes so it really shouldn't be misleading to confuse a non-existing index attribute with the amount of used space as documented in INDEX_STATS... f.. Including in your criteria for rebuilding an index btree_space being greater than a block is redundant when listed with the other criteria. It is fundamentally impossible for an index with 4 levels or more to consist of a single block, so why mention it. It just adds confusion and is silly. The DBA who swears by this criteria (which I noticed has changed in this draft ;), how do they make such a claim? It's one thing to swear, it's quite another to prove. Your table that lists average rows and blocks per different index levels shows that those indexes with a leaf row length of 500,000,000 and with 100,000 blocks require 4 levels. How does rebuilding such indexes with no subsequent change in index level improve performance ? I mean, large indexes need more levels right, so rebuilding them all the time and keeping the levels unchanged only to rebuild them again because they're still 4 or more levels seems like a pointless, never-ending exercise in futility. To rebuild an index that actually results in a reduction in it's level generally requires a drastic reduction in it's data volume due to the orders of sizing magnitude that a new level represents. More on this and the other so-called rebuild criteria later but the current level of an index is not a criteria for a rebuild. A level 3 index could conceivably be rebuilt to just a level 1 (if there were heaps and heaps of deletions) and a level 5 index could be rebuilt to stay at level 5. Which index has benefited . g.. Criteria for a rebuild: or the total length of deleted is 1 block makes no sense whatsoever. Nearly all indexes would have a total length of deleted than 1 block meaning nearly all indexes need rebuilding. I don't think so ... h.. Your discussion on the clustering factor affecting the likelihood of requiring an index rebuild is still flawed, however interestingly, you've now given an example on why this is the case. However, you've still come to the wrong conclusion !! Firstly, you're incorrect in your example to say that a 1,000,000 row table with a clustering factor of 1,000,000 has it's rows in the same order as it's index although I guess this could be a typo. Regardless, if you delete all last_name beginning with a K, you are going to delete consecutive leaf nodes regardless of the clustering factor. So what difference does it make to the index. None. To the table, yes, you either delete rows from all differing blocks or rows from a small number of blocks but to the index, it makes no difference, hence your claim makes no
Re: rebuilding indexes - sure to cause a ruckus
Tanel Poder wrote: Ouch, I gotta take a day off to read this one ;) http://www.netmeister.org/news/learn2quote.html It's all about optimization... -- Vladimir Begun The statements and opinions expressed here are my own and do not necessarily represent those of Oracle Corporation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Vladimir Begun 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: rebuilding indexes - sure to cause a ruckus
somewhat on the longish side??? I'd hate to see a long article! ;-) --- Richard Foote [EMAIL PROTECTED] wrote: Hi Yong, Saying there are a few errors is being a little kind to Don's Inside Oracle Indexing article. In part, these are some of the issues I raised directly with Don in a number of emails (warning somewhat on the longish side ;): __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Paul Baumgartel 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: rebuilding indexes - sure to cause a ruckus
I think it needs an index. ;-) -Original Message- Paul Baumgartel Sent: Monday, December 08, 2003 3:44 PM To: Multiple recipients of list ORACLE-L somewhat on the longish side??? I'd hate to see a long article! ;-) --- Richard Foote [EMAIL PROTECTED] wrote: Hi Yong, Saying there are a few errors is being a little kind to Don's Inside Oracle Indexing article. In part, these are some of the issues I raised directly with Don in a number of emails (warning somewhat on the longish side ;): -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Orr, Steve 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: rebuilding indexes - sure to cause a ruckus
Tanel, I think you're saying a query almost always runs faster right after the index rebuild and there's no point in finding the criterion whether to rebuild an index. (What is 42?) Some time ago I posted a message somewhere else showing a case where rebuilding or coalescing an index may be benefitial. A data warehouse is found to have some data errors. Deletes and updates are done. Then the database goes to mostly read-only again, and will last for a month or quarter. Then shrinking frequently used B*Tree indexes is a good idea. Now I'd like to add one more criterion as a result of reading Jonathan Lewis' dbazine article and email with him (errors are mine): the index is full scanned, or if range scanned or unique scanned, the index selectivity has to be fairly low (but not too low for the index to be ignored by CBO). In a typical working environment, a data warehouse does have plenty of relatively quiet period. I worked on a monthly data load project at an insurance company. I remember we rebuilt a partitioned IOT (one partition at a time) and fast full index scan (certain partitions) did run faster. There're some errors in Don Burleson's dbazine article (e.g. pct_used in dba_indexes) and Mike Hordila's Oramag article (structurally unbalanced index). But one thing alluded to in there is important: study Oracle performance problems as scientific research. You said setting _wait_for_sync to false improves performance. That's a fact. We can only explain and analyze it but not deny it. Similarly, when Mike says queries run 10 to 50% faster after index rebuild, we can't deny unless we find his measurement is wrong. Wouldn't it be nice if Oracle researchers write articles with sections like Abstract - Experimental - Results - Discussion in that order? Yong Huang Tanel Poder wrote: There's no point of arguing about whether a query ran faster right after you rebuilt your index. Nor there is no point in finding some ultimate algorithm for finding the point of index rebuilding, we all know the answer - it's 42. Instead, a long stress test has to be done, e.g. running 10 millions of continous transactions and queries (simulating real life). Do one 10M without rebuilding indexes in the meantime, measure total execution time, IO amount, CPU usage, segment sizes etc. Then restore your database back to starting point and do the same test again with regular index rebuilds during the operations (online or taking users offline, depending on environment type). And then measure the same statistics, especially total execution time. Note, that statistics and time also for rebuilding indexes should be accounted in totals, because in real life they don't just disappear somewhere as in some simple-minded tests. Tanel. __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang 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: rebuilding indexes - sure to cause a ruckus
Wow. We've got a lot of indexes to rebuild... :) Seems like those criteria have been beaten up on this list before. Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Thursday, December 04, 2003 1:55 PM To: Multiple recipients of list ORACLE-L http://www.dbazine.com/burleson18.shtml -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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: rebuilding indexes - sure to cause a ruckus
There's no point of arguing about whether a query ran faster right after you rebuilt your index. Nor there is no point in finding some ultimate algorithm for finding the point of index rebuilding, we all know the answer - it's 42. Instead, a long stress test has to be done, e.g. running 10 millions of continous transactions and queries (simulating real life). Do one 10M without rebuilding indexes in the meantime, measure total execution time, IO amount, CPU usage, segment sizes etc. Then restore your database back to starting point and do the same test again with regular index rebuilds during the operations (online or taking users offline, depending on environment type). And then measure the same statistics, especially total execution time. Note, that statistics and time also for rebuilding indexes should be accounted in totals, because in real life they don't just disappear somewhere as in some simple-minded tests. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Thursday, December 04, 2003 9:54 PM http://www.dbazine.com/burleson18.shtml -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: rebuilding indexes - sure to cause a ruckus
Thanks, Added this one to my bookmarks along with http://www.oracle.com/oramag/webcolumns/2001/index.html?auto_index.html and http://www.dba-oracle.com/art_index1.htm Sten ;) -Original Message- [EMAIL PROTECTED] Sent: Thursday, December 04, 2003 11:55 AM To: Multiple recipients of list ORACLE-L http://www.dbazine.com/burleson18.shtml -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rognes, Sten 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: rebuilding indexes - sure to cause a ruckus
Comments in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 04, 2003 9:04 PM Instead, a long stress test has to be done, e.g. running 10 millions of continous transactions and queries (simulating real life). No ! No ! No ! No ! real life is what happens outside Oracle databases. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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 Rebuilding Indexes in Oracle Apps -- an update
Jared,
I don't see how index skip scans could benefit more
from a rebuild than from coalesce (providing the index height remains the same).
Skip scan doesn't scan the whole index like FFS does, it just does several scans
for each value set in beginning of concatenated index (+some more
mechanisms).
Maybe I'm missing something here, what did you have
in mind?
Tanel.
If the index is
based simply on the unique key, and for some reason you are
using index_ffs on it, then rebuilding will
cut down the number of scanned blocks. It would be interesting to see how skip scans are affected by this as
well. Jared
Hemant K Chitale
[EMAIL PROTECTED] Sent by: [EMAIL PROTECTED]
11/01/2003 12:34 AM
Please respond to ORACLE-L
To:
Multiple recipients of list ORACLE-L
[EMAIL PROTECTED] cc:
Subject:RE: re Rebuilding Indexes in
Oracle Apps -- an updateRichard et al,{for those who've been
following the thread on Rebuilding Indexes ...}I've just been reading
the AskTom thread on rebuilding indexesat
http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:6601312252730and
picked on the important line"Coalesce... reclaim the free space from
mostly empty index leaf blocks that will not be reused otherwise due to
your increasing sequence. "Richard has also pointed COALESCE as a
better option.COALESCE would be a better option than REBUILD for Indexes
onmonotonically increasing sequences where older values are purged
periodically.Hemant K ChitaleOracle 9i Database Administrator
Certified ProfessionalMy personal web site is :
http://hkchital.tripod.com-- Please see the official
ORACLE-L FAQ: http://www.orafaq.net-- Author: Hemant K
ChitaleINET: [EMAIL PROTECTED]Fat City Network
Services -- 858-538-5051 http://www.fatcity.comSan Diego,
California-- Mailing list and web hosting
services-To
REMOVE yourself from this mailing list, send an E-Mail messageto:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing
list you want to be removed from). You mayalso send the HELP command
for other information (like
subscribing).
Re: re Rebuilding Indexes in Oracle Apps -- an update
Now that I think about it, you're probably right.
I'll test it anyway, I like to see numbers. :)
Jared
Tanel Poder [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
11/04/2003 10:04 AM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: re Rebuilding Indexes in Oracle Apps -- an update
Jared,
I don't see how index skip scans could benefit more from a rebuild than from coalesce (providing the index height remains the same). Skip scan doesn't scan the whole index like FFS does, it just does several scans for each value set in beginning of concatenated index (+some more mechanisms).
Maybe I'm missing something here, what did you have in mind?
Tanel.
If the index is based simply on the unique key, and for some reason you are
using index_ffs on it, then rebuilding will cut down the number of scanned blocks.
It would be interesting to see how skip scans are affected by this as well.
Jared
Hemant K Chitale [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
11/01/2003 12:34 AM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: re Rebuilding Indexes in Oracle Apps -- an update
Richard et al,
{for those who've been following the thread on Rebuilding Indexes ...}
I've just been reading the AskTom thread on rebuilding indexes
at
http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:6601312252730
and picked on the important line
Coalesce... reclaim the free space from mostly empty index leaf blocks
that will not be reused otherwise due to your increasing sequence.
Richard has also pointed COALESCE as a better option.
COALESCE would be a better option than REBUILD for Indexes on
monotonically increasing sequences where older values are purged periodically.
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is : http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
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 Rebuilding Indexes in Oracle Apps -- an update
COALESCE would be a better option than REBUILD for Indexes on
monotonically increasing sequences where older values are purged periodically.
Unless you happen to be doing index_ffs on that particular index, in which
case a rebuild *may* be in order. Rebuilding the index may cause insert
performance problems for a time due to block splits.
If the index is a composite index with some other non-unique value(s)
making up the index, a higher than normal number of block splits may be
in your future. I haven't tested the composite index yet, but this seems reasonable.
If the index is based simply on the unique key, and for some reason you are
using index_ffs on it, then rebuilding will cut down the number of scanned blocks.
It would be interesting to see how skip scans are affected by this as well.
Jared
Hemant K Chitale [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
11/01/2003 12:34 AM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: re Rebuilding Indexes in Oracle Apps -- an update
Richard et al,
{for those who've been following the thread on Rebuilding Indexes ...}
I've just been reading the AskTom thread on rebuilding indexes
at
http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:6601312252730
and picked on the important line
Coalesce... reclaim the free space from mostly empty index leaf blocks
that will not be reused otherwise due to your increasing sequence.
Richard has also pointed COALESCE as a better option.
COALESCE would be a better option than REBUILD for Indexes on
monotonically increasing sequences where older values are purged periodically.
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is : http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
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 Rebuilding Indexes in Oracle Apps -- an update
Richard et al,
{for those who've been following the thread on Rebuilding Indexes ...}
I've just been reading the AskTom thread on rebuilding indexes
at
http://asktom.oracle.com/pls/ask/f?p=4950:8:F4950_P8_DISPLAYID:6601312252730
and picked on the important line
Coalesce... reclaim the free space from mostly empty index leaf blocks
that will not be reused otherwise due to your increasing sequence.
Richard has also pointed COALESCE as a better option.
COALESCE would be a better option than REBUILD for Indexes on
monotonically increasing sequences where older values are purged periodically.
Hemant K Chitale
Oracle 9i Database Administrator Certified Professional
My personal web site is : http://hkchital.tripod.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hemant K Chitale
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 Rebuilding Indexes in Oracle Apps -- was RE: RE:
Hi Hemant, How I dislike being immortalised ;) The note basically quoted me word for word on my feedback and that's fine, it's certainly an improvement on what was previously suggested (and yes, Oracle asked for my permission). A point I would add though is that the whole subject of how Oracle indexes function and the various cases when one should or should not rebuild indexes is not black and white and is not easily covered in a couple of paragraphs. There are always exceptions and oddities, the key is determining when these scenarios arrive and taking the appropriate action. Many books/articles emphasise the need to rebuild generally and often, I'm suggesting the emphasis should be far more considered and practical. If anyone reading the note now questions the rebuild generally and often approach, then my comments serve their intentions. Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, October 24, 2003 1:19 AM Yes. However, every time he has replied to me, he has been confident that he IS right. Mind you, Richard, you are immortalised now ! Hemant At 05:04 PM 22-10-03 -0800, you wrote: So now the blame rests solely on Richard for any material in the note that's wrong. :) Check the latest update: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_data b ase_id=NOTp_id=182699.1 Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Millsap Sent: Wednesday, October 22, 2003 2:35 AM To: Multiple recipients of list ORACLE-L Oops, I didn't see that part. Thanks for the catch, Hemant. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Hemant K Chitale Sent: Tuesday, October 21, 2003 10:15 AM To: Multiple recipients of list ORACLE-L Unfortunately, the lines Unoccupied space on indexes occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore, indexes whose columns are subject to intensive value change should be rebuilt periodically, since they become naturally fragmentated. are still visible in Note 182699.1 Hemant At 08:29 AM 20-10-03 -0800, you wrote: Fyi, Oracle updated note 182699.1 last Friday. The inaccurate statements about index fragmentation have been removed. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Richard Foote Sent: Friday, October 17, 2003 6:29 AM To: Multiple recipients of list ORACLE-L Separate Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0-9.0) Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore indexes whose columns are subject to value change must be rebuilt periodically since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE:
Yes. However, every time he has replied to me, he has been confident that he IS right. Mind you, Richard, you are immortalised now ! Hemant At 05:04 PM 22-10-03 -0800, you wrote: So now the blame rests solely on Richard for any material in the note that's wrong. :) Check the latest update: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=NOTp_id=182699.1 Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Millsap Sent: Wednesday, October 22, 2003 2:35 AM To: Multiple recipients of list ORACLE-L Oops, I didn't see that part. Thanks for the catch, Hemant. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Hemant K Chitale Sent: Tuesday, October 21, 2003 10:15 AM To: Multiple recipients of list ORACLE-L Unfortunately, the lines Unoccupied space on indexes occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore, indexes whose columns are subject to intensive value change should be rebuilt periodically, since they become naturally fragmentated. are still visible in Note 182699.1 Hemant At 08:29 AM 20-10-03 -0800, you wrote: Fyi, Oracle updated note 182699.1 last Friday. The inaccurate statements about index fragmentation have been removed. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Richard Foote Sent: Friday, October 17, 2003 6:29 AM To: Multiple recipients of list ORACLE-L Separate Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0-9.0) Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore indexes whose columns are subject to value change must be rebuilt periodically since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: Cary Millsap 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). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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
Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE:
sold out na lahat-yung Tower 3 sa Eastwood is only for Lease?? At 11:34 AM 10/17/2003 -0800, you wrote: The article states that leaf blocks are not reused, which is indeed incorrect, and has been for a very long time. Hemant K Chitale [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/17/2003 11:42 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE: I wonder if it is not necessary to rebuild indexes is also a myth. It IS in some cases necessary 1. Indexes on monotonically increasing values [eg Conrurrent_Request_ID based on a Sequence or even on date columns which signify when the record is created] if the table is also purged by the same columns frequently 2. Because the disk space used by an Index can be inordinately larged after a couple of years and index fast_full_scans are impacted Have you administered an Oracle Applications database ? hemant At 03:29 AM 17-10-03 -0800, you wrote: Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0-9.0) Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore indexes whose columns are subject to value change must be rebuilt periodically since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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: Jerome Roa 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 Rebuilding Indexes in Oracle Apps -- was RE: RE:
So now the blame rests solely on Richard for any material in the note that's wrong. :) Check the latest update: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=NOTp_id=182699.1 Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Millsap Sent: Wednesday, October 22, 2003 2:35 AM To: Multiple recipients of list ORACLE-L Oops, I didn't see that part. Thanks for the catch, Hemant. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Hemant K Chitale Sent: Tuesday, October 21, 2003 10:15 AM To: Multiple recipients of list ORACLE-L Unfortunately, the lines Unoccupied space on indexes occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore, indexes whose columns are subject to intensive value change should be rebuilt periodically, since they become naturally fragmentated. are still visible in Note 182699.1 Hemant At 08:29 AM 20-10-03 -0800, you wrote: Fyi, Oracle updated note 182699.1 last Friday. The inaccurate statements about index fragmentation have been removed. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Richard Foote Sent: Friday, October 17, 2003 6:29 AM To: Multiple recipients of list ORACLE-L Separate Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0-9.0) Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore indexes whose columns are subject to value change must be rebuilt periodically since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: Cary Millsap 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). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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
Re: re Rebuilding Indexes in Oracle Apps --
Hi Hemant,
If you purge 80% of rows from a table without intending to reinsert them
anytime soon, then yes, a table reorg would be a recommended step. No
arguments from me there ;)
I had lunch with Pete Sharman today and he mentioned that he sent an email
to the support person responsible for the note with a copy of my little demo
highlighting how deleted row space can be reused. I also received an email
form Lex de Haan at Oracle inviting me to provide feedback on how the note
can be improved.
So hopefully, a modified note might be available soon. That being the case,
one has to give Oracle much credit for trying to put things to the right.
The tide is turning ;)
Cheers
Richard
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Wednesday, October 22, 2003 1:14 AM
Ah well ! I do not have statistics to present my case.
However, it is not my case that Index rebuilds are necessary,
only that in the case of some Indexes, rebuilds do make sense.
Last week, I'd just purged about 80% of the rows in a few tables and, of
course,
then rebuilt both the tables [to reset the HWM] and indexes.
{These were some Alert tables where the application administrator
had never setup purging and we had 2 years of alerts}
FND_CONCURRENT_REQUESTS is a case of a table with monotonically
increasing values for certain columns [REQUEST_ID and REQUEST_START_DATE].
Although the Note that I referred to in my earlier email [Note 182699.1]
has been updated on 17-Oct, it still includes the paragraph
Unoccupied space on indexes occurs when a key value changes, and the
index
row is deleted from one place (Leaf Block) and inserted into another.
Deleted Leaf Rows are not reused. Therefore, indexes whose columns
are
subject to intensive value change should be rebuilt periodically,
since
they become naturally fragmentated.
Hemant
At 06:44 PM 17-10-03 -0800, you wrote:
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Saturday, October 18, 2003 4:42 AM
Hi Hermant,
I wonder if it is not necessary to rebuild indexes is also a myth.
It might be but I've yet to hear it. It's certainly not something I've
ever
claimed, unless it's a quote taken out of context (the start and end are
missing) which would be unfortunate.
I would re-phrase it as it is *rarely* necessary to rebuild indexes
and
it would be a hell of a lot more accurate than many quotations on this
subject. So let's not confuse and cloud the issue.
It IS in some cases necessary
Yes it is but the point I'm trying to make that the some cases are
relatively *rare*. The emphasis as I often hear it is that indexes
usually/always need to be rebuilt. This is simply incorrect. The
Metalink
note claims that deleted space is not reused. This is not only incorrect
but
helps promote the myth that indexes hence need frequent rebuilding. One
incorrect claim promotes one incorrect conclusion.
1. Indexes on monotonically increasing values [eg
Conrurrent_Request_ID
based on a Sequence
As I've previously stated *but* and it's a big BUT only if there are
subsequent sparse deletions. No spares deletions, no rebuilds are
necessary.
What ratio of indexes in Oracle financials actually meet this criteria ?
Monotonically increasing *and* sparse deletions.
or even on date columns which signify when the record is created] if
the
table is also
purged by the same columns frequently
Similar case to the above. But this implies a specific range of index
values
being deleted which results in a range of index nodes being emptied.
These
blocks therefore *can* be reused. If records are subsequently inserted
*at
the same rate* they are being purged, then again index rebuilds are
potentially unnecessary.
2. Because the disk space used by an Index can be inordinately larged
after a couple of years
and index fast_full_scans are impacted
How ?
We covered one case above. Another is that we simply reduce the volume of
data within a table (and hence index). How does time result
inordinately
enlarged indexes ? As previously discussed, Oracle is very efficient in
the
way it reuses space within an index, suggestions that indexes just become
unnecessarily enlarged over time are generally false.
Have you administered an Oracle Applications database ?
No, but I have a number of SAP applications and they suffer from the same
bad advice that indexes generally require frequently rebuilding. In
actual
fact, the ratio of indexes that actually benefit from rebuilding is tiny
and
then it's generally the table that needs rebuilding more so than the
indexes
directly and then the tiny tiny ratio of indexes that remain generally
need
coalescing rather than rebuilding.
Indexes that exist in Oracle Applications are not special, they follow
the
same rules as those indexes in SAP, or in-house
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE:
Unfortunately, the lines Unoccupied space on indexes occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore, indexes whose columns are subject to intensive value change should be rebuilt periodically, since they become naturally fragmentated. are still visible in Note 182699.1 Hemant At 08:29 AM 20-10-03 -0800, you wrote: Fyi, Oracle updated note 182699.1 last Friday. The inaccurate statements about index fragmentation have been removed. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Richard Foote Sent: Friday, October 17, 2003 6:29 AM To: Multiple recipients of list ORACLE-L Separate Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0-9.0) Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore indexes whose columns are subject to value change must be rebuilt periodically since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: Cary Millsap 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). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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 Rebuilding Indexes in Oracle Apps -- was RE: RE:
Oops, I didn't see that part. Thanks for the catch, Hemant. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Hemant K Chitale Sent: Tuesday, October 21, 2003 10:15 AM To: Multiple recipients of list ORACLE-L Unfortunately, the lines Unoccupied space on indexes occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore, indexes whose columns are subject to intensive value change should be rebuilt periodically, since they become naturally fragmentated. are still visible in Note 182699.1 Hemant At 08:29 AM 20-10-03 -0800, you wrote: Fyi, Oracle updated note 182699.1 last Friday. The inaccurate statements about index fragmentation have been removed. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Richard Foote Sent: Friday, October 17, 2003 6:29 AM To: Multiple recipients of list ORACLE-L Separate Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0-9.0) Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore indexes whose columns are subject to value change must be rebuilt periodically since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: Cary Millsap 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). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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: Cary Millsap 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:
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Fyi, Oracle updated note 182699.1 last Friday. The inaccurate statements about index fragmentation have been removed. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Richard Foote Sent: Friday, October 17, 2003 6:29 AM To: Multiple recipients of list ORACLE-L Separate Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0-9.0) Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore indexes whose columns are subject to value change must be rebuilt periodically since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: Cary Millsap 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 Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Thanks for the info Cary. Jared On Mon, 2003-10-20 at 09:29, Cary Millsap wrote: Fyi, Oracle updated note 182699.1 last Friday. The inaccurate statements about index fragmentation have been removed. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney - SQL Optimization 101: 12/8-12 Dallas - Hotsos Symposium 2004: March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- Richard Foote Sent: Friday, October 17, 2003 6:29 AM To: Multiple recipients of list ORACLE-L Separate Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0-9.0) Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore indexes whose columns are subject to value change must be rebuilt periodically since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: Cary Millsap 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: Jared Still 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 Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0-9.0) Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore indexes whose columns are subject to value change must be rebuilt periodically since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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 Rebuilding Indexes in Oracle Apps -- Quoting an Apps
Doesn't reuse leaf rows?! The myth is perpetuated. Try running these scripts. In a nutshell, a table is created with about 20k rows, with an incrementing id. There is a non-unique index created on the id column. An analyze is done on the table then index_stats is populated with 'analyze index validate structure', and the number of leaf rows, All rows with an id that is evenly divisible by 2 are copied to another table and then deleted. The rows are then reinserted into the first table. Between each step, statistics are computed on the table and index, the index structure validated, and the number of leaf rows, leaf blocks, deleted leaf rows and deleted leaf blocks are displayed. === ai.sql === analyze table t1 compute statistics; analyze index t1_id_idx validate structure; === is.sql === select lf_rows, lf_blks, lf_blk_len, del_lf_rows, pct_used from index_stats / === j1.sql === drop table t1 cascade constraints; drop table t2 cascade constraints; create table t1 ( id number(8) , last_name varchar2(30) , first_name varchar2(30) ) nologging / insert /*+ append */ into t1( id, last_name, first_name) select rownum id , substr(owner,1,30) last_name , substr(object_name,1,30) first_name from dba_objects / create index t1_id_idx on t1(id); @@ai @@is create table t2 nologging as select * from t1 where 1=0 / insert /*+ append */ into t2 select * from t1 where id/2 = floor(id/2) / delete from t1 where id/2 = floor(id/2) / commit; @@ai @@is insert into t1 (id, last_name, first_name) select id+1 id, last_name, first_name from t2 / @@ai @@is == Hemant K Chitale [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/16/2003 08:49 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: re Rebuilding Indexes in Oracle Apps -- Quoting an Apps Richard, Quoting Metalink Note 182699.1 on bde_rebuild.sql - Validates and Rebuilds Fragmentated Indexes (8.0-9.0) Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore, indexes whose columns are subject to value change must be rebuilt periodically, since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. At 06:29 AM 16-10-03 -0800, you wrote: On Wed, 2003-10-15 at 18:04, M Rafiq wrote: Jared, Those tables are transit type of tables and depending on your volume of data, there are lot of deletes and inserts all the time resuling index fragmentation(holes due to deletes) and space usage. The rebuilding not only release the space but also reduces the index fragmentation. If you don't have table truncation option for such tables then it is much better to rebuid indexes on such tables at regular interval to release space and for better performance. Hi Rafiq, I haven't been receiving all the mail from this list so I don't know the full thread and it doesn't appear a mail I sent a few days ago regarding all this ever made it so I could be wasting my time again. But everytime I see comments as in the above, a voice in my head says do something, do something. So I'll try again. Having lots of deletes and inserts of course doesn't necessarily mean fragmentation. These so-called holes are fully re-usable and in the vast majority of cases results in no substantial issues. Having lots of deletes, inserts and updates rarely requires the index to be rebuilt. Simple little demo for any newbies or those force-fed Oracle myths since child birth ... First of all, create a simple table and index. I've intentionally left a value out in the middle of a range for extra effect. SQL create table bowie_test (ziggy number); Table created. SQL insert into bowie_test values (1); 1 row created. SQL insert into bowie_test values (2); 1 row created. SQL insert into bowie_test values (3); 1 row created. SQL insert into bowie_test values (4); 1 row created. SQL insert into bowie_test values (6); 1 row created. SQL insert into bowie_test values (7); 1 row created. SQL insert into bowie_test values (8); 1 row created. SQL insert into bowie_test values (9); 1 row created. SQL insert into bowie_test values (10); 1 row created. SQL insert into bowie_test values (100); 1 row created. SQL commit; Commit complete. SQL create index bowie_test_idx on bowie_test(ziggy); Index created. Now analyze the index ... SQL analyze index bowie_test_idx validate structure; Index analyzed. and we see that everything is sweet with no wasted deleted space ... SQL select lf_rows, del_lf_rows
Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
My experience in contacting Oracle regarding modifying of notes on MetaLink has not been very satisfying. I did take the opportunity to voice my dissatisfaction by using the poll at the top of the article to indicate that I would not recommend this article to others. Jared Richard Foote [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/17/2003 04:29 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0-9.0) Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore indexes whose columns are subject to value change must be rebuilt periodically since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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 Rebuilding Indexes in Oracle Apps -- was RE: RE:
The article states that leaf blocks are not reused, which is indeed incorrect, and has been for a very long time. Hemant K Chitale [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/17/2003 11:42 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE: I wonder if it is not necessary to rebuild indexes is also a myth. It IS in some cases necessary 1. Indexes on monotonically increasing values [eg Conrurrent_Request_ID based on a Sequence or even on date columns which signify when the record is created] if the table is also purged by the same columns frequently 2. Because the disk space used by an Index can be inordinately larged after a couple of years and index fast_full_scans are impacted Have you administered an Oracle Applications database ? hemant At 03:29 AM 17-10-03 -0800, you wrote: Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0-9.0) Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore indexes whose columns are subject to value change must be rebuilt periodically since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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 Rebuilding Indexes in Oracle Apps -- was RE: RE:
If they don't have time to fix bugs, how do you expect them to spend timeto correct documentation and technical notes? Now many people pay to buy documentation nowadays ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Friday, October 17, 2003 3:35 PMTo: Multiple recipients of list ORACLE-LSubject: Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE:The article states that leaf blocks are not reused, which is indeed incorrect, and has been for a very long time.**This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.**5
Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE:
I wonder if it is not necessary to rebuild indexes is also a myth. It IS in some cases necessary 1. Indexes on monotonically increasing values [eg Conrurrent_Request_ID based on a Sequence or even on date columns which signify when the record is created] if the table is also purged by the same columns frequently 2. Because the disk space used by an Index can be inordinately larged after a couple of years and index fast_full_scans are impacted Have you administered an Oracle Applications database ? hemant At 03:29 AM 17-10-03 -0800, you wrote: Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0-9.0) Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore indexes whose columns are subject to value change must be rebuilt periodically since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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 Rebuilding Indexes in Oracle Apps -- was RE: RE:
Title: Message
Well, in a small step in our defense, it DOES state at the top of the
note:
*
This article is being
delivered in Draft form and may contain
errors. Please use the
MetaLink "Feedback" button to advise
Oracle of any issues related
to this article.
*
and in an even larger step, the author of the document
has been asked to review and correct it.
Pete
"Controlling
developers is like herding cats."
Kevin
Loney, Oracle DBA Handbook
"Oh
no, it's not. It's much harder than
that!"
Bruce
Pihlamae, long-term Oracle DBA
-Original Message-From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]Sent: Saturday, October 18, 2003 5:35
AMTo: Multiple recipients of list ORACLE-LSubject: Re:
re Rebuilding Indexes in Oracle Apps -- was RE:
RE:The article states
that leaf blocks are not reused, which is indeed incorrect, and has been for a very long
time.
Hemant K Chitale
[EMAIL PROTECTED] Sent by: [EMAIL PROTECTED]
10/17/2003 11:42 AM
Please respond to ORACLE-L
To:
Multiple recipients of list ORACLE-L
[EMAIL PROTECTED] cc:
Subject:Re: re Rebuilding Indexes in
Oracle Apps -- was RE: RE:I wonder if "it is not necessary to rebuild
indexes" is also a myth.It IS in some cases necessary1.
Indexes on monotonically increasing values [eg Conrurrent_Request_ID
based on a Sequenceor even on date columns which signify when the
record is created] if the table is alsopurged by the same columns
frequently2. Because the disk space used by an Index can be
inordinately larged after a couple of yearsand index fast_full_scans
are impactedHave you administered an Oracle Applications database
?hemantAt 03:29 AM 17-10-03 -0800, you wrote:Hi
Hemant,One word perfectly describes the Metalink article you
highlighted:Crap ;)A nice example of how
Oracle Corp is the greatest myth generator of themall !! It's all
rather sad and embarressing isn't.Thanks for the headsup.
Anyone in a position to get the note removed
?CheersRichard Quoting
Metalink Note 182699.1 bde_rebuild.sql Validates and
RebuildsFragmentated Indexes (8.0-9.0)
Index fragmentation occurs when a key value changes, and the index row
is deleted from one place (Leaf Block) and inserted into
another. Deleted Leaf Rows are not reused. Therefore
indexes whose columns are subject to value change must be
rebuilt periodically since they becomenaturally fragmentated.
An index is considered to be 'fragmentated' when more
than 20% of its LeafRows space is empty because of the
implicit deletes caused by indexed columns valuechanges.
Fragmentated indexes degrade the performance of index
range scanoperations.--Please see the
official ORACLE-L FAQ: http://www.orafaq.net--Author: Richard
Foote INET: [EMAIL PROTECTED]Fat City
Network Services -- 858-538-5051
http://www.fatcity.comSan Diego, California
-- Mailing list and web hosting
services-To
REMOVE yourself from this mailing list, send an E-Mail messageto:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe
message BODY, include a line containing: UNSUB ORACLE-L(or the name of
mailing list you want to be removed from). You mayalso send the
HELP command for other information (like subscribing).Hemant K
ChitaleOracle 9i Database Administrator Certified ProfessionalMy
personal web site is : http://hkchital.tripod.com--
Please see the official ORACLE-L FAQ: http://www.orafaq.net--
Author: Hemant K ChitaleINET: [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 messageto:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing
list you want to be removed from). You mayalso send the HELP command
for other information (like
subscribing).
Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE:
- Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Saturday, October 18, 2003 4:42 AM Hi Hermant, I wonder if it is not necessary to rebuild indexes is also a myth. It might be but I've yet to hear it. It's certainly not something I've ever claimed, unless it's a quote taken out of context (the start and end are missing) which would be unfortunate. I would re-phrase it as it is *rarely* necessary to rebuild indexes and it would be a hell of a lot more accurate than many quotations on this subject. So let's not confuse and cloud the issue. It IS in some cases necessary Yes it is but the point I'm trying to make that the some cases are relatively *rare*. The emphasis as I often hear it is that indexes usually/always need to be rebuilt. This is simply incorrect. The Metalink note claims that deleted space is not reused. This is not only incorrect but helps promote the myth that indexes hence need frequent rebuilding. One incorrect claim promotes one incorrect conclusion. 1. Indexes on monotonically increasing values [eg Conrurrent_Request_ID based on a Sequence As I've previously stated *but* and it's a big BUT only if there are subsequent sparse deletions. No spares deletions, no rebuilds are necessary. What ratio of indexes in Oracle financials actually meet this criteria ? Monotonically increasing *and* sparse deletions. or even on date columns which signify when the record is created] if the table is also purged by the same columns frequently Similar case to the above. But this implies a specific range of index values being deleted which results in a range of index nodes being emptied. These blocks therefore *can* be reused. If records are subsequently inserted *at the same rate* they are being purged, then again index rebuilds are potentially unnecessary. 2. Because the disk space used by an Index can be inordinately larged after a couple of years and index fast_full_scans are impacted How ? We covered one case above. Another is that we simply reduce the volume of data within a table (and hence index). How does time result inordinately enlarged indexes ? As previously discussed, Oracle is very efficient in the way it reuses space within an index, suggestions that indexes just become unnecessarily enlarged over time are generally false. Have you administered an Oracle Applications database ? No, but I have a number of SAP applications and they suffer from the same bad advice that indexes generally require frequently rebuilding. In actual fact, the ratio of indexes that actually benefit from rebuilding is tiny and then it's generally the table that needs rebuilding more so than the indexes directly and then the tiny tiny ratio of indexes that remain generally need coalescing rather than rebuilding. Indexes that exist in Oracle Applications are not special, they follow the same rules as those indexes in SAP, or in-house applications, etc. Hemant, take a look at Jonathan Lewis's article When Should You Rebuild An Index at www.dbazine.com . In it he concludes Will the total cost of rebuilding the index be a reasonable price to pay for the resulting benefit to the system ? The answer to this question is frequently a resounding NO. In fact, sometimes the overall impact of rebuilding an active index will be detrimental to the system. However, there are still plenty of misconceptions about indexes that result in DBAs the world over wasting valuable time and effort rebuilding indexes unnecessarily. Amen to that !! Cheers ;) Richard hemant At 03:29 AM 17-10-03 -0800, you wrote: Hi Hemant, One word perfectly describes the Metalink article you highlighted: Crap ;) A nice example of how Oracle Corp is the greatest myth generator of them all !! It's all rather sad and embarressing isn't. Thanks for the headsup. Anyone in a position to get the note removed ? Cheers Richard Quoting Metalink Note 182699.1 bde_rebuild.sql Validates and Rebuilds Fragmentated Indexes (8.0-9.0) Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore indexes whose columns are subject to value change must be rebuilt periodically since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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
Re: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
On Wed, 2003-10-15 at 18:04, M Rafiq wrote: Jared,Those tables are transit type of tables and depending on your volume of data, there are lot of deletes and inserts all the time resuling index fragmentation(holes due to deletes) and space usage. The rebuilding not only release the space but also reduces the index fragmentation. If you don't have table truncation option for such tables then it is much better to rebuid indexes on such tables at regular interval to release space and for better performance. Hi Rafiq, I haven't been receiving all the mail from this list so I don't know the full thread and it doesn't appear a mail I sent a few days ago regarding all this ever made it so I could be wasting my time again. But everytime I see comments as in the above, a voice in my head says "do something, do something". So I'll try again. Having lots of deletes and inserts of course doesn't necessarily mean fragmentation. Theseso-called holes are fully re-usableand in the vast majority of cases results in no substantial issues. Having lots of deletes, inserts and updates rarely requires the index to be rebuilt. Simple little demo for any newbies or those force-fed Oracle myths since child birth ... Firstof all, create a simple table and index. I've intentionally left a value out "in the middle" of a range for extra effect. SQL create table bowie_test (ziggy number); Table created. SQL insert into bowie_test values (1); 1 row created. SQL insert into bowie_test values (2); 1 row created. SQL insert into bowie_test values (3); 1 row created. SQL insert into bowie_test values (4); 1 row created. SQL insert into bowie_test values (6); 1 row created. SQL insert into bowie_test values (7); 1 row created. SQL insert into bowie_test values (8); 1 row created. SQL insert into bowie_test values (9); 1 row created. SQL insert into bowie_test values (10); 1 row created. SQL insert into bowie_test values (100); 1 row created. SQL commit; Commit complete. SQL create index bowie_test_idx on bowie_test(ziggy); Index created. Now analyze the index ... SQL analyze index bowie_test_idx validate structure; Index analyzed. and we see that everything is sweet with no "wasted" deleted space ... SQL select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN-- --- --- 10 0 0 We now delete a number of rows ... SQL delete bowie_test where ziggy in (2,3,4,6,7,8,9,10); 8 rows deleted. SQL commit; Commit complete. And we see that of the 10 leaf rows, 8 are deleted. As Gollum would say "nasty wasted spaces it is, gollum ..." SQL select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN-- --- --- 10 8 112 However, we now insert a new value (notice it's different from any previous value but obviously belongs in the same leaf node as the others) ... SQL insert into bowie_test values (5); 1 row created. SQL commit; Commit complete. SQL analyze index bowie_test_idx validate structure; Index analyzed. SQL select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN-- --- --- 3 0 0 and we see that *all* the "wasted" deleted space within the leaf node has been freed and is available for reuse ... With few exceptions (the key is picking those rare cases), index rebuildsare redundant, wasteful and can actually be "detrimental" to performance. Cheers Richard
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
All of that is fair enough but the number of rows and the values you've chosen fit the point you wished to prove. The value 5 conveniently fits the range for an existing leaf block with empty space. The facts as I understand them are this : Index space freed by deleted entries can be reused ( by subsequent transactions ) so long as the indexed value 'belongs' in the leaf block which has free space. Index leaf blocks are only placed back on the free list when they are empty of entries. This means that given a constantly incrementing index value no free space will be reused unless whole index blocks are emptied by deletes. This is fine for working tables which are constantly filled and (totally) emptied but it can lead to large indexes for tables which preserve small amounts os data across the range of keys. Such monotonically increasing key values are pretty common in my experience. I agree though that index rebuilds are often necessary. For a while now we've had useful commands like coalesce that could combine logically adjacent, sparsely populated leaf blocks at far less cost than a rebuild. Regards, Mike Hately -Original Message- Sent: 16 October 2003 14:29 To: Multiple recipients of list ORACLE-L On Wed, 2003-10-15 at 18:04, M Rafiq wrote: Jared, Those tables are transit type of tables and depending on your volume of data, there are lot of deletes and inserts all the time resuling index fragmentation(holes due to deletes) and space usage. The rebuilding not only release the space but also reduces the index fragmentation. If you don't have table truncation option for such tables then it is much better to rebuid indexes on such tables at regular interval to release space and for better performance. Hi Rafiq, I haven't been receiving all the mail from this list so I don't know the full thread and it doesn't appear a mail I sent a few days ago regarding all this ever made it so I could be wasting my time again. But everytime I see comments as in the above, a voice in my head says do something, do something. So I'll try again. Having lots of deletes and inserts of course doesn't necessarily mean fragmentation. These so-called holes are fully re-usable and in the vast majority of cases results in no substantial issues. Having lots of deletes, inserts and updates rarely requires the index to be rebuilt. Simple little demo for any newbies or those force-fed Oracle myths since child birth ... First of all, create a simple table and index. I've intentionally left a value out in the middle of a range for extra effect. SQL create table bowie_test (ziggy number); Table created. SQL insert into bowie_test values (1); 1 row created. SQL insert into bowie_test values (2); 1 row created. SQL insert into bowie_test values (3); 1 row created. SQL insert into bowie_test values (4); 1 row created. SQL insert into bowie_test values (6); 1 row created. SQL insert into bowie_test values (7); 1 row created. SQL insert into bowie_test values (8); 1 row created. SQL insert into bowie_test values (9); 1 row created. SQL insert into bowie_test values (10); 1 row created. SQL insert into bowie_test values (100); 1 row created. SQL commit; Commit complete. SQL create index bowie_test_idx on bowie_test(ziggy); Index created. Now analyze the index ... SQL analyze index bowie_test_idx validate structure; Index analyzed. and we see that everything is sweet with no wasted deleted space ... SQL select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN -- --- --- 10 0 0 We now delete a number of rows ... SQL delete bowie_test where ziggy in (2,3,4,6,7,8,9,10); 8 rows deleted. SQL commit; Commit complete. And we see that of the 10 leaf rows, 8 are deleted. As Gollum would say nasty wasted spaces it is, gollum ... SQL select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN -- --- --- 10 8 112 However, we now insert a new value (notice it's different from any previous value but obviously belongs in the same leaf node as the others) ... SQL insert into bowie_test values (5); 1 row created. SQL commit; Commit complete. SQL analyze index bowie_test_idx validate structure; Index analyzed. SQL select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN -- --- --- 3 0 0 and we see that *all* the wasted deleted space within the leaf node has been freed and is available for reuse ... With few exceptions (the key is picking those rare cases), index rebuilds are redundant, wasteful and can actually be detrimental to performance. Cheers Richard
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE:
At 01:34 PM 14-10-03 -0800, you wrote: Hemant, John, My apologies for the delay. I hadn't logged on to my email last night. Here's the output from my site 11.0.3 Purge Obsolete Workflow Runtime Data set to AGE=90 days [ITEM_TYPE and ITEM_KEY null in parameters] SQL set time on 14:57:42 SQL 14:57:42 SQL select activity_status, count(*) 14:57:46 2 from applsys.wf_item_activity_statuses 14:57:46 3 group by activity_status; select item_type,activity_status,count(*) from applsys.wf_item_activity_statuses where activity_status='COMPLETE' group by item_type,activity_status; ACTIVITY COUNT(*) -- ACTIVE 18761 COMPLETE 1039949 DEFERRED 1082 ERROR 5541 NOTIFIED 10489 14:58:03 SQL 14:58:03 SQL 14:58:03 2 14:58:03 3 14:58:03 4 ITEM_TYP ACTIVITY COUNT(*) -- APVRMDER COMPLETE 29739 CREATEPO COMPLETE 154074 POAPPRV COMPLETE 309445 REQAPPRV COMPLETE 546767 14:59:01 SQL Currently I do not have too many rows in the tables but I still plan to rebuild the tables and indexes. Hemant This applies on 11i only. I would rebuild all indexes supporting the WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been working on some AOL table(space) problems in the background and noticed that in 11i by default, we are not be purging _all_ the WF data that we should be purging. I believe the current Purge routine purges activity rows whose persistence has expired and are marked 'TEMPORARY' and ignores those that are COMPLETE (see below). My contention is that it should be deleting old rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes 141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help. You could check this using the following SQLs select activity_status, count(*) from applsys.wf_item_activity_statuses group by activity_status; select item_type,activity_status,count(*) from applsys.wf_item_activity_statuses where activity_status='COMPLETE' group by item_type,activity_status; Once the 'correct' purge is complete, the 'holey' indexes will need to be rebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM to reasonable levels. Let me know what your install shows up. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Tuesday, October 14, 2003 8:39 AM To: Multiple recipients of list ORACLE-L John, I rebuild the FND_CONCURRENT_REQUESTS indexes every four months [and the table itself, occassionally]. This Saturday I will also be rebuilding some ALR indexes. Which WorkFlow Indexes do you rebuild ? Hemant At 11:44 AM 13-10-03 -0800, you wrote: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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). Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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 Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Correction. Paragraph 4 should begin, I agree though that index rebuilds are often unnecessary. Mike -Original Message- Sent: 16 October 2003 15:20 To: '[EMAIL PROTECTED]' All of that is fair enough but the number of rows and the values you've chosen fit the point you wished to prove. The value 5 conveniently fits the range for an existing leaf block with empty space. The facts as I understand them are this : Index space freed by deleted entries can be reused ( by subsequent transactions ) so long as the indexed value 'belongs' in the leaf block which has free space. Index leaf blocks are only placed back on the free list when they are empty of entries. This means that given a constantly incrementing index value no free space will be reused unless whole index blocks are emptied by deletes. This is fine for working tables which are constantly filled and (totally) emptied but it can lead to large indexes for tables which preserve small amounts os data across the range of keys. Such monotonically increasing key values are pretty common in my experience. I agree though that index rebuilds are often necessary. For a while now we've had useful commands like coalesce that could combine logically adjacent, sparsely populated leaf blocks at far less cost than a rebuild. Regards, Mike Hately -Original Message- Sent: 16 October 2003 14:29 To: Multiple recipients of list ORACLE-L Hi Rafiq, I haven't been receiving all the mail from this list so I don't know the full thread and it doesn't appear a mail I sent a few days ago regarding all this ever made it so I could be wasting my time again. But everytime I see comments as in the above, a voice in my head says do something, do something. So I'll try again. Having lots of deletes and inserts of course doesn't necessarily mean fragmentation. These so-called holes are fully re-usable and in the vast majority of cases results in no substantial issues. Having lots of deletes, inserts and updates rarely requires the index to be rebuilt. Simple little demo for any newbies or those force-fed Oracle myths since child birth ... demo snipped on space grounds - Mike Hately With few exceptions (the key is picking those rare cases), index rebuilds are redundant, wasteful and can actually be detrimental to performance. Cheers Richard E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator. This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. CE Electric UK Funding Company Lloyds Court 78 Grey Street Newcastle upon Tyne NE1 6AF Registered in England and Wales: Number 3476201 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (LogicaCMG) 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 Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Jared, Unfortunately at this stage I cannot quantify in numbers as I have left that job 5 months back. But dealing with Oracle Financials 10.7 with version 7.3.4, I observed it practically that this table and it is indexes (i think 4 or 5 indexes) require special attention for performance reasons. At my last employment that table was also used by customized application specially Manufactruring and stock locator application and heavy usage of inserts and deletes. If indexes were not rebuilt on that tablespace then I have seen that users were complaining about slowness of thier jobs. So I made it a maintenance routine to rebuild indexes on gl_interface table after monthly closing. Apart from this, as you cannot change code in Oracle Financials(although I did) , you to deal with indexes either through rebuilding them at regular intervals (may be six moths or a year) or adding new indexes based on your observation of certain codes. One monthly job called ACCRUAL REBUILD RECONCILIATION was passing 36 hours and I have to add 6 indexes on 2 tables and time went down to 1 hour. In certain codes they were suppresing indexes(perfectly indexed columns) resulting 15 mintues to fetch rows and after correcting that code it took less than second. Now another database of Order Entry System. When I joined I observed a lot of performance issues. After consulting with Development team,tracked all those tables with lot of regular deletes and inserts, rebuilt all indexes and got back 5GB of tablespace and performance was at their peak. All those application was based on RULE optimizer so we were not analyzing any table/indexes but based on experience with those applications, I was tracking those tables with large deletes and inserts through application(not data load) and rebuilding indexes with regular interval to keep smooth performance. In my opinion, we always need performance satisfaction of end user instead of numbers. If you have any specific question, please let me know. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 15 Oct 2003 22:04:24 -0800 The 'better performance' part is what I would like to see some metrics on. How much better? Is it worth the trouble? If your indexes continually build up to the same size, what is being gained by saving some space for a period of time? Thanks, Jared On Wed, 2003-10-15 at 18:04, M Rafiq wrote: Jared, Those tables are transit type of tables and depending on your volume of data, there are lot of deletes and inserts all the time resuling index fragmentation(holes due to deletes) and space usage. The rebuilding not only release the space but also reduces the index fragmentation. If you don't have table truncation option for such tables then it is much better to rebuid indexes on such tables at regular interval to release space and for better performance. As regard quantification, you many release sufficient amount of space if your usage is higher. Here it was 7.3.4 database so no LMT involved. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 15 Oct 2003 13:19:24 -0800 Please explain why these indexes must be built. What benefits do you see from it? Are they quantifiable? Jared M Rafiq [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/14/2003 03:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate John What about gl_interface table indexes? I think indexes on all *interface( tables must be rebuild on a regular interval...I was building indexes on gl_interfaces and fnd_request* tables on monthly basis. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 14 Oct 2003 13:34:24 -0800 Hemant, This applies on 11i only. I would rebuild all indexes supporting the WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been working on some AOL table(space) problems in the background and noticed that in 11i by default, we are not be purging _all_ the WF data that we should be purging. I believe the current Purge routine purges activity rows whose persistence has expired and are marked 'TEMPORARY' and ignores those that are COMPLETE (see below). My contention is that it should be deleting old rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes 141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help. You could check this using the following SQLs select activity_status, count(*) from applsys.wf_item_activity_statuses group by activity_status; select item_type,activity_status,count(*) from applsys.wf_item_activity_statuses where activity_status='COMPLETE' group by item_type,activity_status
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Thanks for the info. Too bad you can't get some metrics to show what was happening. Yes, user satisfaction is the ultimate indicator of tuning success, but there are also metrics to back it up, they just need to be collected before and after. Thanks, Jared M Rafiq [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/16/2003 10:34 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate Jared, Unfortunately at this stage I cannot quantify in numbers as I have left that job 5 months back. But dealing with Oracle Financials 10.7 with version 7.3.4, I observed it practically that this table and it is indexes (i think 4 or 5 indexes) require special attention for performance reasons. At my last employment that table was also used by customized application specially Manufactruring and stock locator application and heavy usage of inserts and deletes. If indexes were not rebuilt on that tablespace then I have seen that users were complaining about slowness of thier jobs. So I made it a maintenance routine to rebuild indexes on gl_interface table after monthly closing. Apart from this, as you cannot change code in Oracle Financials(although I did) , you to deal with indexes either through rebuilding them at regular intervals (may be six moths or a year) or adding new indexes based on your observation of certain codes. One monthly job called ACCRUAL REBUILD RECONCILIATION was passing 36 hours and I have to add 6 indexes on 2 tables and time went down to 1 hour. In certain codes they were suppresing indexes(perfectly indexed columns) resulting 15 mintues to fetch rows and after correcting that code it took less than second. Now another database of Order Entry System. When I joined I observed a lot of performance issues. After consulting with Development team,tracked all those tables with lot of regular deletes and inserts, rebuilt all indexes and got back 5GB of tablespace and performance was at their peak. All those application was based on RULE optimizer so we were not analyzing any table/indexes but based on experience with those applications, I was tracking those tables with large deletes and inserts through application(not data load) and rebuilding indexes with regular interval to keep smooth performance. In my opinion, we always need performance satisfaction of end user instead of numbers. If you have any specific question, please let me know. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 15 Oct 2003 22:04:24 -0800 The 'better performance' part is what I would like to see some metrics on. How much better? Is it worth the trouble? If your indexes continually build up to the same size, what is being gained by saving some space for a period of time? Thanks, Jared On Wed, 2003-10-15 at 18:04, M Rafiq wrote: Jared, Those tables are transit type of tables and depending on your volume of data, there are lot of deletes and inserts all the time resuling index fragmentation(holes due to deletes) and space usage. The rebuilding not only release the space but also reduces the index fragmentation. If you don't have table truncation option for such tables then it is much better to rebuid indexes on such tables at regular interval to release space and for better performance. As regard quantification, you many release sufficient amount of space if your usage is higher. Here it was 7.3.4 database so no LMT involved. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 15 Oct 2003 13:19:24 -0800 Please explain why these indexes must be built. What benefits do you see from it? Are they quantifiable? Jared M Rafiq [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/14/2003 03:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate John What about gl_interface table indexes? I think indexes on all *interface( tables must be rebuild on a regular interval...I was building indexes on gl_interfaces and fnd_request* tables on monthly basis. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 14 Oct 2003 13:34:24 -0800 Hemant, This applies on 11i only. I would rebuild all indexes supporting the WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been working on some AOL table(space) problems in the background and noticed that in 11i by default, we are not be purging _all_ the WF data that we should be purging. I believe the current Purge routine purges activity rows whose
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
You are right. As you agreed our ultimate goal is user satisfaction and I believe in that, may be a old habit. I came into computer area because of our dissatisfaction(being enduser) with our IT shop otherwise professionaly I used to be a qualified professional accountant playing with numbers. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 16 Oct 2003 14:59:26 -0800 Thanks for the info. Too bad you can't get some metrics to show what was happening. Yes, user satisfaction is the ultimate indicator of tuning success, but there are also metrics to back it up, they just need to be collected before and after. Thanks, Jared M Rafiq [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/16/2003 10:34 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate Jared, Unfortunately at this stage I cannot quantify in numbers as I have left that job 5 months back. But dealing with Oracle Financials 10.7 with version 7.3.4, I observed it practically that this table and it is indexes (i think 4 or 5 indexes) require special attention for performance reasons. At my last employment that table was also used by customized application specially Manufactruring and stock locator application and heavy usage of inserts and deletes. If indexes were not rebuilt on that tablespace then I have seen that users were complaining about slowness of thier jobs. So I made it a maintenance routine to rebuild indexes on gl_interface table after monthly closing. Apart from this, as you cannot change code in Oracle Financials(although I did) , you to deal with indexes either through rebuilding them at regular intervals (may be six moths or a year) or adding new indexes based on your observation of certain codes. One monthly job called ACCRUAL REBUILD RECONCILIATION was passing 36 hours and I have to add 6 indexes on 2 tables and time went down to 1 hour. In certain codes they were suppresing indexes(perfectly indexed columns) resulting 15 mintues to fetch rows and after correcting that code it took less than second. Now another database of Order Entry System. When I joined I observed a lot of performance issues. After consulting with Development team,tracked all those tables with lot of regular deletes and inserts, rebuilt all indexes and got back 5GB of tablespace and performance was at their peak. All those application was based on RULE optimizer so we were not analyzing any table/indexes but based on experience with those applications, I was tracking those tables with large deletes and inserts through application(not data load) and rebuilding indexes with regular interval to keep smooth performance. In my opinion, we always need performance satisfaction of end user instead of numbers. If you have any specific question, please let me know. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 15 Oct 2003 22:04:24 -0800 The 'better performance' part is what I would like to see some metrics on. How much better? Is it worth the trouble? If your indexes continually build up to the same size, what is being gained by saving some space for a period of time? Thanks, Jared On Wed, 2003-10-15 at 18:04, M Rafiq wrote: Jared, Those tables are transit type of tables and depending on your volume of data, there are lot of deletes and inserts all the time resuling index fragmentation(holes due to deletes) and space usage. The rebuilding not only release the space but also reduces the index fragmentation. If you don't have table truncation option for such tables then it is much better to rebuid indexes on such tables at regular interval to release space and for better performance. As regard quantification, you many release sufficient amount of space if your usage is higher. Here it was 7.3.4 database so no LMT involved. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 15 Oct 2003 13:19:24 -0800 Please explain why these indexes must be built. What benefits do you see from it? Are they quantifiable? Jared M Rafiq [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/14/2003 03:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate John What about gl_interface table indexes? I think indexes on all *interface( tables must be rebuild on a regular interval...I was building indexes on gl_interfaces and fnd_request* tables on monthly basis. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL
Re: re Rebuilding Indexes in Oracle Apps -- Quoting an Apps
Richard, Quoting Metalink Note 182699.1 on bde_rebuild.sql - Validates and Rebuilds Fragmentated Indexes (8.0-9.0) Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore, indexes whose columns are subject to value change must be rebuilt periodically, since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. At 06:29 AM 16-10-03 -0800, you wrote: On Wed, 2003-10-15 at 18:04, M Rafiq wrote: Jared, Those tables are transit type of tables and depending on your volume of data, there are lot of deletes and inserts all the time resuling index fragmentation(holes due to deletes) and space usage. The rebuilding not only release the space but also reduces the index fragmentation. If you don't have table truncation option for such tables then it is much better to rebuid indexes on such tables at regular interval to release space and for better performance. Hi Rafiq, I haven't been receiving all the mail from this list so I don't know the full thread and it doesn't appear a mail I sent a few days ago regarding all this ever made it so I could be wasting my time again. But everytime I see comments as in the above, a voice in my head says do something, do something. So I'll try again. Having lots of deletes and inserts of course doesn't necessarily mean fragmentation. These so-called holes are fully re-usable and in the vast majority of cases results in no substantial issues. Having lots of deletes, inserts and updates rarely requires the index to be rebuilt. Simple little demo for any newbies or those force-fed Oracle myths since child birth ... First of all, create a simple table and index. I've intentionally left a value out in the middle of a range for extra effect. SQL create table bowie_test (ziggy number); Table created. SQL insert into bowie_test values (1); 1 row created. SQL insert into bowie_test values (2); 1 row created. SQL insert into bowie_test values (3); 1 row created. SQL insert into bowie_test values (4); 1 row created. SQL insert into bowie_test values (6); 1 row created. SQL insert into bowie_test values (7); 1 row created. SQL insert into bowie_test values (8); 1 row created. SQL insert into bowie_test values (9); 1 row created. SQL insert into bowie_test values (10); 1 row created. SQL insert into bowie_test values (100); 1 row created. SQL commit; Commit complete. SQL create index bowie_test_idx on bowie_test(ziggy); Index created. Now analyze the index ... SQL analyze index bowie_test_idx validate structure; Index analyzed. and we see that everything is sweet with no wasted deleted space ... SQL select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN -- --- --- 10 0 0 We now delete a number of rows .. SQL delete bowie_test where ziggy in (2,3,4,6,7,8,9,10); 8 rows deleted. SQL commit; Commit complete. And we see that of the 10 leaf rows, 8 are deleted. As Gollum would say nasty wasted spaces it is, gollum .. SQL select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN -- --- --- 10 8 112 However, we now insert a new value (notice it's different from any previous value but obviously belongs in the same leaf node as the others) ... SQL insert into bowie_test values (5); 1 row created. SQL commit; Commit complete. SQL analyze index bowie_test_idx validate structure; Index analyzed. SQL select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN -- --- --- 3 0 0 and we see that *all* the wasted deleted space within the leaf node has been freed and is available for reuse ... With few exceptions (the key is picking those rare cases), index rebuilds are redundant, wasteful and can actually be detrimental to performance. Cheers Richard Hemant K Chitale Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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).
Re: re Rebuilding Indexes in Oracle Apps -- Quoting an Apps
Hemant, It is absolutely true with Oracle Financials Databases and I have seen performance degradation when indexes on such databses are not rebuilt at a regular interval meaning indexes on certain tables on mothly basis. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Thu, 16 Oct 2003 07:49:44 -0800 _ Concerned that messages may bounce because your Hotmail account has exceeded its 2MB storage limit? Get Hotmail Extra Storage! http://join.msn.com/?PAGE=features/es ---BeginMessage--- Richard, Quoting Metalink Note 182699.1 on bde_rebuild.sql - Validates and Rebuilds Fragmentated Indexes (8.0-9.0) Index fragmentation occurs when a key value changes, and the index row is deleted from one place (Leaf Block) and inserted into another. Deleted Leaf Rows are not reused. Therefore, indexes whose columns are subject to value change must be rebuilt periodically, since they become naturally fragmentated. An index is considered to be 'fragmentated' when more than 20% of its Leaf Rows space is empty because of the implicit deletes caused by indexed columns value changes. Fragmentated indexes degrade the performance of index range scan operations. At 06:29 AM 16-10-03 -0800, you wrote: On Wed, 2003-10-15 at 18:04, M Rafiq wrote: Jared, Those tables are transit type of tables and depending on your volume of data, there are lot of deletes and inserts all the time resuling index fragmentation(holes due to deletes) and space usage. The rebuilding not only release the space but also reduces the index fragmentation. If you don't have table truncation option for such tables then it is much better to rebuid indexes on such tables at regular interval to release space and for better performance. Hi Rafiq, I haven't been receiving all the mail from this list so I don't know the full thread and it doesn't appear a mail I sent a few days ago regarding all this ever made it so I could be wasting my time again. But everytime I see comments as in the above, a voice in my head says do something, do something. So I'll try again. Having lots of deletes and inserts of course doesn't necessarily mean fragmentation. These so-called holes are fully re-usable and in the vast majority of cases results in no substantial issues. Having lots of deletes, inserts and updates rarely requires the index to be rebuilt. Simple little demo for any newbies or those force-fed Oracle myths since child birth ... First of all, create a simple table and index. I've intentionally left a value out in the middle of a range for extra effect. SQL create table bowie_test (ziggy number); Table created. SQL insert into bowie_test values (1); 1 row created. SQL insert into bowie_test values (2); 1 row created. SQL insert into bowie_test values (3); 1 row created. SQL insert into bowie_test values (4); 1 row created. SQL insert into bowie_test values (6); 1 row created. SQL insert into bowie_test values (7); 1 row created. SQL insert into bowie_test values (8); 1 row created. SQL insert into bowie_test values (9); 1 row created. SQL insert into bowie_test values (10); 1 row created. SQL insert into bowie_test values (100); 1 row created. SQL commit; Commit complete. SQL create index bowie_test_idx on bowie_test(ziggy); Index created. Now analyze the index ... SQL analyze index bowie_test_idx validate structure; Index analyzed. and we see that everything is sweet with no wasted deleted space ... SQL select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN -- --- --- 10 0 0 We now delete a number of rows .. SQL delete bowie_test where ziggy in (2,3,4,6,7,8,9,10); 8 rows deleted. SQL commit; Commit complete. And we see that of the 10 leaf rows, 8 are deleted. As Gollum would say nasty wasted spaces it is, gollum .. SQL select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN -- --- --- 10 8 112 However, we now insert a new value (notice it's different from any previous value but obviously belongs in the same leaf node as the others) ... SQL insert into bowie_test values (5); 1 row created. SQL commit; Commit complete. SQL analyze index bowie_test_idx validate structure; Index analyzed. SQL select lf_rows, del_lf_rows, del_lf_rows_len from index_stats; LF_ROWS DEL_LF_ROWS DEL_LF_ROWS_LEN -- --- --- 3 0 0 and we see that *all* the wasted deleted space within the leaf node has been freed and is available for reuse ... With few exceptions (the key is picking those rare cases), index rebuilds are redundant, wasteful and can actually be detrimental to performance. Cheers Richard Hemant K Chitale Oracle 9i Database Administrator Certified Professional My
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Please explain why these indexes must be built. What benefits do you see from it? Are they quantifiable? Jared M Rafiq [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/14/2003 03:49 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate John What about gl_interface table indexes? I think indexes on all *interface( tables must be rebuild on a regular interval...I was building indexes on gl_interfaces and fnd_request* tables on monthly basis. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 14 Oct 2003 13:34:24 -0800 Hemant, This applies on 11i only. I would rebuild all indexes supporting the WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been working on some AOL table(space) problems in the background and noticed that in 11i by default, we are not be purging _all_ the WF data that we should be purging. I believe the current Purge routine purges activity rows whose persistence has expired and are marked 'TEMPORARY' and ignores those that are COMPLETE (see below). My contention is that it should be deleting old rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes 141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help. You could check this using the following SQLs select activity_status, count(*) from applsys.wf_item_activity_statuses group by activity_status; select item_type,activity_status,count(*) from applsys.wf_item_activity_statuses where activity_status='COMPLETE' group by item_type,activity_status; Once the 'correct' purge is complete, the 'holey' indexes will need to be rebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM to reasonable levels. Let me know what your install shows up. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Tuesday, October 14, 2003 8:39 AM To: Multiple recipients of list ORACLE-L John, I rebuild the FND_CONCURRENT_REQUESTS indexes every four months [and the table itself, occassionally]. This Saturday I will also be rebuilding some ALR indexes. Which WorkFlow Indexes do you rebuild ? Hemant At 11:44 AM 13-10-03 -0800, you wrote: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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). _ Concerned that messages may bounce because your Hotmail account has exceeded its 2MB storage limit? Get Hotmail Extra Storage! http://join.msn.com/?PAGE=features/es -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq 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 Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Not again :) At least we have to justify our pay :) Waleed Any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the company -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Wednesday, October 15, 2003 5:19 PMTo: Multiple recipients of list ORACLE-LSubject: RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: SeparatePlease explain why these indexes must be built. What benefits do you see from it? Are they quantifiable? Jared "M Rafiq" [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/14/2003 03:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: SeparateJohnWhat about gl_interface table indexes? I think indexes on all *interface( tables must be rebuild on a regular interval...I was building indexes on gl_interfaces and fnd_request* tables on monthly basis.RegardsRafiqReply-To: [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]Date: Tue, 14 Oct 2003 13:34:24 -0800Hemant,This applies on 11i only. I would rebuild all indexes supporting theWF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have beenworking on some AOL table(space) problems in the background and noticed thatin 11i by default, we are not be purging _all_ the WF data that we should bepurging. I believe the current Purge routine purges activity rows whosepersistence has expired and are marked 'TEMPORARY' and ignores those thatare COMPLETE (see below). My contention is that it should be deleting oldrows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help.You could check this using the following SQLsselect activity_status, count(*)from applsys.wf_item_activity_statusesgroup by activity_status;select item_type,activity_status,count(*)fromapplsys.wf_item_activity_statuses where activity_status='COMPLETE'group by item_type,activity_status;Once the 'correct' purge is complete, the 'holey' indexes will need to berebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM toreasonable levels.Let me know what your install shows up.John KanagarajDB Soft IncPhone: 408-970-7002 (W)Grace - Getting something we do NOT deserveMercy - NOT getting something we DO deserveClick on 'http://www.needhim.org' for Grace and Mercy that is freelyavailable!** The opinions and facts contained in this message are entirely mine and donot reflect those of my employer or customers **-Original Message-Sent: Tuesday, October 14, 2003 8:39 AMTo: Multiple recipients of list ORACLE-LJohn,I rebuild the FND_CONCURRENT_REQUESTS indexes every four months [and thetable itself, occassionally].This Saturday I will also be rebuilding some ALR indexes.Which WorkFlow Indexes do you rebuild ?HemantAt 11:44 AM 13-10-03 -0800, you wrote:--Please see the official ORACLE-L FAQ: http://www.orafaq.net--Author: John Kanagaraj INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing)._Concerned that messages may bounce because your Hotmail account has exceeded its 2MB storage limit? Get Hotmail Extra Storage! http://join.msn.com/?PAGE=features/es-- Please see the official ORACLE-L FAQ: http://www.orafaq.net-- Author: M RafiqINET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
List, The %INTERFACE% tables (usually) consist of rows that are temporary in nature. The indexes supporting them are 'fragmented' (the term can be argued I suppose). I did test this out on the GL_INTERFACE_N2 index - ANALYZE/VALIDATE and record INDEX_STATS, Rebuild index, ANALYZE/VALIDATE and record INDEX_STATS again. The figures are below, but just to highlight a few: HEIGHT (Index depth) dropped from 3 to 2; BLKS_GETS_PER_ACCESS (expected number of CR reads to get to a row) dropped from 12 to 3; the PCT_USED (percentage of space allocated that is used) increased from 38% to 99%... HEIGHT 3 2 BLOCKS 44804432 LF_ROWS 362409 22552 LF_BLKS 423075 LF_ROWS_LEN 12531538578797 LF_BLK_LEN 79487780 BR_ROWS 422974 BR_BLKS 58 1 BR_ROWS_LEN 134043 1919 BR_BLK_LEN 80288028 DEL_LF_ROWS 339857 0 DEL_LF_ROWS_LEN 119527410 DISTINCT_KEYS 20869 9548 MOST_REPEATED_KEY 38594 8430 BTREE_SPACE 34085664591528 USED_SPACE 12665581580716 PCT_USED38 99 ROWS_PER_KEY17.3659016 2.36196062 BLKS_GETS_PER_ACCESS12.1829508 3.68098031 PRE_ROWS0 0 PRE_ROWS_LEN0 0 For a detailed explanation, look at the definition of INDEX_STATS. YMMV, but you will probably get the most from Non-unique indexes... (as in this case). John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Wednesday, October 15, 2003 2:19 PM To: Multiple recipients of list ORACLE-L Please explain why these indexes must be built. What benefits do you see from it? Are they quantifiable? Jared M Rafiq [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/14/2003 03:49 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate John What about gl_interface table indexes? I think indexes on all *interface( tables must be rebuild on a regular interval...I was building indexes on gl_interfaces and fnd_request* tables on monthly basis. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 14 Oct 2003 13:34:24 -0800 Hemant, This applies on 11i only. I would rebuild all indexes supporting the WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been working on some AOL table(space) problems in the background and noticed that in 11i by default, we are not be purging _all_ the WF data that we should be purging. I believe the current Purge routine purges activity rows whose persistence has expired and are marked 'TEMPORARY' and ignores those that are COMPLETE (see below). My contention is that it should be deleting old rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes 141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help. You could check this using the following SQLs select activity_status, count(*) from applsys.wf_item_activity_statuses group by activity_status; select item_type,activity_status,count(*) from applsys.wf_item_activity_statuses where activity_status='COMPLETE' group by item_type,activity_status; Once the 'correct' purge is complete, the 'holey' indexes will need to be rebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM to reasonable levels. Let me know what your install shows up. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Tuesday, October 14, 2003 8:39 AM To: Multiple recipients of list ORACLE-L John, I rebuild the FND_CONCURRENT_REQUESTS indexes every four months [and the table itself, occassionally]. This Saturday I will also be rebuilding some ALR indexes. Which WorkFlow Indexes do you rebuild ? Hemant At 11:44 AM 13-10-03 -0800, you wrote: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Jared, Those tables are transit type of tables and depending on your volume of data, there are lot of deletes and inserts all the time resuling index fragmentation(holes due to deletes) and space usage. The rebuilding not only release the space but also reduces the index fragmentation. If you don't have table truncation option for such tables then it is much better to rebuid indexes on such tables at regular interval to release space and for better performance. As regard quantification, you many release sufficient amount of space if your usage is higher. Here it was 7.3.4 database so no LMT involved. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 15 Oct 2003 13:19:24 -0800 Please explain why these indexes must be built. What benefits do you see from it? Are they quantifiable? Jared M Rafiq [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/14/2003 03:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate John What about gl_interface table indexes? I think indexes on all *interface( tables must be rebuild on a regular interval...I was building indexes on gl_interfaces and fnd_request* tables on monthly basis. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 14 Oct 2003 13:34:24 -0800 Hemant, This applies on 11i only. I would rebuild all indexes supporting the WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been working on some AOL table(space) problems in the background and noticed that in 11i by default, we are not be purging _all_ the WF data that we should be purging. I believe the current Purge routine purges activity rows whose persistence has expired and are marked 'TEMPORARY' and ignores those that are COMPLETE (see below). My contention is that it should be deleting old rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes 141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help. You could check this using the following SQLs select activity_status, count(*) from applsys.wf_item_activity_statuses group by activity_status; select item_type,activity_status,count(*) from applsys.wf_item_activity_statuses where activity_status='COMPLETE' group by item_type,activity_status; Once the 'correct' purge is complete, the 'holey' indexes will need to be rebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM to reasonable levels. Let me know what your install shows up. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Tuesday, October 14, 2003 8:39 AM To: Multiple recipients of list ORACLE-L John, I rebuild the FND_CONCURRENT_REQUESTS indexes every four months [and the table itself, occassionally]. This Saturday I will also be rebuilding some ALR indexes. Which WorkFlow Indexes do you rebuild ? Hemant At 11:44 AM 13-10-03 -0800, you wrote: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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). _ Concerned that messages may bounce because your Hotmail account has exceeded its 2MB storage limit? Get Hotmail Extra Storage! http://join.msn.com/?PAGE=features/es -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq 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). _ Concerned that messages may bounce
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
John Thanks foe detailed explanation. Regards Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 15 Oct 2003 14:34:47 -0800 List, The %INTERFACE% tables (usually) consist of rows that are temporary in nature. The indexes supporting them are 'fragmented' (the term can be argued I suppose). I did test this out on the GL_INTERFACE_N2 index - ANALYZE/VALIDATE and record INDEX_STATS, Rebuild index, ANALYZE/VALIDATE and record INDEX_STATS again. The figures are below, but just to highlight a few: HEIGHT (Index depth) dropped from 3 to 2; BLKS_GETS_PER_ACCESS (expected number of CR reads to get to a row) dropped from 12 to 3; the PCT_USED (percentage of space allocated that is used) increased from 38% to 99%... HEIGHT 3 2 BLOCKS 44804432 LF_ROWS 362409 22552 LF_BLKS 423075 LF_ROWS_LEN 12531538578797 LF_BLK_LEN 79487780 BR_ROWS 422974 BR_BLKS 58 1 BR_ROWS_LEN 134043 1919 BR_BLK_LEN 80288028 DEL_LF_ROWS 339857 0 DEL_LF_ROWS_LEN 119527410 DISTINCT_KEYS 20869 9548 MOST_REPEATED_KEY 38594 8430 BTREE_SPACE 34085664591528 USED_SPACE 12665581580716 PCT_USED38 99 ROWS_PER_KEY17.3659016 2.36196062 BLKS_GETS_PER_ACCESS12.1829508 3.68098031 PRE_ROWS0 0 PRE_ROWS_LEN0 0 For a detailed explanation, look at the definition of INDEX_STATS. YMMV, but you will probably get the most from Non-unique indexes... (as in this case). John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Wednesday, October 15, 2003 2:19 PM To: Multiple recipients of list ORACLE-L Please explain why these indexes must be built. What benefits do you see from it? Are they quantifiable? Jared M Rafiq [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/14/2003 03:49 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate John What about gl_interface table indexes? I think indexes on all *interface( tables must be rebuild on a regular interval...I was building indexes on gl_interfaces and fnd_request* tables on monthly basis. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 14 Oct 2003 13:34:24 -0800 Hemant, This applies on 11i only. I would rebuild all indexes supporting the WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been working on some AOL table(space) problems in the background and noticed that in 11i by default, we are not be purging _all_ the WF data that we should be purging. I believe the current Purge routine purges activity rows whose persistence has expired and are marked 'TEMPORARY' and ignores those that are COMPLETE (see below). My contention is that it should be deleting old rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes 141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help. You could check this using the following SQLs select activity_status, count(*) from applsys.wf_item_activity_statuses group by activity_status; select item_type,activity_status,count(*) from applsys.wf_item_activity_statuses where activity_status='COMPLETE' group by item_type,activity_status; Once the 'correct' purge is complete, the 'holey' indexes will need to be rebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM to reasonable levels. Let me know what your install shows up. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Tuesday, October 14, 2003 8:39 AM To: Multiple recipients of list ORACLE-L John, I rebuild the FND_CONCURRENT_REQUESTS indexes every four months [and the table itself, occassionally]. This Saturday I will also be rebuilding some ALR indexes. Which WorkFlow Indexes do you rebuild ? Hemant At 11:44 AM 13-10-03 -0800, you wrote: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
The 'better performance' part is what I would like to see some metrics on. How much better? Is it worth the trouble? If your indexes continually build up to the same size, what is being gained by saving some space for a period of time? Thanks, Jared On Wed, 2003-10-15 at 18:04, M Rafiq wrote: Jared, Those tables are transit type of tables and depending on your volume of data, there are lot of deletes and inserts all the time resuling index fragmentation(holes due to deletes) and space usage. The rebuilding not only release the space but also reduces the index fragmentation. If you don't have table truncation option for such tables then it is much better to rebuid indexes on such tables at regular interval to release space and for better performance. As regard quantification, you many release sufficient amount of space if your usage is higher. Here it was 7.3.4 database so no LMT involved. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Wed, 15 Oct 2003 13:19:24 -0800 Please explain why these indexes must be built. What benefits do you see from it? Are they quantifiable? Jared M Rafiq [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/14/2003 03:49 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate John What about gl_interface table indexes? I think indexes on all *interface( tables must be rebuild on a regular interval...I was building indexes on gl_interfaces and fnd_request* tables on monthly basis. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 14 Oct 2003 13:34:24 -0800 Hemant, This applies on 11i only. I would rebuild all indexes supporting the WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been working on some AOL table(space) problems in the background and noticed that in 11i by default, we are not be purging _all_ the WF data that we should be purging. I believe the current Purge routine purges activity rows whose persistence has expired and are marked 'TEMPORARY' and ignores those that are COMPLETE (see below). My contention is that it should be deleting old rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes 141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help. You could check this using the following SQLs select activity_status, count(*) from applsys.wf_item_activity_statuses group by activity_status; select item_type,activity_status,count(*) from applsys.wf_item_activity_statuses where activity_status='COMPLETE' group by item_type,activity_status; Once the 'correct' purge is complete, the 'holey' indexes will need to be rebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM to reasonable levels. Let me know what your install shows up. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Tuesday, October 14, 2003 8:39 AM To: Multiple recipients of list ORACLE-L John, I rebuild the FND_CONCURRENT_REQUESTS indexes every four months [and the table itself, occassionally]. This Saturday I will also be rebuilding some ALR indexes. Which WorkFlow Indexes do you rebuild ? Hemant At 11:44 AM 13-10-03 -0800, you wrote: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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). _ Concerned that messages may bounce because your Hotmail account has exceeded its 2MB storage limit? Get Hotmail Extra Storage! http://join.msn.com/?PAGE=features/es -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
John, I rebuild the FND_CONCURRENT_REQUESTS indexes every four months [and the table itself, occassionally]. This Saturday I will also be rebuilding some ALR indexes. Which WorkFlow Indexes do you rebuild ? Hemant At 11:44 AM 13-10-03 -0800, you wrote: Jared, Any indexes supporting a In-Today; Gone-Tomorrow status table will require index rebuilds. Most of them have monotonically increasing numbers which lends itself to a 'holey' index... (I have a bunch of them with Oracle Apps Concurrent Manager and Workflow tables) John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Monday, October 13, 2003 11:39 AM To: Multiple recipients of list ORACLE-L Subject: RE: RE: Separate Indexes and Data hmmm... fodder for an article I've been contemplating. Indexes: to rebuild or not to rebuild - that is the question There's no need to reclaim space, except in special circumstances. As Kirti pointed out once, a sequentially incrementing numeric key is possibly one of those circumstances. Not much point in rebuilding indexes in most cases. If anyone cares to submit test cases for validation of the need of an index rebuild, you may do so here. Give me some test fodder! Jared [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 10/13/2003 08:59 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: RE: RE: Separate Indexes and Data I assume that what Rachel is referring to is the fact that indexes will generally not release much space when the underlying rows are deleted. They just keep growing, so if you have a large indexed table that frequently deletes and inserts the indexes can grow to fairly ridiculous sizes over a period of time. We just went through the exercise of rebuilding indexes on a db supporting a 3rd party app and reclaimed about 70% of the allocated index space. Jay Miller Sr. Oracle DBA x68355 -Original Message- Sent: Sunday, October 12, 2003 7:39 AM To: Multiple recipients of list ORACLE-L Hi Rachael, You have me a little confused here. What do you mean by We over allocate space ? To the index segments or to the tablespace ? Why the need to rebuild the indexes ? How are they using more space than required ? What do you mean that you adjust the pctfree so you can determine how small you can resize them to ? You seem to go to a lot of trouble, I'm just failing to see what it all achieves ??? Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 4:34 AM Nuh uh, not me... I have never used or experimented with auto-allocate. I separate indexes and tables so that I can reclaim space by rebuilding the indexes into smaller space. I've just completed writing the scripts for the following: we have a data warehouse, partitioned on the biggest table on date by month. There are 10 or 11 indexes on this table. We overallocate space when we create the new partition for the next month. Data is loaded daily. The hosting company has an automated procedure to add space to the datafile if the used space percentage is greater than some number (we get charged each time they do this, and they never allocate enough space so they do it over and over towards the end of the month). since the indexes are increasing on a daily basis, we overallocate the space. The next month, I go out, determine the partition/tablespace/datafiles that need to be resized (naming standards rule in this case), rebuild the indexes into an interim tablespace, rebuild them back to the original one with a smaller pctfree and then determine how small I can resize them down to. If there were table data in these tablespaces, I'd be out of luck on trying to reclaim space --- [EMAIL PROTECTED] wrote: the defrag paper was written back in 1998 I believe. Uniform extents were a good solution pre-9i. We use them here on our 8i databases. I stick with an uniform 5m extent size even though I have tables that can fit into 128k extents, but feel that the overall time savings by using 1 extent size makes up for this. unfortunately unlike most systems we cannot break up our tables into different tablespaces. We use transportable tablespaces to batch publish data to data marts. New tablespaces mean additional transportable tablespaces and more places for stuff to go wrong. I saw some posts on dejanews recently from some pretty experienced DBAs stating that there may be 'flaws' in auto-allocate leading to poor extent sizes that leads to fragmentation. I believe Rachel Carmichael made a post on here a few months back with the similiar
RE: re Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Hemant, This applies on 11i only. I would rebuild all indexes supporting the WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been working on some AOL table(space) problems in the background and noticed that in 11i by default, we are not be purging _all_ the WF data that we should be purging. I believe the current Purge routine purges activity rows whose persistence has expired and are marked 'TEMPORARY' and ignores those that are COMPLETE (see below). My contention is that it should be deleting old rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes 141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help. You could check this using the following SQLs select activity_status, count(*) from applsys.wf_item_activity_statuses group by activity_status; select item_type,activity_status,count(*) from applsys.wf_item_activity_statuses where activity_status='COMPLETE' group by item_type,activity_status; Once the 'correct' purge is complete, the 'holey' indexes will need to be rebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM to reasonable levels. Let me know what your install shows up. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Tuesday, October 14, 2003 8:39 AM To: Multiple recipients of list ORACLE-L John, I rebuild the FND_CONCURRENT_REQUESTS indexes every four months [and the table itself, occassionally]. This Saturday I will also be rebuilding some ALR indexes. Which WorkFlow Indexes do you rebuild ? Hemant At 11:44 AM 13-10-03 -0800, you wrote: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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 Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
John What about gl_interface table indexes? I think indexes on all *interface( tables must be rebuild on a regular interval...I was building indexes on gl_interfaces and fnd_request* tables on monthly basis. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 14 Oct 2003 13:34:24 -0800 Hemant, This applies on 11i only. I would rebuild all indexes supporting the WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been working on some AOL table(space) problems in the background and noticed that in 11i by default, we are not be purging _all_ the WF data that we should be purging. I believe the current Purge routine purges activity rows whose persistence has expired and are marked 'TEMPORARY' and ignores those that are COMPLETE (see below). My contention is that it should be deleting old rows that are COMPLETEd... (Fyi, this is 12+ million rows...) Notes 141853.1, 144806.1, 132254.1, 148705.1, 148678.1 may help. You could check this using the following SQLs select activity_status, count(*) from applsys.wf_item_activity_statuses group by activity_status; select item_type,activity_status,count(*) from applsys.wf_item_activity_statuses where activity_status='COMPLETE' group by item_type,activity_status; Once the 'correct' purge is complete, the 'holey' indexes will need to be rebuilt and the WF_ tables copied/truncated/recopied to shrink the HWM to reasonable levels. Let me know what your install shows up. John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Grace - Getting something we do NOT deserve Mercy - NOT getting something we DO deserve Click on 'http://www.needhim.org' for Grace and Mercy that is freely available! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- Sent: Tuesday, October 14, 2003 8:39 AM To: Multiple recipients of list ORACLE-L John, I rebuild the FND_CONCURRENT_REQUESTS indexes every four months [and the table itself, occassionally]. This Saturday I will also be rebuilding some ALR indexes. Which WorkFlow Indexes do you rebuild ? Hemant At 11:44 AM 13-10-03 -0800, you wrote: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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). _ Concerned that messages may bounce because your Hotmail account has exceeded its 2MB storage limit? Get Hotmail Extra Storage! http://join.msn.com/?PAGE=features/es -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq 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 Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Rafiq, John What about gl_interface table indexes? I think indexes on all *interface( tables must be rebuild on a regular interval...I was building indexes on gl_interfaces and fnd_request* tables on monthly basis. Indeed the interface tables suffer as well. I would suggest a TRUNCate of these tables after processing monthend (or at an agreed time with the users), so the index will be chopped as well John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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 Rebuilding Indexes in Oracle Apps
John At my location I was not finding those tables without rows so simple truncate was not the easy option. However, from time to time I was truncating them by removing rows into temp type of tables and placing those rows back. However, index rebuilding was more practical under that situation. Truncating gl_interface table was also reducing HWM for better performance. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: Tue, 14 Oct 2003 17:29:25 -0800 Rafiq, John What about gl_interface table indexes? I think indexes on all *interface( tables must be rebuild on a regular interval...I was building indexes on gl_interfaces and fnd_request* tables on monthly basis. Indeed the interface tables suffer as well. I would suggest a TRUNCate of these tables after processing monthend (or at an agreed time with the users), so the index will be chopped as well John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: John Kanagaraj 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). _ Concerned that messages may bounce because your Hotmail account has exceeded its 2MB storage limit? Get Hotmail Extra Storage! http://join.msn.com/?PAGE=features/es -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: M Rafiq 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 Rebuilding Indexes in Oracle Apps -- was RE: RE: Separate
Hi, I do rebuild index for table AP_INVOICES_ALL but it seems that no effect on extents. select owner, segment_name, tablespace_name, count(*), sum(bytes) 2 from sys.dba_extents 3 where segment_name like 'AP_INVOICES_N3' and tablespace_name='APX' 4 group by owner, segment_name, tablespace_name result : AP AP_INVOICES_N3 APX 45 46202880 and then : alter index AP.AP_INVOICES_N3 2* rebuild compute statistics online nologging tablespace APX but the extents still as above. any advice? regards On Tue, 14 Oct 2003, John Kanagaraj wrote: Hemant, This applies on 11i only. I would rebuild all indexes supporting the WF_ITEM_ACTIVITY_STATUSES and WF_ATTRIBUTE_VALUES tables. I have been -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: hernawan 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: Rebuilding Indexes...
Jared, thanks for the asktom article, very interesting reading - I'm not 100% sure I agree with him though.I just recently went through the exercise to rebuild our indexes into locally-managed tablespaces and I saved over 100GB of space through the rebuild process - as an example I had a 51GB unique index that went to 30GB after the rebuild (I'm not implying locally-managed had anything to do with the space savings, that was just for reducing/eliminating fragmentation).The indexes had not been rebuilt in over a year. Our DSS database is next; I expect to save at least that plus more in space.Not to discount Tom's arguments but I was very happy with the space savings and depending on your environment that can be very helpful/worthwhile. Just my opinion - guess it depends on your objectives. This discussion has definitely got me thinking in some different directions which is always a good thing. thanks again, John D. Atlanta, GA Jared Still [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] om cc: (bcc: John Dailey/NAC/ING-FSI-NA) Sent by: Subject: Re: Rebuilding Indexes... [EMAIL PROTECTED] 12/26/2002 10:13 PM Please respond to ORACLE-L Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: 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: Rebuilding Indexes...
The space saving is good if you are not planning on doing much more with those indexes (ie dml). The space saving might be very very bad if there is lots of dml to come. If you're indexes reached equilibrium during normal operation of (say) 70% used, and then you rebuild them with (say) pctfree 10, they pop up to 90% used and thus the saved space. But if normal operations continue, they will eventually drift out to 70% used again and what's worse, you might get a bucketload of index block splits during the process...could be a nasty hit on dml performance hth connor --- [EMAIL PROTECTED] wrote: Jared, thanks for the asktom article, very interesting reading - I'm not 100% sure I agree with him though.I just recently went through the exercise to rebuild our indexes into locally-managed tablespaces and I saved over 100GB of space through the rebuild process - as an example I had a 51GB unique index that went to 30GB after the rebuild (I'm not implying locally-managed had anything to do with the space savings, that was just for reducing/eliminating fragmentation).The indexes had not been rebuilt in over a year. Our DSS database is next; I expect to save at least that plus more in space.Not to discount Tom's arguments but I was very happy with the space savings and depending on your environment that can be very helpful/worthwhile. Just my opinion - guess it depends on your objectives. This discussion has definitely got me thinking in some different directions which is always a good thing. thanks again, John D. Atlanta, GA Jared Still [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] om cc: (bcc: John Dailey/NAC/ING-FSI-NA) Sent by: Subject: Re: Rebuilding Indexes... [EMAIL PROTECTED] 12/26/2002 10:13 PM Please respond to ORACLE-L Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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
Re: Rebuilding Indexes...
Ditto. Conner did a better job of stating it than I was going to. :) Jared On Monday 30 December 2002 11:08, Connor McDonald wrote: The space saving is good if you are not planning on doing much more with those indexes (ie dml). The space saving might be very very bad if there is lots of dml to come. If you're indexes reached equilibrium during normal operation of (say) 70% used, and then you rebuild them with (say) pctfree 10, they pop up to 90% used and thus the saved space. But if normal operations continue, they will eventually drift out to 70% used again and what's worse, you might get a bucketload of index block splits during the process...could be a nasty hit on dml performance hth connor --- [EMAIL PROTECTED] wrote: Jared, thanks for the asktom article, very interesting reading - I'm not 100% sure I agree with him though.I just recently went through the exercise to rebuild our indexes into locally-managed tablespaces and I saved over 100GB of space through the rebuild process - as an example I had a 51GB unique index that went to 30GB after the rebuild (I'm not implying locally-managed had anything to do with the space savings, that was just for reducing/eliminating fragmentation).The indexes had not been rebuilt in over a year. Our DSS database is next; I expect to save at least that plus more in space.Not to discount Tom's arguments but I was very happy with the space savings and depending on your environment that can be very helpful/worthwhile. Just my opinion - guess it depends on your objectives. This discussion has definitely got me thinking in some different directions which is always a good thing. thanks again, John D. Atlanta, GA Jared Still [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] om cc: (bcc: John Dailey/NAC/ING-FSI-NA) Sent by: Subject: Re: Rebuilding Indexes... [EMAIL PROTECTED] 12/26/2002 10:13 PM Please respond to ORACLE-L Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: 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). = Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET
Re: Rebuilding Indexes...
phew! I'm not the only one who doesn't know stuff? Thank you for salvaging my ego! Rachel --- Jared Still [EMAIL PROTECTED] wrote: Ohhh, something else I didn't know yet! Which is why I keep reading this list. That, and playing referee. ;) Jared On Saturday 28 December 2002 17:48, Arup Nanda wrote: In 9.2, you can keep the index by using the KEEP INDEX key words. ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX This will keep the index but drop the constraint. Talk about having your cake and eating it too...;) HTH Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 27, 2002 4:39 PM it'll have to wait until Monday, I'm not at work until then. I'll try it with a non-unique then Hey, if it works, it saves me tons of time, I learn something new and I had fun developing the single SQL statement to rebuild the constraint and index. Win-win Rachel --- Denny Koovakattu [EMAIL PROTECTED] wrote: I don't have access to 9.2.0.1 right now. But can you try creating a non- unique index instead of the unique index. If you create a unique index, it gets dropped. That's the behavior on 8.1.x also. But if it's a non-unique index, it shouldn't get dropped. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: 9.2.0.1 Solaris, and yes, it does drop it I created a unique index in the primary key columns I created the primary key constraint without specifying an index I checked that the index existed, it did I dropped the primary key constraint I checked that the index existed, it didn't try it I tried various combinations before posting this note --- Denny Koovakattu [EMAIL PROTECTED] wrote: If you build a separate index to enforce the primary key, Oracle shouldn't drop it when you disable or drop the primary key. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: Here's a reason: have you ever tried to find the three duplicate rows in a 12 million row table without using the primary key constraint? I've had to disable or drop the constraint in order to use the exceptions table. Once I do that, even if I've built a separate index that enforces the primary key constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow the index to be rebuilt when I re-enable the primary key constraint, it builds it in the default tablespace of the table owner, not where I want it. if anyone has a better way to fix this problem, I'm more than happy to hear it! It's a data warehouse and the third party app has a bug we can't find and on occasion sqlloads (via direct path) duplicate rows Rachel --- Jared Still [EMAIL PROTECTED] wrote: Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services === message truncated === __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: Rebuilding Indexes...
Arup, Excellent practice! I have writtenmuch the same scripts, except I use them as an extension to STATSPACK and named the resulting persistenttable CSTATS$INDEX_STATS. Nevertheless, I'm curious about the formula and will test it out. I'm a little concerned about the HEIGHT 3 in the WHERE clause, as even "small" indexes can go awry(i.e. HEIGHT = 3 can still involve hundreds of thousands of rows and thousands of blocks)... Another use for data from INDEX_STATS -- when the value in the BLKS_GETS_PER_ACCESS columnexceeds several hundred or severalthousand blocks, you have to question the effectiveness ofthe B*Tree index itselfand whether or not it should be dropped. Chances are good that the CBO is ignoring it anyway, so you are paying for the storage costs of the index and the processing costs of maintaining it, but not using it. By no means is it open-and-shut that the index should be dropped -- there might be SQL statements effcientlyusing the index to take advantage of skewed data distribution -- but it should be researched and considered for the old "drop kick" nonetheless. Thanks again! -Tim - Original Message - From: Arup Nanda To: Multiple recipients of list ORACLE-L Sent: Saturday, December 28, 2002 9:08 PM Subject: Re: Rebuilding Indexes... Jared, Did you attach the scripts? I use the index rebuildingregularly for certain applications where buffer busy waitsare prevalent. No, let's not go there why the buffer busy waits occur and whether reverse key indexes would help. All these arepaths well trodden. I use a home grown setup where I ANALYZE VALIDATE STRUCTURE each index and immediately store the INDEX_STATS rw in a table called INDCHK_INDEX_STATS. Then I use the following script to identify the potential indexes candidate for rebuilding. The Height, "Compression Factor", Delete%and "Hole Factor" as calculated below provide an indication whether the index can be considered to be rebuilt. There is no hard threshold value for each, based on all three, I decide whether the index needs to be rebuilt. Finally, how did I come up with the seemingly labyrinthine formulae below?Parts of themare "stolen" from the OEM tool's index check program. I snooped around when the tool was analyzing the indexes and captured the code, modified to some extent and placed in a nice script. It works for me. The indexes are placed in LMT with non-uniform extents and the database is 8.1.7.4. Yes, I know this will probably spark all sorts of reaction; but I would appreciate any feedback on the process. Arup Nanda col name format a30 head "Index Name"col comp_factor head "Compactness"col hole_factor format head "Hole"col del_pct format head "Del%"col height format 9 head "Height"SELECT NAME, HEIGHT, DECODE(HEIGHT, 1, 100, FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100) / (LF_BLK_LEN * LF_BLKS))) Comp_Factor, DECODE(HEIGHT, 1, 0, DECODE(LF_ROWS - DEL_LF_ROWS, 0, 1, DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN / BR_ROWS), LF_BLK_LEN / ((LF_ROWS_LEN - DEL_LF_ROWS_LEN) / (LF_ROWS - DEL_LF_ROWS) - HEIGHT, -1, 1, 0))) +DECODE(LF_ROWS_LEN, 0, 0, FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100)) Hole_Factor,round(decode(lf_rows,0,0,100*DEL_LF_ROWS/LF_ROWS),0) del_pctFROM INDCHK_INDEX_STATSwhere height 3or DECODE(HEIGHT, 1, 100, FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100) / (LF_BLK_LEN * LF_BLKS))) 80or DECODE(HEIGHT, 1, 0, DECODE(LF_ROWS - DEL_LF_ROWS, 0, 1, DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN / BR_ROWS), LF_BLK_LEN / ((LF_ROWS_LEN - DEL_LF_ROWS_LEN) / (LF_ROWS - DEL_LF_ROWS) - HEIGHT, -1, 1, 0))) + DECODE(LF_ROWS_LEN, 0, 0, FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100)) 10ordecode(lf_rows,0,0,100*DEL_LF_ROWS/LF_ROWS) 9order by 3 desc, 2, 1/ - Original Message - From: "Jared Still" [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Thursday, December 26, 2002 10:13 PM Subject: Re: Rebuilding Indexes... Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" Content-Transfer-Encoding: 7bit Conten
Re: Rebuilding Indexes, Now: KEEP INDEX
On the contrary, Connor, it's of tremendous political value, if not practical ;) Besides, the fact that unique indexes may have null values whereas PKs can't. I have found the use in this case: In the US, we all have a Social Security Number (SSN), a sort of like a birth mark ever since we were born. An insurance carrier might have the SSN as a primary key and that would work out fine, till one fine day they decide to accept visitors in a new plan for temporary health insurance coverage. These people have no SSN, making it null. Suddenly the PK on SSN was dropped and a synthetic key containing SSN and a sequence number (9 for SSN is null) was chosen as SSN; but the SSN index was kept since that was unique anyway. With 40 GB tables (in all) this option to keep index came pretty handy. Thanks. Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 29, 2002 3:23 PM I'm a little doubtful about the value of 'keep index'. Consider the scenarios: unique constraint, non-unique index: - keep index redundant because its kept anyway unique constraint, unique index: - keep index redundant because effectively retains the constraint anyway (because you still can't insert dups) So far, the only use for KEEP INDEX I've found is the scenario where you: - decided that column(s) X was the primary key - created a unique index on it - created a primary key constraint on it - loaded the data - decided actually X was NOT the primary key, just a unique value - decided that X could allow nulls as well - dropped the primary kept, kept the index and then added a unique constraint... I would contend that this is a rare occurrence ? Cheers Connor --- Rachel Carmichael [EMAIL PROTECTED] wrote: sigh. I need to find time to read ALL the docs. Yeah, that'll happen. If I can find a parallel universe where time runs at a different rate. Thanks, I'll test this out as well. --- Arup Nanda [EMAIL PROTECTED] wrote: In 9.2, you can keep the index by using the KEEP INDEX key words. ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX This will keep the index but drop the constraint. Talk about having your cake and eating it too...;) HTH Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 27, 2002 4:39 PM it'll have to wait until Monday, I'm not at work until then. I'll try it with a non-unique then Hey, if it works, it saves me tons of time, I learn something new and I had fun developing the single SQL statement to rebuild the constraint and index. Win-win Rachel --- Denny Koovakattu [EMAIL PROTECTED] wrote: I don't have access to 9.2.0.1 right now. But can you try creating a non- unique index instead of the unique index. If you create a unique index, it gets dropped. That's the behavior on 8.1.x also. But if it's a non-unique index, it shouldn't get dropped. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: 9.2.0.1 Solaris, and yes, it does drop it I created a unique index in the primary key columns I created the primary key constraint without specifying an index I checked that the index existed, it did I dropped the primary key constraint I checked that the index existed, it didn't try it I tried various combinations before posting this note --- Denny Koovakattu [EMAIL PROTECTED] wrote: If you build a separate index to enforce the primary key, Oracle shouldn't drop it when you disable or drop the primary key. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: Here's a reason: have you ever tried to find the three duplicate rows in a 12 million row table without using the primary key constraint? I've had to disable or drop the constraint in order to use the exceptions table. Once I do that, even if I've built a separate index that enforces the primary key constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow the index to be rebuilt when I re-enable the primary key constraint, it builds it in the default tablespace of the table owner, not where I want it. if anyone has a better way to fix this problem, I'm more than happy to hear it! It's a data warehouse and the third party app has a bug we can't find and on occasion sqlloads (via direct path) duplicate rows
Re: Rebuilding Indexes...
Tim,
Thanks for the compliments. You are right, HEIGHT
3 is not correct. I lifted it off another place where I temporarily used
the HEIGHT 3 predicate; in the version I always use, height is not in the
filter. Again, just because height , itwould not indicate a case
for rebuilding; rather a subjective evaluation of all four factors presented
would be inputs for decision making.
The BLKS_GETS_PER_ACCESS part is interesting; I
will research that. Thanks for sharing that information.
Arup
- Original Message -
From:
Tim Gorman
To: Multiple recipients of list ORACLE-L
Sent: Sunday, December 29, 2002 1:33
PM
Subject: Re: Rebuilding Indexes...
Arup,
Excellent practice! I have
writtenmuch the same scripts, except I use them as an extension to
STATSPACK and named the resulting persistenttable
CSTATS$INDEX_STATS. Nevertheless, I'm curious about the formula and will
test it out. I'm a little concerned about the HEIGHT 3 in the WHERE
clause, as even "small" indexes can go awry(i.e. HEIGHT = 3 can
still involve hundreds of thousands of rows and thousands of
blocks)...
Another use for data from INDEX_STATS -- when the
value in the BLKS_GETS_PER_ACCESS columnexceeds several hundred or
severalthousand blocks, you have to question the effectiveness
ofthe B*Tree index itselfand whether or not it should be
dropped. Chances are good that the CBO is ignoring it anyway, so you are
paying for the storage costs of the index and the processing costs of
maintaining it, but not using it. By no means is it open-and-shut that
the index should be dropped -- there might be SQL statements
effcientlyusing the index to take advantage of skewed data distribution
-- but it should be researched and considered for the old "drop kick"
nonetheless.
Thanks again!
-Tim
- Original Message -
From:
Arup
Nanda
To: Multiple recipients of list ORACLE-L
Sent: Saturday, December 28, 2002 9:08
PM
Subject: Re: Rebuilding
Indexes...
Jared,
Did you attach the scripts?
I use the index rebuildingregularly for
certain applications where buffer busy waitsare prevalent. No, let's
not go there why the buffer busy waits occur and whether reverse key indexes
would help. All these arepaths well trodden. I use a home grown setup
where I ANALYZE VALIDATE STRUCTURE each index and immediately store the
INDEX_STATS rw in a table called INDCHK_INDEX_STATS. Then I use the
following script to identify the potential indexes candidate for rebuilding.
The Height, "Compression Factor", Delete%and "Hole Factor" as
calculated below provide an indication whether the index can be considered
to be rebuilt. There is no hard threshold value for each, based on all
three, I decide whether the index needs to be rebuilt.
Finally, how did I come up with the seemingly
labyrinthine formulae below?Parts of themare "stolen" from the
OEM tool's index check program. I snooped around when the tool was analyzing
the indexes and captured the code, modified to some extent and placed in a
nice script. It works for me. The indexes are placed in LMT with non-uniform
extents and the database is 8.1.7.4.
Yes, I know this will probably spark all sorts
of reaction; but I would appreciate any feedback on the
process.
Arup Nanda
col name format a30 head "Index Name"col
comp_factor head "Compactness"col hole_factor format head
"Hole"col del_pct format head "Del%"col height format 9
head "Height"SELECT NAME, HEIGHT, DECODE(HEIGHT, 1, 100,
FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100) /
(LF_BLK_LEN * LF_BLKS))) Comp_Factor,
DECODE(HEIGHT, 1, 0, DECODE(LF_ROWS - DEL_LF_ROWS, 0, 1,
DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN / BR_ROWS),
LF_BLK_LEN / ((LF_ROWS_LEN -
DEL_LF_ROWS_LEN) / (LF_ROWS - DEL_LF_ROWS) - HEIGHT, -1,
1, 0))) +DECODE(LF_ROWS_LEN, 0, 0,
FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100))
Hole_Factor,round(decode(lf_rows,0,0,100*DEL_LF_ROWS/LF_ROWS),0)
del_pctFROM INDCHK_INDEX_STATSwhere height 3or
DECODE(HEIGHT, 1, 100,
FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100)
/ (LF_BLK_LEN *
LF_BLKS))) 80or DECODE(HEIGHT, 1, 0, DECODE(LF_ROWS -
DEL_LF_ROWS, 0, 1,
DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN /
BR_ROWS), LF_BLK_LEN
/ ((LF_ROWS_LEN -
DEL_LF_ROWS_LEN) / (LF_ROWS -
DEL_LF_ROWS) - HEIGHT, -1, 1, 0)))
+ DECODE(LF_ROWS_LEN, 0,
0,
FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100))
10ordecode(lf_rows,0,0,100*DEL_LF_ROWS/LF_ROWS) 9order by
3 desc, 2, 1/
- Original Message -
From: "Jare
Re: Rebuilding Indexes...
On Saturday 28 December 2002 20:08, Arup Nanda wrote: Jared, Did you attach the scripts? No, but they're free to download at http://www.oreilly.com/catalog/oracleperl/pdbatoolkit/ww.oreilly.com/catalog/oracleperl/. The script in question is idxr.pl. The algorithm was lifted ( with credit ) from the famous 'How to stop defragmenting...' paper. By the way, the scripts are all Perl. :) Jared I use the index rebuilding regularly for certain applications where buffer busy waits are prevalent. No, let's not go there why the buffer busy waits occur and whether reverse key indexes would help. All these are paths well trodden. I use a home grown setup where I ANALYZE VALIDATE STRUCTURE each index and immediately store the INDEX_STATS rw in a table called INDCHK_INDEX_STATS. Then I use the following script to identify the potential indexes candidate for rebuilding. The Height, Compression Factor, Delete% and Hole Factor as calculated below provide an indication whether the index can be considered to be rebuilt. There is no hard threshold value for each, based on all three, I decide whether the index needs to be rebuilt. Finally, how did I come up with the seemingly labyrinthine formulae below? Parts of them are stolen from the OEM tool's index check program. I snooped around when the tool was analyzing the indexes and captured the code, modified to some extent and placed in a nice script. It works for me. The indexes are placed in LMT with non-uniform extents and the database is 8.1.7.4. Yes, I know this will probably spark all sorts of reaction; but I would appreciate any feedback on the process. Arup Nanda col name format a30 head Index Name col comp_factor head Compactness col hole_factor format head Hole col del_pct format head Del% col height format 9 head Height SELECT NAME, HEIGHT, DECODE(HEIGHT, 1, 100, FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100) / (LF_BLK_LEN * LF_BLKS))) Comp_Factor, DECODE(HEIGHT, 1, 0, DECODE(LF_ROWS - DEL_LF_ROWS, 0, 1, DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN / BR_ROWS), LF_BLK_LEN / ((LF_ROWS_LEN - DEL_LF_ROWS_LEN) / (LF_ROWS - DEL_LF_ROWS) - HEIGHT, -1, 1, 0))) + DECODE(LF_ROWS_LEN, 0, 0, FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100)) Hole_Factor, round(decode(lf_rows,0,0,100*DEL_LF_ROWS/LF_ROWS),0) del_pct FROM INDCHK_INDEX_STATS where height 3 or DECODE(HEIGHT, 1, 100, FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100) / (LF_BLK_LEN * LF_BLKS))) 80 or DECODE(HEIGHT, 1, 0, DECODE(LF_ROWS - DEL_LF_ROWS, 0, 1, DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN / BR_ROWS), LF_BLK_LEN / ((LF_ROWS_LEN - DEL_LF_ROWS_LEN) / (LF_ROWS - DEL_LF_ROWS) - HEIGHT, -1, 1, 0))) + DECODE(LF_ROWS_LEN, 0, 0, FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100)) 10 or decode(lf_rows,0,0,100*DEL_LF_ROWS/LF_ROWS) 9 order by 3 desc, 2, 1 / - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, December 26, 2002 10:13 PM Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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). Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: quoted-printable Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: Rebuilding Indexes...
In 9.2, you can keep the index by using the KEEP INDEX key words. ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX This will keep the index but drop the constraint. Talk about having your cake and eating it too...;) HTH Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 27, 2002 4:39 PM it'll have to wait until Monday, I'm not at work until then. I'll try it with a non-unique then Hey, if it works, it saves me tons of time, I learn something new and I had fun developing the single SQL statement to rebuild the constraint and index. Win-win Rachel --- Denny Koovakattu [EMAIL PROTECTED] wrote: I don't have access to 9.2.0.1 right now. But can you try creating a non- unique index instead of the unique index. If you create a unique index, it gets dropped. That's the behavior on 8.1.x also. But if it's a non-unique index, it shouldn't get dropped. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: 9.2.0.1 Solaris, and yes, it does drop it I created a unique index in the primary key columns I created the primary key constraint without specifying an index I checked that the index existed, it did I dropped the primary key constraint I checked that the index existed, it didn't try it I tried various combinations before posting this note --- Denny Koovakattu [EMAIL PROTECTED] wrote: If you build a separate index to enforce the primary key, Oracle shouldn't drop it when you disable or drop the primary key. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: Here's a reason: have you ever tried to find the three duplicate rows in a 12 million row table without using the primary key constraint? I've had to disable or drop the constraint in order to use the exceptions table. Once I do that, even if I've built a separate index that enforces the primary key constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow the index to be rebuilt when I re-enable the primary key constraint, it builds it in the default tablespace of the table owner, not where I want it. if anyone has a better way to fix this problem, I'm more than happy to hear it! It's a data warehouse and the third party app has a bug we can't find and on occasion sqlloads (via direct path) duplicate rows Rachel --- Jared Still [EMAIL PROTECTED] wrote: Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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
Re: Rebuilding Indexes...
sigh. I need to find time to read ALL the docs. Yeah, that'll happen. If I can find a parallel universe where time runs at a different rate. Thanks, I'll test this out as well. --- Arup Nanda [EMAIL PROTECTED] wrote: In 9.2, you can keep the index by using the KEEP INDEX key words. ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX This will keep the index but drop the constraint. Talk about having your cake and eating it too...;) HTH Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 27, 2002 4:39 PM it'll have to wait until Monday, I'm not at work until then. I'll try it with a non-unique then Hey, if it works, it saves me tons of time, I learn something new and I had fun developing the single SQL statement to rebuild the constraint and index. Win-win Rachel --- Denny Koovakattu [EMAIL PROTECTED] wrote: I don't have access to 9.2.0.1 right now. But can you try creating a non- unique index instead of the unique index. If you create a unique index, it gets dropped. That's the behavior on 8.1.x also. But if it's a non-unique index, it shouldn't get dropped. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: 9.2.0.1 Solaris, and yes, it does drop it I created a unique index in the primary key columns I created the primary key constraint without specifying an index I checked that the index existed, it did I dropped the primary key constraint I checked that the index existed, it didn't try it I tried various combinations before posting this note --- Denny Koovakattu [EMAIL PROTECTED] wrote: If you build a separate index to enforce the primary key, Oracle shouldn't drop it when you disable or drop the primary key. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: Here's a reason: have you ever tried to find the three duplicate rows in a 12 million row table without using the primary key constraint? I've had to disable or drop the constraint in order to use the exceptions table. Once I do that, even if I've built a separate index that enforces the primary key constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow the index to be rebuilt when I re-enable the primary key constraint, it builds it in the default tablespace of the table owner, not where I want it. if anyone has a better way to fix this problem, I'm more than happy to hear it! It's a data warehouse and the third party app has a bug we can't find and on occasion sqlloads (via direct path) duplicate rows Rachel --- Jared Still [EMAIL PROTECTED] wrote: Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED]
Re: Rebuilding Indexes...
Ohhh, something else I didn't know yet! Which is why I keep reading this list. That, and playing referee. ;) Jared On Saturday 28 December 2002 17:48, Arup Nanda wrote: In 9.2, you can keep the index by using the KEEP INDEX key words. ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX This will keep the index but drop the constraint. Talk about having your cake and eating it too...;) HTH Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 27, 2002 4:39 PM it'll have to wait until Monday, I'm not at work until then. I'll try it with a non-unique then Hey, if it works, it saves me tons of time, I learn something new and I had fun developing the single SQL statement to rebuild the constraint and index. Win-win Rachel --- Denny Koovakattu [EMAIL PROTECTED] wrote: I don't have access to 9.2.0.1 right now. But can you try creating a non- unique index instead of the unique index. If you create a unique index, it gets dropped. That's the behavior on 8.1.x also. But if it's a non-unique index, it shouldn't get dropped. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: 9.2.0.1 Solaris, and yes, it does drop it I created a unique index in the primary key columns I created the primary key constraint without specifying an index I checked that the index existed, it did I dropped the primary key constraint I checked that the index existed, it didn't try it I tried various combinations before posting this note --- Denny Koovakattu [EMAIL PROTECTED] wrote: If you build a separate index to enforce the primary key, Oracle shouldn't drop it when you disable or drop the primary key. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: Here's a reason: have you ever tried to find the three duplicate rows in a 12 million row table without using the primary key constraint? I've had to disable or drop the constraint in order to use the exceptions table. Once I do that, even if I've built a separate index that enforces the primary key constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow the index to be rebuilt when I re-enable the primary key constraint, it builds it in the default tablespace of the table owner, not where I want it. if anyone has a better way to fix this problem, I'm more than happy to hear it! It's a data warehouse and the third party app has a bug we can't find and on occasion sqlloads (via direct path) duplicate rows Rachel --- Jared Still [EMAIL PROTECTED] wrote: Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL
Re: Rebuilding Indexes...
Jared, Did you attach the scripts? I use the index rebuildingregularly for certain applications where buffer busy waitsare prevalent. No, let's not go there why the buffer busy waits occur and whether reverse key indexes would help. All these arepaths well trodden. I use a home grown setup where I ANALYZE VALIDATE STRUCTURE each index and immediately store the INDEX_STATS rw in a table called INDCHK_INDEX_STATS. Then I use the following script to identify the potential indexes candidate for rebuilding. The Height, "Compression Factor", Delete%and "Hole Factor" as calculated below provide an indication whether the index can be considered to be rebuilt. There is no hard threshold value for each, based on all three, I decide whether the index needs to be rebuilt. Finally, how did I come up with the seemingly labyrinthine formulae below?Parts of themare "stolen" from the OEM tool's index check program. I snooped around when the tool was analyzing the indexes and captured the code, modified to some extent and placed in a nice script. It works for me. The indexes are placed in LMT with non-uniform extents and the database is 8.1.7.4. Yes, I know this will probably spark all sorts of reaction; but I would appreciate any feedback on the process. Arup Nanda col name format a30 head "Index Name"col comp_factor head "Compactness"col hole_factor format head "Hole"col del_pct format head "Del%"col height format 9 head "Height"SELECT NAME, HEIGHT, DECODE(HEIGHT, 1, 100, FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100) / (LF_BLK_LEN * LF_BLKS))) Comp_Factor, DECODE(HEIGHT, 1, 0, DECODE(LF_ROWS - DEL_LF_ROWS, 0, 1, DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN / BR_ROWS), LF_BLK_LEN / ((LF_ROWS_LEN - DEL_LF_ROWS_LEN) / (LF_ROWS - DEL_LF_ROWS) - HEIGHT, -1, 1, 0))) +DECODE(LF_ROWS_LEN, 0, 0, FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100)) Hole_Factor,round(decode(lf_rows,0,0,100*DEL_LF_ROWS/LF_ROWS),0) del_pctFROM INDCHK_INDEX_STATSwhere height 3or DECODE(HEIGHT, 1, 100, FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100) / (LF_BLK_LEN * LF_BLKS))) 80or DECODE(HEIGHT, 1, 0, DECODE(LF_ROWS - DEL_LF_ROWS, 0, 1, DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN / BR_ROWS), LF_BLK_LEN / ((LF_ROWS_LEN - DEL_LF_ROWS_LEN) / (LF_ROWS - DEL_LF_ROWS) - HEIGHT, -1, 1, 0))) + DECODE(LF_ROWS_LEN, 0, 0, FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100)) 10ordecode(lf_rows,0,0,100*DEL_LF_ROWS/LF_ROWS) 9order by 3 desc, 2, 1/ - Original Message - From: "Jared Still" [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Thursday, December 26, 2002 10:13 PM Subject: Re: Rebuilding Indexes... Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: Rebuilding Indexes...
a few more undocumented commands that might help: ALTER UNIVERSE cosmos PARALLEL 16; ALTER UNIVERSE cosmos INCREMENT BY hour; sigh. I need to find time to read ALL the docs. Yeah, that'll happen. If I can find a parallel universe where time runs at a different rate. Thanks, I'll test this out as well. --- Arup Nanda [EMAIL PROTECTED] wrote: In 9.2, you can keep the index by using the KEEP INDEX key words. ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX This will keep the index but drop the constraint. Talk about having your cake and eating it too...;) HTH Arup - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, December 27, 2002 4:39 PM it'll have to wait until Monday, I'm not at work until then. I'll try it with a non-unique then Hey, if it works, it saves me tons of time, I learn something new and I had fun developing the single SQL statement to rebuild the constraint and index. Win-win Rachel --- Denny Koovakattu [EMAIL PROTECTED] wrote: I don't have access to 9.2.0.1 right now. But can you try creating a non- unique index instead of the unique index. If you create a unique index, it gets dropped. That's the behavior on 8.1.x also. But if it's a non-unique index, it shouldn't get dropped. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: 9.2.0.1 Solaris, and yes, it does drop it I created a unique index in the primary key columns I created the primary key constraint without specifying an index I checked that the index existed, it did I dropped the primary key constraint I checked that the index existed, it didn't try it I tried various combinations before posting this note --- Denny Koovakattu [EMAIL PROTECTED] wrote: If you build a separate index to enforce the primary key, Oracle shouldn't drop it when you disable or drop the primary key. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: Here's a reason: have you ever tried to find the three duplicate rows in a 12 million row table without using the primary key constraint? I've had to disable or drop the constraint in order to use the exceptions table. Once I do that, even if I've built a separate index that enforces the primary key constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow the index to be rebuilt when I re-enable the primary key constraint, it builds it in the default tablespace of the table owner, not where I want it. if anyone has a better way to fix this problem, I'm more than happy to hear it! It's a data warehouse and the third party app has a bug we can't find and on occasion sqlloads (via direct path) duplicate rows Rachel --- Jared Still [EMAIL PROTECTED] wrote: Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: Rebuilding Indexes...
Here's a reason: have you ever tried to find the three duplicate rows in a 12 million row table without using the primary key constraint? I've had to disable or drop the constraint in order to use the exceptions table. Once I do that, even if I've built a separate index that enforces the primary key constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow the index to be rebuilt when I re-enable the primary key constraint, it builds it in the default tablespace of the table owner, not where I want it. if anyone has a better way to fix this problem, I'm more than happy to hear it! It's a data warehouse and the third party app has a bug we can't find and on occasion sqlloads (via direct path) duplicate rows Rachel --- Jared Still [EMAIL PROTECTED] wrote: Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Rebuilding Indexes...
If you know you have 3 duplicate records in the table then the PK must have already been disabled so you have to rebuild anyway. I do not see where you had to disable in order to use the exception table. It was already disabled therefore it probably not an app problem but a disable constraint problem unless direct load bypasses constraint checking which I am not sure. Rick Rachel Carmichael To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] wisernet100@y cc: ahoo.comSubject: Re: Rebuilding Indexes... Sent by: [EMAIL PROTECTED] om 12/27/2002 07:43 AM Please respond to ORACLE-L Here's a reason: have you ever tried to find the three duplicate rows in a 12 million row table without using the primary key constraint? I've had to disable or drop the constraint in order to use the exceptions table. Once I do that, even if I've built a separate index that enforces the primary key constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow the index to be rebuilt when I re-enable the primary key constraint, it builds it in the default tablespace of the table owner, not where I want it. if anyone has a better way to fix this problem, I'm more than happy to hear it! It's a data warehouse and the third party app has a bug we can't find and on occasion sqlloads (via direct path) duplicate rows Rachel --- Jared Still [EMAIL PROTECTED] wrote: Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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
Re: Rebuilding Indexes...
Hey Rachel, Consider using a non-unique index for your primary key constraint. If you prebuild it and then add the constraint, Oracle will not drop the index when you drop the PK constraint, and you can control the index build that a way (and build it in parallel to boot). hth, Jack Here's a reason: have you ever tried to find the three duplicate rows in a 12 million row table without using the primary key constraint? I've had to disable or drop the constraint in order to use the exceptions table. Once I do that, even if I've built a separate index that enforces the primary key constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow the index to be rebuilt when I re-enable the primary key constraint, it builds it in the default tablespace of the table owner, not where I want it. if anyone has a better way to fix this problem, I'm more than happy to hear it! It's a data warehouse and the third party app has a bug we can't find and on occasion sqlloads (via direct path) duplicate rows Rachel --- Jared Still [EMAIL PROTECTED] wrote: Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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). Thanks, Jack Silvey -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jack Silvey 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: Rebuilding Indexes...
Title: Rebuilding Indexes... Thanks for the responses from all the great minds on this list! :) -Original Message-From: Richard Huntley [mailto:[EMAIL PROTECTED]]Sent: Thursday, December 26, 2002 3:24 PMTo: Multiple recipients of list ORACLE-LSubject: Rebuilding Indexes... Anyone have any useful scripts for doing this? TIA, Rich
Re: Rebuilding Indexes...
If you build a separate index to enforce the primary key, Oracle shouldn't drop it when you disable or drop the primary key. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: Here's a reason: have you ever tried to find the three duplicate rows in a 12 million row table without using the primary key constraint? I've had to disable or drop the constraint in order to use the exceptions table. Once I do that, even if I've built a separate index that enforces the primary key constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow the index to be rebuilt when I re-enable the primary key constraint, it builds it in the default tablespace of the table owner, not where I want it. if anyone has a better way to fix this problem, I'm more than happy to hear it! It's a data warehouse and the third party app has a bug we can't find and on occasion sqlloads (via direct path) duplicate rows Rachel --- Jared Still [EMAIL PROTECTED] wrote: Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Denny Koovakattu 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: Rebuilding Indexes...
9.2.0.1 Solaris, and yes, it does drop it I created a unique index in the primary key columns I created the primary key constraint without specifying an index I checked that the index existed, it did I dropped the primary key constraint I checked that the index existed, it didn't try it I tried various combinations before posting this note --- Denny Koovakattu [EMAIL PROTECTED] wrote: If you build a separate index to enforce the primary key, Oracle shouldn't drop it when you disable or drop the primary key. Regards, Denny Quoting Rachel Carmichael [EMAIL PROTECTED]: Here's a reason: have you ever tried to find the three duplicate rows in a 12 million row table without using the primary key constraint? I've had to disable or drop the constraint in order to use the exceptions table. Once I do that, even if I've built a separate index that enforces the primary key constraint, Oracle drops the index. So I HAVE to rebuild it. If I allow the index to be rebuilt when I re-enable the primary key constraint, it builds it in the default tablespace of the table owner, not where I want it. if anyone has a better way to fix this problem, I'm more than happy to hear it! It's a data warehouse and the third party app has a bug we can't find and on occasion sqlloads (via direct path) duplicate rows Rachel --- Jared Still [EMAIL PROTECTED] wrote: Though I have published a script for determining indexes that need to be rebuilt, and then rebuilding them, I have to say that this is almost never necessary. Why are you rebuilding indexes? About the only reason for ever doing so is that the BLEVEL = 5. goto asktom.oracle.com, and do a search on 'index rebuild'. Currently, the third article may be of interest. Jared On Thursday 26 December 2002 12:24, Richard Huntley wrote: Anyone have any useful scripts for doing this? TIA, Rich Content-Type: text/html; charset=iso-8859-1; name=Attachment: 1 Content-Transfer-Encoding: 7bit Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Denny Koovakattu 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). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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
