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= 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;
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;
/
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;
/
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;
/
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.
