Bind Variables are typically considered as one of the major aspects of enhanced SQL query performance. According to Oracle documentation, they serve as a placeholder in a SQL query, getting replaced by a particular value that helps in statement execution.
The use of these variables enables users to create statements that can receive time-running parameters or inputs. One may think of bind variables as “value” given to the SQL query that acts as any function in programming languages. Here, we will talk more about them as well as their advantages and disadvantages in Oracle database and SQL.
Bind Variables: Examples of Their Uses
Consider the following statements in SQL –
Select * from Staff where S_No = 1 ;
Select * from Staff where S_No = :a ;
In the first query, a proper value (1) is applied to operate the query, whereas, in the next query, we have used a bind variable (:a) to operate the statement. This bind variable is given to Oracle when the query is run.
Defining a bind variable in the SQL statements in the place of literal values ensures that a single Parent Cursor is utilized by Oracle for the query. This helps improve database performance because Oracle searches for precise text matches for the query to check whether it already exists in the shared pool. Using a bind variable rather than a literal value saves an expensive hard parse for each run of that query.
Bind variables prove especially useful in OLTP-type environments because their use facilitates soft parsing. In other words, it takes less processing time to re-generate execution plan.
How Bind Variables Help Improve Database Performance
Given below are some advantages of using bind variables:
- Optimal Use of Shared Pool – The Shared Pool in Oracle Database needs to hold a single query instead of possibly numerous queries, thanks to bind variables.
- Improved Performance Due to Zero Hard Parsing – There is no need for hard parsing because SQL queries only diverge in terms of values.
- Decreased “library cache” Latch Contention – Since library cache latch contention is needed during a hard parse, its requirement reduces when bind variables are used.
Shortcomings of Bind Variables
The disadvantages of using bind variables are few. Although bind variables prove excellent if you want to improve Oracle database performance, there are instances where their use can negatively impact results:
- They can decrease the flow of information needed to compute the best access path for CBO (Cost Based Optimizer). The CBO, in turn, may fail to identify the correct selectivity and create insufficient bad execution plans, opting for a complete table scan instead of using indexes.
- Sometimes, the CBO requires the literal value to be used by SQL in order to build a robust execution plan. With bind variables, the literal value gets “hidden”, so the CBO is likely to create a subpar plan.
To overcome this issue, Oracle has tried to provide further assistance to the CBO by enabling it to take a look at the bind variable’s value during execution plan creation, which is known as “Bind Variable Peeking”.
The use of bind variables is extremely useful in Oracle database performance, especially when it comes to OLTP environments. However, as a user, you need to be careful while using bind variables. . It is recommended to use bind variables for short runtime SQL, but use literals for long time SQL statements to more information to CBO to generate good query plans.