When working with Oracle databases, there are times when you need to optimize and homogenize the database to ensure good performance. This is also known as database tuning. In most cases when tuning a database, you’ll focus on SQL query tuning. In Oracle, SQL query tuning is an important part of speeding up database performance.
In this guide, I’ll start with a discussion of SQL tuning and the basics of why it’s important; then I’ll set out six best practices for query tuning in Oracle, and how to make SELECT query faster in Oracle. This includes using SolarWinds® Database Performance Analyzer (DPA) or a similar tool for conducting query tuning.
What Is SQL Tuning?
What Is the SELECT Query?
Best Practices for Query Tuning in Oracle
Speeding Up SELECT Query in Oracle
What Is SQL Tuning?
SQL tuning is essentially the process of optimizing a database for better performance. Most tuning gains will be obtained from focusing on your SQL queries and optimizing how they function.
In SQL tuning, you look at the SQL queries you’re using to access and navigate the database and try to make them run in the shortest possible time. By performing regular SQL performance tuning, you can fix common SQL performance issues, like inefficient indexing and unoptimized SQL queries.
If your queries are inefficient (i.e., if you’re asking for large amounts of data when you only need a small amount), your database responses will be slow, and your applications and services may suffer in terms of performance. Particularly on an enterprise scale, every query matters.
There are numerous ways to write SQL queries, and different parameters you can attach to them. Some queries will be used more than others, and it’s important to make sure you use these efficiently, within the greater context of tuning your database more generally. One of the most commonly used queries is the SELECT query. The remainder of this guide will focus on tips related to SELECT specifically.
What Is the SELECT Query?
The SQL SELECT statement is used to select data from a database. When you use the SELECT query to obtain data, your results will be stored in a “result table.” This table of results is called the “result set.”
Let’s say you have a table of information called “Customers,” displaying five columns:
| Name | City | Age | Zip Code | Gender |
| Alice | London | 27 | 87208 | F |
| Bob | New York | 75 | 15923 | M |
| Charlie | Berlin | 43 | 51480 | M |
You can use the SELECT query to select data from the table, by using the following syntax:
| SELECT Name, Age FROM Customers; |
The result table would display the columns labeled “Name” and “Age” but not the others.
The SELECT query is used to quickly trim down information and filter it into selections, so you can process smaller data sets instead of working with the whole thing. This query is the most commonly used query when dealing with databases, which means its optimization is vital to overall performance.
Sometimes people use SELECT *, which will return all the columns from the specified table, instead of specific columns. This returns a lot of information, much of which may be unnecessary for your purposes.
Best Practices for Query Tuning in Oracle
Tuning Oracle SQL isn’t just something to do at the outset—you need to revisit it regularly. Oracle SQL tuning can be complex and is generally intended to optimize your database to obtain all the information you need (rows and columns) with the fewest number of “touches” (i.e., reads or interactions) with the database. Luckily, the SQL query tuning process is the same across many databases, so we can focus on those best practices applicable regardless of whether you use Oracle or another database.
1. Clarify Goals
One of the first things you need to consider is your business outcomes: Why are you retrieving this information in the first place, and what do you need to meet your business goal? Before tuning, it’s important to identify who your relevant stakeholders are and exactly what the queries are specifically intended for. Also make sure you know the intended audience for the results: this will allow you to produce result tables with the appropriate level of detail for the recipients.
Once you have your requirements set, double-check, and then set up your production database to follow through with necessary requests.
2. Identify High-Impact SQL Statements
High-impact SQL could be determined by number of rows processed, buffer gets, disk reads, memory KB, CPU seconds, sorts, or executions. Any queries with high numbers in any of these areas will be high impact and therefore high cost.
3. Identify Your Execution Plan
Determine how your SQL statements are to be executed. Oracle includes the explain plan utility for this purpose. The Oracle explain plan, or any execution plan, allows you to see how a SQL statement will be executed without having to run it.
There is a range of software available to determine high-impact SQL, look at the configuration of your execution pathways, and then help you complete recommended tunings. I’ll cover tools in detail below.
4. Avoid Large Scans
You need to ensure you avoid full-table scans, particularly if you’re dealing with large tables of data. This creates unnecessary input/output and can slow everything down.
Look at the number of rows you’re requesting with the query and use indexes to break your results down into smaller pieces. Indexes allow you to search through the data more efficiently—much like the index to a book. Creating an index involves identifying which columns you want to include and giving your index a name. The syntax is as follows:
| CREATE INDEX <index_name> on <table_name> ( <column3>, <column42>, … ); |
This will allow you to create an index in a table based on the columns you want to be included in the index. You can use your indexes to access smaller parts of the tables later, instead of having to re-scan the entire thing. When indexing, you should index all the predicates in JOIN, WHERE, ORDER BY, and GROUP BY clauses.
If a full-table scan is the fastest access method, particularly for smaller tables, you can cache the scan, so it can be more quickly accessed again.
5. Optimize SELECTs
One of the primary ways in which you can optimize your SELECT query is to ensure you only include the columns you really need. Avoid using SELECT * where possible, as this will pull out a large amount of data you probably don’t need to deal with.
You should also avoid using SELECT DISTINCT where possible. In many cases a table contains numerous values, and sometimes those values may be duplicated. Using SELECT DISTINCT will return only different values. This sounds useful, but it takes a large amount of resources to process this request, as it works by grouping all the fields in the query to create the distinct results.
6. Use a Third-Party Tool
One of the best approaches to performing query tuning in Oracle is to use a tool fit for the job. My choice is SolarWinds Database Performance Analyzer (DPA).
DPA is specifically built for SQL query performance monitoring, analysis, and tuning. Notably, it focuses on response time analysis, tracking how your database responds and performs. This allows you to identify bottlenecks, determine whether a change is causing a slowdown in response, and pinpoint the most critical root causes. DPA’s response time analysis is based on SQL data collected every second.
The software includes graphs showing you which SQL statements are performing poorly, application wait times, and wait types or events that could be behind a performance bottleneck. The graphs are interactive: you can click down through them to go into more detail on problems.
One of the most useful aspects of DPA for database tuning its special “tuning advisors,” a feature designed to point IT staff in the right direction. Tuning advisors highlight issues in need of immediate focus and provide you with easy-to-view historical data, for comparison with current metrics. The advisors provide actionable advice, and the anomaly detection features can help you quickly spot inefficient SQL statements. Applying the advice to adjust your queries results in a much faster process, facilitating streamlined troubleshooting of query inefficiencies that can make your database run slowly.
Database Performance Analyzer can be integrated with other SolarWinds products through the SolarWinds® Platform. This allows you to combine a range of solutions, including the flagship SolarWinds Network Performance Monitor, to keep your database and networks performing efficiently while minimizing impact on your end users.
Speeding Up SELECT Query in Oracle
The best way to approach your Oracle database tuning is to focus on SQL tuning, in particular the SELECT query. Once you have optimized your SQL queries, your Oracle database will be able to function faster.
You should also ensure you index wherever possible, especially for predicates in JOIN, WHERE, ORDER BY, and GROUP BY clauses. Furthermore, avoid all large table scans at all costs, as the more you scan, the more slowly your database will respond and perform.
Using the right tool can make the process smoother and easier. I recommend SolarWinds Database Performance Analyzer, which has features specifically designed for SQL query optimization. DPA helps you identify which SQL statements you should focus on, and which are causing you the most problems. This helps you troubleshoot quickly and efficiently, clearing issues before they affect your application and service performance for end users.
Finally, be sure to clarify and confirm business requirements at the outset, so your queries have a specific purpose. Having clear business objectives when querying the database will make your requests more defined, faster, and more efficient.
