RE: rebuilding indexes - sure to cause a ruckus
Title: RE: rebuilding indexes - sure to cause a ruckus My brother (the NASA rocket scientist) usually says something like ' "Hey, this is all quite easy, it sure ain't raising kids" Matt Matt Adams - GE Appliances - [EMAIL PROTECTED] Their fundamental design flaws are completely hidden by their superficial design flaws. - Douglas Adams -Original Message- From: Richard Foote [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 10, 2003 5:40 AM To: Multiple recipients of list ORACLE-L Subject: 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
> 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
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
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
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" > > > > 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
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" > > > > 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
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
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" 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
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" 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
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" 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
> 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" 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
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
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
Terse ? You haven't heard me terse until you hear 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 Huang 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).
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
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).
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
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
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
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 Huang 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).
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
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
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
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
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
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
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
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 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
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
"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
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
Hi Yong, Saying there are a "few" errors is being a little kind to Don's "Inside Oracle Indexing" article. In part, these are some of the issues I raised directly with Don in a number of emails (warning somewhat on the longish side ;): a.. There are no such things as star indexes. Star joins, yes, star transformations yes, but not star indexes ? b.. I still disagree with your description of b-tree indexes being complex and difficult to understand, but then again this could just be my personal perception (check out http://groups.google.com/groups?q=g:thl3498916429d&dq=&hl=en&lr=&ie=UTF-8&se lm=ant%259.39604%24jM5.100537%40newsfeeds.bigpond.com&rnum=47 where I have a sample demo on how to investigate the workings of b-tree indexes.) However, by understanding them and a how they function, the question of whether or not they need rebuilding no longer needs to be debated. It becomes easily apparent under what conditions indexes could benefit from a rebuild. I'll expand on this later but I would suggest those that "debate", those that really don't know when a rebuild is justified and just rebuild in the hope it might do some good are those that really don't understand "how" indexes function. Knowledge is the key that unlocks the door of doubt and those without the key fumble aimlessly and prod around in hope... c.. Your subsequent quote "There is enough anecdotal evidence that index rebuilding has helped some systems perform better, and I also have no doubt that there is no scientific basis for the claim" is a nonsense. Of course one explain in scientific terms such performance improvements, I can only suggest that you unfortunately can't. Oracle is not some magic piece of software and it doesn't run on some magical pieces of hardware. Any suggestions to the contrary are not helpful to anyone. d.. I still disagree with the double the block size, halving the logical reads must be a good thing argument. It's a path that could lead to a very disappointing conclusion (read cliff edge). Indexes prefer large block sizes true but if the underlining storage file-system is not tuned to read (or write) these larger block sizes efficiently, then the whole thing is counter productive. You've been warned ... e.. Your description of PCTUSED is still wrong. There is no PCTUSED for indexes so it really shouldn't be misleading to confuse a non-existing index attribute with the amount of used space as documented in INDEX_STATS... f.. Including in your criteria for rebuilding an index "btree_space being greater than a block" is redundant when listed with the other criteria. It is fundamentally impossible for an index with 4 levels or more to consist of a single block, so why mention it. It just adds confusion and is silly. The DBA who swears by this criteria (which I noticed has changed in this draft ;), how do they make such a claim? It's one thing to swear, it's quite another to prove. Your table that lists average rows and blocks per different index levels shows that those indexes with a leaf row length of 500,000,000 and with 100,000 blocks require 4 levels. How does rebuilding such indexes with no subsequent change in index level improve performance ? I mean, large indexes need more levels right, so rebuilding them all the time and keeping the levels unchanged only to rebuild them again because they're still 4 or more levels seems like a pointless, never-ending exercise in futility. To rebuild an index that "actually" results in a reduction in it's level generally requires a "drastic" reduction in it's data volume due to the orders of sizing magnitude that a new level represents. More on this and the other so-called rebuild criteria later but the current level of an index is not a criteria for a rebuild. A level 3 index could conceivably be rebuilt to just a level 1 (if there were heaps and heaps of deletions) and a level 5 index could be rebuilt to stay at level 5. Which index has benefited . g.. Criteria for a rebuild: or the total length of deleted is > 1 block makes no sense whatsoever. Nearly all indexes would have a total length of deleted > than 1 block meaning nearly all indexes need rebuilding. I don't think so ... h.. Your discussion on the clustering factor affecting the likelihood of requiring an index rebuild is still flawed, however interestingly, you've now given an example on why this is the case. However, you've still come to the wrong conclusion !! Firstly, you're incorrect in your example to say that a 1,000,000 row table with a clustering factor of 1,000,000 has it's rows in the same order as it's index although I guess this could be a typo. Regardless, if you delete all last_name beginning with a K, you are going to delete consecutive leaf nodes regardless of the clustering factor. So what difference does it make to the "index". None. To the table, yes, you either delete rows from all differing blocks or rows from a small number of blocks but to the index, it makes no difference, hence
Re: rebuilding indexes - sure to cause a ruckus
Ouch, I gotta take a day off to read this one ;) Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, December 08, 2003 11:29 PM > Hi Yong, > > Saying there are a "few" errors is being a little kind to Don's "Inside > Oracle Indexing" article. > > In part, these are some of the issues I raised directly with Don in a number > of emails (warning somewhat on the longish side ;): > a.. There are no such things as star indexes. Star joins, yes, star > transformations yes, but not star indexes ? > b.. I still disagree with your description of b-tree indexes being complex > and difficult to understand, but then again this could just be my personal > perception (check out > http://groups.google.com/groups?q=g:thl3498916429d&dq=&hl=en&lr=&ie=UTF-8&se > lm=ant%259.39604%24jM5.100537%40newsfeeds.bigpond.com&rnum=47 where I have a > sample demo on how to investigate the workings of b-tree indexes.) However, > by understanding them and a how they function, the question of whether or > not they need rebuilding no longer needs to be debated. It becomes easily > apparent under what conditions indexes could benefit from a rebuild. I'll > expand on this later but I would suggest those that "debate", those that > really don't know when a rebuild is justified and just rebuild in the hope > it might do some good are those that really don't understand "how" indexes > function. Knowledge is the key that unlocks the door of doubt and those > without the key fumble aimlessly and prod around in hope... > c.. Your subsequent quote "There is enough anecdotal evidence that index > rebuilding has helped some systems perform better, and I also have no doubt > that there is no scientific basis for the claim" is a nonsense. Of course > one explain in scientific terms such performance improvements, I can only > suggest that you unfortunately can't. Oracle is not some magic piece of > software and it doesn't run on some magical pieces of hardware. Any > suggestions to the contrary are not helpful to anyone. > d.. I still disagree with the double the block size, halving the logical > reads must be a good thing argument. It's a path that could lead to a very > disappointing conclusion (read cliff edge). Indexes prefer large block sizes > true but if the underlining storage file-system is not tuned to read (or > write) these larger block sizes efficiently, then the whole thing is counter > productive. You've been warned ... > e.. Your description of PCTUSED is still wrong. There is no PCTUSED for > indexes so it really shouldn't be misleading to confuse a non-existing index > attribute with the amount of used space as documented in INDEX_STATS... > f.. Including in your criteria for rebuilding an index "btree_space being > greater than a block" is redundant when listed with the other criteria. It > is fundamentally impossible for an index with 4 levels or more to consist of > a single block, so why mention it. It just adds confusion and is silly. The > DBA who swears by this criteria (which I noticed has changed in this draft > ;), how do they make such a claim? It's one thing to swear, it's quite > another to prove. Your table that lists average rows and blocks per > different index levels shows that those indexes with a leaf row length of > 500,000,000 and with 100,000 blocks require 4 levels. How does rebuilding > such indexes with no subsequent change in index level improve performance ? > I mean, large indexes need more levels right, so rebuilding them all the > time and keeping the levels unchanged only to rebuild them again because > they're still 4 or more levels seems like a pointless, never-ending exercise > in futility. To rebuild an index that "actually" results in a reduction in > it's level generally requires a "drastic" reduction in it's data volume due > to the orders of sizing magnitude that a new level represents. More on this > and the other so-called rebuild criteria later but the current level of an > index is not a criteria for a rebuild. A level 3 index could conceivably be > rebuilt to just a level 1 (if there were heaps and heaps of deletions) and a > level 5 index could be rebuilt to stay at level 5. Which index has > benefited . > g.. Criteria for a rebuild: or the total length of deleted is > 1 block > makes no sense whatsoever. Nearly all indexes would have a total length of > deleted > than 1 block meaning nearly all indexes need rebuilding. I don't > think so ... > h.. Your discussion on the clustering factor affecting the likelihood of > requiring an index rebuild is still flawed, however interestingly, you've > now given an example on why this is the case. However, you've still come to > the wrong conclusion !! Firstly, you're incorrect in your example to say > that a 1,000,000 row table with a clustering factor of 1,000,000 has it's > rows in the same order as it's index although I guess this could be a typo. > Regardless, if you
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-5
Re: rebuilding indexes - sure to cause a ruckus
Tanel, I think you're saying a query almost always runs faster right after the index rebuild and there's no point in finding the criterion whether to rebuild an index. (What is "42"?) Some time ago I posted a message somewhere else showing a case where rebuilding or coalescing an index may be benefitial. A data warehouse is found to have some data errors. Deletes and updates are done. Then the database goes to mostly read-only again, and will last for a month or quarter. Then shrinking frequently used B*Tree indexes is a good idea. Now I'd like to add one more criterion as a result of reading Jonathan Lewis' dbazine article and email with him (errors are mine): the index is full scanned, or if range scanned or unique scanned, the index selectivity has to be fairly low (but not too low for the index to be ignored by CBO). In a typical working environment, a data warehouse does have plenty of relatively quiet period. I worked on a monthly data load project at an insurance company. I remember we rebuilt a partitioned IOT (one partition at a time) and fast full index scan (certain partitions) did run faster. There're some errors in Don Burleson's dbazine article (e.g. pct_used in dba_indexes) and Mike Hordila's Oramag article (structurally unbalanced index). But one thing alluded to in there is important: study Oracle performance problems as scientific research. You said setting _wait_for_sync to false improves performance. That's a fact. We can only explain and analyze it but not deny it. Similarly, when Mike says queries run 10 to 50% faster after index rebuild, we can't deny unless we find his measurement is wrong. Wouldn't it be nice if Oracle researchers write articles with sections like Abstract - Experimental - Results - Discussion in that order? Yong Huang Tanel Poder wrote: There's no point of arguing about whether a query ran faster right after you rebuilt your index. Nor there is no point in finding some ultimate algorithm for finding the point of index rebuilding, we all know the answer - it's "42". Instead, a long stress test has to be done, e.g. running 10 millions of continous transactions and queries (simulating real life). Do one 10M without rebuilding indexes in the meantime, measure total execution time, IO amount, CPU usage, segment sizes etc. Then restore your database back to starting point and do the same test again with regular index rebuilds during the operations (online or taking "users" offline, depending on environment type). And then measure the same statistics, especially total execution time. Note, that statistics and time also for rebuilding indexes should be accounted in totals, because in real life they don't just disappear somewhere as in some simple-minded tests. Tanel. __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Yong Huang INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: rebuilding indexes - sure to cause a ruckus
> > 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. Ok, my mistake. Would "simulating real workload" do the trick? Tanel. -- 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
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: 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
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
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: 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
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
Chitale 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).
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=NOT&p_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
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:
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=NOT&p_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-Ma
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 ind
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=NOT&p_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 ma
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: [EMA
Re: re Rebuilding Indexes in Oracle Apps --
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 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
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 li
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: 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
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:
- 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 li
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 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 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 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 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:
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 time to 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:
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: 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 -- 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_te
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
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 --- Begin Message --- 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
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
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
>
>
> Jo
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
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
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 ... 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:
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
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 performanc
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. 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: 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:
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-- 8
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
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
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 Rafiq 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).
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
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: 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
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 -- 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
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 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 th
RE: Rebuilding Indexes...
Great point. A perfectly randomized used ideal B-Tree index would be 75% full: just split blocks would be 50% full, there would also be some blocks that had reached 100%, and of course most blocks would fall somewhere in between. The average for all blocks would be 75% full. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Monday, December 30, 2002 11:09 AM To: Multiple recipients of list ORACLE-L 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 >
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
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 > -- > Auth
Re: Rebuilding Indexes...
Jared, thanks for the "asktom" article, very interesting reading - I'm not
100% sure I agree with him though.I just recently went through the
exercise to rebuild our indexes into locally-managed tablespaces and I
saved over 100GB of space through the rebuild process - as an example I had
a 51GB unique index that went to 30GB after the rebuild (I'm not implying
locally-managed had anything to do with the space savings, that was just
for reducing/eliminating fragmentation).The indexes had not been
rebuilt in over a year. Our DSS database is next; I expect to save at
least that plus more in space.Not to discount Tom's arguments but I was
very happy with the space savings and depending on your environment that
can be very helpful/worthwhile.
Just my opinion - guess it depends on your objectives. This
discussion has definitely got me thinking in some different directions
which is always a good thing.
thanks again,
John D.
Atlanta, GA
"Jared Still"
<[EMAIL PROTECTED]To: "Multiple recipients of list
ORACLE-L" <[EMAIL PROTECTED]>
om> cc: (bcc: John
Dailey/NAC/ING-FSI-NA)
Sent by: Subject: Re: Rebuilding Indexes...
[EMAIL PROTECTED]
12/26/2002 10:13
PM
Please respond to
ORACLE-L
Though I have published a script for determining indexes that
need to be rebuilt, and then rebuilding them, I have to say that
this is almost never necessary.
Why are you rebuilding indexes? About the only reason for ever
doing so is that the BLEVEL >= 5.
goto asktom.oracle.com, and do a search on 'index rebuild'.
Currently, the third article may be of interest.
Jared
On Thursday 26 December 2002 12:24, Richard Huntley wrote:
> Anyone have any useful scripts for doing this?
>
> TIA,
> Rich
Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
Content-Transfer-Encoding: 7bit
Content-Description:
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Rebuilding Indexes...
On Saturday 28 December 2002 20:08, Arup Nanda wrote: > Jared, > > Did you attach the scripts? No, but they're free to download at http://www.oreilly.com/catalog/oracleperl/pdbatoolkit/ww.oreilly.com/catalog/oracleperl/. The script in question is idxr.pl. The algorithm was lifted ( with credit ) from the famous 'How to stop defragmenting...' paper. By the way, the scripts are all Perl. :) Jared > > I use the index rebuilding regularly for certain applications where buffer > busy waits are prevalent. No, let's not go there why the buffer busy waits > occur and whether reverse key indexes would help. All these are paths well > trodden. I use a home grown setup where I ANALYZE VALIDATE STRUCTURE each > index and immediately store the INDEX_STATS rw in a table called > INDCHK_INDEX_STATS. Then I use the following script to identify the > potential indexes candidate for rebuilding. The Height, "Compression > Factor", Delete% and "Hole Factor" as calculated below provide an > indication whether the index can be considered to be rebuilt. There is no > hard threshold value for each, based on all three, I decide whether the > index needs to be rebuilt. > > Finally, how did I come up with the seemingly labyrinthine formulae below? > Parts of them are "stolen" from the OEM tool's index check program. I > snooped around when the tool was analyzing the indexes and captured the > code, modified to some extent and placed in a nice script. It works for me. > The indexes are placed in LMT with non-uniform extents and the database is > 8.1.7.4. > > Yes, I know this will probably spark all sorts of reaction; but I would > appreciate any feedback on the process. > > Arup Nanda > > col name format a30 head "Index Name" > col comp_factor head "Compactness" > col hole_factor format head "Hole" > col del_pct format head "Del%" > col height format 9 head "Height" > SELECT NAME, HEIGHT, > DECODE(HEIGHT, 1, 100, > FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100) / > (LF_BLK_LEN * LF_BLKS))) Comp_Factor, > DECODE(HEIGHT, 1, 0, DECODE(LF_ROWS - DEL_LF_ROWS, 0, 1, > DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN / BR_ROWS), > LF_BLK_LEN / > ((LF_ROWS_LEN - DEL_LF_ROWS_LEN) / > (LF_ROWS - DEL_LF_ROWS) - HEIGHT, -1, 1, 0))) + > DECODE(LF_ROWS_LEN, 0, 0, > FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100)) Hole_Factor, > round(decode(lf_rows,0,0,100*DEL_LF_ROWS/LF_ROWS),0) del_pct > FROM INDCHK_INDEX_STATS > where height > 3 > or DECODE(HEIGHT, 1, 100, > FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100) / > (LF_BLK_LEN * LF_BLKS))) < 80 > or DECODE(HEIGHT, 1, 0, DECODE(LF_ROWS - DEL_LF_ROWS, 0, 1, > DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN / BR_ROWS), > LF_BLK_LEN / > ((LF_ROWS_LEN - DEL_LF_ROWS_LEN) / > (LF_ROWS - DEL_LF_ROWS) - HEIGHT, -1, 1, 0))) + > DECODE(LF_ROWS_LEN, 0, 0, > FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100)) > 10 > or decode(lf_rows,0,0,100*DEL_LF_ROWS/LF_ROWS) > 9 > order by 3 desc, 2, 1 > / > > > > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, December 26, 2002 10:13 PM > > > Though I have published a script for determining indexes that > > need to be rebuilt, and then rebuilding them, I have to say that > > this is almost never necessary. > > > > Why are you rebuilding indexes? About the only reason for ever > > doing so is that the BLEVEL >= 5. > > > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > > > Currently, the third article may be of interest. > > > > Jared > > > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > > Anyone have any useful scripts for doing this? > > > > > > TIA, > > > Rich > > > > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" > > Content-Transfer-Encoding: 7bit > > Content-Description: > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Jared Still > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1" Content-Transfer-Encoding: quoted-printable Content-Description: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET:
Re: Rebuilding Indexes...
Tim, Thanks for the compliments. You are right, HEIGHT > 3 is not correct. I lifted it off another place where I temporarily used the HEIGHT > 3 predicate; in the version I always use, height is not in the filter. Again, just because height > , it would not indicate a case for rebuilding; rather a subjective evaluation of all four factors presented would be inputs for decision making. The BLKS_GETS_PER_ACCESS part is interesting; I will research that. Thanks for sharing that information. Arup - Original Message - From: Tim Gorman To: Multiple recipients of list ORACLE-L Sent: Sunday, December 29, 2002 1:33 PM Subject: Re: Rebuilding Indexes... Arup, Excellent practice! I have written much the same scripts, except I use them as an extension to STATSPACK and named the resulting persistent table CSTATS$INDEX_STATS. Nevertheless, I'm curious about the formula and will test it out. I'm a little concerned about the HEIGHT > 3 in the WHERE clause, as even "small" indexes can go awry (i.e. HEIGHT <= 3 can still involve hundreds of thousands of rows and thousands of blocks)... Another use for data from INDEX_STATS -- when the value in the BLKS_GETS_PER_ACCESS column exceeds several hundred or several thousand blocks, you have to question the effectiveness of the B*Tree index itself and whether or not it should be dropped. Chances are good that the CBO is ignoring it anyway, so you are paying for the storage costs of the index and the processing costs of maintaining it, but not using it. By no means is it open-and-shut that the index should be dropped -- there might be SQL statements effciently using the index to take advantage of skewed data distribution -- but it should be researched and considered for the old "drop kick" nonetheless. Thanks again! -Tim - Original Message - From: Arup Nanda To: Multiple recipients of list ORACLE-L Sent: Saturday, December 28, 2002 9:08 PM Subject: Re: Rebuilding Indexes... Jared, Did you attach the scripts? I use the index rebuilding regularly for certain applications where buffer busy waits are prevalent. No, let's not go there why the buffer busy waits occur and whether reverse key indexes would help. All these are paths well trodden. I use a home grown setup where I ANALYZE VALIDATE STRUCTURE each index and immediately store the INDEX_STATS rw in a table called INDCHK_INDEX_STATS. Then I use the following script to identify the potential indexes candidate for rebuilding. The Height, "Compression Factor", Delete% and "Hole Factor" as calculated below provide an indication whether the index can be considered to be rebuilt. There is no hard threshold value for each, based on all three, I decide whether the index needs to be rebuilt. Finally, how did I come up with the seemingly labyrinthine formulae below? Parts of them are "stolen" from the OEM tool's index check program. I snooped around when the tool was analyzing the indexes and captured the code, modified to some extent and placed in a nice script. It works for me. The indexes are placed in LMT with non-uniform extents and the database is 8.1.7.4. Yes, I know this will probably spark all sorts of reaction; but I would appreciate any feedback on the process. Arup Nanda col name format a30 head "Index Name"col comp_factor head "Compactness"col hole_factor format head "Hole"col del_pct format head "Del%"col height format 9 head "Height"SELECT NAME, HEIGHT, DECODE(HEIGHT, 1, 100, FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100) / (LF_BLK_LEN * LF_BLKS))) Comp_Factor, DECODE(HEIGHT, 1, 0, DECODE(LF_ROWS - DEL_LF_ROWS, 0, 1, DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN / BR_ROWS), LF_BLK_LEN / ((LF_ROWS_LEN - DEL_LF_ROWS_LEN) / (LF_ROWS - DEL_LF_ROWS) - HEIGHT, -1, 1, 0))) + DECODE(LF_ROWS_LEN, 0, 0, FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100)) Hole_Factor, round(decode(lf_rows,0,0,100*DEL_LF_ROWS/LF_ROWS),0) del_pctFROM INDCHK_INDEX_STATSwhere height > 3or DECODE(HEIGHT, 1, 100, FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100) / (LF_BLK_LEN * LF_BLKS))) < 80or DECODE(HEIGHT, 1, 0, DECODE(LF_ROWS - DEL_LF_ROWS, 0, 1, DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN / BR_ROWS), LF_BLK_LEN / ((LF_ROWS_LEN - DEL_LF_ROWS_LEN) / (LF_ROWS - DEL_LF_ROWS) - HEIGHT, -1, 1, 0))) + DECODE(LF_ROWS_LEN, 0, 0, FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 10
Re: Rebuilding Indexes, Now: KEEP INDEX
On the contrary, Connor, it's of tremendous political value, if not practical ;) Besides, the fact that unique indexes may have null values whereas PKs can't. I have found the use in this case: In the US, we all have a Social Security Number (SSN), a sort of like a birth mark ever since we were born. An insurance carrier might have the SSN as a primary key and that would work out fine, till one fine day they decide to accept visitors in a new plan for temporary health insurance coverage. These people have no SSN, making it null. Suddenly the PK on SSN was dropped and a synthetic key containing SSN and a sequence number (9 for SSN is null) was chosen as SSN; but the SSN index was kept since that was unique anyway. With 40 GB tables (in all) this option to keep index came pretty handy. Thanks. Arup - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Sunday, December 29, 2002 3:23 PM > I'm a little doubtful about the value of 'keep index'. > > Consider the scenarios: > > unique constraint, non-unique index: > - "keep index" redundant because its kept anyway > > unique constraint, unique index: > - "keep index" redundant because effectively retains > the constraint anyway (because you still can't insert > dups) > > > So far, the only use for KEEP INDEX I've found is the > scenario where you: > > - decided that column(s) X was the primary key > - created a unique index on it > - created a primary key constraint on it > - loaded the data > - decided actually X was NOT the primary key, just a > unique value > - decided that X could allow nulls as well > - dropped the primary kept, kept the index and then > added a unique constraint... > > I would contend that this is a rare occurrence ? > > Cheers > Connor > > > --- Rachel Carmichael <[EMAIL PROTECTED]> wrote: > > sigh. I need to find time to read ALL the docs. > > Yeah, that'll happen. > > If I can find a parallel universe where time runs at > > a different rate. > > > > Thanks, I'll test this out as well. > > > > > > --- Arup Nanda <[EMAIL PROTECTED]> wrote: > > > In 9.2, you can keep the index by using the KEEP > > INDEX key words. > > > > > > ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX > > > > > > This will keep the index but drop the constraint. > > Talk about having > > > your > > > cake and eating it too...;) > > > > > > HTH > > > > > > Arup > > > - Original Message - > > > To: "Multiple recipients of list ORACLE-L" > > <[EMAIL PROTECTED]> > > > Sent: Friday, December 27, 2002 4:39 PM > > > > > > > > > > it'll have to wait until Monday, I'm not at work > > until then. I'll > > > try > > > > it with a non-unique then > > > > > > > > Hey, if it works, it saves me tons of time, I > > learn something new > > > and I > > > > had fun developing the single SQL statement to > > rebuild the > > > constraint > > > > and index. Win-win > > > > > > > > > > > > Rachel > > > > > > > > --- Denny Koovakattu <[EMAIL PROTECTED]> > > wrote: > > > > > > > > > > > > > > > I don't have access to 9.2.0.1 right now. > > But can you try > > > creating > > > > > a non- > > > > > unique index instead of the unique index. If > > you create a unique > > > > > index, it gets > > > > > dropped. That's the behavior on 8.1.x also. > > But if it's a > > > non-unique > > > > > index, it > > > > > shouldn't get dropped. > > > > > > > > > > Regards, > > > > > Denny > > > > > > > > > > Quoting Rachel Carmichael > > <[EMAIL PROTECTED]>: > > > > > > > > > > > 9.2.0.1 Solaris, and yes, it does drop it > > > > > > > > > > > > I created a unique index in the primary key > > columns > > > > > > I created the primary key constraint without > > specifying an > > > index > > > > > > I checked that the index existed, it did > > > > > > I dropped the primary key constraint > > > > > > I checked that the index existed, it didn't > > > > > > > > > > > > try it I tried various combinations > > before posting this > > > note > > > > > > > > > > > > > > > > > > --- Denny Koovakattu <[EMAIL PROTECTED]> > > wrote: > > > > > > > > > > > > > > > > > > > > > If you build a separate index to enforce > > the primary key, > > > > > Oracle > > > > > > > shouldn't > > > > > > > drop it when you disable or drop the > > primary key. > > > > > > > > > > > > > > Regards, > > > > > > > Denny > > > > > > > > > > > > > > Quoting Rachel Carmichael > > <[EMAIL PROTECTED]>: > > > > > > > > > > > > > > > Here's a reason: > > > > > > > > > > > > > > > > have you ever tried to find the three > > duplicate rows in a > > > 12 > > > > > > > million > > > > > > > > row table without using the primary key > > constraint? I've > > > had to > > > > > > > > disable > > > > > > > > or drop the constraint in order to use > > the exceptions > > > table. > > > > > Once > > > > > > I > > > > > > > do > > > > > > > > that, even if I've built a separate > > index that enforces the > > > > > > primary > > > > > > > > key > > > > > > > > constraint, Oracle drops t
Re: Rebuilding Indexes...
Arup, Excellent practice! I have written much the same scripts, except I use them as an extension to STATSPACK and named the resulting persistent table CSTATS$INDEX_STATS. Nevertheless, I'm curious about the formula and will test it out. I'm a little concerned about the HEIGHT > 3 in the WHERE clause, as even "small" indexes can go awry (i.e. HEIGHT <= 3 can still involve hundreds of thousands of rows and thousands of blocks)... Another use for data from INDEX_STATS -- when the value in the BLKS_GETS_PER_ACCESS column exceeds several hundred or several thousand blocks, you have to question the effectiveness of the B*Tree index itself and whether or not it should be dropped. Chances are good that the CBO is ignoring it anyway, so you are paying for the storage costs of the index and the processing costs of maintaining it, but not using it. By no means is it open-and-shut that the index should be dropped -- there might be SQL statements effciently using the index to take advantage of skewed data distribution -- but it should be researched and considered for the old "drop kick" nonetheless. Thanks again! -Tim - Original Message - From: Arup Nanda To: Multiple recipients of list ORACLE-L Sent: Saturday, December 28, 2002 9:08 PM Subject: Re: Rebuilding Indexes... Jared, Did you attach the scripts? I use the index rebuilding regularly for certain applications where buffer busy waits are prevalent. No, let's not go there why the buffer busy waits occur and whether reverse key indexes would help. All these are paths well trodden. I use a home grown setup where I ANALYZE VALIDATE STRUCTURE each index and immediately store the INDEX_STATS rw in a table called INDCHK_INDEX_STATS. Then I use the following script to identify the potential indexes candidate for rebuilding. The Height, "Compression Factor", Delete% and "Hole Factor" as calculated below provide an indication whether the index can be considered to be rebuilt. There is no hard threshold value for each, based on all three, I decide whether the index needs to be rebuilt. Finally, how did I come up with the seemingly labyrinthine formulae below? Parts of them are "stolen" from the OEM tool's index check program. I snooped around when the tool was analyzing the indexes and captured the code, modified to some extent and placed in a nice script. It works for me. The indexes are placed in LMT with non-uniform extents and the database is 8.1.7.4. Yes, I know this will probably spark all sorts of reaction; but I would appreciate any feedback on the process. Arup Nanda col name format a30 head "Index Name"col comp_factor head "Compactness"col hole_factor format head "Hole"col del_pct format head "Del%"col height format 9 head "Height"SELECT NAME, HEIGHT, DECODE(HEIGHT, 1, 100, FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100) / (LF_BLK_LEN * LF_BLKS))) Comp_Factor, DECODE(HEIGHT, 1, 0, DECODE(LF_ROWS - DEL_LF_ROWS, 0, 1, DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN / BR_ROWS), LF_BLK_LEN / ((LF_ROWS_LEN - DEL_LF_ROWS_LEN) / (LF_ROWS - DEL_LF_ROWS) - HEIGHT, -1, 1, 0))) + DECODE(LF_ROWS_LEN, 0, 0, FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100)) Hole_Factor, round(decode(lf_rows,0,0,100*DEL_LF_ROWS/LF_ROWS),0) del_pctFROM INDCHK_INDEX_STATSwhere height > 3or DECODE(HEIGHT, 1, 100, FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100) / (LF_BLK_LEN * LF_BLKS))) < 80or DECODE(HEIGHT, 1, 0, DECODE(LF_ROWS - DEL_LF_ROWS, 0, 1, DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN / BR_ROWS), LF_BLK_LEN / ((LF_ROWS_LEN - DEL_LF_ROWS_LEN) / (LF_ROWS - DEL_LF_ROWS) - HEIGHT, -1, 1, 0))) + DECODE(LF_ROWS_LEN, 0, 0, FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100)) > 10or decode(lf_rows,0,0,100*DEL_LF_ROWS/LF_ROWS) > 9order by 3 desc, 2, 1/ - Original Message - From: "Jared Still" <[EMAIL PROTECTED]> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, December 26, 2002 10:13 PM Subject: Re: Rebuilding Indexes... > > Though I have published a script for determining indexes that> need to be rebuilt, and then rebuilding them, I have to say that> this is almost never necessary.> > Why are you rebuilding indexes? About the only reason for ever> doing so is that the BLEVEL >= 5.> > goto asktom.oracle.com, and do a search on 'index rebuild'.> > Currently, the third article may be of interest.> > Jared> > On Thursday 26 December 2002 12:24, Richard Huntley wrote
Re: Rebuilding Indexes...
phew! I'm not the only one who doesn't know stuff? Thank you for salvaging my ego! Rachel --- Jared Still <[EMAIL PROTECTED]> wrote: > > Ohhh, something else I didn't know yet! > > Which is why I keep reading this list. > > That, and playing referee. ;) > > Jared > > > On Saturday 28 December 2002 17:48, Arup Nanda wrote: > > In 9.2, you can keep the index by using the KEEP INDEX key words. > > > > ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX > > > > This will keep the index but drop the constraint. Talk about having > your > > cake and eating it too...;) > > > > HTH > > > > Arup > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Friday, December 27, 2002 4:39 PM > > > > > it'll have to wait until Monday, I'm not at work until then. I'll > try > > > it with a non-unique then > > > > > > Hey, if it works, it saves me tons of time, I learn something new > and I > > > had fun developing the single SQL statement to rebuild the > constraint > > > and index. Win-win > > > > > > > > > Rachel > > > > > > --- Denny Koovakattu <[EMAIL PROTECTED]> wrote: > > > > I don't have access to 9.2.0.1 right now. But can you try > creating > > > > a non- > > > > unique index instead of the unique index. If you create a > unique > > > > index, it gets > > > > dropped. That's the behavior on 8.1.x also. But if it's a > non-unique > > > > index, it > > > > shouldn't get dropped. > > > > > > > > Regards, > > > > Denny > > > > > > > > Quoting Rachel Carmichael <[EMAIL PROTECTED]>: > > > > > 9.2.0.1 Solaris, and yes, it does drop it > > > > > > > > > > I created a unique index in the primary key columns > > > > > I created the primary key constraint without specifying an > index > > > > > I checked that the index existed, it did > > > > > I dropped the primary key constraint > > > > > I checked that the index existed, it didn't > > > > > > > > > > try it I tried various combinations before posting this > note > > > > > > > > > > --- Denny Koovakattu <[EMAIL PROTECTED]> wrote: > > > > > > If you build a separate index to enforce the primary key, > > > > > > > > Oracle > > > > > > > > > > shouldn't > > > > > > drop it when you disable or drop the primary key. > > > > > > > > > > > > Regards, > > > > > > Denny > > > > > > > > > > > > Quoting Rachel Carmichael <[EMAIL PROTECTED]>: > > > > > > > Here's a reason: > > > > > > > > > > > > > > have you ever tried to find the three duplicate rows in a > 12 > > > > > > > > > > > > million > > > > > > > > > > > > > row table without using the primary key constraint? I've > had to > > > > > > > disable > > > > > > > or drop the constraint in order to use the exceptions > table. > > > > > > > > Once > > > > > > > > > I > > > > > > > > > > > do > > > > > > > > > > > > > that, even if I've built a separate index that enforces > the > > > > > > > > > > primary > > > > > > > > > > > > key > > > > > > > constraint, Oracle drops the index. So I HAVE to rebuild > it. If > > > > > > > > I > > > > > > > > > > > allow > > > > > > > the index to be rebuilt when I re-enable the primary key > > > > > > > > > > > > constraint, > > > > > > > > > > > > > it > > > > > > > builds it in the default tablespace of the table owner, > not > > > > > > > > where > > > > > > > > > I > > > > > > > > > > > > want it. > > > > > > > > > > > > > > if anyone has a better way to fix this problem, I'm more > than > > > > > > > > > > happy > > > > > > > > > > > to > > > > > > > > > > > > > hear it! It's a data warehouse and the third party app > has a > > > > > > > > bug > > > > > > > > > we > > > > > > > > > > > > can't find and on occasion sqlloads (via direct path) > duplicate > > > > > > > > > > > > rows > > > > > > > > > > > > > Rachel > > > > > > > > > > > > > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > > > > > > > Though I have published a script for determining > indexes that > > > > > > > > need to be rebuilt, and then rebuilding them, I have > to say > > > > > > > > > > that > > > > > > > > > > > > > this is almost never necessary. > > > > > > > > > > > > > > > > Why are you rebuilding indexes? About the only reason > for > > > > > > > > ever > > > > > > > > > > > > doing so is that the BLEVEL >= 5. > > > > > > > > > > > > > > > > goto asktom.oracle.com, and do a search on 'index > rebuild'. > > > > > > > > > > > > > > > > Currently, the third article may be of interest. > > > > > > > > > > > > > > > > Jared > > > > > > > > > > > > > > > > On Thursday 26 December 2002 12:24, Richard Huntley > wrote: > > > > > > > > > Anyone have any useful scripts for doing this? > > > > > > > > > > > > > > > > > > TIA, > > > > > > > > > Rich > > > > > > > > > > > > > > > > > > > > > > > > Content-Type: text/html; charset="iso-8859-1"; > > > > > > > > name="Attachment: > > > > > > 1" > > > > > > > > > > > > > > Content-Transfer-Encoding: 7bit > > > > > > > > Content-Description: > > > > > > > > ---
Re: Rebuilding Indexes...
a few more undocumented commands that might help: ALTER UNIVERSE cosmos PARALLEL 16; ALTER UNIVERSE cosmos INCREMENT BY hour; > sigh. I need to find time to read ALL the docs. Yeah, that'll happen. > If I can find a parallel universe where time runs at a different rate. > > Thanks, I'll test this out as well. > > > --- Arup Nanda <[EMAIL PROTECTED]> wrote: > > In 9.2, you can keep the index by using the KEEP INDEX key words. > > > > ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX > > > > This will keep the index but drop the constraint. Talk about having > > your > > cake and eating it too...;) > > > > HTH > > > > Arup > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Friday, December 27, 2002 4:39 PM > > > > > > > it'll have to wait until Monday, I'm not at work until then. I'll > > try > > > it with a non-unique then > > > > > > Hey, if it works, it saves me tons of time, I learn something new > > and I > > > had fun developing the single SQL statement to rebuild the > > constraint > > > and index. Win-win > > > > > > > > > Rachel > > > > > > --- Denny Koovakattu <[EMAIL PROTECTED]> wrote: > > > > > > > > > > > > I don't have access to 9.2.0.1 right now. But can you try > > creating > > > > a non- > > > > unique index instead of the unique index. If you create a unique > > > > index, it gets > > > > dropped. That's the behavior on 8.1.x also. But if it's a > > non-unique > > > > index, it > > > > shouldn't get dropped. > > > > > > > > Regards, > > > > Denny > > > > > > > > Quoting Rachel Carmichael <[EMAIL PROTECTED]>: > > > > > > > > > 9.2.0.1 Solaris, and yes, it does drop it > > > > > > > > > > I created a unique index in the primary key columns > > > > > I created the primary key constraint without specifying an > > index > > > > > I checked that the index existed, it did > > > > > I dropped the primary key constraint > > > > > I checked that the index existed, it didn't > > > > > > > > > > try it I tried various combinations before posting this > > note > > > > > > > > > > > > > > > --- Denny Koovakattu <[EMAIL PROTECTED]> wrote: > > > > > > > > > > > > > > > > > > If you build a separate index to enforce the primary key, > > > > Oracle > > > > > > shouldn't > > > > > > drop it when you disable or drop the primary key. > > > > > > > > > > > > Regards, > > > > > > Denny > > > > > > > > > > > > Quoting Rachel Carmichael <[EMAIL PROTECTED]>: > > > > > > > > > > > > > Here's a reason: > > > > > > > > > > > > > > have you ever tried to find the three duplicate rows in a > > 12 > > > > > > million > > > > > > > row table without using the primary key constraint? I've > > had to > > > > > > > disable > > > > > > > or drop the constraint in order to use the exceptions > > table. > > > > Once > > > > > I > > > > > > do > > > > > > > that, even if I've built a separate index that enforces the > > > > > primary > > > > > > > key > > > > > > > constraint, Oracle drops the index. So I HAVE to rebuild > > it. If > > > > I > > > > > > > allow > > > > > > > the index to be rebuilt when I re-enable the primary key > > > > > > constraint, > > > > > > > it > > > > > > > builds it in the default tablespace of the table owner, not > > > > where > > > > > I > > > > > > > want it. > > > > > > > > > > > > > > if anyone has a better way to fix this problem, I'm more > > than > > > > > happy > > > > > > to > > > > > > > hear it! It's a data warehouse and the third party app has > > a > > > > bug > > > > > we > > > > > > > can't find and on occasion sqlloads (via direct path) > > duplicate > > > > > > rows > > > > > > > > > > > > > > Rachel > > > > > > > > > > > > > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > > > > > > > > > > > > > > > Though I have published a script for determining indexes > > that > > > > > > > > need to be rebuilt, and then rebuilding them, I have to > > say > > > > > that > > > > > > > > this is almost never necessary. > > > > > > > > > > > > > > > > Why are you rebuilding indexes? About the only reason > > for > > > > ever > > > > > > > > doing so is that the BLEVEL >= 5. > > > > > > > > > > > > > > > > goto asktom.oracle.com, and do a search on 'index > > rebuild'. > > > > > > > > > > > > > > > > Currently, the third article may be of interest. > > > > > > > > > > > > > > > > Jared > > > > > > > > > > > > > > > > On Thursday 26 December 2002 12:24, Richard Huntley > > wrote: > > > > > > > > > Anyone have any useful scripts for doing this? > > > > > > > > > > > > > > > > > > TIA, > > > > > > > > > Rich > > > > > > > > > > > > > > > > > > > > > > > > Content-Type: text/html; charset="iso-8859-1"; > > > > name="Attachment: > > > > > > 1" > > > > > > > > Content-Transfer-Encoding: 7bit > > > > > > > > Content-Description: > > > > > > > > > > > > > > > > -- > > > > > > > > Please see the official ORACLE-L FAQ: > > http://www.orafaq.ne
Re: Rebuilding Indexes...
Jared, Did you attach the scripts? I use the index rebuilding regularly for certain applications where buffer busy waits are prevalent. No, let's not go there why the buffer busy waits occur and whether reverse key indexes would help. All these are paths well trodden. I use a home grown setup where I ANALYZE VALIDATE STRUCTURE each index and immediately store the INDEX_STATS rw in a table called INDCHK_INDEX_STATS. Then I use the following script to identify the potential indexes candidate for rebuilding. The Height, "Compression Factor", Delete% and "Hole Factor" as calculated below provide an indication whether the index can be considered to be rebuilt. There is no hard threshold value for each, based on all three, I decide whether the index needs to be rebuilt. Finally, how did I come up with the seemingly labyrinthine formulae below? Parts of them are "stolen" from the OEM tool's index check program. I snooped around when the tool was analyzing the indexes and captured the code, modified to some extent and placed in a nice script. It works for me. The indexes are placed in LMT with non-uniform extents and the database is 8.1.7.4. Yes, I know this will probably spark all sorts of reaction; but I would appreciate any feedback on the process. Arup Nanda col name format a30 head "Index Name"col comp_factor head "Compactness"col hole_factor format head "Hole"col del_pct format head "Del%"col height format 9 head "Height"SELECT NAME, HEIGHT, DECODE(HEIGHT, 1, 100, FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100) / (LF_BLK_LEN * LF_BLKS))) Comp_Factor, DECODE(HEIGHT, 1, 0, DECODE(LF_ROWS - DEL_LF_ROWS, 0, 1, DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN / BR_ROWS), LF_BLK_LEN / ((LF_ROWS_LEN - DEL_LF_ROWS_LEN) / (LF_ROWS - DEL_LF_ROWS) - HEIGHT, -1, 1, 0))) + DECODE(LF_ROWS_LEN, 0, 0, FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100)) Hole_Factor, round(decode(lf_rows,0,0,100*DEL_LF_ROWS/LF_ROWS),0) del_pctFROM INDCHK_INDEX_STATSwhere height > 3or DECODE(HEIGHT, 1, 100, FLOOR(((LF_ROWS_LEN - DEL_LF_ROWS_LEN) * 100) / (LF_BLK_LEN * LF_BLKS))) < 80or DECODE(HEIGHT, 1, 0, DECODE(LF_ROWS - DEL_LF_ROWS, 0, 1, DECODE(SIGN(CEIL(LOG(BR_BLK_LEN / (BR_ROWS_LEN / BR_ROWS), LF_BLK_LEN / ((LF_ROWS_LEN - DEL_LF_ROWS_LEN) / (LF_ROWS - DEL_LF_ROWS) - HEIGHT, -1, 1, 0))) + DECODE(LF_ROWS_LEN, 0, 0, FLOOR((DEL_LF_ROWS_LEN/LF_ROWS_LEN) * 100)) > 10or decode(lf_rows,0,0,100*DEL_LF_ROWS/LF_ROWS) > 9order by 3 desc, 2, 1/ - Original Message - From: "Jared Still" <[EMAIL PROTECTED]> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, December 26, 2002 10:13 PM Subject: Re: Rebuilding Indexes... > > Though I have published a script for determining indexes that> need to be rebuilt, and then rebuilding them, I have to say that> this is almost never necessary.> > Why are you rebuilding indexes? About the only reason for ever> doing so is that the BLEVEL >= 5.> > goto asktom.oracle.com, and do a search on 'index rebuild'.> > Currently, the third article may be of interest.> > Jared> > On Thursday 26 December 2002 12:24, Richard Huntley wrote:> > Anyone have any useful scripts for doing this?> >> > TIA,> > Rich> > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"> Content-Transfer-Encoding: 7bit> Content-Description: > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net> -- > Author: Jared Still> INET: [EMAIL PROTECTED]> > Fat City Network Services -- 858-538-5051 http://www.fatcity.com> San Diego, California -- Mailing list and web hosting services> -> To REMOVE yourself from this mailing list, send an E-Mail message> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in> the message BODY, include a line containing: UNSUB ORACLE-L> (or the name of mailing list you want to be removed from). You may> also send the HELP command for other information (like subscribing).>
Re: Rebuilding Indexes...
Ohhh, something else I didn't know yet! Which is why I keep reading this list. That, and playing referee. ;) Jared On Saturday 28 December 2002 17:48, Arup Nanda wrote: > In 9.2, you can keep the index by using the KEEP INDEX key words. > > ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX > > This will keep the index but drop the constraint. Talk about having your > cake and eating it too...;) > > HTH > > Arup > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Friday, December 27, 2002 4:39 PM > > > it'll have to wait until Monday, I'm not at work until then. I'll try > > it with a non-unique then > > > > Hey, if it works, it saves me tons of time, I learn something new and I > > had fun developing the single SQL statement to rebuild the constraint > > and index. Win-win > > > > > > Rachel > > > > --- Denny Koovakattu <[EMAIL PROTECTED]> wrote: > > > I don't have access to 9.2.0.1 right now. But can you try creating > > > a non- > > > unique index instead of the unique index. If you create a unique > > > index, it gets > > > dropped. That's the behavior on 8.1.x also. But if it's a non-unique > > > index, it > > > shouldn't get dropped. > > > > > > Regards, > > > Denny > > > > > > Quoting Rachel Carmichael <[EMAIL PROTECTED]>: > > > > 9.2.0.1 Solaris, and yes, it does drop it > > > > > > > > I created a unique index in the primary key columns > > > > I created the primary key constraint without specifying an index > > > > I checked that the index existed, it did > > > > I dropped the primary key constraint > > > > I checked that the index existed, it didn't > > > > > > > > try it I tried various combinations before posting this note > > > > > > > > --- Denny Koovakattu <[EMAIL PROTECTED]> wrote: > > > > > If you build a separate index to enforce the primary key, > > > > > > Oracle > > > > > > > > shouldn't > > > > > drop it when you disable or drop the primary key. > > > > > > > > > > Regards, > > > > > Denny > > > > > > > > > > Quoting Rachel Carmichael <[EMAIL PROTECTED]>: > > > > > > Here's a reason: > > > > > > > > > > > > have you ever tried to find the three duplicate rows in a 12 > > > > > > > > > > million > > > > > > > > > > > row table without using the primary key constraint? I've had to > > > > > > disable > > > > > > or drop the constraint in order to use the exceptions table. > > > > > > Once > > > > > > > I > > > > > > > > > do > > > > > > > > > > > that, even if I've built a separate index that enforces the > > > > > > > > primary > > > > > > > > > > key > > > > > > constraint, Oracle drops the index. So I HAVE to rebuild it. If > > > > > > I > > > > > > > > > allow > > > > > > the index to be rebuilt when I re-enable the primary key > > > > > > > > > > constraint, > > > > > > > > > > > it > > > > > > builds it in the default tablespace of the table owner, not > > > > > > where > > > > > > > I > > > > > > > > > > want it. > > > > > > > > > > > > if anyone has a better way to fix this problem, I'm more than > > > > > > > > happy > > > > > > > > > to > > > > > > > > > > > hear it! It's a data warehouse and the third party app has a > > > > > > bug > > > > > > > we > > > > > > > > > > can't find and on occasion sqlloads (via direct path) duplicate > > > > > > > > > > rows > > > > > > > > > > > Rachel > > > > > > > > > > > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > > > > > > Though I have published a script for determining indexes that > > > > > > > need to be rebuilt, and then rebuilding them, I have to say > > > > > > > > that > > > > > > > > > > > this is almost never necessary. > > > > > > > > > > > > > > Why are you rebuilding indexes? About the only reason for > > > > > > ever > > > > > > > > > > doing so is that the BLEVEL >= 5. > > > > > > > > > > > > > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > > > > > > > > > > > > > Currently, the third article may be of interest. > > > > > > > > > > > > > > Jared > > > > > > > > > > > > > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > > > > > > > Anyone have any useful scripts for doing this? > > > > > > > > > > > > > > > > TIA, > > > > > > > > Rich > > > > > > > > > > > > > > > > > > > > > Content-Type: text/html; charset="iso-8859-1"; > > > > > > name="Attachment: > > > > > 1" > > > > > > > > > > > > Content-Transfer-Encoding: 7bit > > > > > > > Content-Description: > > > > > > > > > > > > > > -- > > > > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > > > > > -- > > > > > > > Author: Jared Still > > > > > > > INET: [EMAIL PROTECTED] > > > > > > > > > > > > > > Fat City Network Services-- 858-538-5051 > > > > > > > > > > http://www.fatcity.com > > > > > > > > > > > > San Diego, California-- Mailing list and web hosting > > > > > > > > > > > > services > > > > > > ---
Re: Rebuilding Indexes...
sigh. I need to find time to read ALL the docs. Yeah, that'll happen. If I can find a parallel universe where time runs at a different rate. Thanks, I'll test this out as well. --- Arup Nanda <[EMAIL PROTECTED]> wrote: > In 9.2, you can keep the index by using the KEEP INDEX key words. > > ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX > > This will keep the index but drop the constraint. Talk about having > your > cake and eating it too...;) > > HTH > > Arup > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Friday, December 27, 2002 4:39 PM > > > > it'll have to wait until Monday, I'm not at work until then. I'll > try > > it with a non-unique then > > > > Hey, if it works, it saves me tons of time, I learn something new > and I > > had fun developing the single SQL statement to rebuild the > constraint > > and index. Win-win > > > > > > Rachel > > > > --- Denny Koovakattu <[EMAIL PROTECTED]> wrote: > > > > > > > > > I don't have access to 9.2.0.1 right now. But can you try > creating > > > a non- > > > unique index instead of the unique index. If you create a unique > > > index, it gets > > > dropped. That's the behavior on 8.1.x also. But if it's a > non-unique > > > index, it > > > shouldn't get dropped. > > > > > > Regards, > > > Denny > > > > > > Quoting Rachel Carmichael <[EMAIL PROTECTED]>: > > > > > > > 9.2.0.1 Solaris, and yes, it does drop it > > > > > > > > I created a unique index in the primary key columns > > > > I created the primary key constraint without specifying an > index > > > > I checked that the index existed, it did > > > > I dropped the primary key constraint > > > > I checked that the index existed, it didn't > > > > > > > > try it I tried various combinations before posting this > note > > > > > > > > > > > > --- Denny Koovakattu <[EMAIL PROTECTED]> wrote: > > > > > > > > > > > > > > > If you build a separate index to enforce the primary key, > > > Oracle > > > > > shouldn't > > > > > drop it when you disable or drop the primary key. > > > > > > > > > > Regards, > > > > > Denny > > > > > > > > > > Quoting Rachel Carmichael <[EMAIL PROTECTED]>: > > > > > > > > > > > Here's a reason: > > > > > > > > > > > > have you ever tried to find the three duplicate rows in a > 12 > > > > > million > > > > > > row table without using the primary key constraint? I've > had to > > > > > > disable > > > > > > or drop the constraint in order to use the exceptions > table. > > > Once > > > > I > > > > > do > > > > > > that, even if I've built a separate index that enforces the > > > > primary > > > > > > key > > > > > > constraint, Oracle drops the index. So I HAVE to rebuild > it. If > > > I > > > > > > allow > > > > > > the index to be rebuilt when I re-enable the primary key > > > > > constraint, > > > > > > it > > > > > > builds it in the default tablespace of the table owner, not > > > where > > > > I > > > > > > want it. > > > > > > > > > > > > if anyone has a better way to fix this problem, I'm more > than > > > > happy > > > > > to > > > > > > hear it! It's a data warehouse and the third party app has > a > > > bug > > > > we > > > > > > can't find and on occasion sqlloads (via direct path) > duplicate > > > > > rows > > > > > > > > > > > > Rachel > > > > > > > > > > > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > > > > > > > > > > > > > Though I have published a script for determining indexes > that > > > > > > > need to be rebuilt, and then rebuilding them, I have to > say > > > > that > > > > > > > this is almost never necessary. > > > > > > > > > > > > > > Why are you rebuilding indexes? About the only reason > for > > > ever > > > > > > > doing so is that the BLEVEL >= 5. > > > > > > > > > > > > > > goto asktom.oracle.com, and do a search on 'index > rebuild'. > > > > > > > > > > > > > > Currently, the third article may be of interest. > > > > > > > > > > > > > > Jared > > > > > > > > > > > > > > On Thursday 26 December 2002 12:24, Richard Huntley > wrote: > > > > > > > > Anyone have any useful scripts for doing this? > > > > > > > > > > > > > > > > TIA, > > > > > > > > Rich > > > > > > > > > > > > > > > > > > > > > Content-Type: text/html; charset="iso-8859-1"; > > > name="Attachment: > > > > > 1" > > > > > > > Content-Transfer-Encoding: 7bit > > > > > > > Content-Description: > > > > > > > > > > > > > > -- > > > > > > > Please see the official ORACLE-L FAQ: > http://www.orafaq.net > > > > > > > -- > > > > > > > Author: Jared Still > > > > > > > INET: [EMAIL PROTECTED] > > > > > > > > > > > > > > Fat City Network Services-- 858-538-5051 > > > > > http://www.fatcity.com > > > > > > > San Diego, California-- Mailing list and web > hosting > > > > > > services > > > > > > > > > > > > > > > > > > > > > > > > > > - > > > > > > > To REMOVE yourself f
Re: Rebuilding Indexes...
In 9.2, you can keep the index by using the KEEP INDEX key words. ALTER TABLE XXX DROP CONSTRAINT PK_XXX KEEP INDEX This will keep the index but drop the constraint. Talk about having your cake and eating it too...;) HTH Arup - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, December 27, 2002 4:39 PM > it'll have to wait until Monday, I'm not at work until then. I'll try > it with a non-unique then > > Hey, if it works, it saves me tons of time, I learn something new and I > had fun developing the single SQL statement to rebuild the constraint > and index. Win-win > > > Rachel > > --- Denny Koovakattu <[EMAIL PROTECTED]> wrote: > > > > > > I don't have access to 9.2.0.1 right now. But can you try creating > > a non- > > unique index instead of the unique index. If you create a unique > > index, it gets > > dropped. That's the behavior on 8.1.x also. But if it's a non-unique > > index, it > > shouldn't get dropped. > > > > Regards, > > Denny > > > > Quoting Rachel Carmichael <[EMAIL PROTECTED]>: > > > > > 9.2.0.1 Solaris, and yes, it does drop it > > > > > > I created a unique index in the primary key columns > > > I created the primary key constraint without specifying an index > > > I checked that the index existed, it did > > > I dropped the primary key constraint > > > I checked that the index existed, it didn't > > > > > > try it I tried various combinations before posting this note > > > > > > > > > --- Denny Koovakattu <[EMAIL PROTECTED]> wrote: > > > > > > > > > > > > If you build a separate index to enforce the primary key, > > Oracle > > > > shouldn't > > > > drop it when you disable or drop the primary key. > > > > > > > > Regards, > > > > Denny > > > > > > > > Quoting Rachel Carmichael <[EMAIL PROTECTED]>: > > > > > > > > > Here's a reason: > > > > > > > > > > have you ever tried to find the three duplicate rows in a 12 > > > > million > > > > > row table without using the primary key constraint? I've had to > > > > > disable > > > > > or drop the constraint in order to use the exceptions table. > > Once > > > I > > > > do > > > > > that, even if I've built a separate index that enforces the > > > primary > > > > > key > > > > > constraint, Oracle drops the index. So I HAVE to rebuild it. If > > I > > > > > allow > > > > > the index to be rebuilt when I re-enable the primary key > > > > constraint, > > > > > it > > > > > builds it in the default tablespace of the table owner, not > > where > > > I > > > > > want it. > > > > > > > > > > if anyone has a better way to fix this problem, I'm more than > > > happy > > > > to > > > > > hear it! It's a data warehouse and the third party app has a > > bug > > > we > > > > > can't find and on occasion sqlloads (via direct path) duplicate > > > > rows > > > > > > > > > > Rachel > > > > > > > > > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > > > > > > > > > > > Though I have published a script for determining indexes that > > > > > > need to be rebuilt, and then rebuilding them, I have to say > > > that > > > > > > this is almost never necessary. > > > > > > > > > > > > Why are you rebuilding indexes? About the only reason for > > ever > > > > > > doing so is that the BLEVEL >= 5. > > > > > > > > > > > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > > > > > > > > > > > Currently, the third article may be of interest. > > > > > > > > > > > > Jared > > > > > > > > > > > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > > > > > > Anyone have any useful scripts for doing this? > > > > > > > > > > > > > > TIA, > > > > > > > Rich > > > > > > > > > > > > > > > > > > Content-Type: text/html; charset="iso-8859-1"; > > name="Attachment: > > > > 1" > > > > > > Content-Transfer-Encoding: 7bit > > > > > > Content-Description: > > > > > > > > > > > > -- > > > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > > > > -- > > > > > > Author: Jared Still > > > > > > INET: [EMAIL PROTECTED] > > > > > > > > > > > > Fat City Network Services-- 858-538-5051 > > > > http://www.fatcity.com > > > > > > San Diego, California-- Mailing list and web hosting > > > > > services > > > > > > > > > > > > > > > > > > > > - > > > > > > To REMOVE yourself from this mailing list, send an E-Mail > > > message > > > > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') > > and > > > > in > > > > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > > > > (or the name of mailing list you want to be removed from). > > You > > > > may > > > > > > also send the HELP command for other information (like > > > > subscribing). > > > > > > > > > > > > > > > > > > > > > __ > > > > > Do you Yahoo!? > > > > > Yahoo! Mail Plus - Powerful. Affordable
Re: Rebuilding Indexes...
fair enough. I retract the example :) --- Jared Still <[EMAIL PROTECTED]> wrote: > > Yes, but that's a special case. You are not rebuilding > the index as part of some regular index maintenance. > > Jared > > On Friday 27 December 2002 04:43, Rachel Carmichael wrote: > > Here's a reason: > > > > have you ever tried to find the three duplicate rows in a 12 > million > > row table without using the primary key constraint? I've had to > disable > > or drop the constraint in order to use the exceptions table. Once I > do > > that, even if I've built a separate index that enforces the primary > key > > constraint, Oracle drops the index. So I HAVE to rebuild it. If I > allow > > the index to be rebuilt when I re-enable the primary key > constraint, it > > builds it in the default tablespace of the table owner, not where I > > want it. > > > > if anyone has a better way to fix this problem, I'm more than happy > to > > hear it! It's a data warehouse and the third party app has a bug we > > can't find and on occasion sqlloads (via direct path) duplicate > rows > > > > Rachel > > > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > > Though I have published a script for determining indexes that > > > need to be rebuilt, and then rebuilding them, I have to say that > > > this is almost never necessary. > > > > > > Why are you rebuilding indexes? About the only reason for ever > > > doing so is that the BLEVEL >= 5. > > > > > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > > > > > Currently, the third article may be of interest. > > > > > > Jared > > > > > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > > > Anyone have any useful scripts for doing this? > > > > > > > > TIA, > > > > Rich > > > > > > > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: > 1" > > > Content-Transfer-Encoding: 7bit > > > Content-Description: > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Jared Still > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > > > San Diego, California-- Mailing list and web hosting > services > > > > - > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and > in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You > may > > > also send the HELP command for other information (like > subscribing). > > > > __ > > Do you Yahoo!? > > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > > http://mailplus.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jared Still > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Rebuilding Indexes...
As Denny also suggested. I'm gonna try that on Monday, on my sandbox database. If this does work in 9i as well (and it should, I hope), I can just rebuild the unusable partition and not the entire index. The index build will only have to happen once. --- Jack Silvey <[EMAIL PROTECTED]> wrote: > Hey Rachel, > > Consider using a non-unique index for your primary key constraint. If > > you prebuild it and then add the constraint, Oracle will not drop the > > index when you drop the PK constraint, and you can control the index > build that a way (and build it in parallel to boot). > > hth, > > Jack > > > > > > Here's a reason: > > > > have you ever tried to find the three duplicate rows in a 12 > million > > row table without using the primary key constraint? I've had to > disable > > or drop the constraint in order to use the exceptions table. Once I > do > > that, even if I've built a separate index that enforces the primary > > key > > constraint, Oracle drops the index. So I HAVE to rebuild it. If I > allow > > the index to be rebuilt when I re-enable the primary key > constraint, > it > > builds it in the default tablespace of the table owner, not where I > > want it. > > > > if anyone has a better way to fix this problem, I'm more than happy > to > > hear it! It's a data warehouse and the third party app has a bug we > > can't find and on occasion sqlloads (via direct path) duplicate > rows > > > > Rachel > > > > --- Jared Still <[EMAIL PROTECTED]> wrote: > > > > > > Though I have published a script for determining indexes that > > > need to be rebuilt, and then rebuilding them, I have to say that > > > this is almost never necessary. > > > > > > Why are you rebuilding indexes? About the only reason for ever > > > doing so is that the BLEVEL >= 5. > > > > > > goto asktom.oracle.com, and do a search on 'index rebuild'. > > > > > > Currently, the third article may be of interest. > > > > > > Jared > > > > > > On Thursday 26 December 2002 12:24, Richard Huntley wrote: > > > > Anyone have any useful scripts for doing this? > > > > > > > > TIA, > > > > Rich > > > > > > > > > Content-Type: text/html; charset="iso-8859-1"; name="Attachment: > 1" > > > Content-Transfer-Encoding: 7bit > > > Content-Description: > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Jared Still > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > > > San Diego, California-- Mailing list and web hosting > services > > > > > - > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and > in > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > (or the name of mailing list you want to be removed from). You > may > > > also send the HELP command for other information (like > subscribing). > > > > > > > > > __ > > Do you Yahoo!? > > Yahoo! Mail Plus - Powerful. Affordable. Sign up now. > > http://mailplus.yahoo.com > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Rachel Carmichael > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting > services > > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like > subscribing). > > > > > > > > Thanks, > > Jack Silvey > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jack Silvey > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > __ 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
