Try setting
optimizer_index_caching to 90
optimizer_index_cost_adj to 10
It does the trick for me when I see RBO is giving better performance than
CBO
"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 - 11:59:24 CDT
