Re: Oracle index rebuilding performance

ryanbreakspear_at_norubbishgmail.com wrote:
> Hi all,
>
> I have a live database, which basically slows down as it's used. The
> current solution is to issue an ALTER TABLE tablename MOVE TABLESPACE
> spacename, then ALTER INDEX indexname REBUILD.

Biting my tongue really really hard.

> This is run every day on about half a dozen of the main tables. The
> strange thing is that this doesn't always work

Actually the strange thing is that it works at all and that you keep doing it.

> I've heard that version 8.1.7.0 is "a bit dodgy", is it worth trying to
> upgrade? I know my questions are extremely vague, but it seems very
> odd that this has any impact at all when it's run so often. I come
> from a SQL Server background and know very little about Oracle, any
> help on the subject would be much appreciated.
>
> Thanks
>
> Ryan

Upgrade from waht? And why would any sane person upgrade to 8.1.7.0?

First off it would be nice to know what you are running. But if you were to upgrade to 8i it should be to 8.1.7.4 which I hope you realize is in desupport. So why not upgrade to 9.2.0.6 or 10.1.0.4 which are both supported and substantially faster than either what you have or what you are considering.

What really bothers me is your current "solution." For me it would be a complete non-starter. If I did it once and it worked ... I would consider that diagnostic information ... not a solution. A broken lamp, moved to a different table is still a broken lamp.

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?

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)