Re: A bit disappointed in CBO..

On Thu, 13 Feb 2003 12:58:53 -0000, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>The fact that you have to switch to ALL_ROWS to make
>the CBO kick in is a cleare indication that you do not
>have any statistics on any of the tables in the query.
>Consequently, the CBO is using default values, which
>are not suitable for your tables.
>
>NB - if the optimizer mode in the plan from autotrace
>shows "optimizer=CHOOSE", and there are no items
>like "(Cost=15 Card=11 Bytes=187)" in the output,
>then you have no stats and are optimising through
>RBO.
>

I tend to disagree:
schemas were analyzed via:
dbms_stats.gather_schema_stats(ownname=>'MY_SCHEMA', cascade=>TRUE) notice, no estimation
anyway, it seems to work now (chooses CBO in CHOOSE), query is still bad though.
going to research/play around with
optimizer_index_cost_adj
and
 optimizer_index_caching

SQL> select count(*) from user_tables where last_analyzed<sysdate-2;   COUNT(*)

         0

SQL> select count(*) from user_indexes where last_analyzed<sysdate-2;

  COUNT(*)

         0

SQL> select count(*) from user_indexes;

  COUNT(*)

       101

SQL> select count(*) from user_tables;

  COUNT(*)

        82
.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Thu Feb 13 2003 - 10:08:48 CST