Re: dbms_stats set_table_stats, set_index_stats and set_columns_stats

Sorry for the multi-post ...

Dias

ydias_at_hotmail.com (dias) wrote in message news:<55a68b47.0311181359.3921d411_at_posting.google.com>...
> Hi,
>
> I am using dbms_stats to set the stats for partitions just after the
> creating these partitions. This is done for some reasons I can't
> change for the moment.
>
> In a 8i db, this works fine.
>
> I'm testing a new 9i db with the same functions, but the optimizer
> choose very bad plans.
>
> For a query, the results of 10053 event shows (SINGLE TABLE ACCESS
> PATH)
>
> - in 8i,
> Table A : full scan : 200
> Index 1 : 1000(CST: 1000 IXSEL: 1.0000e+00 TBSEL: 1.0000e+00)
> Index 2 : 6200 (CST: 620 IXSEL: 1.0000e+00 TBSEL: 1.0000e+00)
>
> The best single access cost for table A is 200 (full scan).
>
> - In 9i,
> Table A : full scan : 200
> Index 1 : 10 (RSC_IO: 10 IX_SEL: 1.7580e-10 TB_SEL: 1.7580e-10)
> Index 2 : 5 (RSC_IO: 5 IX_SEL: 1.7580e-10 TB_SEL: 1.7580e-10)
>
> The best single access cost for table A is 5 (access to table A using
> index 2)
>
> The stats listed in the section "BASE STATISTICAL INFORMATION" of the
> event trace are the same in 8i and 9i.
>
> Any idea ?
>
> Thanks
Received on Wed Nov 19 2003 - 00:42:10 CST