Optimizer not using histogram to calculate estimated number of rows?

From: David Kurtz <info_at_go-faster.co.uk>
Date: Wed, 10 Jun 2009 23:20:55 +0100
Message-ID: <A0AB08C2FAB543C1B8301F22693B4CA5_at_GOFASTER4>

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_at_go-faster.co.uk
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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 10 2009 - 17:20:55 CDT