Top Activity Lite for efficient real-time database performance monitoring

November 3, 2022 | 8 minute read
Anusha Vojjola
Senior Product Manager
Prabhaker Gongloor
Vice President of Product Management
Text Size 100%:

Oracle Cloud Infrastructure Database Management Service now supports the Top Activity Lite feature! It is a simplified version of Performance Hub, optimized for quick response under heavy loads while providing key performance diagnostics information through simple and effective visualization. This feature helps DBAs monitor their database using a Network Operations Center (NOC) like screen.

Typically, such screens are used for Level 1 service desk triaging of performance issues. Top Activity Lite has been designed to provide optimal response time for real-time monitoring while providing all the information required for performance diagnosis on a single screen with easy visualization of issues through compact UI (less spacing and no scrolling down involved). Top Activity Lite has been implemented, taking into account the Level 1 triaging use cases and combining it with the power and flexibility of Performance Hub. This feature is available for on-premises and Cloud databases. 

Top Activity Lite page
Figure 1:  Top Activity Lite page

Key DBA use cases supported by the Top Activity Lite page

A DBA or L1 triage analyst or operator wants to monitor the performance of databases in real-time (the last 1 hour) in an efficient or performant manner with a single-page view of all the critical information required for diagnostics. The performance diagnostics provided on the single-page view should render well when displayed on a large screen or monitor without additional navigation to support the following:

  • Visualize information on the solution area to the problem (Wait Class Category)
  • Obtain details on Top SQLs and Top Sessions for the selected period
  • Envision SQL Monitoring details for the monitored SQL
  • Choose refresh frequency:  none, manual, or automatic refresh at intervals of 15 seconds, 30 seconds, or 60 seconds
  • Retain previously selected settings in the browser (across browser tabs/windows or browser sessions)

 

Top Activity Lite displays last-hour activity efficiently, even under heavy load

Top Activity Lite gives us a brief overview of the database activity over the last hour by default. It is designed to be highly performant and makes a single call to the database for a UI page refresh. This tailored implementation differs from Performance Hub, where multiple data calls are required to render the more extensive set of data exposed in the UI. This allows Top Activity Lite to render the page efficiently even for a database with a heavy load, such as with thousands of Average Active Sessions.

Top Activity Lite page can be accessed from the Performance Hub page via the "Top Activity Lite" button.

Top Activity Lite button
Figure 2:  Top Activity Lite button on the Performance Hub page

Auto Refresh provides the latest snapshot preference

Top Activity Lite supports real-time analysis for the last hour only, as shown in Figure 3.  The time zone selector provides three options: Browser, Database, and UTC (default). The top Activity Lite page provides the following page refresh options such as 15 seconds, 30 seconds, 60 seconds, and Off (default).  The grey slider displays the Top SQL and Top Session activity tables for the selected period. Whenever the page is auto or manually refreshed, the Top SQL and Session details sections are updated with the latest data for the last 5 minutes. The selected viewport can be moved to focus on a specific period, which will stop further updates of the top activity tables to facilitate detailed analysis.

Time Range and Auto Refresh of Top Activity Lite page
Figure 3:  Top Activity Lite page with Time Range and Auto Refresh options

 

ASH analytics and SQL monitoring tabs available on the Top Activity Lite page

The Top Activity Lite page provides two tabs - ASH Analytics and SQL Monitoring:

  1. ASH Analytics: This page displays the Top SQL and session information for the selected period.
    1. Although a few dimensions are not available in Top Activity Lite ASH Analytics as compared to standard Performance Hub (Perfhub) ASH Analytics, the vast majority of the rest of the ASH Analytics functionality is still present, including:
      • Filtering data by multiple dimensions
      • Sample resolution control
      • SQL tuning
      • SQL Details and Sessions Details drilldowns
  2. SQL Monitoring: This page gives a much more detailed understanding of a SQL that ran during a period. All the functionality of Performance Hub SQL Monitoring is present (with fewer data calls).
ASH Analytics and SQL Monitoring tabs
Figure 4:  ASH Analytics and SQL Monitoring tabs of Top Activity Lite

 

Time viewport/ Time slider function supports a 5-minute database activity fixed-span 

The highlighted area of the Average Active Sessions chart serves as a moveable viewport selector that controls the SQL statements and user sessions shown in the top activity tables below the chart. It has a fixed size of 5 minutes. By default, the viewport updates to cover the last five minutes of data. However, if moved, it will remain fixed in absolute terms (ex: 1:00 - 1:05 PM) as the chart updates to show the last hour of data. The top SQL statements and top sessions are updated to reflect the newly selected period when the viewport is dragged to the right or left.

The data shown in the activity tables will, by default, be refreshed when the activity chart is refreshed. If the customer drags the viewport to some historical time selection, then the activity table content will remain constant unless the customer moves the viewport selector. Otherwise, if auto-refresh is enabled, the activity tables will be updated approximately once an hour when the viewport position resets (to the right side of the chart after reaching the left side).

Viewport behavior changes as follows:

  • The viewport, by default, shows the last 5 minutes of data (as it is pinned to the far-right side of the chart) unless the customer moves it to a historical time range.
  • Moving the viewport back to the far-right side of the chart should pin it again.
  • If the viewport reaches the left side of the chart and rolls over, it should be auto-pinned again to the right side of the chart.
Time viewport or Time slider
Figure 5:  Time viewport/Time slider with a movable 5-minute window

 

SQL Monitoring tab is available to monitor the performance of SQLs

The SQL Monitoring tab of Top Activity Lite can be used to monitor long-running or parallel SQL statements. This page is similar to the SQL Monitoring page of Performance Hub.

SQL monitoring on Top Activity Lite page
Figure 6:  SQL Monitoring page, which shows the monitored SQLs

 

Differences between Performance Hub and Top Activity Lite

The notable differences between the Performance Hub page and the Top Activity Lite page are listed below.

No Attribute Performance Hub Top Activity Lite
1 Page view Detailed view for monitoring, historical and real-time monitoring Optimized view for real-time monitoring (last 1 hour)
2 Tabs Various tabs such as ADDM, Blocking Sessions, Workload, and Exadata, apart from ASH Analytics and SQL Monitoring Two tabs: ASH Analytics and SQL Monitoring. This is to keep the performance optimal
3 Time Range Flexible in choosing the time for monitoring Fixed to 1-hour time range
4 Time viewport/ Time slider Adjustable Fixed to 5 minutes
5 Auto Refresh Not available in the OCI Performance hub but is available in Enterprise Manager Cloud Control Performance Hub Available in 15, 30, and 60 seconds interval
6 Average Active Sessions (AAS) An overview chart for viewport selection combined with a child chart for the display of Average Active Sessions with ASH dimensions drill-down Combined chart for viewport selection and display of Average Active Sessions data with ASH dimensions drill-down. This is for compact UI purposes (no scroll down required to view the page through a single glance)
7 Reports AWR, ASH, and Performance Hub reports can be generated Only ASH report can be generated
8 Dimensions

It has an exhaustive dimension list, useful for detailed interactive performance analysis

There are limited dimensions on the page for maintaining fast response times. Some of the non-critical information is omitted on this page. For e.g., Client Host Name, ECID, Database Operation, etc.

 

Note: Top Activity Lite is not a one-to-one replacement for Enterprise Manager Cloud Control’s Top Activity which existed in previous releases and was deprecated due to its reliance on Flash technology. Top Activity Lite provides the best of both - it combines the rich capabilities of Performance Hub with optimizations for quick triaging by operators and compact visualization for performance diagnosis that existed before with Top Activity.

Conclusion

Top Activity Lite supports efficient real-time monitoring of database activity while providing all the information required for performance diagnosis on a single screen with easy visualization of issues. The Top Activity Lite page summarizes the overall database, and it's easy to interpret the issues with ASH analytics and SQL monitoring features within a period by using the Time viewport/Time slider and Auto Refresh option. It is an extremely useful tool for customers who want to get insights into the real-time database performance as it provides optimal page response time even under high load and can be used in NOC-like environments to project to large screens.

To learn more about Top Activity Lite, also watch the demonstration here.

Resources

Real-Time SQL Monitoring 

Available now: Performance Hub for on-premises databases (oracle.com)

The Best Kept Secret for Database Performance Monitoring (oracle.com)

Database Management Service - Top Activity Lite - YouTube

Anusha Vojjola

Senior Product Manager

Anusha is a Senior Product Manager working in Observability and Manageability team. Her focus of work is mainly with Performance of the database. Anusha has recently completed her Master's program in Engineering Management at Duke University in May'22.

Prabhaker Gongloor

Vice President of Product Management

GP works as Vice President of Product Management covering areas of Database Manageability, Enterprise Manager, and OCI Database Management and Operations Insights services.  GP has more than 20 years of industry experience spanning many roles - Developer, Consulting Technical Advisor - Centers of Expertise, and Consulting Product Manager. He holds Bachelor’s and Master's degrees in Computer Science and Engineering.


Previous Post

Integrating Oracle Cloud Guard with Logging Analytics using Streaming and Functions (Part 1)

Vishak Chittuvalapil | 8 min read

Next Post


Monitoring a TCPS enabled Oracle Database with Stack Monitoring

Aaron Rimel | 3 min read