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:
- Performance Tuning Basics 1 : Selectivity and Cardinality
- Performance Tuning Basics 2 : Parsing
- Performance Tuning Basics 3 : Parent and Child Cursors
- Performance Tuning Basics 4 : Bind Variables
- Performance Tuning Basics 5 : Trace and TKPROF – Part 1: Trace
- Performance Tuning Basics 6 : Trace and TKPROF – Part 2: Generating TKPROF
- Performance Tuning Basics 7 : Trace and TKPROF – Part 3: Analyzing TKPROF Files
- Performance Tuning Basics 8 : Trace File Analyzer (TRCA)
- Performance Tuning Basics 9 : Optimizer Mode
- Performance Tuning Basics 10 : Histograms
- Performance Tuning Basics 11 : Steps to analyze a performance problem
- Performance Tuning Basics 12 : Dynamic Performance Views
- Performance Tuning Basics 13 : Automatic Workload Repository (AWR) Basics
- Performance Tuning Basics 14 : Active Sessions History (ASH) Basics
- Performance Tuning Basics 15 : AWR Report Analysis
- Performance Tuning Basics 16 : Using SQL Tuning Health-Check Script (SQLHC)
[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