Literals v/s Bind Variables as an Input / Output parameter to a Procedure

My previous blog was on Library Cache Latch Contention and in this blog too, I am going to discuss something that has an Impact on Latches, especially Library Cache Latches. During my investigation on a Severe Library Cache Latch Contention, I came across many procedures that are executed through out the day and surprisingly use Literals as an Input Parameters. There were around 30 to 50 such procedures and the executions of each of these were more than 7000 (the top most being 9000). With this finding, it was quite obvious that the recommendation would be to make use of Bind Variables and this is what we recommended. One of the curious Developer came back to me with a query

"We have checked for the performance of a procedure after passing a Literal and Bind Variables, as an Input Variables, and the performance of both the approach was found to be same. Therefore, it seems for procedures, Literals and Bind does not make any difference."

Further drilling revealed that the developer was talking about testing of performance in a test environment and on single or few users. On a Single User system, it is obvious that the performance impact will not be known, but the issue arises in a Concurrent Environment when multiple user execute the same code with different values and this impacts the Scalability.

Just to demonstrate(though I was also working on a single user system a local database on my laptop), I created a test case with the amount of latching and this would be enough to predict the performance impact in a concurrent enviroment.

The test case was :

1. Created a simple procedure, with an Input / Output Parameter

2. Created 2 Global Temporary Tables to record the GETS of each of the latches. These tables will be used to get the difference after the test is complete.

3. Execute the procedure 5 times with different literal values.

4. Get the Latching activity for interested latches

5. Execute the procedure 5 times with different values into a bind variables.

6. Get the Latching activity for interested latches

After the completion of the test case, it was clearly evident that bind variables overcome Literals when it comes to concurrency and this is applicable for execution of any piece of code that is subject to parse.

Finally at the end of this test, I checked the sql_id's, loads, executions of each of these executed procedures with the child latch number they are protected by. On my 2 CPU laptop and 3 Child Latches, the procedures with Literal were protected by multiple latches, whereas, with Bind, it was protected by only 1 latch, therefore, in a concurrent enviroment it will lead to less contention on library cache latches.

Here goes the test case :

.
.
Setup
.
.
create or replace procedure test_bind(p_in in number, p_out out varchar2)
is
begin
 if p_in <=10 then    p_out:='Less Than 10';   end if;  if p_in >10 and p_in<=100 then    p_out:='Between 10 and 100';   end if;  if p_in >100 then
  p_out:='Greater than 100';
 end if;
dbms_output.put_line(p_out);
end;
/
.
create global temporary table latch_stats_start as
select name, gets from v$latch;
.
.
create global temporary table latch_stats_end as
select name, gets from v$latch;
.
-----------------------------------------
.
Test with Literals (5 Executions)
.
insert into latch_stats_start select name, gets from v$latch;
.
variable b2 varchar2(20);
exec test_bind(20,:b2);
.
exec test_bind(40,:b2);
.
exec test_bind(60,:b2);
.
exec test_bind(80,:b2);
.
exec test_bind(100,:b2);
.
insert into latch_stats_end select name, gets from v$latch;
.
column name for a30
set lines 200
set pages 1000
select * from (
select a.name, b.gets end_gets, a.gets Start_gets, b.gets-a.gets
from  latch_stats_start a,
 latch_stats_end b
where  b.name = a.name
order by 4 desc)
where rownum<=20; . . . ----------------------------------------- . Test with Bind Variable (5 Executions)
.
insert into latch_stats_start select name, gets from v$latch;
.
variable b1 number;
variable b2 varchar2(20);
exec :b1:=20;
exec test_bind(:b1,:b2);
.
.
exec :b1:=40;
exec test_bind(:b1,:b2);
.
exec :b1:=60;
exec test_bind(:b1,:b2);
.
exec :b1:=80;
exec test_bind(:b1,:b2);
.
exec :b1:=100;
exec test_bind(:b1,:b2);
.
insert into latch_stats_end select name, gets from v$latch;
.
column name for a30
set lines 200
set pages 1000
select * from (
select a.name, b.gets end_gets, a.gets Start_gets, b.gets-a.gets
from  latch_stats_start a,
 latch_stats_end b
where  b.name = a.name
order by 4 desc)
where rownum<=20;  ----------------------------------------- . Latch Activity
.
NAME                        LITERALS    BIND
---------------------  ------------- -------
library cache                   2176  605
library cache pin               1306  318
library cache lock               828  285
shared pool                      684  328
row cache objects                521  151
.
.
Child Latch Details
.
select sql_id, sql_text, loads, executions, child_latch
from v$sqlarea
where lower(sql_text) like 'begin test_bind%'

SQL_ID        SQL_TEXT                                LOADS EXECUTIONS CHILD_LATCH
------------- ---------------------------------- ---------- ---------- -----------
2223svsqfnu4d BEGIN test_bind(40,:b2); END;               1          1           1
7ukjn7sr0j12g BEGIN test_bind(20,:b2); END;               1          1           2
dw8cdd6zcjvhz BEGIN test_bind(80,:b2); END;               1          1           1
bcmahtyp069uw BEGIN test_bind(60,:b2); END;               1          1           3
10rcans35b9a0 BEGIN test_bind(100,:b2); END;              1          1           1
7rg64r0c3j994 BEGIN test_bind(:b1,:b2); END;              1          5           3

Last SQL_ID is with Bind. 1 Load and Multiple Executions.
.

.
.