Bind vs Literal variables in SQL execution

• By -ndmsoft

Bind vs Literal variables in SQL execution

• Posted on 4th March 2024
• Posted in SQL

When it comes to best practices, don’t just believe everything you hear. You need to understand what you are implementing and why you implement it in a certain way.

In order to make a best practice part of your work ethic ,you need to know two things:

1. The why   – understand the reason/logic behind it
2. The effect – see what happens when you use it  vs  (more important) what happens when you don’t.

This is the most effective way to asimilate new information I know of.

Let’s take one of the most important best practice when developping applications of top of Oracle database:

Always use bind variables in your sql statemens

select name from customers when id =: id; 

vs

select name from customers when id = 1011;

The Why

• When using bind variables  – the database will resuse the same execution plan created regardless of the value received in the parameter. This is called a library cache hit and will generate a soft parse.
• When using literal variables –  the database will generate a new execution plan for each SQL. This is called a library cache miss and will generate a hard parse – quite expensive CPU operation.
• also using bind variables reduces the risk of SQL injection attacks. (not covered in this post)

The Effect

Let’s test it with a simple example and analyze the results from the performance and resource consumption point of view.

We’ll select the NAME of 100 CUSTOMERS based on the ID in both scenarious and compare the results.

The code is written is PL/SQL but it applies to any application sending SQLs to the database.

 

set serveroutput on 
declare 
 
  v_name varchar2(100);
  v_nr_rows number := 0;
 
begin 

 for rec in ( select id,name 
              from customers 
             )
 loop
 
     -- use a bind variable 
     execute immediate 'select name from customers where id = :id' into v_name using rec.id;
     
     -- use a literal variable 
     --execute immediate 'select name from customers where id = '||rec.id into v_name;
     
     v_nr_rows := v_nr_rows +1;
 
 end loop;
 
     dbms_output.put_line ('Rows processed '||v_nr_rows);

end;

Oracle displays the information about the SQLs cached and executed in the library cache in the v$sqlarea.

The details:

select  sql_id 
       ,sql_text
       ,executions
       ,cpu_time
       ,case when sql_text like '%:%' then 'BIND'
             else 'LITERAL'
        end as sql_type     
from v$sqlarea 
where sql_text like 'select name from customers where id%'
order by 4 desc;

… 100 ROWS

We can clearly see that the bind SQL is stored 1 time and executed 100 times where as there is a separate SQL statement stored for each execution of the non-bind version.

The summary:

The average execution time of SQL bind is 2x time smaller that the average for the non-bind SQL.

To put things in perspective for all 100 executions:

Conclusions:

• using bind variables consumes 2x time less CPU vs literal variables. Database response time is 2xtimes faster. In any cloud environment this translates in a smaller bill at the end of the month. Let that sink in: poor written SQL always translates in higher cloud bills.
• But there is more and this effect can be way worse to the overall database performace. The SQL library cache memory is limited and the SQLs are stored using LRU (Last Recently Used) algorithm. This means that any new SQL parsed and stored in the cache replaces a older one. So to the CPU cost of hard parse-ing the non-bind SQLs we need to add the CPU cost of all the replaced SQLs that would need to be hard parse again. This indirect effect is hard to be estimated.