Cursor_sharing - Optimizer - Histograms

Dear All,

Oracle 9.2.0.5.0 RAC on Solaris 8 Nodes, with cursor_sharing = SIMILAR.
Application uses literals.

I was investigating the reason for one particular query having very high
version count and too many child cursors (v$sql_shared_cursor has no
mismatches, and plenty of rows). And found that setting
CURSOR_SHARING=FORCE was one way of avoiding these child cursors. So,  I
deduce that, with cursor sharing set to SIMILAR the optimizer peeks at the
bind variables everytime, and comes to the wrong conclusion that it could
possibly arrive at a different plan.

The table has one primary key (LOGIN_ID number) , and one unique key (LOGIN
varchar2), which are often queried upon. When I look at the histogram, I
see that login_id has one bucket, and login has 200 buckets. The two
queries in question are:
select * from table where login_id = :"SYS_B_0"
select * from table where login = :"SYS_B_0"

The first one does not create multiple child cursors.
The second one creates multiple child cursors.

I observed that no multiple child cursors are created, when :
1. No stats on the table
2. analyze using "analyze table .... estimate statistics sample 20 percent"
(Creates one bucket each for both the columns)
2. Analyze using dbms_stats with method_opt set to "For all columns size 1"
(Creates one bucket each for both the columns)

And Multiple child cursors are created when:
1. analyze using dbms_stats with method_opt set to "For all columns size
auto"
2. analyze using dbms_stats with method_opt set to "For all columns size
skewonly"

Both of the analyze above creates 200 buckets for the unique key column,
and just one for the primary key column

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?
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?

Regards
Raj

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

Other related posts: