Oracle Database 12c: EM Express Performance Hub


The Performance Hub is a feature in Oracle Enterprise Manager Database Express (EM Express) that provides a new consolidated view of all performance data for a given time range.  The user can select a time range using a timepicker at the top of the page, and the detail tabs will display the available performance data for the selected time range.

The Performance Hub can also be saved as a Composite Active Report.  For more information, see the section on Composite Active Reports.

You will need the Oracle Diagnostics Pack to use the Performance Hub.

EM Express Performance Hub
 

 

The Performance Hub is designed in a master-detail format, where the timepicker at the top of the page drives the contents of the details displayed in the tabs below.  The timepicker displays average active sessions over time, and if there are peaks in the timepicker, the user can move the selected time range to the period of interest to get more information.

The Performance Hub can be used to view both historical and realtime data. 

In realtime mode, performance data is retrieved from in-memory views.  The timepicker shows data for the past hour and the user can select any time range from within this period.  The default selection is the past 5 minutes.

In historical mode, data is retrieved from the Automatic Workload Repository (AWR).  The user can select any time period, provided there is sufficient data in AWR.  When the user switches to historical mode, the default selected time range is dependent on the amount of data shown in the timepicker: if the timepicker displays data for the past week, the default selected time range is one day; and if the timepicker displays data for the past day, the default selected time range is one hour.

The Performance Hub organizes performance data by dividing it into different tabs.  Each tab addresses a specific aspect of database performance.  Details for what each tab displays can be found below:


  • Summary - this tab is available in both realtime and historical mode.  In realtime mode, this tab shows metrics data that gives an overview of system performance in terms of Host Resource Consumption (CPU, I/O and Memory), and Average Active Sessions.  In historical mode, the tab displays system performance in terms of resource consumption, average active sessions, and load profile information.
  •  
  • Activity - this tab displays ASH Analytics, and is available in both realtime and historical mode.
  •  
  • Workload - this tab is available in both realtime and historical mode, and shows metric information about the workload profile, such as call rates, logon rate and the number of sessions.  In addition, the tab also displays the Top SQL for the selected time range.  In realtime mode, the tab displays Top SQL only by DB time, but in historical mode, the user can also display Top SQL by other metrics, such as CPU time or Executions.
  •  
  • RAC - this tab is only available in RAC.  It displays RAC-specific metrics such as the number of global cache blocks received, and the average block latency.
  •  
  • Monitored SQL - this tab displays Monitored Executions of SQL, PL/SQL and Database Operations, and is available in both realtime and historical mode.
  •  
  • ADDM - this tab is available in both realtime and historical mode.  It displays ADDM and Automatic Real Time ADDM reports.
  •  
  • Current ADDM Findings - this tab is available only in realtime mode, and displays a realtime analysis of system performance for the past 5 minutes.  The contents of this tab are populated only if the time selector includes the current time, and the findings displayed are always for the past 5 minutes regardless of the length of the selected time range.


The user can drill down from the Performance Hub to other pages to get more information.  A user can view Monitored Executions, ADDM reports, as well as the SQL Details and the Session Details pages.


Performance Hub Active Report Examples


Real Time Performance Hub
Historical Performance Hub 
Automatic Real Time ADDM Active Report
Performance Hub (with RTADDM icons)
Real Time Performance Hub (RAC)
   
SQL Details Active Report
SQL Details Active Report  (PL/SQL)
Session Details Active Report
Session Details Active Report (PQ)


Frequently Asked Questions



Answers



Why does the Performance Hub show different tabs in realtime and historical mode?

In realtime mode, the Performance Hub only displays performance data for the past hour.  This data is retrieved from in-memory views and gives more granularity so that minute by minute changes can be displayed.

In historical mode, performance data is retrieved from AWR, which means that minute by minute changes are not available since most of the data is based on AWR snapshots.  However, historical mode does give us access to some additional information. For example, in realtime mode, the Workload tab only displays Top SQL by DB time, but in historical mode, it can also display Top SQL by other metrics, such as CPU time or Executions.

Other statistics that are available only in historical mode can be found in the Database Time, Resources, and System Statistics tabs. These tabs are only available in historical mode.


Why is the timepicker in the Performance Hub different from the timepicker SQL Details?

 

The timepicker component is context sensitive.  In Performance Hub, it shows average active sessions for the entire database, but in SQL Details, it shows average active sessions only for the SQL statement in question (the average active sessions are broken down by CPU, I/O, Wait and recursive calls).  Similarly, in Session Details, the timepicker shows activity for the selected session including any parallel query slaves that were used by the session.

In both SQL Details and Session Details, total system activity is indicated by the gray line.


What are "Current ADDM Findings"?

The "Current ADDM Findings" tab is available only in realtime mode, and displays a realtime analysis of system performance for the past 5 minutes.  The contents of this tab are populated only if the time selector includes the current time, and the findings displayed are always for the past 5 minutes regardless of the length of the selected time range.


In realtime mode, some tabs show information for the entire hour instead of the selected time range.  Why?

If the data shown in the charts is not for the selected time range, the charts display a gray box that indicates the selected time range.  In realtime mode, it is not always feasible to display data for the selected time range as enough data might not be available.

In realtime mode, the charts in the Summary, Workload, and RAC tabs show data for the entire hour, with gray boxes indicating the selected time range, but the Top SQL table in the Workload tab and the Instances table in the RAC tab only display data for the selected time range.  The ADDM tab shows all ADDM tasks for the entire hour, as there is typically only a single ADDM task in the past hour.

The Activity and Monitored SQL tabs always shows data for the selected time range.


Can I resize the selected time range from the charts?

No. The Performance Hub has been designed in a master-detail format, and the time selection can only be done from the timepicker at the top of the page.  The gray boxes in the charts are meant to highlight the time range selected in cases where the charts display more data than the selected time range.


What is the difference between SQL Details and SQL Monitor Details?

SQL Monitor monitors a single execution of a SQL statement, and displays detailed information about that execution including statistics and activity per plan line.

SQL Details, on the other hand, displays information for a given sql_id aggregated across all executions in a given time range; the SQL may have been executed multiple times, and may have multiple plans.  A list of monitored executions is also available along with any SQL Plans or SQL Profiles for the SQL statement.  The SQL Details page also shows activity per plan line aggregated across all executions, but does not have the detailed statistics available in SQL Monitor.


Why is the average active sessions in the Summary tab different from that in the Activity tab?

In realtime mode, the information in the Summary tab is based on database metrics measured at 1 minute intervals.  The Activity tab, on the other hand, leverages ASH data, which is sampled every second. The peaks that may show up in ASH may not be as pronounced when averaged over a minute.

Discrepancies in average active sessions data can also be caused by database instrumentation, as metric data is measured while ASH data is sampled.  For example, if the host is cpu bound, metrics will measure the actual time spent on CPU, but ASH will not distinguish between CPU and run queue (CPU + Wait for CPU). 

This behavior can also be observed when viewing the Performance Page and Top Activity (or ASH Analytics) in Enterprise Manager Cloud Control.


I just navigated from the Performance Hub to a different page.  How can I go back?

Simply click the browser back button and it will take you to the previous page.  Alternatively, you can use the menu to go back; simply go to the Performance menu and click Performance Hub.  Using the back button will maintain the page context when you go back (time selection, selected tab, etc.), while using the menu will take you to the Performance Hub with the default selections.

In general, navigation in EM Express can be done using the menu and the browser buttons.


What are the icons shown on the timepicker?

The timepicker icons are an attempt to show interesting events on a timeline.  These events include instance startups, ADDM reports and Real Time ADDM reports.  Usually, an icon represents a single event, but if the same event is seen to occur several times during a small window of time, a single icon can also represent several occurrences of an event.  Use the icon tooltip to distinguish between the two cases.
  


Do I need any licenses or packs to use the Performance Hub?

Yes.  In order to use the Performance Hub, you will need the Diagnostics Pack.  If you want to view SQL Monitor data, you will need the Tuning Pack as well.


Why does SQL Details have both an Activity Tab and an Activity Panel in the Summary Tab?

The Activity Panel in the Summary Tab is displayed only when viewing the SQL Details of a PL/SQL statement.  It shows other SQL statements sampled in ASH that were executed by the PL/SQL; these SQL statements are identified by ASH rows where the top_level_sql_id column is set to the sql_id of interest and the sql_id column is set to a different value.

The Activity Tab shows ASH data for the sql_id of interest.  This data does not take into account other SQL statements that may have been executed by the SQL of interest.

Click here for an example.

 


In SQL Details and Session Details, why does the timepicker show higher load than the Activity Tab?

As explained here, there are different sources of data which can account for differences.

For SQL Details, the timepicker takes into account SQL statements executed on behalf of the SQL of interest.  For example, when the SQL of interest is a PL/SQL call, there may be other SQL statements executed on behalf of that PL/SQL call.  The Activity tab, however, only shows activity for the SQL of interest.

Similarly, for Session Details, the timepicker takes into account PQ, and shows the activity of all the PQ slaves active on behalf of the session of interest.  The Activity Tab, however, is restricted to viewing only the session of interest.  Click her for an example.


I clicked on a Real Time ADDM report and can no longer move the timepicker.  Why?

An Automatic Real Time ADDM report is essentially a Performance Hub active report that was automatically generated by Real Time ADDM when it detected potential issues on your system.  Since you can't move the timepicker in a Performance Hub active report, you can't move the timepicker in a Real Time ADDM report either.

To go back from a Real Time ADDM report to the Performance Hub, use the browser back button or the menu.