dbms_stats set_table_stats, set_index_stats and set_columns_stats
dias
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
dias
dias
Jonathan Lewis
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" <yd...@hotmail.com> wrote in message
news:55a68b47.03111...@posting.google.com...
dias
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" <jona...@jlcomp.demon.co.uk> wrote in message news:<bpe6d7$11r$1$830f...@news.demon.co.uk>...
dias
Dias
yd...@hotmail.com (dias) wrote in message news:<55a68b47.03111...@posting.google.com>...
dias
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" <jona...@jlcomp.demon.co.uk> wrote in message news:<bpe6d7$11r$1$830f...@news.demon.co.uk>...
Jonathan Lewis
There may be a hidden flag somewhere defining
the stats as user-created. I vaguely recall Wolfgang
Breitling saying something like that once.
Try exporting the stats to a stats table, then re-importing
them to see if that changes anything.
Also try a 10053 trace for the two queries to see if
there is any difference between the quoted stats
and the stored stats.
--
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" <yd...@hotmail.com> wrote in message
news:55a68b47.03111...@posting.google.com...
> Hi,
>
Anurag Varma
"Jonathan Lewis" <jona...@jlcomp.demon.co.uk> wrote in message news:bpgsab$s4k$1$8300...@news.demon.co.uk...
>
> There may be a hidden flag somewhere defining
> the stats as user-created. I vaguely recall Wolfgang
> Breitling saying something like that once.
Maybe its the flag in sys.tab$.
A look in the view definition of dba_tables (in 9i) shows the column
user_stats defined as "decode(bitand(t.flags, 256), 0, 'NO', 'YES')"
Which might be the flag you talk about here?
Anurag
Wolfgang Breitling
he?) provided me with the 10053 traces and more details on the dbms_stats
calls used. From that I could create a testcase and reproduce the problem.
It turns out it has nothing to do with the fact that the tables are
partitioned. I could get the same behaviour using just one non-partitioned
table. The cause for the problem lies with the omission of a reference to a
filled-in srec structure in the set_column_stats call to provide the min
and max values for the column, if not an entire histogram array. The Oracle
8i optimizer obviously didn't mind, but the Oracle 9i cbo does.
"Jonathan Lewis" <jona...@jlcomp.demon.co.uk> wrote in news:bpe6d7$11r$1
$830f...@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.
>
--
What lies behind us and what lies before us are small matters when compared
to what lies within us.
Wolfgang Breitling
Oracle 7, 8, 8i, 9i OCP
dias
I (he) really appreciated your help.
Dias
Wolfgang Breitling <John...@aol.com> wrote in message news:<Xns943AEE698253br...@198.80.55.250>...