Re: ANALYZE ISSUES IN 9i?

From: Anurag Varma <avarmadba.skipthis_at_yahoo.com>
Date: Fri, 13 Jun 2003 14:22:46 GMT
Message-ID: <W6lGa.831$Bw2.37@nwrddc03.gnilink.net>

Find out what query/queries is/are performing bad as a result of you analyzing the table.

Then do the following:
tune the query by either

* providing hints
* create histogram for relevant columns (if it helps and if using literals)
* create stored outline for this query when performance is acceptable (if

using binds)
... or rewrite the query if that helps....

Anurag

"Spark" <markfle_at_yahoo.com> wrote in message news:3ee9da97$0$18497$ed9e5944_at_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:22:46 CDT