Bind variables - The key to application performance

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 it 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 known as a hard parse.

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.

The way to get Oracle to reuse the execution plans for these statements is to use bind variables. Bind variables are substitution 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.

1. Bind Variables in SQL*Plus

In SQL*Plus you can use bind variables as follows:

SQL> variable deptno numberSQL> exec :deptno := 10SQL> 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.

2. 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 update_sal(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.

3. Bind Variable in 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 update_sal(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 update_sal(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 substitute literals.

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:

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 .. 5000
   LOOP
      OPEN l_rc

FOR 'SELECT object_name
             FROM all_objects
            WHERE object_id = ' || i;
      FETCH l_rc INTO l_dummy;
      CLOSE l_rc;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2) ||' Seconds...' );
END;
/
166.62 Seconds...

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 .. 5000
   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;
   END LOOP;
   DBMS_OUTPUT.PUT_LINE(ROUND((DBMS_UTILITY.GET_TIME - l_start)/100, 2) ||' Seconds...' );
END;
/

0.72 Seconds...

Improvement in Performance is pretty dramatic (230 times).  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.