X

An Oracle blog about Oracle Enterprise Manager and Oracle Management Cloud

  • Tips
    December 4, 2020

The Best Kept Secret for Database Performance Monitoring

Oracle Enterprise Manager product manager, and Diagnostics and Tuning expert, Kurt Engelieter is my guest blogger today.

In this post, I'll talk about the advantages of using ASH Analytics for monitoring database performance.

A good place to start is in the Performance Hub screen below, where I show a time window of active sessions, with 2 tables below showing top SQL ID on the left, and top user sessions on the right. This page shows what the previously popular Top Activities page provided. Let's go into some of the advantages of where ASH Analytics provides additional capabilities for monitoring and diagnosing database performance characteristics.

Time Picker

One advantage of ASH Analytics is having the interactive time window, the time-picker.  Use the time-picker to expand and contract the time range selected for your performance analysis.  You can interactively zoom out to several hours or days of data, then zoom in to one or two minutes of information depending on the direction of your investigation.

Figure 1. Time PickerFigure 1. Time Picker

Master-Detail Design

A key to using ASH Analytics is understanding the design.  The ASH Analytics screen is designed with a master-detail architecture. That means, for the time range you select in the time-picker, the corresponding time range is reflected in the center ‘Average Active Sessions’ graph. In addition, all the other charts on the page are updated to reflect your current time-picker selection. The lower two graphs, by default ‘Top SQL’ on the lower left, and ‘Top Sessions’ on the lower right also update to reflect the time range selected and any filtering done on the page.

ASH Analytics offers filtering on over 100 parameters

ASH Analytics provides analytics on a full set of ‘Active Session History’ data.   Whereas the Top Activity page had fixed parameters it pre-selected from the ASH dataset, ASH Analytics provides more choice, and the ability to change your choices as your investigation progresses.

Active Session History (ASH)

Every active database session is sampled once a second.   An active session is defined as a session that is using CPU, doing I/O or in a non-idle wait.   More than one hundred pieces of information are gathered about what each active session is doing every second.   To see the data gathered, use the v$active_session_history view in the database.

ASH Analytics Filters

ASH Analytics allows you to use menus to filter and zoom in on many of these ASH dimensions in the multi-tier drop-down boxes in the center of the screen by the ‘Average Active Sessions’ label.   In addition to the default ‘Wait Class’ view, you can view the data by ‘Wait Event’, ‘Instance’, ‘PDB’ among many, many others.

ASH DimensionsFigure 2. ASH Dimensions

An Example

If you click on the legend to the right of the Average Action Session graph, the data on the page is filtered to the corresponding parameter.   For instance, if you click on ‘CPU’ in the legend when the view is on ‘Wait Class’, then the data on the page is filtered to just display the ASH data for the samples in the time range that were on CPU when the session was sampled.    The Average Active Sessions chart is updated, along with the lower two charts.    This allows you to do a very targeted performance analysis.

Figure 3. ASH data filtered to CPU samplesFigure 3. ASH data filtered to CPU samples

A short demonstrationDemo on Youtube channelIn this demonstration, you'll see a quick demo from the Performance Hub showing ASH Analytics, and Realtime SQL monitoring. With ASH Analytics, you can start from a page that looks like Top Activity, and go so much further by selecting from a long list of the parameters available in ASH.

Summary

ASH Analytics is a powerful tool that allows you to investigate more than what was previously available with the "Top Activities" page. You have the flexibility to interactively select a wider timeline, then zoom-in as needed. You also have the interactive flexibility to filter on any of the parameters collected for the Active Session History database. Finally, the architecture provides graphical targeted performance analysis allowing you to zoom into specific areas of interest to gain a better understanding of your database performance.

You can find more detail in the webinar in the MOS note covering some background and more screen views highlighting use cases using ASH Analytics. You will also find a link to the presentation materials as well.

My Oracle Support (MOS) Note:

Advisor Webcast Recording: Enterprise Manager (EM) - New OEM JET Pages and Flash End Of Life held on November 19, 2020 [video] (Doc ID 2729947.1)

 presentation pdf on My Oracle Support [requires login]

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.