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
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 sense. I think you've confused what the clustering factor of an index represents ... OK, your whole discussion of these two camps, this whole concept of both being right, or wrong, or whatever, is pointless as it doesn't resolve anything. You mention that the Academics (a term I dislike) claim that indexes rarely benefit from a rebuild without discussing what academics mean by rarely. Obviously they accept that index rebuilds are sometimes (rarely) beneficial, so what are these cases ? You mention that the pragmatic approach sometimes results in better performance and that index rebuilds are sometimes clearly beneficial. So obviously, they have a case. It seems obvious (to me anyway) that perhaps there's an overlap here, that perhaps everyone agrees that index rebuilds are beneficial. Maybe some have the key and know how to unlock the doors directly whiles others do a bit more pocking around in the dark ? I think there are two fundamental questions/issues you've failed to address: - Why/when would an index require and benefit from a rebuild ? - How would one monitor that such a rebuild indeed has been beneficial ? Let me attempt to address these questions. Firstly, why would an index require a rebuild ? Answer, because the index is currently inefficient and by rebuilding it, Oracle will noticeably improve it's performance to the point that the cost of rebuilding the thing is justified. It's all kinda simple really. So what is an inefficient index ? One that has so much wasted space, that by rebuilding and reclaiming this space, would reduce the cost of accessing this index (or indeed Oracle could now choose to use the index in the first place) such that performance now noticeably improves. The key words here are wasted, cost and noticeably. So what is wasted space ? Well any space that is not currently used within the index structure is potentially wasted. However, a key point is that if this space is either: - going to be subsequently used within an appropriate timeframe, or - going to reoccur within an appropriate timeframe then it's not really wasted is it ? I mean, if we're going to subsequently use this space, then this unused space is not really an issue. If after the rebuild, this unused space subsequently returns, then the rebuild is kinda pointless isn't it ? So space is only really wasted if we don't intend to use it or if by getting rid of the wasted space, we keep it away. Note that some unused space is a good thing. Why, because it gives index blocks spare capacity to avoid block splits. Block splits occur when a block has insufficient free space in which to store new index entries and a block split is not particularly nice. It involves extra I/O to get a new leaf node, it involves extra CPU to redistribute the index data, it requires extra redo, etc. etc. It also results in now two leaf nodes having 50% unused space. Net effect, reduced performance and the generation of unused space, exactly what the rebuild was trying to prevent . So avoiding block splits is a good thing that unused space provides. How does an index get wasted space. Well if we keep our above criteria in mind, not that easily. Note that current free space within an index can generally be consumed by subsequent inserts, note that deleted index space can be subsequently reused, note that totally emptied blocks can be reused by subsequent index splits. So the chance of any free space being eventually used is high (please see Metalink Note 182699.1 where Oracle have published my warnings regarding unnecessarily rebuilding indexes due to these factors). However, there are situations when this free space may never get reused and so is potentially wasted which include: a.. An index is created with an excessive PCTFREE value which subsequent index growth will never use (somewhat rare and a stupid thing to do in the first place) b.. When we have deletes with monotonically increasing index entries. The deleted space can not be reused as all new entries live in the last index node unless all entries are deleted from the node. So it's sparse deletes on incrementally increasing index values. Note this requires knowledge of the characteristics of the index to identify. c.. Similar scenario to above, but sparse deletions of ranges of values that are no longer valid insertable values d.. When we perform a large/bulk delete with no prospect of re-entering the same volume of data. However note in this case the table itself would likely have an inflated HWM and so it's the table (and hence implicitly the indexes) that would potentially benefit from a rebuild. e.. When
Re: rebuilding indexes - sure to cause a ruckus
no sense. I think you've confused what the clustering factor of an index represents ... OK, your whole discussion of these two camps, this whole concept of both being right, or wrong, or whatever, is pointless as it doesn't resolve anything. You mention that the Academics (a term I dislike) claim that indexes rarely benefit from a rebuild without discussing what academics mean by rarely. Obviously they accept that index rebuilds are sometimes (rarely) beneficial, so what are these cases ? You mention that the pragmatic approach sometimes results in better performance and that index rebuilds are sometimes clearly beneficial. So obviously, they have a case. It seems obvious (to me anyway) that perhaps there's an overlap here, that perhaps everyone agrees that index rebuilds are beneficial. Maybe some have the key and know how to unlock the doors directly whiles others do a bit more pocking around in the dark ? I think there are two fundamental questions/issues you've failed to address: - Why/when would an index require and benefit from a rebuild ? - How would one monitor that such a rebuild indeed has been beneficial ? Let me attempt to address these questions. Firstly, why would an index require a rebuild ? Answer, because the index is currently inefficient and by rebuilding it, Oracle will noticeably improve it's performance to the point that the cost of rebuilding the thing is justified. It's all kinda simple really. So what is an inefficient index ? One that has so much wasted space, that by rebuilding and reclaiming this space, would reduce the cost of accessing this index (or indeed Oracle could now choose to use the index in the first place) such that performance now noticeably improves. The key words here are wasted, cost and noticeably. So what is wasted space ? Well any space that is not currently used within the index structure is potentially wasted. However, a key point is that if this space is either: - going to be subsequently used within an appropriate timeframe, or - going to reoccur within an appropriate timeframe then it's not really wasted is it ? I mean, if we're going to subsequently use this space, then this unused space is not really an issue. If after the rebuild, this unused space subsequently returns, then the rebuild is kinda pointless isn't it ? So space is only really wasted if we don't intend to use it or if by getting rid of the wasted space, we keep it away. Note that some unused space is a good thing. Why, because it gives index blocks spare capacity to avoid block splits. Block splits occur when a block has insufficient free space in which to store new index entries and a block split is not particularly nice. It involves extra I/O to get a new leaf node, it involves extra CPU to redistribute the index data, it requires extra redo, etc. etc. It also results in now two leaf nodes having 50% unused space. Net effect, reduced performance and the generation of unused space, exactly what the rebuild was trying to prevent . So avoiding block splits is a good thing that unused space provides. How does an index get wasted space. Well if we keep our above criteria in mind, not that easily. Note that current free space within an index can generally be consumed by subsequent inserts, note that deleted index space can be subsequently reused, note that totally emptied blocks can be reused by subsequent index splits. So the chance of any free space being eventually used is high (please see Metalink Note 182699.1 where Oracle have published my warnings regarding unnecessarily rebuilding indexes due to these factors). However, there are situations when this free space may never get reused and so is potentially wasted which include: a.. An index is created with an excessive PCTFREE value which subsequent index growth will never use (somewhat rare and a stupid thing to do in the first place) b.. When we have deletes with monotonically increasing index entries. The deleted space can not be reused as all new entries live in the last index node unless all entries are deleted from the node. So it's sparse deletes on incrementally increasing index values. Note this requires knowledge of the characteristics of the index to identify. c.. Similar scenario to above, but sparse deletions of ranges of values that are no longer valid insertable values d.. When we perform a large/bulk delete with no prospect of re-entering the same volume of data. However note in this case the table itself would likely have an inflated HWM and so it's the table (and hence implicitly the indexes) that would potentially benefit from a rebuild. e.. When we have enough occurrences of particular index values that they span over multiple index nodes. As Oracle: a.. performs 50/50 block splits (unless it's the highest value in the leaf node where a 90-10 split is generated), and b.. inserts only into the last referenced leaf node of the value Oracle will leave behind
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).
rebuilding indexes - sure to cause a ruckus
http://www.dbazine.com/burleson18.shtml
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: any problem rebuilding indexes used for replication
I'm curious, how have you identified the fragmentation? What benefits do you expect from the rebuild of the indexes? Are you targeting certain indexes that have been identified as benefiting from a rebuild, or just planning to rebuild all indexes? Jared renu r [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2003 06:14 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:any problem rebuilding indexes used for replication Hello, I have to rebuild some primary key indexes due to excessive fragmentation. It is rebuild not drop and create. We have multi master replication running. Is there any problem to replication if I do that. Has anyone tried it? TIA. Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
Re: any problem rebuilding indexes used for replication
Jared : I think it is fragmented based on scripts and knowing that there have been lot of deletes. Onescript uses the table index_stats and looks at field del_lf_rows which should be less at least in comparison to field lf_rows. Imean less is good. more bad. I am not sure about the script but I will post it here if someonesays soor send it to anyone if they want. I am sure the expertshere know about it and can clarify if it is any good to look at the index_stats table. One other simple useful script is : SELECTowner, index_name, blevelFROMall_indexesWHEREblevel 2 This can be bacause the size of table is big so it is not definitive. I will check the level after rebuild. Benefits expected : Space savings (definitely). performance (hopefully). I will let you and Yong know about the benefits if any. If I get some help. [EMAIL PROTECTED] wrote: I'm curious, how have you identified the fragmentation? What benefits do you expect from the rebuild of the indexes? Are you targeting certain indexes that have been identified as benefiting from a rebuild, or just planning to rebuild all indexes? Jared renu r [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2003 06:14 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:any problem rebuilding indexes used for replicationHello, I have to rebuild some primary key indexes due to excessive fragmentation. It is rebuild not drop and create. We have multi master replication running. Is there any problem to replication if I do that. Has anyone tried it? TIA. Do you Yahoo!?Protect your identity with Yahoo! Mail AddressGuard Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
Re: any problem rebuilding indexes used for replication
Re the space savings: that may or not be important. Guess it depends on how tight storage space is in your environment, and how much of an impact it may make on your backups. Re the performance: Some queries could perform better due to the index data being packed into fewer blocks, as you are expecting. It could also result in decreased performance for inserts, for the same reason. An index with all data tightly packed into nearly full blocks makes it a good candidate for excessive block splits when data is inserted into the table. You could allow a generous PCTFREE when you rebuild to alleviate this, but then there would not be much point in rebuilding the index. You might consider targeting your most expensive operations ( eg. the SQL that causes the users and/or the system the most pain ) and then look at the indexes involved to see if rebuilding them would increase or decrease performance. Lots of discussion lately on this list on how to go about doing that. ( Optimizing Oracle Performance - http://www.oreilly.com/catalog/optoraclep/ ) HTH Jared renu r [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2003 02:29 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: any problem rebuilding indexes used for replication Jared : I think it is fragmented based on scripts and knowing that there have been lot of deletes. One script uses the table index_stats and looks at field del_lf_rows which should be less at least in comparison to field lf_rows. I mean less is good. more bad. I am not sure about the script but I will post it here if someone says so or send it to anyone if they want. I am sure the experts here know about it and can clarify if it is any good to look at the index_stats table. One other simple useful script is : SELECT owner, index_name, blevel FROM all_indexes WHERE blevel 2 This can be bacause the size of table is big so it is not definitive. I will check the level after rebuild. Benefits expected : Space savings (definitely). performance (hopefully). I will let you and Yong know about the benefits if any. If I get some help. [EMAIL PROTECTED] wrote: I'm curious, how have you identified the fragmentation? What benefits do you expect from the rebuild of the indexes? Are you targeting certain indexes that have been identified as benefiting from a rebuild, or just planning to rebuild all indexes? Jared renu r [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2003 06:14 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:any problem rebuilding indexes used for replication Hello, I have to rebuild some primary key indexes due to excessive fragmentation. It is rebuild not drop and create. We have multi master replication running. Is there any problem to replication if I do that. Has anyone tried it? TIA. Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
Re: any problem rebuilding indexes used for replication
renu (and Jared), The reason I'm very interested in whether there's performance improvement is that there's a thread on the newsgroup about index rebuild recently. See http://groups.google.com/groups?threadm=pan.2003.11.03.08.09.24.330520%40RE-MO-VE.BountifulSolutions.co.uk Jonathan Lewis says there's almost no need to rebuild indexes, although his Practical Oracle8i lists at least one case you may benefit by rebuilding. (I don't have the book with me). Asktom.oracle.com has numerous messages advising against rebuilding indexes. Let's set theory aside for a moment and do the experiment. Please post your report of performance change. Thanks. Yong Huang --- renu r [EMAIL PROTECTED] wrote: Jared : I think it is fragmented based on scripts and knowing that there have been lot of deletes. One script uses the table index_stats and looks at field del_lf_rows which should be less at least in comparison to field lf_rows. I mean less is good. more bad. I am not sure about the script but I will post it here if someone says so or send it to anyone if they want. I am sure the experts here know about it and can clarify if it is any good to look at the index_stats table. One other simple useful script is : SELECT owner, index_name, blevel FROM all_indexes WHERE blevel 2 This can be bacause the size of table is big so it is not definitive. I will check the level after rebuild. Benefits expected : Space savings (definitely). performance (hopefully). I will let you and Yong know about the benefits if any. If I get some help. [EMAIL PROTECTED] wrote: I'm curious, how have you identified the fragmentation? What benefits do you expect from the rebuild of the indexes? Are you targeting certain indexes that have been identified as benefiting from a rebuild, or just planning to rebuild all indexes? Jared renu r [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2003 06:14 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:any problem rebuilding indexes used for replication Hello, I have to rebuild some primary key indexes due to excessive fragmentation. It is rebuild not drop and create. We have multi master replication running. Is there any problem to replication if I do that. Has anyone tried it? TIA. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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: any problem rebuilding indexes used for replication
I think Jonathan is correct when he says that index rebuilds are not often needed. Tom Kyte states that they are never needed, which I don't believe to be correct. I'll try to back this up with data in the future. So if Tom asks any of you why you don't use automatic space management in your LMT's, you can ask him why he doesn't use 'alter index rebuild'. ;) Jared Yong Huang [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2003 02:59 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: any problem rebuilding indexes used for replication renu (and Jared), The reason I'm very interested in whether there's performance improvement is that there's a thread on the newsgroup about index rebuild recently. See http://groups.google.com/groups?threadm=pan.2003.11.03.08.09.24.330520%40RE-MO-VE.BountifulSolutions.co.uk Jonathan Lewis says there's almost no need to rebuild indexes, although his Practical Oracle8i lists at least one case you may benefit by rebuilding. (I don't have the book with me). Asktom.oracle.com has numerous messages advising against rebuilding indexes. Let's set theory aside for a moment and do the experiment. Please post your report of performance change. Thanks. Yong Huang --- renu r [EMAIL PROTECTED] wrote: Jared : I think it is fragmented based on scripts and knowing that there have been lot of deletes. One script uses the table index_stats and looks at field del_lf_rows which should be less at least in comparison to field lf_rows. I mean less is good. more bad. I am not sure about the script but I will post it here if someone says so or send it to anyone if they want. I am sure the experts here know about it and can clarify if it is any good to look at the index_stats table. One other simple useful script is : SELECT owner, index_name, blevel FROM all_indexes WHERE blevel 2 This can be bacause the size of table is big so it is not definitive. I will check the level after rebuild. Benefits expected : Space savings (definitely). performance (hopefully). I will let you and Yong know about the benefits if any. If I get some help. [EMAIL PROTECTED] wrote: I'm curious, how have you identified the fragmentation? What benefits do you expect from the rebuild of the indexes? Are you targeting certain indexes that have been identified as benefiting from a rebuild, or just planning to rebuild all indexes? Jared renu r [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2003 06:14 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:any problem rebuilding indexes used for replication Hello, I have to rebuild some primary key indexes due to excessive fragmentation. It is rebuild not drop and create. We have multi master replication running. Is there any problem to replication if I do that. Has anyone tried it? TIA. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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: any problem rebuilding indexes used for replication
Hi,
In some case, rebuild index can help, this is from my work log on my Datawarehouse
project.
The effect of rebuiding index:
SQL exec show_space('INX_BID_BIDTYPE',USER,'INDEX')
Free Blocks.22
Total Blocks4090
Total Bytes.33505280
Unused Blocks...823
Unused Bytes6742016
Last Used Ext FileId11
Last Used Ext BlockId...52851
Last Used Block.547
PL/SQL procedure successfully completed.
SQL select index_name,owner,blevel,index_type from dba_indexes where blevel4;
INDEX_NAME OWNER BLEVEL INDEX_TYPE
-- -- --
---
INX_BID_BIDTYPEDAILYLOAD 10 BITMAP
INX_TX_CC_STATUS_IDDAILYLOAD 6 BITMAP
INX_TX_BD_STATUS_IDDAILYLOAD 5 BITMAP
SQL ALTER INDEX INX_BID_BIDTYPE REBUILD;
Index altered.
SQL exec show_space('INX_BID_BIDTYPE',USER,'INDEX')
Free Blocks.0
Total Blocks5
Total Bytes.40960
Unused Blocks...3
Unused Bytes24576
Last Used Ext FileId15
Last Used Ext BlockId...39837
Last Used Block.2
PL/SQL procedure successfully completed.
DAILYSOURCEINX_STD_ST_STATEMENTID
122910
149 rows selected.
SQL CONN SYSTEM/[EMAIL PROTECTED]
Connected.
SQL ALTER INDEX DAILYSOURCE.INX_STD_ST_STATEMENTID REBUILD;
Index altered.
SQL select 122910*16/1024 from dual;
122910*16/1024
--
1920.46875
SQL select blocks from dba_segments where segment_name='INX_STD_ST_STATEMENTID';
BLOCKS
--
54642
SQL SELECT 54642*16/1024 FROM DUAL;
54642*16/1024
-
853.78125
SQL SET PAUSE OFF
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, November 07, 2003 6:59 AM
renu (and Jared),
The reason I'm very interested in whether there's performance improvement is
that there's a thread on the newsgroup about index rebuild recently. See
http://groups.google.com/groups?threadm=pan.2003.11.03.08.09.24.330520%40RE-MO-VE.BountifulSolutions.co.uk
Jonathan Lewis says there's almost no need to rebuild indexes, although his
Practical Oracle8i lists at least one case you may benefit by rebuilding. (I
don't have the book with me). Asktom.oracle.com has numerous messages advising
against rebuilding indexes. Let's set theory aside for a moment and do the
experiment. Please post your report of performance change. Thanks.
Yong Huang
--- renu r [EMAIL PROTECTED] wrote:
Jared : I think it is fragmented based on scripts and knowing that there have
been lot of deletes.
One script uses the table index_stats and looks at field del_lf_rows which
should be less at least in comparison to field lf_rows. I mean less is good.
more bad. I am not sure about the script but I will post it here if someone
says so or send it to anyone if they want.
I am sure the experts here know about it and can clarify if it is any good
to look at the index_stats table.
One other simple useful script is :
SELECT owner, index_name, blevel
FROM all_indexes
WHERE blevel 2
This can be bacause the size of table is big so it is not definitive. I will
check the level after rebuild.
Benefits expected : Space savings (definitely). performance (hopefully). I
will let you and Yong know about the benefits if any. If I get some help.
[EMAIL PROTECTED] wrote:
I'm curious, how have you identified the fragmentation?
What benefits do you expect from the rebuild of the indexes?
Are you targeting certain indexes that have been identified as
benefiting from a rebuild, or just planning to rebuild all indexes?
Jared
renu r [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
11/05/2003 06:14 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:any problem rebuilding indexes used for replication
Hello,
I have to rebuild some primary key indexes due to excessive fragmentation.
It is rebuild not drop and create. We have multi master replication running.
Is there any problem to replication if I do that. Has anyone tried it? TIA.
__
Do you Yahoo!?
Protect your identity with Yahoo! Mail AddressGuard
http://antispam.yahoo.com/whatsnewfree
--
Please see the official ORACLE-L FAQ
Re: any problem rebuilding indexes used for replication
--- [EMAIL PROTECTED] wrote: Tom Kyte states that they are never needed, which I don't believe to be correct. As I said two weeks ago, somewhere on the Internet people over-trusted authorities. It's different here. I love this place! I'll try to back this up with data in the future. As a Ph.D in chemistry (sorry to say this), I know how more important repeated experiments done by different people in different labs are than theory. Furthermore, a lab experiment is nothing if a product coming out of a chemical plant says no good. So if Tom asks any of you why you don't use automatic space management in your LMT's, you can ask him why he doesn't use 'alter index rebuild'. ;) Jared Yong Huang [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/06/2003 02:59 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: any problem rebuilding indexes used for replication renu (and Jared), The reason I'm very interested in whether there's performance improvement is that there's a thread on the newsgroup about index rebuild recently. See http://groups.google.com/groups?threadm=pan.2003.11.03.08.09.24.330520%40RE-MO-VE.BountifulSolutions.co.uk Jonathan Lewis says there's almost no need to rebuild indexes, although his Practical Oracle8i lists at least one case you may benefit by rebuilding. (I don't have the book with me). Asktom.oracle.com has numerous messages advising against rebuilding indexes. Let's set theory aside for a moment and do the experiment. Please post your report of performance change. Thanks. Yong Huang --- renu r [EMAIL PROTECTED] wrote: Jared : I think it is fragmented based on scripts and knowing that there have been lot of deletes. One script uses the table index_stats and looks at field del_lf_rows which should be less at least in comparison to field lf_rows. I mean less is good. more bad. I am not sure about the script but I will post it here if someone says so or send it to anyone if they want. I am sure the experts here know about it and can clarify if it is any good to look at the index_stats table. One other simple useful script is : SELECT owner, index_name, blevel FROM all_indexes WHERE blevel 2 This can be bacause the size of table is big so it is not definitive. I will check the level after rebuild. Benefits expected : Space savings (definitely). performance (hopefully). I will let you and Yong know about the benefits if any. If I get some help. [EMAIL PROTECTED] wrote: I'm curious, how have you identified the fragmentation? What benefits do you expect from the rebuild of the indexes? Are you targeting certain indexes that have been identified as benefiting from a rebuild, or just planning to rebuild all indexes? Jared renu r [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 11/05/2003 06:14 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:any problem rebuilding indexes used for replication Hello, I have to rebuild some primary key indexes due to excessive fragmentation. It is rebuild not drop and create. We have multi master replication running. Is there any problem to replication if I do that. Has anyone tried it? TIA. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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: any problem rebuilding indexes used for replication
zhu chao,
Your numbers are impressive. But they're not ultimate gauge, which should only
be performance (well, in very special cases, taking less disk space becomes
more important). Jonathan and Tom's points are indexes should not be too
densely packed unless the DML's on them already make them so.
So show us some timing data.
Yong Huang
--- zhu chao [EMAIL PROTECTED] wrote:
Hi,
In some case, rebuild index can help, this is from my work log on my
Datawarehouse project.
The effect of rebuiding index:
SQL exec show_space('INX_BID_BIDTYPE',USER,'INDEX')
Free Blocks.22
Total Blocks4090
Total Bytes.33505280
Unused Blocks...823
Unused Bytes6742016
Last Used Ext FileId11
Last Used Ext BlockId...52851
Last Used Block.547
PL/SQL procedure successfully completed.
SQL select index_name,owner,blevel,index_type from dba_indexes where
blevel4;
INDEX_NAME OWNER BLEVEL
INDEX_TYPE
-- -- --
---
INX_BID_BIDTYPEDAILYLOAD 10
BITMAP
INX_TX_CC_STATUS_IDDAILYLOAD 6
BITMAP
INX_TX_BD_STATUS_IDDAILYLOAD 5
BITMAP
SQL ALTER INDEX INX_BID_BIDTYPE REBUILD;
Index altered.
SQL exec show_space('INX_BID_BIDTYPE',USER,'INDEX')
Free Blocks.0
Total Blocks5
Total Bytes.40960
Unused Blocks...3
Unused Bytes24576
Last Used Ext FileId15
Last Used Ext BlockId...39837
Last Used Block.2
PL/SQL procedure successfully completed.
DAILYSOURCEINX_STD_ST_STATEMENTID
122910
149 rows selected.
SQL CONN SYSTEM/[EMAIL PROTECTED]
Connected.
SQL ALTER INDEX DAILYSOURCE.INX_STD_ST_STATEMENTID REBUILD;
Index altered.
SQL select 122910*16/1024 from dual;
122910*16/1024
--
1920.46875
SQL select blocks from dba_segments where
segment_name='INX_STD_ST_STATEMENTID';
BLOCKS
--
54642
SQL SELECT 54642*16/1024 FROM DUAL;
54642*16/1024
-
853.78125
SQL SET PAUSE OFF
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, November 07, 2003 6:59 AM
renu (and Jared),
The reason I'm very interested in whether there's performance improvement
is
that there's a thread on the newsgroup about index rebuild recently. See
http://groups.google.com/groups?threadm=pan.2003.11.03.08.09.24.330520%40RE-MO-VE.BountifulSolutions.co.uk
Jonathan Lewis says there's almost no need to rebuild indexes, although his
Practical Oracle8i lists at least one case you may benefit by rebuilding.
(I
don't have the book with me). Asktom.oracle.com has numerous messages
advising
against rebuilding indexes. Let's set theory aside for a moment and do the
experiment. Please post your report of performance change. Thanks.
Yong Huang
--- renu r [EMAIL PROTECTED] wrote:
Jared : I think it is fragmented based on scripts and knowing that there
have
been lot of deletes.
One script uses the table index_stats and looks at field del_lf_rows
which
should be less at least in comparison to field lf_rows. I mean less is
good.
more bad. I am not sure about the script but I will post it here if
someone
says so or send it to anyone if they want.
I am sure the experts here know about it and can clarify if it is any
good
to look at the index_stats table.
One other simple useful script is :
SELECT owner, index_name, blevel
FROM all_indexes
WHERE blevel 2
This can be bacause the size of table is big so it is not definitive. I
will
check the level after rebuild.
Benefits expected : Space savings (definitely). performance (hopefully).
I
will let you and Yong know about the benefits if any. If I get some help.
[EMAIL PROTECTED] wrote:
I'm curious, how have you identified the fragmentation?
What benefits do you expect from the rebuild of the indexes?
Are you targeting certain indexes that have been identified as
benefiting from a rebuild, or just planning to rebuild all indexes?
Jared
renu r [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
11/05/2003 06:14 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L
[EMAIL PROTECTED
Re: any problem rebuilding indexes used for replication
OK, that's a bitmap index. Bitmap indexes do need to be rebuilt after
accumulated maintenance (insert-update-delete) to reclaim space and
performance. Not even Tom denies that. What he advises against is the
rebuild of b-tree indexes.
At 07:49 PM 11/6/2003, you wrote:
Hi,
In some case, rebuild index can help, this is from my work log on my
Datawarehouse project.
The effect of rebuiding index:
SQL exec show_space('INX_BID_BIDTYPE',USER,'INDEX')
Free Blocks.22
Total Blocks4090
Total Bytes.33505280
Unused Blocks...823
Unused Bytes6742016
Last Used Ext FileId11
Last Used Ext BlockId...52851
Last Used Block.547
PL/SQL procedure successfully completed.
SQL select index_name,owner,blevel,index_type from dba_indexes where
blevel4;
INDEX_NAME OWNER BLEVEL
INDEX_TYPE
-- -- --
---
INX_BID_BIDTYPEDAILYLOAD 10
BITMAP
INX_TX_CC_STATUS_IDDAILYLOAD 6
BITMAP
INX_TX_BD_STATUS_IDDAILYLOAD 5
BITMAP
SQL ALTER INDEX INX_BID_BIDTYPE REBUILD;
Index altered.
SQL exec show_space('INX_BID_BIDTYPE',USER,'INDEX')
Free Blocks.0
Total Blocks5
Total Bytes.40960
Unused Blocks...3
Unused Bytes24576
Last Used Ext FileId15
Last Used Ext BlockId...39837
Last Used Block.2
PL/SQL procedure successfully completed.
DAILYSOURCEINX_STD_ST_STATEMENTID
122910
149 rows selected.
SQL CONN SYSTEM/[EMAIL PROTECTED]
Connected.
SQL ALTER INDEX DAILYSOURCE.INX_STD_ST_STATEMENTID REBUILD;
Index altered.
SQL select 122910*16/1024 from dual;
122910*16/1024
--
1920.46875
SQL select blocks from dba_segments where
segment_name='INX_STD_ST_STATEMENTID';
BLOCKS
--
54642
SQL SELECT 54642*16/1024 FROM DUAL;
54642*16/1024
-
853.78125
SQL SET PAUSE OFF
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, November 07, 2003 6:59 AM
renu (and Jared),
The reason I'm very interested in whether there's performance
improvement is
that there's a thread on the newsgroup about index rebuild recently. See
http://groups.google.com/groups?threadm=pan.2003.11.03.08.09.24.330520%40RE-MO-VE.BountifulSolutions.co.uk
Jonathan Lewis says there's almost no need to rebuild indexes, although his
Practical Oracle8i lists at least one case you may benefit by
rebuilding. (I
don't have the book with me). Asktom.oracle.com has numerous messages
advising
against rebuilding indexes. Let's set theory aside for a moment and do the
experiment. Please post your report of performance change. Thanks.
Yong Huang
--- renu r [EMAIL PROTECTED] wrote:
Jared : I think it is fragmented based on scripts and knowing that
there have
been lot of deletes.
One script uses the table index_stats and looks at field del_lf_rows
which
should be less at least in comparison to field lf_rows. I mean less
is good.
more bad. I am not sure about the script but I will post it here if
someone
says so or send it to anyone if they want.
I am sure the experts here know about it and can clarify if it is
any good
to look at the index_stats table.
One other simple useful script is :
SELECT owner, index_name, blevel
FROM all_indexes
WHERE blevel 2
This can be bacause the size of table is big so it is not definitive.
I will
check the level after rebuild.
Benefits expected : Space savings (definitely). performance
(hopefully). I
will let you and Yong know about the benefits if any. If I get some help.
[EMAIL PROTECTED] wrote:
I'm curious, how have you identified the fragmentation?
What benefits do you expect from the rebuild of the indexes?
Are you targeting certain indexes that have been identified as
benefiting from a rebuild, or just planning to rebuild all indexes?
Jared
renu r [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
11/05/2003 06:14 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:any problem rebuilding indexes used for
replication
Hello,
I have to rebuild some primary key indexes due to excessive
fragmentation.
It is rebuild not drop and create. We have multi master replication
running.
Is there any problem to replication if I do that. Has anyone tried
any problem rebuilding indexes used for replication
Hello, Ihave torebuild some primary key indexesdue to excessive fragmentation. It is rebuild not drop and create.We have multi master replication running. Is there any problem to replicationif I do that. Has anyone tried it? TIA. Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
Re: any problem rebuilding indexes used for replication
Hi, renu, I'll let experts anwser your question. But I have a request for you. Before and after you rebuild (or coalesce) your indexes, please make close observation on your application performance, as well as the statistics and sizes of the indexes. I'd like to know whether rebuilding them actually makes much difference. (I don't know the answer but am very interested to know). Thanks. Yong Huang --- renu r [EMAIL PROTECTED] wrote: Hello, I have to rebuild some primary key indexes due to excessive fragmentation. It is rebuild not drop and create. We have multi master replication running. Is there any problem to replication if I do that. Has anyone tried it? TIA. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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: 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
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: Was: Rebuilding Indexes, Now: KEEP INDEX
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 === 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 - 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: =?iso-8859-1?q?Connor=20McDonald?= 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: Was: Rebuilding Indexes, Now: KEEP INDEX
]: 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 === 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 - 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: =?iso-8859-1?q?Connor=20McDonald?= 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: Arup Nanda 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: Was: Rebuilding Indexes, Now: KEEP INDEX
yes but :) It's a partitioned index. Yes, the partition goes into an UNUSABLE state. If I drop the constraint without keep index and without saving off the statement to rebuild it properly, I drop the ENTIRE index and I end up with a non-partitioned index in the schema owner's default tablespace when I rebuild the constraint. So if I use KEEP INDEX, yes I'll need to rebuild the partition, but I won't have to rebuild the entire index and I won't have to save off the SQL to rebuild it properly. As the number of rows grows, rebuilding the entire index becomes time-prohibitive. Of course, I've already written that SQL statement, but that was fun. I'd still rather do the work properly and in a more efficient manner. --- Connor McDonald [EMAIL PROTECTED] wrote: but if you direct load dups into a table with a unique cons/index, won't the index be left as 'UNUSABLE' thus necessitating an index rebuild anyway. If the index was non-unique, then this is not a problem, but in this case, you don't need KEEP INDEX anyway. Happy New Year Cheers Connor --- Rachel Carmichael [EMAIL PROTECTED] wrote: unique constraint, unique index: - keep index redundant because effectively retains the constraint anyway (because you still can't insert dups) you can insert dups via sqlloader using direct=true so in my case, this would indeed be helpful and without the keep index I lose the index when I do an alter table drop constraint Keep index sounds like it will help me in this scenario: primary key constraint with unique index insert dups via sqlloader direct=true drop constraint with keep index recreate constraint with exceptions into exceptions table delete dups re-enable constraint this doesn't happen often, and we are working to fix the app so it doesn't put the dups into the input file for the sqlload. However, until it gets fixed, I need to do the above so that we actually have usable indexes on the partitioned fact tables --- Connor McDonald [EMAIL PROTECTED] wrote: 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
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).
rebuilding indexes, the tests
Okay, I did 4 different tests, as follows: in each test I created a partitioned table. Test 1 -- create a unique partitioned index, then create a primary key constraint with the same columns Test 2 -- create a non-unique partitioned index, then create a primary key constraint with the same columns Test 3 -- create a primary key constraint with the using index clause, partitioned Test 4 -- same as Test 3 In each case I then loaded valid data via insert and checked that for Test 1 and Test 2 the indexes were being used to enforce the constraint. They were. All partitions were in a USABLE state. I then loaded, via sqlloader direct=true, one row that would cause one of the partitions to become UNUSABLE. I then disabled the primary key constraint. For Test 4 only, I did the disable using the keep index clause. Next I checked for the existence of the index. In Test 2 (non-unique index) and Test 4 (keep index clause) the index remained. In Test 1 (unique index) and Test 3 (without the keep clause), the index disappeared. For both Test 2 and Test 4, I still needed to rebuild the index partition after I removed the duplicates to make the partition USABLE again. so the conclusion is, if I want not to lose the entire partitioned index, I need either a non-unique index to enforce the constraint or, for 9i only, the keep index clause when I disable the constraint. Since, in our case, the constraints have already been built using the INDEX TABLESPACE clause, I'll use the keep index clause when I have to remove duplicates. Thanks to all who responded and boy did I have fun proving this out :) Rachel __ 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...
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
