SQL tuning involves some basic steps that businesses should take into consideration before they undertake the process. If you are a business owner, you should ensure that your SQL servers perform to their optimal levels. However, before you take action steps, you should keep in mind some basic steps to help you tune the SQL server effectively.
What are the basic steps for SQL tuning?
The following are the basic steps for SQL tuning-
- Identify top SQL statements and high load responsible for managing the huge share of the application system and workload resources by evaluating previous SQL execution history available in the system.
- Verify the execution plans that the query optimizer produces for these SQL statements.
- Incorporating corrective measures to boost improved execution plans for SQL statements that perform poorly.
All the above steps need to be repeated until the performance of the system reaches a level that is satisfactory, and no more SQL statements have to be tuned.
What are the objectives of tuning SQL server systems?
The major goal for tuning the SQL system is to reduce the response time for the end-user of the database or to decrease the resources deployed for processing similar work. Businesses are able to accomplish the above objectives in many ways.
Reducing the workload
SQL tuning generally involves discovering effective ways to process similar workloads. This takes place when it becomes possible to alter the execution plan of the statement without changing its functionality for reducing the consumption of resources.
The following are two instances of how you can resource the database usage-
- If a general executed SQL query needs to access a less percentage of the data in a table, the database can execute it better when it deploys an index. When an index is created, businesses are able to decrease the number of resources deployed.
- If the user looks at the first 20 rows of 10,000 rows returned in an order that is specific, and if the sort order or the query is able to be satisfied with the use of an index, the user does not have to sort and access the 10,000 rows to view the first 20 rows.
Balancing the workload
Systems have peak usage during the day when many users are accessing the database. During the night, the volume of use is low. Businesses can reduce the workload if they are able to schedule non-critical reports and other batch jobs to operate at night. This helps the business to decrease the concurrency in the day, allowing the database to free up resources for more critical programs during that time.
Parallelize workload
The SQL queries that access a huge amount of data like the data warehouse queries can run parallel. This method helps businesses to decrease response time in a data warehouse that has a low concurrency. However, for OLTP environments that lead to high concurrency, parallelism adversely impacts other users with an increase if the total usage of resources in the program.
Experts in SQL server consulting lists the steps that are involved in the identification and collection of data for SQL statements that have a high load. Note that these high load SQL statements perform poorly. Businesses need to focus on resource-intensive SQL statements that positively influence the performance of the Oracle database. The following are the tools that can recognize SQL statements that are of a high-load nature-
- Automatic SQL tuning
- Automatic Workload repository
- V$SQL view
- Custom workload, and
- SQL trace
Identifying resource-intensive SQL
The first steps for businesses to identify resource-intensive SQL is to categorize the issue they wish to fix-
- The business needs to determine if there is an issue that is specific to one program or programs
- Is the issue generic over the system application?
Tuning specific programs
If the business is tuning a specific program like 3GL or GUI, the SQL needs to be identified for examining the simple matter of viewing the executed SQL that has been executed in the program. The Oracle Enterprise Manager or the Enterprise Manager offers you tools for recognizing resource-intensive SQL statements that generate plans as well as evaluate the SQL performance.
In some cases, the business cannot identify SQL, especially in instances where the SQL statements are dynamically generated. It is here they should use SQL_ TRACE to generate the trace file with the executed SQL. After this, the business should use TKPROF to generate the output file. SQL statements that are present in the TKPROF output file are ordered by several parameters like the execution elapsed time that generally helps in identifying the elapsed time of the SQL statements that are present on the top of the file. This makes the task of identifying the SQL statements that are performing poorly simpler if there is a large number of SQL statements present.
Reducing the load or tuning the system application
There are instances where the whole application does not perform well. If the business is making efforts to decrease the total CPU workload or the input/output load on the SQL server, then identifying the resource-intensive SQL plays a crucial role in involving the given steps-
- The business should decide on the time of the day it would like to take the examination. Most businesses prefer to examine the system during the peak processing period of the application.
- The operating statistics both in the beginning and the end of the period should be collected.
- The SQL statements using maximum resources should be identified.
Consultants in SQL servers sum up by saying that when the above SQL statements have been collected and identified, the next stage is to tune them. The SQL statements are examined, and the tuning is done by database administrators with the goal of boosting the optimal performance of the SQL servers of the business. If the business cannot resort to the manual tuning process due to the challenges it poses, they can always choose the automatic tuning process for the task.
Add Comment