Database is extremely slow

[Scott Ashby]

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

[Noons]

"Scott Ashby" <Scott.Ashby@SMNA.com> wrote in message
news:bd74662.0306250453.f13d73f@posting.google.com ...

> I have an Oracle 7.3.4.4 database that is experiencing extremely slow

Ah OK. No point going into details, then.

> 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?

Most likely no.

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

Yes.

>
> 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

Hurry up then.


--
Cheers
Nuno Souto
[email]wizofoz2k@yahoo.com.au.nosp[/email]am

[Niall Litchfield]

"Scott Ashby" <Scott.Ashby@SMNA.com> wrote in message
news:bd74662.0306250453.f13d73f@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

[Sybrand Bakker]

On Wed, 25 Jun 2003 17:20:38 +0100, "Niall Litchfield"
<n-litchfield@audit-commission.gov.uk> wrote:

>However 7.3.4 was pretty much the
>first production release with a CBO in it

Completely incorrect. CBO was available from 7.1. However it was so
lousy at that time that no one used. Stating though 7.3.4 is the first
release with CBO 1.x in it is just plain untrue.


Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

[Jan Gelbrich]

"Scott Ashby" <Scott.Ashby@SMNA.com> schrieb im Newsbeitrag
news:bd74662.0306250453.f13d73f@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