Dive Into Oracle Cursor_Sharing

First, Let us review some very basic database knowledge.

Literals V.S. Bind Variables

select * from t where id=1 -- 1 is literal

Select * from t where id：=id -- id is bind variable

OLTP applications, generally speaking, should be using bind variables.

Bind variable peeking

This was Introduced in Oracle 9. 

For a sql with bind variables, Oracle will peek at the values of bind variables, based on the nature of the data distribution, pick up a plan that is optimal for the sql with the current values. Without ACS(auto cursor sharing), Oracle will reuse the plan (decision made on the first time values) for later invocations. This will cause performance problems if data distribution is skewed. 

ACS(auto cursor sharing)

This was introduced in Oracle 11. 

With ACS, a simple way to understand is Oracle will not be fixated on one plan; instead, it will do bind variable peek as many times as it thinks it should do, and pick the optimal plans for the current bind variable values.

Now the basic stuff is over, let us create a demo table to illustrate Cursor_Sharing effect:

create table t as
   select case when rownum <99 then rownum else 99 end id, a.* from all_objects  a

In my test, table t has 71898 rows, 71898-98=71800 has id=99, so it is a table with data distribution very skewed.

Effect of Cursor_sharing=exact 

 By default cursor_sharing=exact. Sqls, if there is anything different (even if it is just about white spaces), will have different cursors. So the following sqls all get to have their own cursors. 

select * from t where id=1

select * from t where id  =1 (has a whitespace)

select * from t where id=2

select * from t where id=3

select * from t where id=:id

Back to my previous statement, "OLTP applications, generally speaking, should be using bind variables", this is because sqls with different literal values can't share cursors, and will degrade performance. But another important consideration is about security: the notorious sql injection. 

Effect of Cursor_sharing=similar

This was deprecated in Oracle 11g (but still works). A simple way to understand similar is: when data is skewed, Oracle does bind variable peeking every time. 

SQL> alter system flush shared_pool;

SQL> alter session set cursor_sharing=similar;

SQL> select * from t where id=99;

SQL> select * from t where id=1;

SQL> select s.sql_id,s.sql_text,s.CHILD_NUMBER from v$sql s where sql_text like '%select % from t %';

SQL_ID        SQL_TEXT                                                            CHILD_NUMBER

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

4f9x0qxxbkbx2  select * from t where id=:"SYS_B_0"           0

4f9x0qxxbkbx2  select * from t where id=:"SYS_B_0"           1

Notice:

1. Literals are replaced with ‘SYS_B_#’ 
2.  There are 2 cursors, one is a child cursor. 
3. select * from t where id=1 uses full table scan.

SQL> select * from table( dbms_xplan.display_cursor( '4f9x0qxxbkbx2', 0 )); 

shows full table scan

    4.   select * from t where id=99 uses index scan. 

SQL> select * from table( dbms_xplan.display_cursor( '4f9x0qxxbkbx2', 1 ));

shows index scan.

 

Effect of Cursor_sharing=force

(i do not understand why Oracle would deprecate similar, but not force)

Force replaces all literals, not just literals in the where clause. 

So this sql:

select substr( x, 1, 5 ) x, y, to_char( z, 'dd-mon-yyyy' ) z from t2  where a=55;

become this: 

select substr( x, :"SYS_B_0", :"SYS_B_1" ) x, y, to_char( z, :"SYS_B_2" ) z from t2 where a=:"SYS_B_3"

cursor_sharing=force can have interesting side effects. Take the above sql for an example, Oracle doesn't know if know if substr( x, :"SYS_B_0", :"SYS_B_1" ) is for:

substr( x, 1, 5 )

substr( x, 1, 50 )

substr( x, 1, 500 )

Oracle might think a cursor for substr( x, 1, 500 ) can be shared more (it can accommodate all 3 sqls). If you code relies on column width, you will be in for a surprise:

 

--first try with cursor_sharing=exact

SQL>create table t2 ( a int, x varchar2(2000), y int, z date );

SQL>insert into t2 values ( 55, 'abcdefg', 2, sysdate );

SQL> select substr( x, 1, 5 ) x, y, to_char( z, 'dd-mon-yyyy' ) z from t2 where a=55;

X                 Y                 Z

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

abcde        2               13-Feb-2012

--now try with cursor_sharing=force

SQL> select substr( x, 1, 5 ) x, y, to_char( z, 'dd-mon-yyyy' ) z from t2 where a=55;

 X                                                                                                                      Y Z

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

abcde                                                                                                                  2 13-Feb-2012 

The column width of X is way more than 5 characters.  Oracle does this to maximize cursor sharing. 

Effect of ACS (Auto Cursor Sharing)

Without ACS, exact/force sticks to one plan and one plan only, until the plan is flushed out of the shared pool. similar creates new plans at its discretion.

With ACS, Oracle will create new plans at its discretion, this alleviate performance issues if data distribution is skewed, tuning hard parse to soft parse. 

Best Practice

• An OLTP application should use cursor_sharing=exact. 

• An OLTP application should always use bind variables whenever it makes sense:

select * from t where id=:id

(id should be bound, later invocation can avoid hard parse)

select substr (name, 1, 5) from t

(should 1, 5 be bound? Hardly)

 

• If you know a column is skewed, you can use literals for that column, so Oracle can create different plans Or use bind variables and let Oracle ACS help you. E.g.

select * from employees where manager=‘Y’ 

-- normally,  managers are few if the company is not overly bureaucratic, should use index scan

select * from employees where manager=‘N’ 

-- should use table scan