Re: Database is extremely slow

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

In addition to what the other posters said:

it is not only the indexes to rebuild, but also the tables themselves also (ALTER TABLE xyz MOVE), as the High Water Mark remains up when
deleting large data portions of it, and so e.g. a COUNT(*) would just take the same time as if no rows were deleted. Unfortunately, I think that MOVE is only available from 8i on ...

hth, Jan Received on Thu Jun 26 2003 - 01:43:12 CDT