Inside the Bind Variables

Inside the Bind Variables

I’m sure that you know the phrase: “We will not reinvent the wheel”. Oracle also follows this maxim with introduction of Bind Variables. These are variables whose purpose is to allow the developer to write SQL queries that share their property. This note will explain the principle of Bind Variable and its usefulness in writing OLTP applications.

1.      SQL Statement Processing Phases

A good understanding of SQL processing is helpful for understanding the SQL statistics. In SQL statement processing, there are four phases:

•  Parsing
•  Binding
•  Executing
•  Fetching

Before understanding the use of bind variables, it should be very important to understand how the Oracle Engine works:

1.1.    Parsing Phase

When and application issues a SQL statement, it makes a parse call to the Oracle Database Server. During this phase the Oracle database Server:

-          Checks the statement for syntactic and semantic validity

-          Determines whether the process issuing the statement has privileges to run it

-          Searches for shareable match of the statement in the share pool

-          Allocates a private SQL area for the statement

Syntax Check:

Semantic Check:

There are two types of parse operations:

-          Soft Parsing: A SQL statement is submitted, and match is found in the shared pool. The match can be result of a previous execution by another user. The SQL statement is shared, which is good for performance. However, soft parses still require syntax and security checking, which consumes resources.

-          Hard Parsing: A SQL statement is submitted for the first time, and no shareable match is found in the shared pool. Hard parses are the most resource-intensive and unscalable, because they perform all the operations involved in a parse,

When bind variables are used properly, more soft parses are possible, thereby reducing hard parses and keeping parses statement in the library cache for a longer period. Ideally, the SQL statement gets one hard parse the first time that is submitted, and on soft parse for each additional session that uses the statement. This depends on sufficient memory both in the session cache, and the shared pool to retain the cursor information.

1.2.    Binding Phase

The binding phase is the replacement of variables input values in the statement, in places indicated by bind placeholders.

During the bind phase, the Oracle Database server:

-          Checks the statement for references to bind variables

-          Assigns or reassigns a value to each variable

The input values are submitted into the SQL statement in place of the bind placeholders. Theses placeholders have the form “:number”, or “%bindop(:number[, parm]…)” where the number indicates which input value is to be substituted, and the bindop and parm strings indicate what meta-SQL binding function is to be performed.

When bind variables are used in a statement, the optimizer assumes that cursor sharing in intended and that different invocations should use the same execution plan. This helps performance by reducing hard parse.

1.3.    Execution Phase

The execution phase:

-          Executes the SQL statement

-          Performs necessary I/O and sorts for database manipulation language (DML) statements

Multiple users can share the same execution plan. The Oracle database performs physical reads or logical reads/writes for DML statements and sorts the data when needed.

Physical reads are disk reads; logical reads are blocks already in memory in the buffer cache. Physical reads use more resources and time because they require I/O from disk.

1.4.    Fetch Phase

The Oracle database server retrieves rows for a SELECT statement during the fetch phase. Each fetch typically retrieves multiple rows, using an array fetch. Array fetches can improve performance by reducing network round trips. Each Oracle toll offers its own ways of influencing the array size.

2.      Binds variables

If you have been developing applications, on Oracle for a while, you have no doubt come across the concept of “Bind Variable”. Bind variable are one of those Oracle concepts that is key of application performance. To understand bind variable, consider an application that generates thousands of SELECT statements against a table; for example:

SELECT firtname, lastname, salary FROM employees where emp_id= 102;
SELECT firtname, lastname, salary FROM employees where emp_id= 110;

SELECT firtname, lastname, salary FROM employees where emp_id= 120;

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 known 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 emp_id = 102 to emp_id=110 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 its 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 «substitution» variables that are used in place of literals (such as 102, 110, 120) 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 firtname, lastname, salary FROM employees where emp_id = :emp_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.

2.1.    Bind Variables in SQL*Plus

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

SQL> variable deptno number
SQL> exec: deptno:= 15
SQL> select * from employees 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 straightforward (you declare a bind variable in SQL*Plus, then reference the bind variable in the SELECT statement)

2.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 updsal(p_empno in number)
as
  begin
    update employees
    set salary=salary*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.

2.3.    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 updsal(p_empno in number)
as
  begin
    execute immediate
     'update emp set salaray = salary*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 updsal(p_empno in number)
as
  begin
    execute immediate
     'update employees set
     salary = salary*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. 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.