RE: Cursor_sharing - Optimizer - Histograms

We ran into the exact same issue noticing a lot of sql with high version counts. After talking to oracle developers who worked on parts of the code the bottom line is if CURSOR_SHARING=3DSIMILAR the following will happen (with 9i- this is expected behavior):

1. If there is a non-equal clause (<, >, BETWEEN) Oracle will reparse and you will get a new child
2. If there is a histogram on a column in the where clause Oracle will reparse and you will get a new child.

Basically CURSOR_SHARING=3DSIMILAR + HISTOGRAMS can be as bad as CURSOR_SHARING=3DEXACT on a system with no bind variables.

This should be interesting when people upgrade to 10g who are using SIMILAR and the default METHOD_OPT- it changes from FOR ALL COLUMNS SIZE 1 to FOR ALL COLUMNS SIZE AUTO in 10g. Assuming SIMILAR acts the same in 10g imagine the possible problems for people not aware of this "feature".

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rajesh.Rao_at_jpmchase.com
Sent: Thursday, March 31, 2005 10:00 AM
To: Oracle-L_at_freelists.org
Subject: Cursor_sharing - Optimizer - Histograms

Dear All,

Oracle 9.2.0.5.0 RAC on Solaris 8 Nodes, with cursor_sharing =3D = 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=3DFORCE 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 =3D :"SYS_B_0" select * from table where login =3D :"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


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