Bind variables

Bind Variables are important because one of the design feature in Oracle is ability to reuse the optimizer plans whenever possible. whenever you submit any SQL or PL/SQL blocks to database, Oracle will look if the query is already parsed and optimized (Shared pool) if it finds then the execution plan can be reused. If it cannot find then Oracle Hard parses the query, performs security checks and optimizes the plan and so on..This not only consumes CPU but tends to lock the portion of the library cache for relatively long periods. The more people hard parsing the query increases then longer the wait.

Here is the demonstration about Not using Bind variables in multiuser environment.

scott@9iR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

scott@9iR2> SELECT tablespace_name, segment_space_management
  2  FROM dba_tablespaces
  3  where tablespace_name ='DATA1';

TABLESPACE_NAME                SEGMEN
------------------------------ ------
DATA1                          MANUAL

Elapsed: 00:00:00.04

create table t(x number,y number,z number) tablespace  DATA1 storage(freelists 5);

create global temporary table temp_sess
on commit preserve rows
as
select *
from v$session_event where 1 = 0;

scott@9iR2> insert into temp_sess
  2  select * from v$session_event
  3  where sid = (select sid from v$mystat where rownum = 1);

12 rows created.

Elapsed: 00:00:00.06

scott@9iR2> declare
  2     l_value number;
  3  begin
  4     for i in 1..100000
  5     loop
  6             l_value := dbms_random.value;
  7             execute immediate ' insert into t (x,y,z) values ('||l_value||','||l_value||','||l_value||')';
  8     end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:52.95

scott@9iR2> SELECT aft.event,(nvl(aft.total_waits,0) - nvl(bfr.total_waits,0)) as total_waits
  2               (nvl(aft.time_waited,0) - nvl(bfr.time_waited,0)) as time_waited
  3  FROM V$SESSION_EVENT aft,temp_sess bfr
  4  WHERE aft.SID = (select sid from v$mystat where rownum = 1)
  5  and   aft.event = bfr.event(+)
  6  and   abs(aft.total_waits - nvl(bfr.total_waits,0)) > 0 ;

EVENT                                         TOTAL_WAITS     TIME_WAITED
---------------------------------------------------------------- ----------- -----------
latch free                                        226                          30
enqueue                                             3                            5
free buffer waits                                 1                           67
buffer busy waits                               33                           0
log file switch completion                   2                           6
db file sequential read                        8                           4
row cache lock                                 15                          31
SQL*Net message to client               8                           0
SQL*Net message from client           7                           11895

9 rows selected.

The Oracle wait interface are documented in Oracle 9i Reference manual.
This latch free is actually a latch in shared sql area.So how this can be reduced. one way by using Bind variables.

Now using Bind variables Latch Free has gone down.

scott@9iR2> declare
  2     l_value number;
  3  begin
  4     for i in 1..100000
  5     loop
  6             l_value := dbms_random.value;
  7             execute immediate ' insert into t (x,y,z) values (:x,:y,:z) ' using l_value,l_value,l_value;
  8     end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:19.29
scott@9iR2> SELECT aft.event,(nvl(aft.total_waits,0) - nvl(bfr.total_waits,0)) as total_waits,
  2               (nvl(aft.time_waited,0) - nvl(bfr.time_waited,0)) as time_waited
  3  FROM V$SESSION_EVENT aft,temp_sess bfr
  4  WHERE aft.SID = (select sid from v$mystat where rownum = 1)
  5  and   aft.event = bfr.event(+)
  6  and   abs(aft.total_waits - nvl(bfr.total_waits,0)) > 0 ;

EVENT                                        TOTAL_WAITS       TIME_WAITED
---------------------------------------------------------------------------
latch free                                          123                           8
buffer busy waits                                1000                         350
log file switch completion                    3                               9
db file sequential read                         677                         373
SQL*Net message to client                5                            0
SQL*Net message from client            4                            4538

6 rows selected.

Elapsed: 00:00:00.04

Another way to drop this considerably is static SQL's in Pl/SQL block.

scott@9iR2> declare
  2     l_value number;
  3  begin
  4     for i in 1..100000
  5     loop
  6             l_value := dbms_random.value;
  7             insert into t (x,y,z)  values (l_value,l_value,l_value);
  8     end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.55
scott@9iR2> SELECT aft.event,(nvl(aft.total_waits,0) - nvl(bfr.total_waits,0)) as total_waits,
  2               (nvl(aft.time_waited,0) - nvl(bfr.time_waited,0)) as time_waited
  3  FROM V$SESSION_EVENT aft,temp_sess bfr
  4  WHERE aft.SID = (select sid from v$mystat where rownum = 1)
  5  and   aft.event = bfr.event(+)
  6  and   abs(aft.total_waits - nvl(bfr.total_waits,0)) > 0 ;

EVENT                                      TOTAL_WAITS  TIME_WAITED
-----------------------------------------------------------------------
enqueue                                        9                           0
free buffer waits                            5                          70
write complete waits                     1                          20
buffer busy waits                           3                           0
log buffer space                             7                          64
log file switch completion               2                          18
db file sequential read                    2                           1
SQL*Net message to client           5                           0
SQL*Net message from client       4                        3020

9 rows selected.

Now when executing static sql's in Pl./Sql block ( PL/SQL will cache my cursor for me – that is one of the major advantages of PL/SQL. This insert will typically be soft parsed once per session for me if it was in a
procedure ) . The latching is totally gone.

The above demonstrates that using Bind variable is crucial to performance.