Cursor Sharing in oracle

Before oracle8.1.6, When application sends a SQL, oracle first check the shared pool to see if there is an exact statement in the shared pool. If the exact statement is not found, then a hard parse is necessary to generate the executable form of the statement. Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only when their texts match exactly. This is the default behavior. Using this setting, similar statements cannot shared; only textually exact statements can be shared

Let me demonstrate CURSOR_SHARING=EXACT

SQL> select count(*),empno from emphist group by empno;

COUNT(*) EMPNO
---------- ----------
1 7369
1 7902
4194304 7934

SQL> create index idxemphist on emphist(empno);

Index created.

SQL> execute DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCOTT', TABNAME => 'EMPHIST',ESTIMATE_PERCENT
=> 10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1',CASCADE => TRUE);

PL/SQL procedure successfully completed.

SQL> alter system flush shared_pool;

System altered.
SQL> set autotrace traceonly explain

SQL> alter session set cursor_sharing='EXACT';

Session altered.

SQL> select count(ename) from emphist where empno=7934;

COUNT(ENAME)
------------
4194304

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2297 Card=1 Bytes=11
)

1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMPHIST' (Cost=2297 Card=4193270
Bytes=46125970)

SQL> select count(ename) from emphist where empno=7902;

COUNT(ENAME)
------------
1
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMPHIST' (Cost=4 Card=
1 Bytes=11)

3 2 INDEX (RANGE SCAN) OF 'IDXEMPHIST' (NON-UNIQUE) (Cost=
3 Card=1)

SQL> select count(ename) from emphist where empno=7902;

COUNT(ENAME)
------------
1

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMPHIST' (Cost=4 Card=
1 Bytes=11)

3 2 INDEX (RANGE SCAN) OF 'IDXEMPHIST' (NON-UNIQUE) (Cost=
3 Card=1)

SQL> select count(ename) from emphist where empno=7369;

COUNT(ENAME)
------------
1

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMPHIST' (Cost=4 Card=
1 Bytes=11)

3 2 INDEX (RANGE SCAN) OF 'IDXEMPHIST' (NON-UNIQUE) (Cost=
3 Card=1)

SQL> select count(ename) from emphist where empno=7369;

COUNT(ENAME)
------------
1

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=11)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMPHIST' (Cost=4 Card=
1 Bytes=11)

3 2 INDEX (RANGE SCAN) OF 'IDXEMPHIST' (NON-UNIQUE) (Cost=
3 Card=1)

SQL> set autotrace off
SQL> select sql_text
2 from v$sql
3 where sql_text like 'select count(ename) from emphist where empno=%';

SQL_TEXT
--------------------------------------------------------------------------------
select count(ename) from emphist where empno=7369
select count(ename) from emphist where empno=7902
select count(ename) from emphist where empno=7934

Conclusion : Oracle does not use bind variable and every unique SQL statement, execution plan will be generated. It forced to hardparse every uniqe SQL statement and it leads to consume lot of CPU cycles. Oracle can not hardparse hundreds of SQL statement concurrently and it end up waiting for shared pool. To overcome this problem, oracle introduced FORCE paramter to use bind variable.

In oracle8.1.6, Oracle introduced new parameter FORCE. This is accomplished by transforming the statement into a new one by replacing the literals with system generated bind variables. The problem with bind variables is that the optimizer cannot predict precise selectivity.

Using cursor_sharing = force will parse the FIRST sql statement loaded into memory and will use this execution plan for the subsequent similar sql statements. This might be a problem if the FIRST statement's literals have good selectivity but those of the subsequent statements don't. In this case the statements with poor selectivity will also use the index whereas a full table scan would perform better. However, if the firstly run statement doesn't have good selectivity and a full table scan is used, the subequent similar statements will also use FTS.

Let me demonstrate CURSOR_SHARING=FORCE

SQL> select count(ename) from employee where empno=7934;

COUNT(ENAME)
------------
2097152
SQL>

The below plan is extracted from tkprof output file. Optimizer use the FTS which is right choice.

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
2097152 TABLE ACCESS FULL OBJ#(30800)

Let us run the another query and see the execution plan.

SQL> select count(ename) from employee where empno=7902;

COUNT(ENAME)
------------
1

SQL>

The below plan is extracted from tkprof output file. Optimizer again use the FTS for this subsequent query which is bad choice.

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
1 TABLE ACCESS FULL OBJ#(30800)

SQL> select sql_text from v$sql
2 where sql_text like 'select count(ename) from emphist where empno=%';

SQL_TEXT
--------------------------------------------------------------------------------
select count(ename) from emphist where empno=:"SYS_B_0"

SQL>

Conclusion : Oracle generate only one plan and use for all the SQL code. This would turn the hard parse into soft parse. It would require fewer resource. FORCE option is good when the query table is not skewed and all the queries require same execution plan. But in real world, this is not a good option.

To overcome this problem, In Oracle9i we can use CURSOR_SHARING=SIMILAR setting which makes the right plan based on the statistics.

Let me demonstrate CURSOR_SHARING=SIMILAR

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL>

SQL> select count(ename) from employee where empno=7934;

COUNT(ENAME)
------------
2097152

Let us run the query and see the actual execution plan. Optimizer use the FTS which is right choice.

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
2097152 TABLE ACCESS FULL OBJ#(30800)


Let us run the another query and see the actual execution plan.

SQL> select count(ename) from employee where empno=7902;

COUNT(ENAME)
------------
1

SQL>

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
1 TABLE ACCESS BY INDEX ROWID OBJ#(30800)
1 INDEX RANGE SCAN OBJ#(30801) (object id 30801)


SQL> select sql_text
2 from v$sql
3 where sql_text like 'select count(ename)%';

SQL_TEXT
--------------------------------------------------------------------------------
select count(ename) from emphist where empno=:"SYS_B_0"
select count(ename) from emphist where empno=:"SYS_B_0"
SQL>

Optimizer use the Index range scan which is right choice. Optimizer makes the right choice based on the statistics.

Conclusion : Optimizer replace the literal with bind variable and check if it requires different plan for the query based on the current statistics. If so then, optimizer mark the bind variable as unsafe and store the value in the signature of the query. Next time, user submit the query with the same bind variable value, then oracle resuse the plan. Optimizer would use the same execution plan based on the statistics if user submit the same query with different literal .

SIMILAR option resolve the issues which we had in EXACT. Exact does not use bind variable. But here it is using bind variable. In FORCE option, optimizer use the same execution plan for same query with different literal. But SIMILAR option, it use the different plan for the same query with different literal values based on the current statistics.

Please read the below link if you need more info.

http://www.oracle.com/technology/oramag/oracle/06-jan/o16asktom.html