Re: Log file sync and log file parallel write.

Can you explain why rebuilding the index helped?

Do you know what was causing the performance problem, and why rebuilding the index(s) improved performance?

This is how silver bullets are born.

1. performance problems is found
2. someone tells you to 'do this' In this case, 'this' happens to be rebuilding an index
3. you have the experience again
4. silver bullet is born - rebuilding indexes fixes performance ( there are of course reverse corollaries to this. _Removing indexes is always beneficial to INSERT for example )

This then becomes "Rebuild all indexes at N interval to avoid performance problems"
This particular strategy will likely _cause_ performance problems.

There are definitely situations where rebuilding an index is the right thing to do.

That doesn't mean that you should go about rebuilding all indexes on a regular basis.
Which the the kind of 'advice' that is handed out from time to time.

How about the cases where rebuilding an index kills performance?

So naturally when someone says that 'Rebuilding indexes is causing a performance problem", a number of folks here are going to ask "Why are you doing that?"

Here's a couple of links to understanding indexes:

Richard Foote's index presentation
http://tinyurl.com/27gt2v*

*Richard's blog
http://tinyurl.com/create.php

On Wed, Mar 5, 2008 at 5:37 PM, Johnson, William L (TEIS) < WLJohnson_at_tycoelectronics.com> wrote:

> Can someone help point me to documentation that will show me the silver
> bullet for not rebuilding indexes? I can provide countless examples over
> the past several years where rebuilding indexes corrected performance
> problems that developed over months. Are we saying that something else
> fixed the problem and that the index rebuild was not the real solution? Is
> the index rebuild forcing explain plans out of the shared_pool and giving
> false results? If so, wouldn't a DB stop/start accomplish the same result?
> I am by no means an optimizing expert, but won't Oracle switch to something
> like full table scans if the index is no longer an effective method to
> obtain your data? This quickly becomes a problem when joining multiple
> tables with 10+ million rows each. I deal a lot with third party
> applications like SAP, Click Commerce and Matrix One Engineering modules
> where we can not alter the sql running behind the scenes.
>
>
>
> I would really like to understand.
>
>
>
> Bill
>
>
> ------------------------------
>
> *From:* Jared Still [mailto:jkstill_at_gmail.com]
> *Sent:* Wednesday, March 05, 2008 7:39 PM
> *To:* asif_oracle_at_yahoo.com
> *Cc:* Johnson, William L (TEIS); oracle-l_at_freelists.org
> *Subject:* Re: Log file sync and log file parallel write.
>
>
>
>
> Lastly, why are you rebuilding your indexes???
> Regards
> Asif Momen
> http://momendba.blogspot.com
>
>
>
>
>
> Same question. Why are you rebuilding the indexes?
>
> You *could* just stop doing that.
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l