Performance Tuning Basics 4 : Bind Variables

 Bind variables are often known as one of the key feature for better SQL query performance. Bind variables as per Oracle documentation is a placeholder in a SQL statement that must be replaced with a valid value or value address for the statement to execute successfully. By using bind variables, you can write a SQL statement that accepts inputs or parameters at run time.

You can think of SQL query as a kind of “function” in any programming language and bind variables as “values” that you pass to the function.

Example:

Select * from EMP where EMP_ID=1;
Select * from EMP where EMP_ID=:a;

First statement uses a literal value (1) to run the query while the second SQL statement uses bind variable (:a) to run the SQL statement. The value of (:a) will be provide to Oracle at run time.

Having bind variable defined in the SQL query instead of literal values (which can be different every time) will make sure that Oracle will create only one Parent Cursor for the SQL statement. Oracle look for exact text match for the SQL statement to see if it is already present in the shared pool and having a bind variable instead of literal value will save a costly hard parse every time same SQL is executed.

Bind variables are specially important in OLTP kind of environments as using bind variables enables soft parsing, which means that less processing time is spent on choosing an optimized execution plan.

You create bind variables in SQL*Plus with the VARIABLE command. Example:

VARIABLE mybindVariable VARCHAR2(10)

 

ADVANTAGES

So, if we have to list down key benefits of using bind variables then these will be:

1. Better Shared Pool Utilization: Oracle Shared Pool has to hold only one statement rather than a potentially very high number.

2. No Hard Parsing so Better performance: No hard parsing required for SQL statements that only differ in the values.

3. Reduced “library cache” latch contention: Bind variables helps in avoiding performance problems due to library cache latch contention which happens every time a hard parse is required.

 

DISADVANTAGES

Now coming to disadvantages of using bind variables. Note that in many cases bind variables will prove excellent for improving the performance of the database but at time it may produce negative results. Bind variables can reduce the information to calculate optimal access path for (Cost Based Optimizer) CBO

Due to the bind value utilization the CBO may not be able to determine the real selectivity and produce bad execution plans. it may go for full table scan where as index can be used. At times, Oracle CBO needs the literal value that SQL is going to use to create better execution plan. SInce bind variable have “hide” the literal value so CBO may create a sub-optimal plan. To counter this situation, Oracle tried to help CBO by allowing it to “peek” at the value of bind variable at the time of creating execution plan. This brings us to another term “Bind Variable Peeking”.

 

Bind Variable Peeking:  It is an Oracle method in which before generating an execution plan, the CBO peeks at the values of bind variables and uses them as literals for generating better execution plan. The loop hole with this approach was that generated execution plan depends on the values provided by the first execution. Note that depending on value provided in where condition at time Full Table scan is required while at other time Index usage is required. Since bind variable peeking was based on the values provided by the first execution so this approach also produced bad execution plan at times. To counter this, Oracle added little more intelligence into the software with “Adaptive Cursor Sharing”.

 

Adaptive Cursor Sharing: This feature was available Oracle 11g onward. In this approach Oracle CBO do not just blindly use cursor even if it has sub-optimal plan (like it used to happen when using bind variable peeking) instead it recognizes when the re-use of an already available cursor will lead to inefficient SQL execution. If CBO using Adaptive Cursor Sharing finds that existing plan will not prove efficient then it will generate yet another child cursor with different execution plan.

 

 To conclude Bind Variables usage can prove really helpful in OLTP kind of environment in improving performance but you have to really careful in using bind variables as sometimes it may not be the silver bullet for your performance issue and even can deteriorate the performance.