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

"Scott Ashby" <Scott.Ashby (AT) SMNA (DOT) com> wrote


Ah OK. No point going into details, then.

Most likely no.

Yes.

Hurry up then.


--
Cheers
Nuno Souto
wizofoz2k (AT) yahoo (DOT) com.au.nospam

"Scott Ashby" <Scott.Ashby (AT) SMNA (DOT) com> wrote

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.

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

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

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

"Scott Ashby" <Scott.Ashby (AT) SMNA (DOT) com> schrieb im Newsbeitrag
news:bd74662.0306250453.f13d73f (AT) posting (DOT) google.com...
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