{"id":73084,"date":"2017-01-20T15:41:07","date_gmt":"2017-01-20T15:41:07","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/understanding-sql-query-parsing-part-3-bind-variables-and-cursor-sharing\/"},"modified":"2021-07-14T13:06:58","modified_gmt":"2021-07-14T13:06:58","slug":"understanding-sql-query-parsing-part-3-bind-variables-and-cursor-sharing","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/understanding-sql-query-parsing-part-3-bind-variables-and-cursor-sharing\/","title":{"rendered":"Understanding SQL Query Parsing \u2013 Part 3: Bind Variables and Cursor Sharing"},"content":{"rendered":"

In the first<\/a> and the second<\/a> 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.<\/p>\n

Are cursors being shared?<\/h2>\n

For a query to perform optimally, it\u2019s 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<\/a>). But hard parsing is a resource-intensive process. As much as it\u2019s 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.<\/p>\n

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 \u2013 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\u2019s why most of the statements entered into the system will be unique, with slim-to-almost-no chance of being repeated. If care isn\u2019t taken to ensure that such statements are sharable, very soon the database will be filled up with duplicate statements.<\/p>\n

Let\u2019s 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.<\/p>\n

SQL> select * from scott.emp where empno=7369;\r\n\r\n     EMPNO      ENAME       JOB\t       MGR  HIREDATE\t    SAL      COMM      DEPTNO\r\n---------- ---------- --------- ---------- --------- ---------- ---------- ----------\r\n      7369      SMITH     CLERK\t      7902 17-DEC-80\t    800                    20\r\n\r\nSQL> select * from scott.emp where empno=7499;\r\n\r\n     EMPNO      ENAME       JOB\t       MGR  HIREDATE\t    SAL      COMM      DEPTNO\r\n---------- ---------- --------- ---------- --------- ---------- ---------- ----------\r\n      7499      ALLEN  SALESMAN\t      7698 20-FEB-81\t   1600       300          30<\/pre>\n
Two statements are executed and the only difference between them is the chosen employee number, which is a literal.<\/p>\n
Now, let\u2019s see the cursors created for these two statements in the database:<\/p>\n
SQL> select sql_id, sql_text, version_count, hash_value\r\nfrom V$sqlarea where sql_text like 'select * from scott%';  2\r\n\r\nSQL_ID\t      SQL_TEXT\t\t\t\t\t\t VERSION_COUNT HASH_VALUE\r\n------------- -------------------------------------------------- ------------- ----------\r\n109sb6ztrc2zp select * from scott.emp where empno=7369\t\t             1 4084599797\r\na3r8shubwbr14 select * from scott.emp where empno=7499\t\t\t     1 2546326564<\/pre>\n
We can see that, despite the fact that the only difference between the statement was the employee number, two different SQL ID\u2019s were created and two distinct statements were loaded in the memory.<\/p>\n
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\u2019t 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.<\/p>\n
Below is a diagram demonstrating this:<\/p>\n
\"Cursors<\/p>\n
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\u2019t be good at all for the overall performance of the database.<\/p>\n
It\u2019s of paramount importance to have cursors being shared and that\u2019s what we can achieve using Bind Variables!<\/p>\n
Bind Variables and their impact on cursor sharing<\/h2>\n
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?<\/p>\n
Well, bind variables act as a placeholder, a template that\u2019s 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\u2019s already is now available in the Library Cache.<\/p>\n
Below is a diagram:<\/p>\n
\"Cursors<\/p>\n
Bind variables minimize the number of cursors in the system \u2013 this is good for those queries which are otherwise identical but can\u2019t be shareable because of the literals being used.<\/p>\n
Using bind variables<\/h3>\n
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:<\/p>\n
SQL> variable dno number\r\nSQL> exec :dno:=10                  \r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> select * from emp where deptno=:dno;\r\n\r\nEMPNO      ENAME      JOB \tMGR        HIREDATE\t    SAL       COMM     DEPTNO\r\n---------- ---------- --------- ---------- --------- ---------- ---------- ----------\r\n      7782 CLARK      MANAGER\t      7839 09-JUN-81\t   2450                    10\r\n      7839 KING       PRESIDENT \t   17-NOV-81\t   5000                    10\r\n      7934 MILLER     CLERK\t      7782 23-JAN-82\t   1300                    10\r\n\r\nSQL> exec :dno:=20\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL> \/\r\n\r\nEMPNO      ENAME      JOB \t       MGR  HIREDATE\t    SAL       COMM     DEPTNO\r\n---------- ---------- --------- ---------- --------- ---------- ---------- ----------\r\n      7369 SMITH      CLERK\t      7902 17-DEC-80\t    800                    20\r\n      7566 JONES      MANAGER\t      7839 02-APR-81\t   2975                    20\r\n      7788 SCOTT      ANALYST\t      7566 19-APR-87\t   3000                    20\r\n\r\n\r\nEMPNO      ENAME      JOB \t       MGR  HIREDATE\t    SAL       COMM     DEPTNO\r\n---------- ---------- --------- ---------- --------- ---------- ---------- ----------\r\n      7876 ADAMS      CLERK\t      7788 23-MAY-80       1100                    20\r\n      7902 FORD       ANALYST\t      7566 03-DEC-81\t   3000                    20<\/pre>\n
Here is the parent cursor created for the above statement\u2019s two iterations.<\/p>\n
SQL> select sql_text, loaded_versions, plan_hash_value\r\nfrom V$sqlarea\r\nwhere sql_text like 'select * from emp%';  2    3  \r\n\r\nSQL_TEXT                                           LOADED_VERSIONS PLAN_HASH_VALUE\r\n-------------------------------------------------- --------------- ---------------\r\nselect * from emp where deptno=:dno                              1      3956160932<\/pre>\n
And we can see that there is a single child cursor created for the same:<\/p>\n
SQL> select sql_id, sql_text, plan_hash_value, child_number\r\nfrom v$sql\r\nwhere sql_text like 'select * from emp%'; \r\n  2    3  \r\nSQL_ID\t      SQL_TEXT                                           PLAN_HASH_VALUE CHILD_NUMBER\r\n------------- -------------------------------------------------- --------------- ------------\r\nc1nx6x02h655a select * from emp where deptno=:dno                     3956160932            0<\/pre>\n
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\u2019s guide in the documentation<\/a>.<\/p>\n
It\u2019s 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\u2019s 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\u2019t 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.<\/p>\n
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.<\/p>\n
CURSOR_SHARING parameter<\/h3>\n
The CURSOR_SHARING parameter defines how the database engine should treat the literals. This decision is based on the values of the parameter \u2013 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.<\/p>\n
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\u2019s see what this value does to our statement when we start by changing the parameter value to Force at session level.<\/p>\n
SQL> alter session set cursor_sharing=Force;\r\nSession altered.\r\n\r\nSQL> show parameter cursor_sharing\r\n\r\nNAME                                 TYPE                             VALUE\r\n------------------------------------ -------------------------------- ------------------------------\r\ncursor_sharing                       string                           FORCE<\/pre>\n
Now, let\u2019s execute a statement and see what this parameter does to the literal used in the WHERE clause.<\/p>\n
SQL> select * from scott.emp where deptno=10;\r\n\r\nEMPNO      ENAME      JOB \t       MGR  HIREDATE\t    SAL       COMM     DEPTNO\r\n---------- ---------- --------- ---------- --------- ---------- ---------- ----------\r\n      7782 CLARK      MANAGER\t      7839 09-JUN-81\t   2450                    10\r\n      7839 KING       PRESIDENT \t   17-NOV-81\t   5000                    10\r\n      7934 MILLER     CLERK\t      7782 23-JAN-82\t   1300                    10\r\n\r\nSQL> select sql_text from V$sql where sql_text like 'select * from scott%';\r\n\r\nSQL_TEXT\r\n--------------------------------------------------------------------------------\r\nselect * from scott.emp where deptno=:\"SYS_B_0\"<\/pre>\n
We can see that due to the parameter having the value Force, the statement\u2019s literal is now replaced with a system-defined bind variable \u2013 SYS_B_0. But using Force has one peculiar drawback \u2013 it simply converts all the literals to bind variables via a single execution plan.<\/p>\n
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 \u2013 you\u2019re right. Of course it is, and that\u2019s why we can use Similar!<\/p>\n
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 \u201cforced\u201d to share the same execution plan -even when they may not really need it.<\/p>\n
Unfortunately, from 11g onwards, SIMILAR is now deprecated.<\/p>\n
To change the parameter in later editions of Oracle, you can use ALTER SESSION, ALTER SYSTEM or even can use the CURSOR_SHARING hint.<\/p>\n
Conclusion<\/h2>\n
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 \u2013 it\u2019s a must.<\/p>\n
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!<\/p>\n","protected":false},"excerpt":{"rendered":"
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…<\/p>\n","protected":false},"author":316201,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533,143531],"tags":[],"coauthors":[48556],"class_list":["post-73084","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","category-t-sql-programming-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73084","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316201"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73084"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73084\/revisions"}],"predecessor-version":[{"id":73569,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73084\/revisions\/73569"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73084"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73084"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73084"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73084"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}