Re: cost base optimization

I have run a series of tests on Oracle 7.3.x comparing Cost based to Rule based optimization. With a few very specific instances Rule based performed as good or better than Cost based.

I am hoping to reproduce a complete set of test on 8.0 in the near future. The preliminary tests indicate Cost based will generally perform better than Rule based with 8.0.

Having said that and surely starting a religious war. If you need to use Cost based with 7.3.3 I would suggest increasing your sampling on your analyze. If that does not help, run some traces to see what queries are performing poorly. Then start looking at the cardinality of your indices on those tables. You may need to change how and what you are indexing.

Scott

Andreas Doerler wrote in message <914002785.561490_at_news.vbs.at>...
>hi!
>
>im running a 7.3.3 db with currently
>no analyzed tables.
>i recently tried to enable cost based
>optimization on certain schemas using
>the dbms_utility.analyze_schema procedure.
>
>some queries were really faster.
>some queries did take the worst-case path
>without using existing indexes and took up
>to 3000% more resources than running in
>rule-based mode (after deleting the analysations)
>
>whats wrong? why the optimizer ignores existing
>indexes when a schema is fully analyzed?
>is o733 not capable using the benefits of cbo?
>
>thanks in advance!
>
> <ad>
Received on Mon Dec 21 1998 - 11:16:31 CST