Optimizer not using histogram to calculate estimated number of rows?
- From: David Kurtz <info@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 10 Jun 2009 23:20:55 +0100
I am working on 10.2.0.4 on Linux. I have a table with a highly skewed set
of data.
166067 rows, 52999 distinct values in an indexed column.
For 3000+ values have 3 rows each, about 60 values have over 100 rows each.
So I thought this is a good candidate for a histogram.
When I gather stats for all columns size auto I do not get a histogram on
this column.
I have to force gather_table_stats to create a histogram on this column
(I've tried various number of buckets from 25 to 254)
However, the optimizer doesn't seem to use the histogram in calculating the
cost
(That is causing problems in a more complex query, because the is causing
the plan not to change).
select /*+GATHER_PLAN_STATISTICS*/ count(*) from psroleuser where roleuser
='HVA_FUNC'
----------------------------------------------------------------------------
-----------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
A-Time | Buffers |
----------------------------------------------------------------------------
-----------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1
|00:00:00.01 | 4 |
|* 2 | INDEX RANGE SCAN| PS_PSROLEUSER | 1 | 3 | 169
|00:00:00.01 | 4 |
----------------------------------------------------------------------------
-----------------
I have another version of this table on a test database, but the volume is
slightly smaller (26000 rows, 3300 distinct values)
That uses the histogram exactly as I would expect, the estimated number of
rows is close to reality, and the execution plan of the complex query
changes.
I get the feeling I am missing something. Has anyone seen this before?
The compatible and OPTIMIZER_FEATURES are both set to 10.2.0.4.
Cursor Sharing is set to EXACT
regards
_________________________
David Kurtz
Go-Faster Consultancy Ltd.
mailto:david.kurtz@xxxxxxxxxxxxxxx
web: www.go-faster.co.uk
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com
DBA Blogs: PeopleSoft: http://blog.psftdba.com, Oracle:
http://blog.go-faster.co.uk
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
Other related posts:
