Poor Oracle database performance is quite insidious. It can have various ramifications that will often translate into poor application performance. This can lead a typical administrator on a wild goose chase as he goes looking for a problem somewhere it is not.
There is but one way to ensure that the database won’t be the bottleneck of your distributed multi-tiered applications: tuning Oracle database performance. That will be the topic of today’s post. Our goal is not to provide an in-depth tutorial on the subject but to provide you with enough information to let you pick the best possible tool to help you handle this seemingly overwhelming task.
We’ll begin by introducing databases, what they are and how they work. We will, of course, be a bit more specific about Oracle as it is our primary focus today. Then, we’ll tackle performance tuning. We’ll explain what it is and how it works. Our next order of business will be database performance analysis as it is at the core of any Oracle database performance tuning effort. And since that is so important, we’ll finally review the best tools for Oracle database performance analysis.
About Relational Database Management Systems
In its simplest expression, a database is a data structure that stores organized information. Data in a database is stored in tables where each row represent one item of data and each column is a piece of information about this item. For example, an address book could be stored in a database where each entry would be a line and where there would be columns for the last name, first name, address, street, city, state, country, etc.
Database systems—also referred to as relational database managers—offer several ways to manipulate that data. Several tables could be joined, data can be indexed for faster retrieval or manipulated in different ways. Explaining databases would take a whole book. In fact, hundreds of books have been written on the subject. For the purpose of our discussion, the important thing to realize is that databases are quite complex pieces of software. Due in part to that complexity, many factors can affect their performance.
As for Oracle, it a proprietary brand of Relational DataBase Management System (RDBMS). It’s been around for ages and, when initially released back in 1979, it was the first SQL-based RDBMS. It never stopped evolving since and its version 19 was just released last August. Nowadays, the RDBMS market is saturated with offerings from multiple vendors. Microsoft SQL Server and IBM DB2 are probably the most popular alternatives but free and open-source offerings are getting increasingly popular and they often offer comparable features and performance for most use cases.
What Is Oracle Performance Tuning?
Oracle database performance tuning refers to the process of optimizing Oracle performance by streamlining the execution of SQL statements. In a nutshell, performance tuning simplifies the process of accessing and altering the information contained within the database with the intention of improving query response times and, consequently application performance. No matter how complex some textbooks will make it look, it actually boils down to just that. Doing it, however, is the complicated part. Like many things in life, this is easier said than done.
How Does It Work?
Performance tuning considers the many elements in an RDBMS to pinpoint the source of performance problems. Database Administrators (DBAs) are often faced with a difficult task. On one hand, users report experiencing app delays and slow-loading pages, but on the other hand, administrators cannot pinpoint the source of database bottlenecks. Is it an optimizer issue? Is it the actual coding of query statements? Is it an issue with the server?
Performance-tuning administrators should consider the many elements in an Oracle database to troubleshoot any operation lag. Performance tuning can not only be quite time-intensive but, more importantly, it can be difficult to figure out where to begin. An intensive performance tuning process takes a systems-level approach and considers every Oracle component from top to bottom.
As many database experts will know, tuning individual SQL statements one by one will have little effect unless the administrator has first performed system-level tuning on the server, the database instances, and its objects. Assessing input and output (I/O) measures, optimizer parameters and statistics, and instance settings before tuning individual SQL statements is highly recommended. Otherwise, your SQL tuning efforts may be rendered useless later by the optimizer as it determines execution protocol contrary to designed execution plans.
RELATED READING: 6 Best SQL Server Monitoring Tools
The First Step, Analyzing Database Performance
Analyzing database servers’ performance and monitoring their uptime and other operational parameters is one of the most important tasks of network, system, and/or database administrators. It will help keep your database server software running smoothly and efficiently. Done properly—using the appropriate tools—it can prove to be beneficial to not only your server hardware and software but, more importantly, to your end-users’ experience.
Several issues can plague database servers and adversely affect their performance. Among them, here are a few of the most important ones:
As the size of a database grows, the server will need more memory and CPU to read and process data from the tables. Increasing the size of an existing table can, for instance, cause a noticeable impact of server performance.
Queries are the commands given to the database to fetch some specific data. The more complex a query is, the bigger its impact on database performance. Identifying the most taxing queries on the database server is crucial to managing servers’ performance. Once the queries that are taking up the most resources are identified, efforts can be put into reducing their load on server cycles and memory usage. Also, finding out which application is causing these expensive queries on your database can help you diagnose the underlying issues behind these queries and fix them quickly and efficiently. Monitoring queries will help you identify which host application is causing issues and potentially help you diagnose other applications issues as well.
Data stored in databases is indexed to make searching the database and retrieving data faster and more efficient. Although Proper indexing will help search and retrieve data faster, when data is modified, deleted or removed, the indexed content gets scattered and the database becomes fragmented which could result in performance degradation.
Hardware performance of the server running the database can also have a big impact on database performance. Memory, Cache and paging should be monitored in real-time to ensure your database server software is performing optimally and not experiencing bottlenecks or slowdowns.
The Top Tools for Oracle Database Performance Analysis
Since Oracle database performance analysis is such an important yet complex matter, we’ve scoured the market for some of the best tools available. While each and any of the tools reviewed below is highly recommendable, they are all very different. Take some time to read detailed reviews or take advantage of the available free trials that most of them offer before you finalize your pick of the best tool for your specific needs.
Our first entry is an excellent tool from SolarWinds, the same company that brought us the Network Performance Monitor, regarded by many as one of the best network bandwidth monitoring tools. SolarWinds has become a household name among network administrators due mainly to its impressive selection of tools which include some of the best free tools out there.
The SolarWinds Database Performance Analyzer (or DPA) monitors and analyzes your Oracle database instances to resolve issues. It uses a Response Time Analysis method and focuses on the time between a query requests and the corresponding response from the database and analyzes wait times and events to pinpoint the bottlenecks of databases.
- FREE TRIAL: SolarWinds Database Performance Analyzer
- Official Download Link: https://www.solarwinds.com/database-performance-analyzer/registration
The tool boasts an easy to use interface that will assist you in finding issues quickly and easily. The main screen will let you view database instances, wait times, query advice, CPU (with warning and critical alerts), memory, disk and sessions.
The Trend dashboard of a specific database instance shows you the total wait times (or, alternatively, your choice of either average or typical day wait times) for the users over the course of a month in a graphical format. Each colour of the graph represents an individual SQL statement, giving you a representation of which statement takes the longest to run.
The SolarWinds Database Performance Analyzer is so feature-packed that a whole article could be dedicated to it. Here’s a summary of its most important features.
Database tuning advice
The tool gives you advice for tuning individual database instances and ensure they’re running at their peak performance. It does that by analyzing all parameters of an instance and recommending a course of action to optimize certain queries or whole database instances for peak performance.
Active monitoring of all active sessions
The highly detailed native polling engine records exactly everything that’s happening within your database instances with no additional load to the database themselves.
Tracking, monitoring and analysis of database components
To help pinpoint the root cause of performance issues, the tool automatically correlates queries, users, files, plans, objects, wait times, storage and date/time/hour/minute to accurately discover and diagnose problems on the fly.
Single point of Monitoring for all your Databases
The SolarWinds Database Performance Analyzer does not only work with Oracle. It actually supports most major products including Microsoft SQL Server, MySQL, DB2, and ASE. It also supports either physical on-premises servers, Virtual Machines (either under Vmware or Hyper-V) and cloud-based databases.
Agentless and Highly Scalable
This product will let you monitor as many database instances as you want. It can easily scale from one to a thousand instances. It also does not require any monitoring or analysis agent to be installed on your servers. There’s no risk, then, that the tool will impact your databases’ performance.
Prices for the SolarWinds Database Performance Analyzer start at $1 995 and vary according to the number and type of database instances to monitor. If you’d rather try the product before purchasing it, a fully functional 14-day trial version is available.
2. Idera Diagnostic Manager
The Idera Diagnostic Manager offers performance monitoring of Oracle and other databases in both physical and virtual environments. The tool empowers administrators with the ability to see performance statistics, metrics and alerts from physical servers as well as from virtual machines and their underlying host hardware. The monitored metrics include CPU, memory, hard disk usage and space, network usage, and more. The product also provides proactive monitoring of SQL queries along with transnational monitoring and can provide administrators with recommendations for the health of their Oracle databases.
The Idera Diagnostic Manager offers either a web-based interface or mobile apps that are available for both Android and iOS phones and tablets as well as the Blackberry platform. The availability of real-time and historical data about your Oracle server instances lets administrators make changes on the fly right from the mobile app. And although the mobile apps are not quite as feature-packed as the web console is, you can run several administrative tasks from them including viewing and killing processes, starting and stopping jobs as needed, and running queries to help resolve issues.
Other features of this powerful tool include a predictive alerting system which is built to avoid any false alerts and an excellent query performance monitoring function that can pinpoint queries that are not efficient and/or performing poorly. Prices for the Idera Diagnostic Manager start at $1 996 per database instance and a fully functional albeit rather short 14-day trial is available.
3. ManageEngine Applications Manager
While it may not be as well-known as SolarWinds, ManageEngine is another name which enjoys an excellent reputation among network administrators. Don’t let its name mislead you. Although the ManageEngine Application Manager primarily deals with application management, it also includes some powerful Oracle performance monitoring and management features and it is as much a monitoring platform as it is a management tool.
The ManageEngine Applications Manager‘s Oracle database monitor offers multi-dimensional, out-of-the-box Oracle monitoring. It also boasts prompt alerting, and insightful reports. Because of its numerous features, Database Performance Analyzer for oracle by Applications Manager is a powerful Oracle database monitoring tool used by thousands of IT admins. Oracle databases are complex and monitoring is often both challenging and time-consuming. Important parameters can easily slip through the cracks. This tool’s effective Oracle monitoring support makes it easy to track the performance of various facets of Oracle database, especially metrics related to tablespaces, sessions, SGAs, data files, and disk I/O.
There’s a lot more that can be done with this tool. For instance, you can track application response times with code-level information about your application performance monitoring environment. Its transaction tracing feature will let you detect slow transactions. It will monitor database query executions and track background transactions.
The ManageEngine Application Manager is available in several editions. There’s a feature-limited Free edition as well as a Professional and an Enterprise paid versions. Pricing starts at $945 and details can be obtained by contacting ManageEngine. A free 30-day trial version is also available.
4. eG Enterprise
eG Innovation is a company which specializes in performance monitoring tools. Its flagship product is called eG Enterprise. It is a total performance management solution that provides automated IT monitoring, diagnosis, and reporting to troubleshoot application slowdowns, user experience issues, network outages, virtualization bottlenecks, server failures, storage hot spots, and more.
eG Enterprise uses a combination of agent-based and agentless technologies to monitor the health and performance of your whole IT infrastructure from application code to hardware across any type of environment: physical, virtual, cloud or hybrid. The tool uses a patented performance correlation and root cause diagnosis technology. This simplifies performance troubleshooting and can result in faster problem resolution and increased operational efficiency.
eG Enterprise includes extensive monitoring capabilities for Oracle databases. A single eG monitoring agent is capable of monitoring all the Oracle database instances being executed on a system. The actual monitoring of the Oracle database instances is performed non-intrusively and administrators have the option of configuring whether the monitoring is to be performed in an agent-based or agentless manner.
eG Enterprise‘s web-based architecture allows for the management and monitoring of distributed Oracle servers from a central manager. Administrators can view and analyze the performance of Oracle database servers in real-time over the web. The tool’s Oracle monitor includes a specialized model for an Oracle database server built to avoid overwhelming the administrator with a ton of performance data. By viewing the layer model of an Oracle database server, an administrator can quickly determine which layer(s) of the database server is causing a problem.
eG Enterprise’s pricing and deployment model is rather complex. The tool is available as a locally installed software either with a perpetual license or on a subscription basis. If you prefer, a cloud-based SaaS option is also available. Prices are not readily available from eG Innovation and you’ll need to request a formal quote from the company. If you can to test the product’s features, a free trial is available from the publisher.
5. Idera Precise Platform
Last on our list is another product from Idera called the Precise Platform. It differs from the Diagnostic Manager reviewed above in that the Precise Platform is designed specifically to keep your Oracle environment available and performing at peak efficiency. The tool captures, measures, and analyzes performance metrics from all critical system components and helps you detect and correct the root causes of problems before end-users are affected. This product provides high-level visibility across multiple Oracle and Oracle RAC instances, providing alerting when problems are detected, offering reports on collected data, and maintaining a warehouse of Oracle database performance information.
The Idera Precise Platform continuously analyzes database behaviour to find the poorest performing objects or processes. This can include heavy statements, heavy objects, high I/O, RAC events, storage devices, and more. Historical statistics are stored by instance, by user, and by program to provide the necessary context to see the whole picture. It will quickly analyze SQL statements, database objects, and Oracle instances to uncover specific causes of performance problems. It also presents the Oracle access path in detailed steps and displays the statistics needed to understand each step, letting you isolate problems fast.
The Idera Precise Platform provides DBAs with a library of knowledge and experience designed to help them tune their Oracle environment. It will offer recommendations about SQL statement tuning, database object tuning, index tuning, and index cost. It also automatically identifies problem areas and provides actionable advice on how to address them. Furthermore, DBAs can perform what-if analysis to predict the impact of proposed changes before actually making them, thereby reducing the risk of accidentally making things worse. Finally, this tool makes it easier to align server and storage capacity with the growing needs of your business.
Pricing for the Idera Precise Platform is not readily available and you’ll need to contact the company for a quote adapted to your specific environment. Likewise, a free trial does not seem to be available although a demo session can be arranged, again by contacting the publisher.