Bind Variables

Part of tuning any application developed means making your code more secure and friendly.

This should mean you use bind variables. This should make your code more secure and resilient and potentially better performance wise.

At an application level it is preferable to have one hard parse of a SQL statement and many soft parses of the same, and this reduces contention and CPU load. Bind variables also have an added benefit in eliminating the effects of SQL Injection. SQL Injection will be discussed in a later posting.

The first time a query is run it is hard parsed. It is checked for syntax, semantics, validity and finally a plan is generated, a very time consuming job. The next time this SQL statement is encountered a soft parse ensues, a more simple 3 stage execution:

1. syntax check
2. semantics check
3. go to shared pool and look if the SQL is there

Executing a SQL statement by concatenating the variables to the SQL string results in hard parses for each value of the variable. Lots of serialisation, contention and less scalability. Rewriting code to use bind variables is not time consuming or hard. A PL/SQL block of code might contain the following:

....
execute immediate 'select * from customer 
                    where first_name = :first_name' 
  using l_fname 
   into lr_customer;
....

Upon encountering this statement for the first time a hard parse is performed and all subsequent times a soft parse for all values of l_fname.

This will give us our goal of having a high soft to hard parse ratio. There is so much more to bind variables that I'm sure I'll be revisiting this subject again and again.

Thanks for the inspiration for this subject Tom.