Re: A bit disappointed in CBO..

From: Ana C. Dent <anacedent_at_hotmail.com>
Date: Sat, 15 Feb 2003 02:30:05 GMT
Message-ID: <Xns9322BC35AC894SunnySD@68.6.19.6>

andreyNSPAM_at_bookexchange.net (NetComrade) wrote in news:3e4bc198.693607865_at_nyc.news.speakeasy.net:

> 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
>

My initial SWAG would be

optimizer_index_cost_adj = 30

optimizer_index_caching = 95 Received on Fri Feb 14 2003 - 20:30:05 CST