Re: Database is extremely slow

"Scott Ashby" <Scott.Ashby_at_SMNA.com> 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?

Yes, and You dont know without trying it. However 7.3.4 was pretty much the first production release with a CBO in it. So you'd be changing from a mature technology to a 1.x technology. Guess which is likely to be more reliable.

> 2. Should I go ahead and rebuild the indexes on the tables that I
> know have been radically reduced in row count?

Yes.

As to recommendations, find out when the problem started and if this coincides with any significant changes.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK