Re: dbms_stats set_table_stats, set_index_stats and set_columns_stats

I think there was a change of strategy between 8 and 9 on multi-partition queries.

In Oracle 8, Oracle would examine the statistics of all relevant partitions. On Oracle 9, it uses the stats of the base table.

Are you setting the base table stats, and are you running multi-partition queries, or single partition queries.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Belgium__November (EOUG event - "Troubleshooting")
____UK_______December (UKOUG conference - "CBO")


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"dias" <ydias_at_hotmail.com> wrote in message
news:55a68b47.0311181358.4b605a92_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