Re: Database is extremely slow

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 .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 Thu Jun 26 2003 - 15:03:43 CDT