Re: dbms_stats set_table_stats, set_index_stats and set_columns_stats

Hi Jonathan,

I set only the new partition stats. No table stats are set. All the queries are single partition. In the where clause, I have "where id = ...", the id is the partitioning key.

I'll test setting table stats and partition stats today.

Thanks

Dias

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<bpe6d7$11r$1$830fa7a5_at_news.demon.co.uk>...
> 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
Received on Wed Nov 19 2003 - 00:41:19 CST