Hi, my name is Tim Fritz, and I’m a database solution architect at Quest Software. I get asked often by customers for tips on solving database-related performance problems more quickly - both proactively (before customers are impacted) and reactively (after an alert). In both cases, DBAs and others need to find offensive users and SQL causing the slowdowns to focus their tuning efforts as quickly as possible. Foglight Performance Analysis has many features that help with this, but one that is often overlooked is QuickCompare.
With Foglight Database Performance Analysis QuickCompare, you can very quickly choose the Performance Analysis ‘dimensions’ (instance, program, user, all SQL, etc.) and time ranges (or baseline) to compare. The result is a formatted list of the dimensional changes and the highlights of largest changes in active time, CPU usage and wait , and I/O usage and wait. These are important clues, obtained with a few mouse clicks in Performance Analysis, which will aid everyone responsible for fixing a performance problem.
Here are three common uses for QuickCompare. You will undoubtedly find many others as you look for ways to accelerate problem resolution.
1. Monitor for database workload problems
- Be more proactive – look for problems that are building before they affect customers
- Respond to alerts, possibly from Foglight or whatever 24x7 monitor solution you are using
- Here’s a an example scenario:
The DBA team gets an alert that Oracle logins on an application instance are taking a very long time. Some people are trying to connect to run some critical queries. Since the problem that is happening today was not happening yesterday, we will compare the workloads from today on this instance with yesterday’s workloads.
Performance Analysis shows if changing Oracle workloads are the culprit. Using QuickCompare, we will see the movement of resource consumption and wait times (up or down) over time. And we will see exactly which users, programs, and specific SQL statements have changed the most – clues to identifying our root cause of the bottlenecks happening on the instance today. Here is an example of the QuickCompare output:
The degree of change will be important, too. If no significant changes are seen on the QuickCompare report, we are less certain that Oracle is really causing our application to slow down. If there are significant changes seen between the dimensions and time ranges chosen for the comparison, those highlights will be clearly displayed, pointing us in the right direction to get this problem resolved for good.
Similarly, you can use this same type of time-range comparison to assess your database and system tuning efforts. After you REORG a problem table or add that new index, be proactive and compare the workloads (resources being consumed, wait times occurring) with those before the tuning. Have things indeed improved as you had hoped? Or have new problems arisen?
2. Troubleshoot a performance problem
- Find the cause quickly
- Choose a non-problematic time frame to compare with
- Here’s an example scenario:
A severe lock wait problem has been detected this afternoon. This is not a problem we have very often, but when it happens, the application can come to a halt for many users. Performance Analysis will allow us to quickly see any new workload components (programs, SQL statements, e.g.) that are likely causing our problem. We can compare the time period that the problem has been appearing (our monitor, Foglight, alerted us) with the hours this morning before the problem arose. We see a user that is running things during the blocking situation that wasn’t running things before. Here, the active run times for SELECT and DELETE statements are much higher during the blocking time:
We will be able to use Performance Analysis to investigate this MEDREC user’s workloads between the morning and the afternoon. Perhaps we will see statements running in the afternoon that were not running in the morning – pertinent clues, found in seconds, to focus on as we resolve this blocking problem and try to keep it from happening again.
3. Respond to a sudden system slowdown
- Determine quickly if Oracle workloads have changed
- Whatever has changed is a candidate as our root cause
- Use Performance Analysis baseline for comparison
- Here’s an example scenario:
We are, indeed, seeing several database-related alerts from our monitor about high resource consumption and wait times on our Oracle instance. Rather than investigate each alert and each baseline deviation and advisory produced by Foglight Performance Analysis, we will first use QuickCompare to see exactly what has changed in the Oracle workloads. Whatever has changed may be our cause of slowdowns in the application. QuickCompare will take just a few mouse clicks to find these differences.
In the screen shot above we can see that today total CPU usage for workloads are higher than the baseline (calculated expected range), Oracle CPU usage is higher, and the number of SQL executions is up. From here, we can investigate SQL more closely with Performance Analysis. To do so, start the comparison at the “SQL Statement” dimension rather than the “Instance” dimension, as shown below:
On the following screen, I have expanded the details of ‘Active Time’ on program sqlplus.exe. It looks like there may be more ad hoc queries executing today.
I/O details point to sqlplus as well:
Investigating the workload details for sqlplus.exe during this time period, we see much higher logical and physical reads, and we see exactly which SQL statement was being executed that did so much more I/O today than previously:
Now we can focus on that particular statement within Performance Analysis and see if the execution plan has changed, or if there is some other reason for so much I/O and CPU usage today. If the plan has changed, maybe we can tune the indexes for new workload demands, for example.
To summarize, I described three use cases where the QuickCompare feature of Foglight Performance Analysis will help you uncover clues to the root causes of performance problems more quickly. This feature is part of Performance Analysis, so get the most out of the product and work smarter and faster with QuickCompare!
To help you learn more about QuickCompare, and other features of Performance Analysis, please visit the Quest Software website using one of these links: