Page History

Is the database idle, working, or bottlenecked?

When a bottleneck happens how can you know which of these problems are causing the problem? A bottleneck could be caused by:

...

All of these can be easily identified from SQL Query TunerDB Optimizer's performance profiling screen. Let's look at the components of the performance profiling screen.

...

1. Databases. For more information, see Databases.
2. AverageActiveSessionsAverage Active Sessions (AAS) LoadofselecteddatabaseLoad of selected database. For more information, see Average Active Sessions (AAS) Load of selected database.
3. MaximumCPUlineMaximum CPU line. For more information, see Maximum CPU line.
4. TopSQLTop SQL. For more information, see Top SQL, Top Bottlenecks, and Top Sessions.
5. TopBottlenecksTop Bottlenecks. For more information, see Top SQL, Top Bottlenecks, and Top Sessions.
6. TopSessionsTop Sessions. For more information, see Top SQL, Top Bottlenecks, and Top Sessions.

Anchor
databases databases

Databases

First, on top left, is a list of our databases we have registered.

Anchor
aas aas

...

Average Active Sessions (AAS)

...

Load of selected database

The most important part of the screen is the Average Active Sessions (AAS) graph. AAS shows the performance of the database measured in the single powerful unified metric AAS. AAS easily and quickly shows any performance bottlenecks on the database when compared to the Maximum CPU line. The Max CPU line is a yardstick for performance on the database. When AAS is larger than the Max CPU line there is a bottleneck on the database. Bottleneck identification is that easy. 

AAS or the average number or sessions active, shows how many sessions are active on average (over a 5 second range in SQL Query TunerDB Optimizer) and what the breakdown of their activity was. If all the users were running on CPU then the AAS bar is all green. If some users were running on CPU and some were doing IO, represented by blue, then the AAS bar will be partly green and partly blue.

Anchor
maxcpu maxcpu

...

Maximum CPU line

The line "Max CPU" represents the number of CPU processors on the machine. If we have one CPU then only one user can be running on the CPU at a time. If we have two CPUs then only 2 users can be on CPU at any instant in time. Of course users can go on and off the CPU extremely rapidly. When we talk about sessions on the CPU we are talking about the average number of sessions on CPU. A load of one session on the CPU thus would be an average which could represent one user who is consistently on the CPU or many users who are on the CPU for short time periods. When a CPU becomes a resource bottleneck on the database we will see the average active sessions in CPU state go over the Max CPU line. The number of sessions above the max CPU line is the average number of sessions waiting for CPU.

...

From looking at the previous chart the problem is a machine resource problem.

Anchor
topcpu topcpu

...

Top SQL,

...

Top Bottlenecks, and

...

Top Sessions

In order to know what the problem is, we have to find out where that demand is coming from. To find out where the demand is coming from we can look at Top SQL and Top Session tables below the load chart. In our case shown here the load is well distributed over all SQL in Top SQL and all sessions in Top Session. There is no outlier or resource hog. In this case it's the machine that's underpowered. What does a case look like where we should tune the application? The following screenshot depicts such a problem.

...

In this case we can see the load is higher than the Max CPU line but the load is coming from brown colored bars and the green CPU colored bars. If we look at Top SQL we see that there is only one SQL taking up almost all the load, but it's not because of CPU which would be a green bar, but some other color. What does this other color represent? We can look at the Top Bottleneck section and see that it is "log file switch (incomplete)" which basically means the log files are too small, the database is not correctly configured. This bottleneck can be resolved simply by increasing the log size.