How to do performance tuning in Oracle – Guide & Tools
Have you noticed your database running slower than usual? Then you might need a touch of performance tuning. Performance tuning in Oracle databases eliminates bottlenecks that make applications unresponsive and increases the load your database can handle.
What is Performance Tuning?
Performance tuning is the process of administering a database to improve performance. Performance tuning in Oracle databases includes optimizing SQL statements and query execution plans so that the requests can be completed more efficiently.
The organization of a database the SQL statements used to access that data, determine the level of resources needed to respond to queries when an application communicates with the database.
Problems like poorly optimized SQL statements force the database to work much harder to retrieve information (resulting in more system resources being used). The more system resources that are used the greater the chance it will affect the experience of users on connected applications.
In an enterprise, users will report a slow application to a database administrator who will then attempt to pinpoint the root cause of the problem. The administrator analyzes statement code and searches for database bottlenecks. The process is extensive, as the administrator has to diagnose the root cause of the problem before it can be addressed.
Monitoring Performance in Oracle Databases: Response Time and Throughput
When performance tuning an Oracle database there are two metrics that are useful to measure:
- Response time – How long the database takes to complete a request.
- System throughput – The number of processes completed in a period of time.
High response time means that an application is providing a slow user experience. On the other hand, low system throughput means that the database only has the resources to manage a small number of tasks in a short time period. An administrator has to be able to know how they are trying to improve performance before tuning.
How you optimize an Oracle database comes down to your goals and the type of applications you are using. Many goals like having a fast response time or a high throughput are contradictory.
Tuning for fast response times may speed up individual queries made by users but sacrifice other tasks in the workload. In contrast, achieving a high throughput would aim to optimize the performance of the entire workload to support a larger output of transactions per second (but not necessarily speed up individual queries).
The type of application you’re using makes all the difference. If you’re using an online transaction process (OLTP) application then you would use throughput to measure performance. This is because of the high volume of transactions the application needs to manage.
However, if you were using a decision support system (DSS) with users running queries on everything from a handful of records to thousands of records, then you would measure performance by response time (unless you were supporting lots of users running concurrent queries)!
The Two Types of Tuning: Proactive Monitoring and Bottleneck Elimination
Now that you know what performance tuning is, it’s important to look at the two main models of tuning:
- Proactive Monitoring
- Bottleneck Elimination
Database administrators use these two models to manage performance issues and keep applications functioning at a high level.
Proactive monitoring is the process of monitoring a database to discover and address performance issues early rather than simply reacting when there is a problem. With proactive monitoring, administrators will periodically review databases to identify the signs of performance degradation.
The idea behind proactive monitoring is to catch issues and inefficiencies before they develop into greater problems further down the line. Some common issues database administrators look out for include:
- Database wait events – A high number of events can negatively affect database performance. Finding obstructive sessions and killing them can prevent performance degradation.
- Load average – Monitoring the load average of a server will tell you if server resources are functioning as normal. A high load average can result in slow database performance.
- Database sessions – Monitoring the number of active sessions can stop you from reaching the maximum (which will prevent you from being able to open new sessions).
However, monitoring proactively does carry some risk. Any changes an administrator makes can result in a decrease in performance for the database. Administrators can mitigate the risks by being cautious before making new changes.
Bottlenecks are one of the most common causes of poor performance. Bottlenecks block requests from reaching the destination and increase the response time of applications. Bottlenecks can be caused by a range of factors from badly coded SQL statements and high resource usage.
Bottleneck elimination is more of a reactive process than proactive monitoring. An administrator identifies a bottleneck and then finds a way to fix it. Fixing a bottleneck is a complex process and depends on what the root cause is (and whether it is internal or external). Recoding SQL statements is one solution for fixing internal bottlenecks, which should be addressed first.
Once internal bottlenecks have been resolved the administrator can start to look at external factors like CPU and storage performance that could be causing the problem. An administrator can choose between making changes to the application (or how it is used), Oracle, or the hardware configuration of the host.
How to Performance Tune
Performance tuning an Oracle database is a very complex subject because there are so many different factors that can affect database performance. To keep things simple, we’re going to look at some basic ways you can optimize performance.
1. Identify High-Cost Queries
The first step to tuning SQL code is to identify high-cost queries that consume excessive resources. Rather than optimizing every line of code it is more efficient to focus on the most widely-used SQL statements and have the largest database / I/O footprint.
One easy way to identify high-cost queries is to use database monitoring software (we look at some of these platforms in more detail further below). One useful tool is Oracle SQL Analyze, which can identify resource-intensive SQL statements. Tuning these statements will give you the greatest return on your time investment.
2. Minimize the workload (Use Indexes!)
You can make the same query in many different ways so it is advantageous to write code that minimizes the workload as much as possible. If you only need a snapshot of data from a table it makes no sense processing thousands of rows you don’t need (all you’re doing is wasting system resources!) A full table scan takes up more database resources and I/O.
To eliminate the stress of sustaining a large workload you can use indexes to access small sets of rows rather than processing the entire database at once. Use indexes in those scenarios where a column is regularly queried.
3. Use Stateful Connections with Applications
Sometimes the cause of poor performance doesn’t come from code but because the connection keeps dropping between the application and the database. If your application isn’t configured correctly then it could form a connect to the database to access a table and then drop the connection once it has the information it needs.
Dropping the connection after accessing the table is terrible for performance. Instead, try to keep a stateful connection so that the application stays connected to the database at all times. Maintaining the connection will stop system resources from being wasted each time the application interacts with the database.
4. Collect and Store Optimizer Statistics
Optimizer statistics are data that describe a database and its objects. These statistics are used by the database to choose the best execution plan for SQL statements. Regularly collecting and storing optimizer statistics on database objects is essential for maintaining efficiency.
Collecting optimizer statistics makes sure that the database has accurate information on table contents. If the data is inaccurate then the database can choose a poor execution plan, which will affect the end-user experience. Oracle databases can automatically collect optimizer statistics or you can do so manually with the DBMS_STATS package.
Tools for Monitoring Query Performance
Using a software agent to monitor SQL query performance is the most effective way to manage query performance. Database performance monitoring tools can help to identify poorly performing SQL code and enable you to pinpoint the root cause of performance issues. The user can view query performance on a dashboard so they don’t need to search for information manually.
SolarWinds Database Performance Analyzer
SolarWinds Database Performance Analyzer is a tool that can monitor the performance of SQL queries in real-time. You can view a table of the Top SQL Statements in your database to see the highest impact SQL statements. Viewing the top statements allows you to focus your remediation efforts on those statements that have the greatest impact on performance.
There is also a blocking analysis feature, which allows you to view the blocking hierarchy of the database and to view the total wait caused. Being able to see those queries that are obscuring the routes of other queries tells you where to make changes to improve performance. You can download a free trial.
SolarWinds Database Performance AnalyzerStart 14-day FREE Trial
dbForge Studio for Oracle (Oracle SQL Profiler)
dbForge Studio for Oracle is an integrated development environment (IDE) that comes with Oracle SQL Profiler. With the profiler, you can identify queries with the highest duration. You can also view query execution plans and sessions statistics for additional information. Any changes you make will be saved so you can revert to earlier versions if you make a mistake.
In the Session Statistics tab, you can compare the results of new queries against older versions. Differences are highlighted in red and green so you can easily tell the performance impact. Session stats you can monitor include, CPU used by this session and sorts (rows). The Session Statistics tab allows you to make sure that your changes are actually improving performance!
Tune with a Goal in Mind!
Having clear expectations with a specific goal in mind is critical for maintaining your database and tuning the system. Performance tuning is easier when you know what you’re trying to achieve.
For example, if you want to minimize the response time of an application so that it completes queries in 1-3 seconds then you can take action to tune for that scenario. That means diagnosing bottlenecks and performance issues that slow response time down.
Being able to refer back to goals will also help when you’re using a database analyzer. You’ll be able to monitor disk, CPU, and network usage to identify how performance is affected. The better you understand your goals, the better you know how to tune your resources.