Vikas AroraParsing is a big problem of dynamic SQL that affects the overall performance of SQL statements. As SQL developers cannot access the production environment, so they are usually not aware of the exact problems.
Database vendors of the earlier version usually ship the database with parsed SQL statements that are stored in the database and these plans can be even reused by the database developers. This storage is known as execution plan cache.
Through bind variables, the database can recognize the identical SQL statements even from the earlier parsed executed statements. Moreover, previous execution database plan can be re-executed to generate a new plan.
How Are Bind Variables Associated with SQL Performance?
When an SQL statement gets executed in Oracle, it’s engine checks the validity of statements and plans that how the tables that are used in the statement can be accessed and joined together? This process is called parsing. Here, the optimizer determines the tables need to be accessed and joined. In this way, a complete execution plan gets formed. The step in which Oracle sends the statement to optimizer is known as hard parse.
In case, if the execution plan already exists for any query then Oracle does not send the query to the optimizer and even use the existing plan. This process is called soft parsing. Oracle engine even considers minor formatting differences while comparing the two SQL statements.
Like both of the following statements are different for Oracle:
select * from students where student_id=s012;
And
SELECT * FROM students WHERE student_id=s012;
Though both of the statements are same, Oracle engine will hardly parse these statements when it will execute them at the first time.
How Bind Variables Improve the SQL Performance?
Usually, any table is queried by Primary key at the time of query execution. The query is executed in the following steps:
- A unique scan is performed for the primary key index
- By using the rowed of index required row is accessed
This plan is performed for every call or query execution. Like for both of the following listed queries, the same above-listed plan will be executed twice or in other words, a hard phase will get executed each time.
select * from students where student_id=4444;
select * from students where student_id=1233;
So, for both of these queries, same plan will be generated and executed twice that degrades the SQL performance. In such cases, to avoid the extra hard parses, we can replace the numbers by a variable or bind variable and just by a single statement the task can be performed:
select * from students where student_id=:std;
Here, now when the same query will be executed twice for two different values of std, then first-time hard parse will be done and in the second time, soft parse will be done to generate the result.
The impact of using Bind Variables
By using bind variables two types of performances are impacted a lot that are listed and defined below:
*Individual Execution Performance
*Entire System Performance
How does bind variables affect Individual Execution Performance?
When it comes to cache some information, then it brings some overhead as well. Somewhat overhead always involved in parsing. Even if a user needs to execute a small execution plan, still it will bring some overhead. Here, in such cases, the overhead will be SQL statement parsing and creation of execution plan for the same.
Use of bind variable can speed up the execution plan up to 2.5 times as compared to those that do not use execution plan. For trivial queries, such performances matter a lot, while for the complex queries it can be a bit less, as in complex queries execution itself takes a lot of time. No one will ever want to pay the overhead price, so query and its plan are usually cached and are a more preferred option.
How does bind variables affect the Entire System?
Without bind variables, not only individual query suffers instead entire system can suffer. In some cases, a single query can generate additional 20,000 queries, due to which many of the important queries may need to wait for the cache. Due to this, not only the execution of this single query slows down, but a number of important queries may need to wait as a result of which the speed of entire system may slow down.
If every query gets slow down then the speed of entire system will get slow down and affected a lot. Many queries that may be more important will be queued up and server speed will get slow down ultimately.
Workaround
In some databases, constant literals are bounded to enforce parsing. In Oracle database, the variable CURSOR_SHARING=FORCE can be specified as “quick fix”. In another database SQL Server, this variable is called as forced parameterization.
In such cases, there are some limitations as well. Additional parsing work may be required in special situations. At the time of parsing, the overhead increases as these literals have to be finding out and will be then replaced with bind variables. Such overhead is then applied to all queries. So, where bind variables improve the performance soon another hand they also increase the overhead.
Final Words:
For SQL performance, bind variables are much important. Especially if you are using client-side tools like Hibernate, jOOQ etc., here bind variables must be used as default variables. In case of procedural languages like T-SQL or PL/SQL, bind variables are generated automatically. With this Post, you have a clear idea of bind variables and how they can affect the SQL performance positively. To know more about SQL and it's bind variables you may join Oracle certification program at JanBask Training.
