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