dbms_stats set_table_stats, set_index_stats and set_columns_stats

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 Tue Nov 18 2003 - 16:03:56 CST