Literals, Substitution Variables and Bind Variables

If you've read anything about writing OLTP applications that talk to Oracle databases, you will know that bind variables are very important.

Each time an SQL statement is sent to the database, an exact text match is performed to see if the statement is already present in the shared pool. If no matching statement is found a hard parse is performed, which is a resource intensive process. If the statement is found in the shared pool this step is not necessary and a soft parse is performed. Concatenating variable values into an SQL statement makes the statement unique, forcing a hard parse. By contrast, using bind variables allow reuse of statements as the text of the statement remains the same. Only the value of the bind variable changes.

Why do we care?

• Holding many similar SQL statements in the shared pool is a waste of memory.
• Filling the shared pool with similar statements will cause well written statements to get paged out of the shared pool quickly, forcing them to be reparsed also.
• Parsing SQL statements is a resource intensive process. Reducing the number of hard parses results in reduced CPU usage.

The following example shows the affect of using literals on the shared pool. First the shared pool is cleared of previously parsed statements. Then two queries are issued, both specifying literal values in the WHERE clause. Finally the contents of the shared pool is displayed by querying the V$SQL view.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.


SQL> SELECT * FROM dual WHERE dummy = 'LITERAL1';
no rows selected

SQL> SELECT * FROM dual WHERE dummy = 'LITERAL2';
no rows selected


SQL> COLUMN sql_text FORMAT A60

SQL> SELECT sql_text,
2         executions
3  FROM   v$sql
4  WHERE  INSTR(sql_text, 'SELECT * FROM dual WHERE dummy') > 0
5  AND    INSTR(sql_text, 'sql_text') = 0
6  ORDER BY sql_text;


SQL_TEXT                                                     EXECUTIONS

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

SELECT * FROM dual WHERE dummy = 'LITERAL1'                           1
SELECT * FROM dual WHERE dummy = 'LITERAL2'                           1

2 rows selected.

SQL>

From this we can see that both queries were parsed separately.

Substitution variables are a feature of the SQL*Plus tool. They have nothing to do with the way SQL is processed by the database server. When a substitution variable is used in a statement, SQL*Plus requests an input value and rewrites the statement to include it. The rewritten statement is passed to the database. As a result, the database server knows nothing of the substitution variable. The following example illustrates this by repeating the previous test, this time using substitution variables.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.


SQL> SELECT * FROM dual WHERE dummy = '&dummy';

Enter value for dummy: SUBSTITUTION_VARIABLE1

old   1: SELECT * FROM dual WHERE dummy = '&dummy'

new   1: SELECT * FROM dual WHERE dummy = 'SUBSTITUTION_VARIABLE1'

no rows selected


SQL> SELECT * FROM dual WHERE dummy = '&dummy';

Enter value for dummy: SUBSTITUTION_VARIABLE2

old   1: SELECT * FROM dual WHERE dummy = '&dummy'

new   1: SELECT * FROM dual WHERE dummy = 'SUBSTITUTION_VARIABLE2'

no rows selected


SQL> COLUMN sql_text FORMAT A60

SQL> SELECT sql_text,
2         executions
3  FROM   v$sql
4  WHERE  INSTR(sql_text, 'SELECT * FROM dual WHERE dummy') > 0
5  AND    INSTR(sql_text, 'sql_text') = 0
6  ORDER BY sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
SELECT * FROM dual WHERE dummy = 'SUBSTITUTION_VARIABLE1'             1
SELECT * FROM dual WHERE dummy = 'SUBSTITUTION_VARIABLE2'             1

2 rows selected.

SQL>

Once again, both statements were parsed separately. As far as the database server is concerned, literals and substitution variables are the same thing.

The following example illustrates the affect of bind variable usages on the shared pool. It follows the same format as the previous examples.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> VARIABLE dummy VARCHAR2(30);

SQL> EXEC :dummy := 'BIND_VARIABLE1';

PL/SQL procedure successfully completed.

SQL> SELECT * FROM dual WHERE dummy = :dummy;

no rows selected

SQL> EXEC :dummy := 'BIND_VARIABLE2';

PL/SQL procedure successfully completed.

SQL> SELECT * FROM dual WHERE dummy = :dummy;

no rows selected

SQL> COLUMN sql_text FORMAT A60

SQL> SELECT sql_text,
2         executions
3  FROM   v$sql
4  WHERE  INSTR(sql_text, 'SELECT * FROM dual WHERE dummy') > 0
5  AND    INSTR(sql_text, 'sql_text') = 0
6  ORDER BY sql_text;

SQL_TEXT                                                     EXECUTIONS
----------------------------------------------------------- ----------
SELECT * FROM dual WHERE dummy = :dummy                               2

1 row selected.

SQL>

This clearly demonstrates that the same SQL statement was executed twice.

The following example measures the amount of CPU used by a session for hard and soft parses when using literals. The shared pool is flushed and a new session is started. Dynamic SQL is used to mimic an application sending 10 statements to the database server. Notice that the value of the loop index is concatinated into the string, rather than using a bind variable. The CPU usage is retrieved from the V$MYSTAT view by querying the "parse time cpu" statistic. This statistic represents the total CPU time used for parsing (hard and soft) in 10s of milliseconds. The statements present in the shared pool are also displayed.

SQL> CONN sys/password AS SYSDBA

Connected.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

SQL> CONN sys/password AS SYSDBA

Connected.

SQL> DECLARE
2    l_dummy  dual.dummy%TYPE;
3  BEGIN
4    FOR i IN 1 .. 10 LOOP
5      BEGIN
6        EXECUTE IMMEDIATE 'SELECT dummy FROM dual WHERE dummy = ''' || TO_CHAR(i) || ''''
7        INTO l_dummy;
8      EXCEPTION
9        WHEN NO_DATA_FOUND THEN
10          NULL;
11      END;
12    END LOOP;
13  END;
14  /

PL/SQL procedure successfully completed.

SQL> SELECT sn.name, ms.value
2  FROM   v$mystat ms, v$statname sn
3  WHERE  ms.statistic# = sn.statistic#
4  AND    sn.name       = 'parse time cpu';


NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                           63

1 row selected.


SQL> COLUMN sql_text FORMAT A60

SQL> SELECT sql_text,
2         executions
3  FROM   v$sql
4  WHERE  INSTR(sql_text, 'SELECT dummy FROM dual WHERE dummy') > 0
5  AND    INSTR(sql_text, 'sql_text') = 0
6  AND    INSTR(sql_text, 'DECLARE') = 0
7  ORDER BY sql_text;


SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
SELECT dummy FROM dual WHERE dummy = '1'                              1
SELECT dummy FROM dual WHERE dummy = '10'                             1
SELECT dummy FROM dual WHERE dummy = '2'                              1
SELECT dummy FROM dual WHERE dummy = '3'                              1
SELECT dummy FROM dual WHERE dummy = '4'                              1
SELECT dummy FROM dual WHERE dummy = '5'                              1
SELECT dummy FROM dual WHERE dummy = '6'                              1
SELECT dummy FROM dual WHERE dummy = '7'                              1
SELECT dummy FROM dual WHERE dummy = '8'                              1
SELECT dummy FROM dual WHERE dummy = '9'                              1

10 rows selected.


SQL>

The results show that 630 milliseconds of CPU time were used on parsing during the session. In addition, the shared pool contains 10 similar statements using literals.

The following example is a repeat of the previous example, this time using bind variables. Notice that the USING clause is used to supply the loop index, rather than concatenating it into the string.

SQL> CONN sys/password AS SYSDBA

Connected.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.


SQL> CONN sys/password AS SYSDBA

Connected.

SQL> 

SQL> DECLARE
2    l_dummy  dual.dummy%TYPE;
3  BEGIN
4    FOR i IN 1 .. 10 LOOP
5      BEGIN
6        EXECUTE IMMEDIATE 'SELECT dummy FROM dual WHERE dummy = TO_CHAR(:dummy)'
7        INTO l_dummy USING i;
8      EXCEPTION
9        WHEN NO_DATA_FOUND THEN
10          NULL;
11      END;
12    END LOOP;
13  END;
14  /

PL/SQL procedure successfully completed.

SQL> 

SQL> SELECT sn.name, ms.value
2  FROM   v$mystat ms, v$statname sn
3  WHERE  ms.statistic# = sn.statistic#
4  AND    sn.name       = 'parse time cpu';

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
parse time cpu                                                           40

1 row selected.



SQL> 

SQL> COLUMN sql_text FORMAT A60

SQL> SELECT sql_text,
2         executions
3  FROM   v$sql
4  WHERE  INSTR(sql_text, 'SELECT dummy FROM dual WHERE dummy') > 0
5  AND    INSTR(sql_text, 'sql_text') = 0
6  AND    INSTR(sql_text, 'DECLARE') = 0
7  ORDER BY sql_text;

SQL_TEXT                                                     EXECUTIONS
------------------------------------------------------------ ----------
SELECT dummy FROM dual WHERE dummy = TO_CHAR(:dummy)                 10

1 row selected.

SQL>

The results show that 400 milliseconds of CPU time were used on parsing during the session, less than two thirds the amount used in the previous example. As expected, there is only a single statement in the shared pool.

These simple examples clearly show how replacing literals with bind variables can save both memory and CPU, making OLTP applications faster and more scalable. If you are using third-party applications that don't use bind variables you may want to consider setting the CURSOR_SHARING parameter.

For more information see:

• V$SQL
• V$MYSTAT
• CURSOR_SHARING for Existing Applications

REFERENCES

http://www.oracle-base.com

Bind variables - The key to application performance

Overview  If you've been developing applications on Oracle for a while, you've no doubt come across the concept of «Bind Variables». Bind variables are one of those Oracle concepts that experts frequently cite as being key to application performance, but it's often not all that easy to pin down exactly what they are and how you need to alter your programming style to use them.

To understand bind variables, consider an application that generates thousands of SELECT statements against a table; for example: SELECT fname, lname, pcode FROM cust WHERE id = 674; SELECT fname, lname, pcode FROM cust WHERE id = 234; SELECT fname, lname, pcode FROM cust WHERE id = 332;  Each time the query is submitted, Oracle first checks in the shared pool to see whether this statement has been submitted before. If it has, the execution plan that this statement previously used is retrieved, and the SQL is executed. If the statement cannot be found in the shared pool, Oracle has to go through the process of parsing the statement, working out the various execution paths and coming up with an optimal access plan before it can be executed. This process is know as a «hard parse» and for OLTP applications can actually take longer to carry out that the DML instruction itself. When looking for a matching statement in the shared pool, only statements that exactly match the text of the statements are considered; so, if every SQL statement you submit is unique (in that the predicate changes each time, from id = 674 to id=234 and so on) then you'll never get a match, and every statement you submit will need to be hard parsed. Hard parsing is very CPU intensive, and involves obtaining latches on key shared memory areas, which whilst it might not affect a single program running against a small set of data, can bring a multi-user system to it's knees if hundreds of copies of the program are trying to hard parse statements at the same time. The extra bonus with this problem is that contention caused by hard parsing is pretty much immune to measures such as increasing available memory, numbers of processors and so on, as hard parsing statements is one thing Oracle can't do concurrently with many other operations, and it's a problem that often only comes to light when trying to scale up a development system from a single user working on subset of records to many hundreds of users working on a full data set. The way to get Oracle to reuse the execution plans for these statements is to use bind variables. Bind variables are «substituion» variables that are used in place of literals (such as 674, 234, 332) and that have the effect of sending exactly the same SQL to Oracle every time the query is executed. For example, in our application, we would just submit SELECT fname, lname, pcode FROM cust WHERE id = :cust_no;  and this time we would be able to reuse the execution plan every time, reducing the latch activity in the SGA, and therefore the total CPU activity, which has the effect of allowing our application to scale up to many users on a large dataset. Bind Variables in SQL*Plus  In SQL*Plus you can use bind variables as follows: SQL> variable deptno number SQL> exec :deptno := 10 SQL> select * from emp where deptno = :deptno;  What we've done to the SELECT statement now is take the literal value out of it, and replace it with a placeholder (our bind variable), with SQL*Plus passing the value of the bind variable to Oracle when the statement is processed. This bit is fairly straighforward (you declare a bind variable in SQL*Plus, then reference the bind variable in the SELECT statement) Bind Variables in PL/SQL  Taking PL/SQL first of all, the good news is that PL/SQL itself takes care of most of the issues to do with bind variables, to the point where most code that you write already uses bind variables without you knowing. Take, for example, the following bit of PL/SQL: create or replace procedure dsal(p_empno in number) as   begin     update emp     set sal=sal*2     where empno = p_empno;     commit;   end; / Now you might be thinking that you've got to replace the p_empno with a bind variable. However, the good news is that every reference to a PL/SQL variable is in fact a bind variable. Dynamic SQL  In fact, the only time you need to consciously decide to use bind variables when working with PL/SQL is when using Dynamic SQL. Dynamic SQL, allows you to execute a string containing SQL using the EXECUTE IMMEDIATE command. For next example would always require a hard parse when it is submitted: create or replace procedure dsal(p_empno in number) as   begin     execute immediate      'update emp set sal = sal*2 where empno = '||p_empno;   commit;   end; / The way to use bind variables instead is to change the EXECUTE IMMEDIATE command as follows: create or replace procedure dsal(p_empno in number) as   begin     execute immediate      'update emp set      sal = sal*2 where empno = :x' using p_empno;   commit;   end; / And that's all there is to it. One thing to bear in mind, though, is that you can't substitute actual object names (tables, views, columns etc) with bind variables - you can only subsitute literals. If the object name is generated at runtime, you'll still need to string concatenate these parts, and the SQL will only match with those already in the shared pool when the same object name comes up. However, whenever you're using dynamic SQL to build up the predicate part of a statement, use bind variables instead and you'll reduce dramatically the amount of latch contention going on. The Performance Killer  Just to give you a tiny idea of how huge of a difference this can make performance wise, you only need to run a very small test: Here is the Performance Killer .... SQL> alter system flush shared_pool; SQL> set serveroutput on; declare       type rc is ref cursor;       l_rc rc;       l_dummy all_objects.object_name%type;       l_start number default dbms_utility.get_time;   begin       for i in 1 .. 1000       loop           open l_rc for           'select object_name              from all_objects             where object_id = ' || i;           fetch l_rc into l_dummy;           close l_rc;           -- dbms_output.put_line(l_dummy);       end loop;       dbms_output.put_line        (round((dbms_utility.get_time-l_start)/100, 2) ||         ' Seconds...' );   end; / 101.71 Seconds... ... and here is the Performance Winner: declare       type rc is ref cursor;       l_rc rc;       l_dummy all_objects.object_name%type;       l_start number default dbms_utility.get_time;   begin       for i in 1 .. 1000       loop           open l_rc for           'select object_name              from all_objects             where object_id = :x'           using i;           fetch l_rc into l_dummy;           close l_rc;           -- dbms_output.put_line(l_dummy);       end loop;       dbms_output.put_line        (round((dbms_utility.get_time-l_start)/100, 2) ||         ' Seconds...' ); end; / 1.9 Seconds... That is pretty dramatic.  The fact is that not only does this execute much faster (we spent more time PARSING our queries then actually EXECUTING them!) it will let more users use your system simultaneously. Bind Variables in VB, Java and other applications  The next question is though, what about VB, Java and other applications that fire SQL queries against an Oracle database. How do these use bind variables? Do you have to in fact split your SQL into two statements, one to set the bind variable, and one for the statement itself? In fact, the answer to this is actually quite simple. When you put together an SQL statement using Java, or VB, or whatever, you usually use an API for accessing the database; ADO in the case of VB, JDBC in the case of Java. All of these APIs have built-in support for bind variables, and it's just a case of using this support rather than just concatenating a string yourself and submitting it to the database.  For example, Java has PreparedStatement, which allows the use of bind variables, and Statement, which uses the string concatenation approach. If you use the method that supports bind variables, the API itself passes the bind variable value to Oracle at runtime, and you just submit your SQL statement as normal. There's no need to separately pass the bind variable value to Oracle, and actually no additional work on your part. Support for bind variables isn't just limited to Oracle - it's common to other RDBMS platforms such as Microsoft SQL Server, so there's no excuse for not using them just because they might be an Oracle-only feature. Conclusion  Lastly, it's worth bearing in mind that there are some instances where bind variables are probably not appropriate, usually where instead of your query being executed many times a second (as with OLTP systems) your query in fact actually takes several seconds, or minutes, or hours to execute - a situation you get in decision support and data warehousing. In this instance, the time taken to hard parse your query is only a small proportion of the total query execution time, and the benefit of avoiding a hard parse is probably outweighed by the reduction in important information you're making available to the query optimizer - by substituting the actual predicate with a bind variable, you're removing the ability for the optimiser to compare your value with the data distribution in the column, which might make it opt for a full table scan or an index when this isn't appropriate. Oracle 9i helps deal with this using a feature known as bind variable peeking, which allows Oracle to look at the value behind a bind variable to help choose the best execution plan. Another potential drawback with bind variables and data warehousing queries is that the use of bind variables disallows the potential for star transformations, taking away this powerful option for efficiently joining fact and dimension tables in a star schema. REFERENCES http://www.orafaq.net