Improve the Performance using Bind Variable in Oracle

What is a Bind Variable?

-It is a placeholder for a value .It is replaced by actual value when the SQL statement is executed
-In PL/SQL it is called a local or global variable.

DECLARE
l_empl_id DEPT.emp_id%TYPE;
BEGIN
select first_name  from DEPT  where id = l_empl_id;
END;

-in SQL*Plus it is a variable

variable a number
BEGIN
:a = 1000;
dbms_output.put_line(a);
END;
/
select * from emp where dept_no=:a;
or
variable a number
exec :a :=1000;
select * from emp where dept_no=:a;

• Sql without binds

SELECT id FROM users WHERE user_id=875875;
SELECT id FROM users  WHERE user_id=86986;

'select * from emp where dept_no='||i

• Sql with  binds

SELECT id FROM users WHERE user_id==:a_user_id;

• When sql statement with binds are used, It can be shared with multiple user session and Soft parse is just good to execute the statement. Hard parsing each statement is quite a pain and severely impact the performance of the oracle database.

-Bind Variables helps application to send exactly the same SQL to Oracle every time. Bind variable can highly improve performance by avoiding hard parsing. It also helps Oracle to lower Shared Pool Memory consumption and also helpful to avoid SQL injection.

What is Hard Parsing and Soft Parsing

Soft parsing

If Oracle is able to find the same statement in shared pool .then Oracle picks the execution plan from shared pool

Hard Parsing

If the SQL submitted is not found in Oracle Shared Pool, Oracle has to do the hard parsing which is SQL statement needs to be checked for syntax and semantics errors, and generating various execution plans to find and select optimal one. Hard parsing is very CPU intensive.

Example of Performance Test

First we create oracle table with 10,000 rows

create table EXP_T as select level EXP from dual connect by level <= 10000;

Create index on the column

create index exmpl_indx on EXP_T(EXP);

Now let’s check out how bind variable impact the performance

PLSQL with out Bind

SQL> set timing on
SQL> declare
test number;
i number;
begin
i := 1;
while i<=1000
loop
execute immediate 'select * from EXP_T where EXP=' || i
into test;
i := i+1;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:17:00

SQL with bind variable

SQL> set timing on
SQL> declare
test number;
i number;
begin
i := 1;
while i<=1000
loop
execute immediate 'select * from EXP_T where EXP=:i'
into test
using i;
i := i+1;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:05.03

we can code with binds which executes faster that code with out binds

Advantages of Binds

1. Bind variable reduces the Oracle latches or Oracle locks required by the application when parsing queries
2.  Bind variable promotes Soft parsing and Soft parsing does less CPU
3. it avoids SQL INJECTION

Related Articles

Oracle Performance tuning Glossary
Explain Plan in Oracle
Optimizer hints
Optimizer Mode
Gathering Statistics in Release 11i and R12
Oracle Documentation on Binds