Cursor Sharing and Bind Variable Usage Effect on Oracle Database Performance

I will explain what is the Cursor Sharing and Bind Variable Usage Effect on Oracle Database Performance in this post.

Cursor Sharing and Bind Variable Usage Effect

If you don’t know how to tune of Oracle database, you should read the following article before this.

Performance Tuning Features and Tools in Oracle | Oracle Database Performance Tuning Tutorial -2

When any SQL is sent Oracle for the first time, Oracle will create a plan by following the steps below. Or, if the plan has been created, the existing plan will be used or new plan will be created even if the SQL is the same (different literal values, or not using bind variable ).

If a new SQL doesn’t exists in the Shared pool, it must be parsed from scratch.

The cost of creating the plan for the first time is very high for Oracle database.

If any SQL will run for the first time, Oracle has to do a hard parse. If this SQL has been run at least once before, that is, Oracle does a Soft parse for this execution.

If you want to learn more detail about SQL Statement phases and parsing, you should read the following article.

SQL Statement Processing in Oracle Database

Bind Variable in Oracle

For Oracle, One character of SQL is different or existing lower case-upper case character, then new plan is created for this time.

If you don’t use bind variables in the SQL queries then Oracle will know similar SQLs differently like following.

select * from customer where id=63;

select * from customer where id=34;

Above queries are almost same, just id variable is different. But Oracle optimizer will evaluate these SQLs like different SQL.

If you use bind variable instead of literal like following then Oracle will evaluate as same SQL and will use same execution plan and won’t be hard parse in Oracle.

variable SYS_B_0 number; 
exec :SYS_B_0:= 63 
select * from customer where id= :SYS_B_0;

Cursor Sharing in Oracle

CURSOR_SHARING is an init.ora parameter and related with bind variables usage which decides whether a SQL send from user is a candidate for new parsing ( hard parse ) or will use an existing plan ( Soft Parse ).

The CURSOR_SHARING parameter can get the following 3 values.

Exact

Exact is default value of Cursor_sharing parameter. In this value, Oracle will share the same plan exactly if text of SQL matches exactly with the text of SQL kept in the shared pool and use that cursor and don’t do any changes in the SQL. Otherwise Oracle will generate different execution plans for every query( If they are not same exactly).

Let’s look at an example.

SQL> create table deveci (id number);

Table created.

SQL> insert into deveci values(1);

1 row created.

SQL> insert into deveci values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from deveci where id=1;

ID
----------
1

SQL> select * from deveci where id=2;

ID
----------
2



SQL> select sql_text
2 from v$sql
3 where sql_text like 'select * from deveci%'
4 order by sql_text;

SQL_TEXT
-----------------------------------------------------

select * from deveci where id=1
select * from deveci where id=2

As you see there are 2 statements in V$sql result and Shared pook, so Oracle has generated 2 plans for these 2 SQLs.  Oracle will generate the new plan in this case even when the difference between the two SQL was just literal value.

Force

Force is introduced in 8.1.6. In this value, Oracle will share the same plan forcibly if text of SQL matches with the text of SQL except the literal values kept in the shared pool.  Oracle will replace every literals with bind variables and share the same plan if 2 SQL’s are same except their literal values

SQL> alter session set cursor_sharing=force;

Session altered.


SQL> select * from deveci where id=1;

id
----------
1

SQL> select * from deveci where id=2;

id
----------
2

SQL> select sql_text
2 from v$sql
3 where sql_text like 'select * from deveci%'
4 order by sql_text;

SQL_TEXT
---------------------------------------------------

select * from deveci where id=:"SYS_B_0"

There is one SQL in the Shared pool and V$SQL result, it means Oracle didn’t generated a new plan for the second SQL statement.

Duplicate Plan generate will make the Database and Operating system resources busy ( CPU,RAM,IO ) unnecessarily

Similar

Similar is introduced in 9i. In this value, Oracle will share the same plan if text of SQL matches with the text of SQL except the literal values kept in the shared pool. Namely SQL statements differ just only in literal values.

Create a new table for simulating this case.

SQL> create table deveci (id1 number,id2 number);

Table created.

SQL>
begin
for i in 1 .. 100 loop
insert into deveci values(1,i);
end loop;
commit;
/

PL/SQL procedure successfully completed.

update deveci set id1=2 where rownum =1;
commit;

Deveci table have 99rows in 1 value and 1 row in 2 value.

SQL> create index ix_on_id on deveci(id1);

Index created.




SQL> alter session set cursor_sharing=similar;

Session altered.

SQL> select * from deveci where id=2;

1 row selected.

SQL> select * from deveci where id=1;

99 rows selected.

SQL> select * from deveci where id=10;

0 row selected.

SQL>



SQL> select sql_text
2 from v$sql
3 where sql_text like 'select * from deveci%'
4 order by sql_text;

SQL_TEXT
----------------------------------------------------

select * from deveci where id=:"SYS_B_0"
select * from deveci where id=:"SYS_B_0"
select * from deveci where id=:"SYS_B_0"

Oracle generated 3 plans for these similar SQLs, even they are similar, but their Plans should be different. Oracle Optimizer stored literals to ensure the reusability of the plan only in case the same literal is provided.

Even though SIMILAR and EXACT look like same but they are not same. For example; Following queries looks like similar but literals are different. Because Plan should be same, Oracle generated same plan for 2 SQLs.

SQL> alter system flush shared_pool;

System altered.

SQL> select * from deveci where id1=1 and id1=2;

no rows selected

SQL> select * from deveci where id1=2 and id1=1;

no rows selected

SQL> select sql_text
2 from v$sql
3 where sql_text like 'select * from deveci%'
4 order by sql_text;

SQL_TEXT
--------------------------------------------------------------

select * from deveci where id1=:"SYS_B_0" and id1=:"SYS_B_1"

You should set CURSOR_SHARING parameter value according to your database and software. If related application is not used Bind Variable , response time low due to a very high number of library cache misses and lots of SQL Statements matches with the text of SQL except the literal, then you should use CURSOR_SHARING=FORCE. 

Actually Cursor_sharing= force is temporary solution, Permanent solution is to use Bind Variable.

Oracle doesn’t recommend CURSOR_SHARING=FORCE in the DSS(Decision support system) environment or if you are using complex queries

Do you want to learn Oracle Database Performance Tuning detailed, then Click this link.

Performance Tuning and SQL Tuning Tutorial in the Oracle Database