Re: Cursor_sharing - Optimizer - Histograms

Rajesh.Rao_at_jpmchase.com wrote:

> Dear All,
>
> Oracle 9.2.0.5.0 RAC on Solaris 8 Nodes, with cursor_sharing = SIMILAR.
> Application uses literals.
>
>
> My questions (if my inferences are right) are :
>
> 1. The optimizer for some reason believes that the unique key values are
> skewed, whereas for the primary key the values are not skewed. Why?

A bug. I believe it is fixed in 9.2.0.6 Unless my memory fails me, I tried that in preparation for my Hotsos presentation on histograms

> 2. The presence of an unique index should tell the optimizer that only one
> row should be returned when queried using the unique key predicate? So, why
> does the optimizer infer that the plan could change, after peeking at the
> bind variables?

a possible bug? Remember that the optimizer only gets involved AFTER the cursos_sharing=similar codepath decided that the plan could change and it probably only looks at the presence of the histogram as the deciding factor.

As you found out, "for all columns size {auto|skewonly}" is bad. Don't do it (btw, "for all indexed columns size {auto|skewonly}" is no better). Create histogram specifically and ONLY for those columns where you demonstrated a benefit.

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
http://www.freelists.org/webpage/oracle-l