An MRI for your Oracle database performance [New tech brief]

The image above is an MRI of a knee, showing a 360-degree view of all aspects of the knee’s infrastructure. This image shows multiple dimensions simultaneously. What if you could prescribe an MRI that showed you the health of your Oracle database?

You can. In my previous post, I discussed four objectives of database management and innovative tools to meet those objectives. In this post, we’ll dive deep into the data to get clear, multi-dimensional views of database performance.

In the same way that an MRI provides a complete view of your knee, wouldn’t you like to have a similar view of your database? Think about how easy it would be to find and take care of performance problems if you could examine your database across that many dimensions.

We’ve built drill-down capabilities into our new Performance Investigator feature of Foglight for Oracle. We call it Performance Investigator because it gets to the bottom of almost any performance problem in your database environment.

Multi-dimensional workload analysis – An MRI for your Oracle database performance

Performance Investigator combines a monitoring tool that reports real-time and historical data on what’s happening inside Oracle, and an analysis tool for forensic exploration of queries, events and resource usage. One of its capabilities is multi-dimensional workload analysis, which works like an MRI on your Oracle Database 12c environment.

Suppose the users on the northwest regional sales team are complaining that the book-to-bill report they run every week is suddenly taking 20 minutes to execute, when it normally takes less than a minute. How would you troubleshoot that? You’d think about all the possible problems with resource utilization and network outages, then probably start poking around in dashboards. You might get lucky and find the problem in short order or you could tie up a fat chunk of your time on it.

Now suppose Foglight PI is running on your Oracle instance. Since you know which report is causing the problem, drill into the Program that runs the book-to-bill report, check which SQL statements the report depends on and see whether any of them are hanging or hogging resources. Or, since you also know which users the problem affects, you could click on Consumer Groups, drill into the northwest regional sales group, see which databases the report runs against, examine the current sessions and figure out what’s bottlenecking the report.

Better yet, multi-dimensional workload analysis can compare the same dimensions at two different points in time, so you can store a baseline performance profile and compare chronologically.

With multi-dimensional workload analysis, any database administrator can run a kind of database MRI on the most sensitive areas of Oracle performance:

  • SQL statements
  • Databases (Oracle Database 12c)
  • PL/SQL blocks
  • Users and sessions
  • Locked objects
  • Command types
  • I/O-related dimensions (files, objects, disks)

An MRI of your Oracle database helps you find inefficient SQL statements or allocate more memory to your virtual server – the same way an MRI on your knee tells you that your ligaments have had enough of half-pipes and downhill skiing for now.

New tech brief on Foglight PI

Multi-dimensional workload analysis is just one of several new capabilities built into Performance Investigator. To cover all of them, we’ve put together a new tech brief called Optimize Your Databases with Far Less Effort using Foglight for Oracle’s Performance Investigator . Read it to find out more about Performance Investigator capabilities, such as I/O analysis, change tracking and execution plan analysis.

About the Author