While trying to tune the low-performing SQL queries, there are a lot of things that you need to check. It may be sometime the poor design of the query, which may be compromising the performance. Some other times, it could be an underlying hardware issue like IO or CPU, which brings forth the query's lower performance. It is also a chance that there may be some missing indices or stale statistics on any of the important columns. Similarly, there are many other unforeseen reasons, too, which may adversely contribute to query performance.
In short, you cannot pinpoint any single reason for the poor performance of the given SQL query. It is also a fact that we may not fine-tune everything in a complex SQL query at one go. However, for the admins and SQL programmers, you need to know one thing for sure, which may be certainly bringing down query performance, i.e., query parsing or parsing of the query. This article will discuss this in more detail for you to understand what parsing is and its impact on query performance.
Also Read >>>> How to rename SQL Server Database?
Understanding the workflow of query processing
Before exploring what parsing is, we may first understand the various steps involved in the query processing on a given database. There are many steps involving query processing ranging from writing the query and submitting it by the users to the execution and final output of the results. An outline of this workflow is as below.
→ Query execution workflow
• A new client-side cursor is set open
• Next, the client-server process starts to search for a shareable cursor at the server-side inside the Shared Pool memory of SGA
• If the search succeeds, it will return a reusable cursor, marked as a hit
o The curse is executed
• If the search fails in finding a cursor, it will be marked as Miss
o A new cursor area gets allocated, and parsing of the same will be initiated.
• Once the cursor parsing gets over, the used bind variables get replaced with the supplied actual values
• This query may be further chosen to use more than a single process to execute (parallelism)
• Once the query execution is completed, it results in getting the queries data, and then the results are fetched for the server process
• After fetching the needed data and the query is executed successfully, the cursor is then closed.
→ The concept of query parsing
As you know, a typical SQL statement consists of many different inputs as different functions, tables, and expressions. So, it is also possible that there are various ways to execute every given query. However, queries should run optimally desirably to implement it in the shortest time and provide the optimum output. With this objective in mind, parsing is the process meant to make an appropriate decision on the query given by calculating what different ways are there in which a query can be processed. Each query should be parsed a minimum of one time before execution. Remote database services to query parsing in a very professional manner. To know about such services, you can explore RemoteDBA offerings.
Query parsing is ideally performed within a database by using an Optimizer component. This Optimizer will evaluate various attributes of each query taken to it as:
o The number involved tables
o Indexes are made available or not
o What types of expressions are involved in the query, etc?
By considering all these, Optimizer will decide the optimum way for query execution. This information gets storedin the SGA in Library Cache, which functions as a sub-pool within the given Shared Pool.
Query processing info is possibly returned in two states as:
1. The info can be found at the Library Cache
2. It is not found
→ Soft Parsing
The memory area of the Library Cache on which the query processing info is kept is known as Cursor. So, when the parsing is done, if there is a reusable cursor found in the Library Cache, the process is to pick it up and execute the statement instantaneously. This is known as Soft Parsing.
Also Read >>>> SQL Server - Error Code 18456
→ Hard Parsing
If there is no reusable cursor is found on the Library Cache as there is no similar query executed in the past, then query optimization is needed at the first point. This is known as Hard Parsing.
In the case of Hard Parsing, the case may be either that a pre-existing cursor is not found in the cache or if the found cursor is invalidated. Whatever the case is, Hard Parsing may mean that the optimizer has to do the work to make sure of the optimal execution plan for a given query. An optimizer does it by exploring all possible inputsgiven by the user. It will include checking for:
- Presence or absence of expressions
- Presence of absence of indexes
- The functions applied to given columns
- Whether the query to be executed is a join query or not
- Any specific hints provided etc.
All this info is crucial in terms of optimizing the query performance. The presence of such inputs or the absence of the same can significantly impact the execution plan.
However, before attempting to find the best plan for q query execution. There are some essentials tasks to be completed as:
- Syntax check to make sure that it is correct. It is usually done by comparing the entire query text against supported keywords for the database version
- Semantics check to confirm that a correct object name is used and that the user has privileges to execute the given query on objects. If found otherwise, the execution will be aborted with an error message
- Hashing query text and hash key-value pair generation
- Query transformation, which is the process of converting the query into simple FROM and SELECT statements
- Estimation of the query processing
- Plan generation
We can conclude with the fact that Hard parsing is sometimes a necessary evil. Sometimes, it is the only way for the optimizer to derive the possible approach for query execution.
Other Articles:
No comments:
Post a Comment