Re: dbms_stats set_table_stats, set_index_stats and set_columns_stats

Hi,

I've used only one query for the tests. This query is only single partition, the where clause contains the partitioning key.

I have tested these cases:
- dbms_stats. set table, index and column stats for the partition. - dbms_stats. set table, index and column stats for the partition AND global stats for table, index and column.

The CBO generates wrong costs to access indexes

The second case was to gather the stats for a partition A, and to use GET stats procs (dbms_stats) from this partition and SET stats into a partition B. The query against partition A is ok, against partition B, the same wrong execution plan ...

I think that setting stats do not populate all the stats needed by the optimizer, but I cant see what is missing.

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 - 14:36:14 CST