In the first and the second parts of this series, we learned what query parsing is, how hard and soft parsing work, what their impact on the query is, and then moved on to understand the concept of parent and child cursors. In this third article, we will look at the issue of duplicate SQL statements and how Bind Variables can help us.

Are cursors being shared?

For a query to perform optimally, it’s essential that the best possible execution method is chosen. This is done via hard parsing of the query (for more detail, check out part 1 of this series). But hard parsing is a resource-intensive process. As much as it’s required (any query, at least the first time it runs, must be hard parsed), it will adversely affect server performance if hard parsing is required for every query.

This is even more important in environments where the chances of having a statement reused more than once are lower, i.e. an OLTP database – a banking system or a human resources database system. For example, in a banking system, every customer is unique and the chances of one customer doing their banking twice in a day are very low. That’s why most of the statements entered into the system will be unique, with slim-to-almost-no chance of being repeated. If care isn’t taken to ensure that such statements are sharable, very soon the database will be filled up with duplicate statements.

Let’s execute a few statements with the only difference being in the literals used in them. These statements are executed on the EMP table of the Scott schema.

SQL> select * from scott.emp where empno=7369;

     EMPNO      ENAME       JOB	       MGR  HIREDATE	    SAL      COMM      DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369      SMITH     CLERK	      7902 17-DEC-80	    800                    20

SQL> select * from scott.emp where empno=7499;

     EMPNO      ENAME       JOB	       MGR  HIREDATE	    SAL      COMM      DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499      ALLEN  SALESMAN	      7698 20-FEB-81	   1600       300          30

Two statements are executed and the only difference between them is the chosen employee number, which is a literal.

Now, let’s see the cursors created for these two statements in the database:

SQL> select sql_id, sql_text, version_count, hash_value
from V$sqlarea where sql_text like 'select * from scott%';  2

SQL_ID	      SQL_TEXT						 VERSION_COUNT HASH_VALUE
------------- -------------------------------------------------- ------------- ----------
109sb6ztrc2zp select * from scott.emp where empno=7369		             1 4084599797
a3r8shubwbr14 select * from scott.emp where empno=7499			     1 2546326564

We can see that, despite the fact that the only difference between the statement was the employee number, two different SQL ID’s were created and two distinct statements were loaded in the memory.

Now, think about an ACME corporation with hundreds of employees and such queries being executed all the time, flooding the database memory with statements that can be shared but aren’t because of literals being used! For every such statement, the database engine has to undergo the rigorous task of optimizing every single statement as a newly-executed one.

Below is a diagram demonstrating this:

Cursors with literals

It is possible that for different literal values, different execution plans are going to be beneficial. For example, for one value X, the database could prefer to go for an index-based execution and, for another value Y, it may prefer to opt for the full table scan. If we only consider the performance of each query executed in its own entirety, having a different cursor created for every individual statement is good. But this won’t be good at all for the overall performance of the database.

It’s of paramount importance to have cursors being shared and that’s what we can achieve using Bind Variables!

Bind Variables and their impact on cursor sharing

If there is one golden principle that every Oracle developer must follow, it is that they always use bind variables in their SQL statements. But how do bind variables help?

Well, bind variables act as a placeholder, a template that’s going to replace its inputs with every execution. This means that just a single version of the statement is loaded into the database memory. With this single iteration of the statement being loaded in the memory, the overhead of reparsing the same statement again and again is eliminated. Database would replace the bind variable with the supplied value of it and reuses the same cursor that’s already is now available in the Library Cache.

Below is a diagram:

Cursors with Bind Variables

Bind variables minimize the number of cursors in the system – this is good for those queries which are otherwise identical but can’t be shareable because of the literals being used.

Using bind variables

Bind variables can be used depending on the client tool that you are using, i.e. SQL*PLUS, SQL Developer, etc. Here is an example of using bind variables in SQL*PLUS:

SQL> variable dno number
SQL> exec :dno:=10                  

PL/SQL procedure successfully completed.

SQL> select * from emp where deptno=:dno;

EMPNO      ENAME      JOB 	MGR        HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450                    10
      7839 KING       PRESIDENT 	   17-NOV-81	   5000                    10
      7934 MILLER     CLERK	      7782 23-JAN-82	   1300                    10

SQL> exec :dno:=20

PL/SQL procedure successfully completed.

SQL> /

EMPNO      ENAME      JOB 	       MGR  HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK	      7902 17-DEC-80	    800                    20
      7566 JONES      MANAGER	      7839 02-APR-81	   2975                    20
      7788 SCOTT      ANALYST	      7566 19-APR-87	   3000                    20


EMPNO      ENAME      JOB 	       MGR  HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7876 ADAMS      CLERK	      7788 23-MAY-80       1100                    20
      7902 FORD       ANALYST	      7566 03-DEC-81	   3000                    20

Here is the parent cursor created for the above statement’s two iterations.

SQL> select sql_text, loaded_versions, plan_hash_value
from V$sqlarea
where sql_text like 'select * from emp%';  2    3  

SQL_TEXT                                           LOADED_VERSIONS PLAN_HASH_VALUE
-------------------------------------------------- --------------- ---------------
select * from emp where deptno=:dno                              1      3956160932

And we can see that there is a single child cursor created for the same:

SQL> select sql_id, sql_text, plan_hash_value, child_number
from v$sql
where sql_text like 'select * from emp%'; 
  2    3  
SQL_ID	      SQL_TEXT                                           PLAN_HASH_VALUE CHILD_NUMBER
------------- -------------------------------------------------- --------------- ------------
c1nx6x02h655a select * from emp where deptno=:dno                     3956160932            0

If you are a Java developer and are planning to use bind variables, you would need create a Prepared statement. For details, refer to the Java Developer’s guide in the documentation.

It’s important to mention that, even if a developer misses out on using the bind variables, the Oracle database engine is getting smarter at identifying the statements that can possibly be made sharable. Based on this intelligence, from 9i onwards Oracle can decide if it’s better to share the cursors, depending on the literals used. For example, in the employee table, every employee number is unique and that means that the execution plan being chosen for every given employee ID search would result in the same plan. Thus, the database would automatically decide to create a single cursor in this case. But the same can’t be said if the column used for filtration of the data is department number. Since the number of employees in every department can be significantly different, in this case, Oracle would not implicitly share the cursor for the different department numbers being searched.

But what if the developer forgets to use bind variables in his code? Fortunately, we have a solution, and that is to convert the literals to bind variables using the parameter CURSOR_SHARING.

CURSOR_SHARING parameter

The CURSOR_SHARING parameter defines how the database engine should treat the literals. This decision is based on the values of the parameter – Exact (default), Force and Similar. Exact is the default value and that meansthat, unless the cursors are not made sharable by the developer explicitly, literals would be treated as literals and statements would be left unshared. Thus this value of the parameter leaves it up to the developer to ensure that the statement is using bind variables.

Another value for this parameter is Force. Using Force as a value, the database gets the option to convert all literals to a system-defined bind variable. Let’s see what this value does to our statement when we start by changing the parameter value to Force at session level.

SQL> alter session set cursor_sharing=Force;
Session altered.

SQL> show parameter cursor_sharing

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- ------------------------------
cursor_sharing                       string                           FORCE

Now, let’s execute a statement and see what this parameter does to the literal used in the WHERE clause.

SQL> select * from scott.emp where deptno=10;

EMPNO      ENAME      JOB 	       MGR  HIREDATE	    SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450                    10
      7839 KING       PRESIDENT 	   17-NOV-81	   5000                    10
      7934 MILLER     CLERK	      7782 23-JAN-82	   1300                    10

SQL> select sql_text from V$sql where sql_text like 'select * from scott%';

SQL_TEXT
--------------------------------------------------------------------------------
select * from scott.emp where deptno=:"SYS_B_0"

We can see that due to the parameter having the value Force, the statement’s literal is now replaced with a system-defined bind variable – SYS_B_0. But using Force has one peculiar drawback – it simply converts all the literals to bind variables via a single execution plan.

Therefore, for two literals, X and Y, you will only see a single exection plan, A, rather than separate plans A and B as you might expect. If that sounds bad for the performance – you’re right. Of course it is, and that’s why we can use Similar!

SIMILAR makes cursor sharing possible only when the literals used in the statement will generate different execution plans. Unlike FORCE, using SIMILAR will create more than one cursor depending on the value passed to the database. Using the same example as above, if for value X a different plan is going to be generated (for example a Full Table Scan), it would create a separate cursor. For value Y, if the plan chosen is distinct (an index access is chosen) , it would create a separate cursor. Thus two cursors would be created instead of both the X and Y values being “forced” to share the same execution plan -even when they may not really need it.

Unfortunately, from 11g onwards, SIMILAR is now deprecated (you can read about this in the MOS #1169017.1 (requires signin))

To change the parameter in later editions of Oracle, you can use ALTER SESSION, ALTER SYSTEM or even can use the CURSOR_SHARING hint.

Conclusion

Parsing, especially hard parsing and reparsing, is a major reason for slow performance of the database. Bind variables ensure that the database is not suffering from any unnecessary reparsing issues. If you are developing using Oracle database, using bind variables is not optional – it’s a must.

But as with any feature, the use of Bind variables is not always the best thing to do. What is that possible gray area when using bind variables and how is it best handled? We shall see it in the next part of the series. Stay tuned!