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.
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)
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.
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
This is one of the post in Performance Tuning Fundamentals Series. Click on below links to read more posts from the series:
[catlist name="Performance Tuning Series"]
[Post Views: 138]
You can connect with me on LinkedIn.
Latest posts by Brijesh Gogia (see all)
- Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC) - April 1, 2018
- Physical and Logical Block Corruption in Oracle Database - March 10, 2018
- Performance Tuning Basics 15 : AWR Report Analysis - February 6, 2018