CURSOR_SHARING

Back a thousand years ago (in Oracle time) there weren’t bind variables in SQL.  Everything was a literal.  We had substitution parameters in SQLPlus, but to the optimizer it was still a literal because the substitution happened in SQLPlus before the SQL was sent to the Optimizer.  Along comes V7 and binds come into the mix, and this is very good.  Now we can have one SQL with one cursor in the shared pool (which also was new in V7) and run it with multiple values.  (By the way, substitution parameters still exist, and are cool for scripts, these are the “&” things you might see some times.)

But there was a major problem.  There were likely millions of SQL statements out in the world that should be using binds that weren’t.  Without rewriting everything how could we take advantage of this great new feature?

To combat this problem, Oracle introduced CURSOR_SHARING.  It was (and still is) the lazy programmer’s way to get binds into the SQL.  In the beginning it was the only practical way to take advantage of binds.  But it was always just a “Band-Aid”, not really the best way to run your system.

It had three values: EXACT, FORCE and SIMILAR.  The default is EXACT and that is what you should be using.  This means only shared cursors that match the SQL text “exactly”.  Different bind values are fine, but even the names of the binds have to be exactly the same.   FORCE does what it sounds like it “forces” binds into a SQL statement for every literal it finds.  This will make statements that only differ in the literals used be sharable.  The setting of SIMILAR was supposed to make intelligent choices about binds.  It worked like FORCE, but would come up with different named binds if it thought that there might be different plans.  This option never worked well for the most part. Too often it created lots of different version of the SQL which were not helpful and tending to make things worse not better for sharing of statements.

Starting in 11.2.0.3 SIMILAR is now desupported.  You can still set CURSOR_SHARING to SIMILAR, but it now works the same as FORCE.  This came about because of Adaptive Cursor Sharing which is much better at working with different bind values for the same statement.  Not perfect either but certainly much better and more robust.

Here is a little demo.  The demo just creates a simple table, with no data:

CREATE TABLE rvd99 (x NUMBER, y NUMBER);

Then I have a set of ten queries that look like this:

select count(y) from rvd99 where x = 1;

But each one has a different value for the predicate.  The values are 1, 10, 20, 30, 40, 50, 60, 70, 80 and 99.

I’ll use a select from v$sql_bind_capture and v$sql to see the SQL statements generated for setting CURSOR_SHARING to the values of FORCE and SIMILAR.

Prior to 11.2.0.3 when running this set of queries with FORCE you would see this:

SQL> SELECT s.sql_text, b.value_string, s.plan_hash_value, s.child_number

  2  FROM v$sql_bind_capture b, v$sql s

  3  WHERE s.hash_value = b.hash_value

  4  AND s.address = b.address

  5  AND s.child_number = b.child_number

  6  AND s.sql_text LIKE 'select count(y) from rvd99 %';

SQL_TEXT                                           VALUE PLAN_HASH_VALUE CHILD_NUMBER

-------------------------------------------------- ----- --------------- ------------

select count(y) from rvd99 where x = :"SYS_B_0"    1          4162104232            0

This seems reasonable, just put in the value at run time.  Only one cursor was created for all 10 runs.  But for SIMILAR the output looks like this:

SQL_TEXT                                           VALUE PLAN_HASH_VALUE CHILD_NUMBER

-------------------------------------------------- ----- --------------- ------------

select count(y) from rvd99 where x = :"SYS_B_0"    1          4162104232            0

select count(y) from rvd99 where x = :"SYS_B_0"    1          4162104232            1

select count(y) from rvd99 where x = :"SYS_B_0"    10         4162104232            2

select count(y) from rvd99 where x = :"SYS_B_0"    20         4162104232            3

select count(y) from rvd99 where x = :"SYS_B_0"    30         4162104232            4

select count(y) from rvd99 where x = :"SYS_B_0"    40         4162104232            5

select count(y) from rvd99 where x = :"SYS_B_0"    50         4162104232            6

select count(y) from rvd99 where x = :"SYS_B_0"    60         4162104232            7

select count(y) from rvd99 where x = :"SYS_B_0"    70         4162104232            8

select count(y) from rvd99 where x = :"SYS_B_0"    80         4162104232            9

select count(y) from rvd99 where x = :"SYS_B_0"    99         4162104232           10

Notice how similar ended up creating a new child for each value, which is pretty much what we were hoping to avoid with CURSOR_SHARING.  After 11.2.0.3 you get the same thing for FORCE and SIMILAR which is just the one plan:

SQL_TEXT                                           VALUE PLAN_HASH_VALUE CHILD_NUMBER

-------------------------------------------------- ----- --------------- ------------

select count(y) from rvd99 where x = :"SYS_B_0"    1          4162104232            0

So what does all this mean?  Two things that I can think of, use EXACT unless you have code you can’t change, then use FORCE as a temporary fix until you can get binds in the code where they should be.  And two, let Adaptive Cursor Sharing do what SIMILAR was supposed to do and never did very well, that is have different plans for different sets of binds.