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