When you purchase an Oracle Database license, you get more than just the database management system itself. Your...
license also includes use of the base features in Oracle Enterprise Manager, the company's suite of tools for monitoring and managing an Oracle database infrastructure.
You may find Enterprise Manager (EM) to be a very useful product. But you may also want to consider third-party Oracle database monitoring tools to support your performance tuning and management needs, even if you already use EM.
To explain why, let's look at what EM and two popular third-party tools offer database administrators (DBAs) for that particular aspect of managing Oracle databases.
An at-a-glance view of Oracle performance
One of the best features of Enterprise Manager is the ability to see the performance of an Oracle database at a glance. Signing on to EM and selecting a database leads to the Performance Home screen, which displays a chart that tracks the performance of the database based on Oracle's average active sessions value.
Figure 1 shows a screenshot of that chart. The green indicates user sessions that are actively using the available CPU capacity or waiting for other system resources, as you can see from the legend on the right. There also are some brown spikes that point to concurrency issues. When the colored areas in the chart go above the red "CPU Cores" line, database users likely will start to experience performance pain.
A DBA can click on any one of the items in the legend or the corresponding color in the chart to quickly drill down into that area for more information. For example, when I click on the Concurrency category, EM displays a new chart with data on the concurrency-related wait events in the database.
Figure 2 shows that chart, which includes a shaded box that I can drag to an area of interest to get details on performance during the highlighted time interval. In this case, the Top SQL and Top Sessions information is displayed for the period covered by the shaded box; that shows me the SQL statements and user sessions that are consuming the most CPU resources. Note that I've redacted some of the information in the screenshot for security purposes since it was taken from an actual production system.
But I don't only use EM. One of my favorite performance tuning and Oracle database monitoring tools is Lighty for Oracle, which is developed by Orachrome. Figure 3 is a screenshot from Lighty showing the same information as in EM, also with some proprietary info redacted.
Faster fixes for Oracle performance problems
It's natural to ask why I would pay for a third-party product that gives me the same information EM provides. For starters, working with a product like Lighty is faster for me than using EM is. Lighty focuses only on performance tuning. As such, it's written to give you performance information at your fingertips. In EM, whenever you drill down for more detail, the higher-level information is no longer on the screen; in Lighty, you can see the performance graph and SQL details together.
I've used Lighty for several years now, and I can resolve performance issues with it much more quickly than I could with EM. That has saved me a lot of time as a DBA, so much so that the third-party tool essentially paid for itself after just two months.
Also, while a set of EM's database management features is included with Oracle Database, the performance monitoring screens require users to license the optional Diagnostics Pack from Oracle. Lighty will use the add-on pack if you have it installed. But if you don't, the tool will create its own objects to simulate the Diagnostics Pack, saving you money for the same performance monitoring and tuning functionality.
There are plenty of other Oracle database monitoring tools available, too. One that's even more popular than Lighty is Toad for Oracle, from Quest Software.
Toad offers a variety of database development and administration features -- it's much more than just a performance tuning tool. But Toad for Oracle DBA Edition includes Spotlight on Oracle, which enables users to monitor database performance and alerts them when a bottleneck occurs. An example of Spotlight's performance monitoring is shown in Figure 4.
Third-party Oracle database monitoring tools often complement the tool set provided with your database software. Yes, the third party may require you to spend more money than you would otherwise; however, in most cases, I've found that a tool pays for itself within the first year based on the amount of time I save by using it. That also frees up DBAs like me to perform other functions.
And I've yet to see a third-party tool vendor that doesn't let you download its product for a free trial. Why not test out some performance monitoring and tuning tools other than Oracle Enterprise Manager to see if they're a worthwhile investment for you and your company?