A bind variable is simply a mechanism through which you can create a placeholder for a literal (constant) value within an SQL statement. For example, to retrieve the record for employee id is 198, 199, 200.... we can write SQL like:
SELECT first_name, last_name, email FROM employees WHERE employee_id = 199;
SELECT first_name, last_name, email FROM employees WHERE employee_id = 200;
SELECT first_name, last_name, email FROM employees WHERE employee_id = 201;
To use bind variable, we can use following instead:
SQL> variable emp_id number
SQL> exec :emp_id := 199
PL/SQL procedure successfully completed.
SQL> select first_name, last_name, email from employees where employee_id = :emp_id;
FIRST_NAME LAST_NAME EMAIL
-------------------- ------------------------- -------------------------
Douglas Grant DGRANT
SQL> exec :emp_id := 200
PL/SQL procedure successfully completed.
SQL> select first_name, last_name, email from employees where employee_id = :emp_id;
FIRST_NAME LAST_NAME EMAIL
-------------------- ------------------------- -------------------------
Jennifer Whalen JWHALEN
SQL>
Why use bind variables?
Every time we submit a brand new sql statement to the database , it must be parsed, qualified, security checked, optimized, and so on. All of these will consume a large amount of CPU. Hence, the fewer new SQL statements that are presented to the system, the better your system will scale and perform. To minimize the number of different SQL statements that are parsed by the database, we must make the use of bind variables. Let’s have a look at the next example. Here I created 2 procedures, proc1 is using bind variables and proc2 is not using bind variable.
drop table demo;
/
create table demo(x int);
/
create or replace procedure proc1 as
begin
for i in 1 .. 100000 loop
execute immediate 'insert into demo values (:x)'
using i;
end loop;
end;
/
create or replace procedure proc2 as
begin
for i in 1 .. 100000 loop
execute immediate 'insert into demo values ( ' || i || ')';
end loop;
end;
/
Now we use runstats package to compare the cost of above two procedures. For details on setting up runstats, please see my last post.
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as appuser
SQL> set serveroutput on size 100000;
SQL> exec runstats_pkg.rs_start
PL/SQL procedure successfully completed
SQL> exec proc1
PL/SQL procedure successfully completed
SQL> exec runstats_pkg.rs_middle
PL/SQL procedure successfully completed
SQL> exec proc2
PL/SQL procedure successfully completed
SQL> exec runstats_pkg.rs_stop
Run1 ran in 1637 hsecs
Run2 ran in 3180 hsecs
run 1 ran in 51.48% of the time
Name Run1 Run2 Diff
STAT...parse time elapsed 2 1,444 1,442
STAT...parse time cpu 1 1,481 1,480
STAT...Elapsed Time 1,638 3,181 1,543
STAT...DB time 261 1,851 1,590
STAT...recursive cpu usage 218 1,836 1,618
STAT...CPU used when call star 242 1,873 1,631
STAT...CPU used by this sessio 241 1,875 1,634
STAT...bytes received via SQL* 4,428 2,403 -2,025
LATCH.shared pool sim alloc 0 2,109 2,109
LATCH.messages 535 3,204 2,669
STAT...redo size 23,705,976 23,701,772 -4,204
LATCH.simulator lru latch 7,476 107,418 99,942
STAT...calls to get snapshot s 139 100,130 99,991
STAT...recursive calls 101,212 201,203 99,991
STAT...enqueue requests 56 100,049 99,993
STAT...enqueue releases 56 100,049 99,993
STAT...parse count (total) 53 100,048 99,995
STAT...parse count (hard) 10 100,007 99,997
LATCH.simulator hash latch 7,671 107,720 100,049
STAT...consistent gets 685 100,802 100,117
STAT...consistent gets from ca 685 100,802 100,117
STAT...db block gets 102,757 302,556 199,799
STAT...db block gets from cach 102,757 302,556 199,799
LATCH.enqueues 376 200,713 200,337
LATCH.enqueue hash chains 424 200,844 200,420
STAT...session logical reads 103,442 403,358 299,916
STAT...physical read bytes 32,768 352,256 319,488
STAT...physical read total byt 360,448 679,936 319,488
LATCH.kks stats 483 552,311 551,828
STAT...session pga memory 131,072 -458,752 -589,824
LATCH.library cache lock 456 600,524 600,068
LATCH.library cache pin 455 600,540 600,085
LATCH.cache buffers chains 511,275 1,111,634 600,359
LATCH.row cache objects 871 1,201,081 1,200,210
LATCH.shared pool simulator 60 1,792,124 1,792,064
LATCH.library cache 1,238 2,569,396 2,568,158
LATCH.shared pool 1,181 3,413,797 3,412,616
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
545,818 12,481,378 11,935,560 4.37%
PL/SQL procedure successfully completed
SQL>
Now, you can see the proc1 is the clear winner, which took significantly less time to run also with much less latches.
When to use bind variables?
Do we need to use bind variables explicitly in our PL/SQL store procedures? The answer may surprise you? Most of time we don’t need to, because every reference to a PL/SQL variable is in fact a bind variable. This is just how PL/SQL works. Want a prove? Look at the next example:
create or replace procedure find_emp(p_empno in number) as
type rc is ref cursor;
l_cursor rc;
begin
open l_cursor for
select * from emp where empno = p_empno;
end;
/
Execute the procedure and trace the execution plan using TKPROF.
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Feb 8 23:53:32 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> alter session set timed_statistics = true;
Session altered.
SQL> alter session set sql_trace = true;
Session altered.
SQL> exec find_emp(7839);
PL/SQL procedure successfully completed.
SQL> SELECT pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
2 '_ora_' || p.spid || '.trc' AS trace_file
3 FROM v$session s,
4 v$process p,
5 v$parameter pa
6 WHERE pa.name = 'user_dump_dest'
7 AND s.paddr = p.addr
8 AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
TRACE_FILE
--------------------------------------------------------------------------------
D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP/orcl_ora_4328.trc
SQL> alter session set sql_trace = false;
Session altered.
SQL>
Use TKPFRO generate SQL trace report: Please refer here for detail set up.
D:\> tkprof D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP/orcl_ora_4328.trc d:\trcout.txt
Tkprof shows us:
SELECT *
FROM
EMP WHERE EMPNO = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.02 0 1 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.02 0 1 0 0
ALL plsql variable references were bound. However it is not always the case, the only time we need to specifically use bind variables is when you're putting together dynamic SQL. For example, when we put DML statement after ‘execute immediate’ in plsql program.
-- Not using Bind Variable. Hard parse required when exec the procedure each time
create or replace procedure no_bv(p_empno number) as
begin
execute immediate 'update emp set sal = sal*2 where empno = ' || p_empno;
end;
/
-- Bind Variable, no hard parse required
create or replace procedure bv(p_empno number) as
begin
execute immediate 'update emp set sal = sal*2 where empno = :x '
using p_empno;
end;
/
When not to use bind variables?
Every rule has its exception. In performance tuning world, nothing is absolute. In a data warehouse environment, it’s normal you would have a bunch of SQL queries which can take seconds or minutes or even hours to run, because most of data warehouse jobs are running over night, so nobody is actually waiting for the result of each query, users are waiting for all queries to finish getting the answer. In this scenario, the overhead of parsing time is a tiny propotion of the overall execution time. In data warehouse system, the goal is to get the best query optimization plan possible to reduce the runtime, not to execute as many queries as possible like we do in OLTP system. In this case, not to use bind variables could allow CBO to see actual literal so that it can come up with a optimal plan for your query.
