Re: Database is extremely slow

"michael ngong" <mngong_at_yahoo.com> wrote in message news:ecf365d5.0306261203.45cfcb23_at_posting.google.com...
> Scott.Ashby_at_SMNA.com (Scott Ashby) wrote in message
news:<bd74662.0306250453.f13d73f_at_posting.google.com>...
> > I have an Oracle 7.3.4.4 database that is experiencing extremely slow
> > response times in the past few days. I know that one of the tables
> > had as many as 3,000,000 rows, but over the past week some of the
> > information has been purged, so now there are about 2,200,000 rows;
> > other tables have also been drastically reduced in row count. Last
> > night, I updated statistics (hoping it might make a difference), but
> > unfortunately, we don't observe any real difference.
> >
> > I have two questions:
> >
> > 1. The Optimizer_Mode parameter for Oracle is set to RULE. Does this
> > mean that the server does not rely on statistics stored in the catalog
> > when it is putting together the execution plan for a query? I am
> > considering changing this to CHOOSE. Can I do this without having an
> > adverse effect on performance or changing query results?
> >
> > 2. Should I go ahead and rebuild the indexes on the tables that I
> > know have been radically reduced in row count?
> >
> > This system has never performed well, it is running on older, obsolete
> > hardware and software, and a replacement system for it has been
> > targeted and is being tested as I write this, but does anyone out
> > there have any specific recommendations that I could try to help our
> > current situation?
> >
> > Thanks,
> >
> > Scott Ashby
> > DBA
> > Swedish Match North America
>
> I am not positive that alter table move was available as of version
> 7 .

You should be a *bit* more positive. That particular feature didn't appear until 8i.

It wasn't *that* long ago, surely??!

~QM

>If it was then remember you would have to rebuild/recerate
> indexes(since the availability of online index rebuilds can be
> questioned too as of that version).There is every reason to move from
> version 7.One of them could be getting away from having to tune
> parameters like
> small_entry_max_size and the likes.....
> If you decide to change the optimizer to the likes of choose then
> remember to run statistics on the database objects or it may default
> to using the rule
> .
> Version 7 might be getting a bit outdated to expect great performance.
> Your system seems to be visibly ripe (like a banana that changes
> colours :))
> for upgrades.IMHO
>
>
> Michael Tubuo Ngong
Received on Sat Jun 28 2003 - 22:51:17 CDT