Re: Oracle index rebuilding performance

DA Morgan wrote:
> DA Morgan wrote:
>
> > There is no question you have a problem with your application. Rather
> > than treating it with duct tape and paper clips why not identify the
> > underlying cause and fix it?
>
> As follow up to the other posts in this thread that were not here when
> I originally responded.
>
> Lack of current statistics for the optimizer is near-fatal. Create the
> statistics with dbms_stats.

Use of statistics on applications that were specifically designed for a different optimizer doesn't look so good either.

To the OP:

What is the setting of optimizer_mode in the init.ora? If it is RULE, do not run dbms_stats. If it is CHOOSE, it might or might not be good, we don't have enough information to decide. But Daniel is right about the stated rebuilding sequence not being right. Do you know when the app was written? That might give a clue.

You might post the rebuilding code, many things from olden tymes were built upon myths, and we might shed light thereupon. Also, it might simply be that the indexes are not being rebuilt when it "doesn't work", the symptom of that can be that things run, but slowly.

> Go to http://www.psoug.org
> click on Morgan's Library
> click on DBMS_STATS

That is correct for more recent versions, arguable for pre-8174.

>
> look at dbms_stats.gather_schema_stats.
>
> The pain of moving to a supported version of Oracle will always be less
> than the pain of staying where you are. Especially when you consider

That is quite true.

> that a resume that isn't at least at 9i will get you a job ...
> unfortunately that job may be flipping burgers.

jg

--
@home.com is bogus.
http://catless.ncl.ac.uk/Risks/prefs.html Do not click on this link.