Page History

...

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

The screen has six the following important partsareas. 

1. Databases. For more information, see Databases.
2. Average Active Sessions (AAS) Load of selected database. For more information, see Average Active Sessions (AAS) Load of selected database.
3. Maximum CPU line. For more information, see Maximum CPU line.
4. Top SQL. For more information, see Top SQL, Top Bottlenecks, and Top Sessions.
5. Top Bottlenecks. For more information, see Top SQL, Top Bottlenecks, and Top Sessions.
6. Top Sessions. For more information, see 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.

Image Modified

In this case, again the CPU demand is more than the machine can supply but if we look at "Top SQL" we can see that the first SQL statement (with the large green bar) uses up much more CPU than any of the rest, actually 60%! If we could get it down to 10% CPU then we'd save 50% of the CPU usage on the machine! Thus in this case it's worth our while to spend a day or week or even a couple weeks trying to tune that one SQL statement instead of buying a bigger machine. 

Finally, how do we know when the database configuration is a problem? We know it's a configuration problem when we are seeing something other than CPU as the bottleneck in Top Bottleneck section. Here's an example 

Image Modified

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.

...