ANALYZE ISSUES IN 9i?

From: Spark <markfle_at_yahoo.com>
Date: Fri, 13 Jun 2003 15:07:19 +0100
Message-ID: <3ee9da97$0$18497$ed9e5944@reading.news.pipex.net>

Oracle9i Release 9.2.0.3.0 on Windows 2000 server sp3.

Hi all

We have recently upgraded a major database to 9i and we are having a few performance issues on lookups and returning queries. We have run the following Analyze script in the hope that it would speed up the system;

begin dbms_stats.gather_schema_stats(OWNNAME => 'SCHEMANAME',CASCADE => TRUE); END; This has had the effect of further slowing down the relevant queries.

On the test machine we have found that if we delete the stats (Analyze Table_name delete stats) for the offending table and rebuild the indexes, performance comes back to the point where users agree it should be.

I don't see this as a lasting fix and was wondering if anyone could postulate a reason for this behaviour. I have had a scan through metalink and tahiti.oracle, but can find nothing that relates to this problem. Is there a default for number of rows analyzed in any given table?

The table itself contains greater than 3300000 rows.

Many Thanks Received on Fri Jun 13 2003 - 09:07:19 CDT