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
If Oracle is able to find the same statement in shared pool .then Oracle picks the execution plan from shared pool
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
- Bind variable reduces the Oracle latches or Oracle locks required by the application when parsing queries
- Bind variable promotes Soft parsing and Soft parsing does less CPU
- it avoids SQL INJECTION