Structured Query Language (SQL) is the key to retrieving data from relational databases. When written properly and with efficiency in mind, SQL queries keep databases running smoothly and with ease of scalability. When written inefficiently database response times become too long causing applications to run longer and work harder. Sometimes poor SQL queries can even create service outages. The best way to assure that your SQL queries are efficient is by SQL optimization, a process that cleans up your queries and enables them to run smoothly and efficiently.
A 3-Step SQL Optimization Process
The goal of SQL optimization is determine the execution plan of SQL statements. Those that run rows the fastest or consume the least amount of system resources should be kept. All others should be tuned to improve their execution plan.
The following is a simple 3-step process for identifying and tuning poorly performing SQL statements. The data required to perform this process can be captured using OEM database tools or third-party tools like Confio Ignite.
- Identify High-Impact SQL Statements
These are the statements that are either run most frequently or use the most system resources. Look for these data sets:
- Rows processed – large number of rows processed will greater impact
- Buffer gets – Resource intensive queries tend to cause high buffer gets
- Disk reads – Statements with high disk reads may be causing high disk I/O
- Memory allocation – high memory allocation may mean SQL statements are performing resources intensive actions like in-memory table joins
- CPU secs – The more CPU secs the more processor resources being consumed
- Sorts – Sorts can lead to great slow downs
- Executions – Frequently used statements need to be as efficient as possible to keep the system running at peak performance
- Determine the Execution Plan
As you identify each SQL statement, they can be explained by inputting the statement into an optimizer tool and outputting the results to plan table. The table can then be viewed to determine each statements execution plan performance by determining the access path to the requested data. Again, you can use OEM or third-party tools
- SQL Optimization Tuning
Any statement that is found to be performing at suboptimal levels must be optimized to improve its performance. Statements can be tuned by the use of hints to improve the data access path or by rewriting the statements with temporary tables.
No matter is creating performance issues for your databases SQL statements, following this path will help you identify and optimize them.
Guest post created by Josh Stein on behalf of Confio Software, a leader in SQL Optimization tools.