Performance Hub Report

Hi Guys,
In case you do not have Oracle Enterprise Manager or Grid Cloud Control and want to have almost similar features for Oracle monitoring purpose, Performance Hub Reports can help you up to a great extent.

Performance Hub report helps to view all performance data available for a specified time period. Various tabs are available in the Performance Hub, depending on whether real-time or historical data is selected for the time period. When real-time data is selected, more granular data is presented, because real-time data for the last hour is displayed. When historical data is selected, more detailed data is presented, but the data points are averaged out to the Automatic Workload Repository (AWR) interval for the selected time period.

To use the performance hub report, underlying Oracle must be at least 12c. Following is the way how to generate this report.

1.        At the SQL prompt, enter:

             @?/$ORACLE_HOME/rdbms/admin/perfhubrpt.sql

2.        Specify the desired report type:

              Please enter report type: typical <all, and basic options also available>

3.        Enter the value for the database identifier of the database you want to use:

               Please enter database ID: 158275777

4.        Enter the value for the instance number of the database instance you want to use:

               Please enter instance number: all instances

5.        Enter the desired time period by specifying an end time and start time in the format of dd:mm:yyyy hh:mi:ss:

               Please enter start time [05/07/2019 12:01:59]: -24:00

               Please enter end time [05/07/2019 12:06:59]: -2:00

6.        Enter a report name, or accept the default report name:

               Enter value for report_name: perfhub_ht_05071010.html

Here, a Performance Hub active report named perfhub_ht_05071010.html is generated on all database instances with a database ID value of 158275777 for the specified time period from 05/06/2019 12:10:34 to 05/07/2019 10:10:34.

SQL> @?/rdbms/admin/perfhubrpt

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~                   PERFHUB ACTIVE REPORT                              ~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~  This script will generate PerfHub active report according to        ~

~  users input.  The script will prompt users for the                  ~

~  following information:                                              ~

~     (1) report level: basic, typical or all                          ~

~     (2) dbid                                                         ~

~     (3) instance id                                                  ~

~     (4) selected time range                                          ~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the report level

~~~~~~~~~~~~~~~~~~~~~~~~

*  Please enter basic, typical or all

*  Report level "basic" - include tab contents but no further details

*  Report level "typical" - include tab contents with details for top

*                           SQL statements

*  Report level "all" - include tab contents with details for all SQL

*                       statements

Please enter report level [typical]: all

Using all for report level

Available Databases and Instances.

The database with * is current database

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DB Id     Inst Num DB Name      Instance

------------ -------- ------------ ------------

* 158275777         1 DB_NAME          DB_NAME

Specify the database ID

~~~~~~~~~~~~~~~~~~~~~~~

Please enter database ID [ 158275777]:

Using  158275777 for database ID

Specify the Instance Number

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Single Instance Database. Please press Enter

Default Start Time  Default End Time    AWR Start Time

------------------- ------------------- -------------------

05/07/2019 12:05:18 05/07/2019 12:10:18 11/08/2018 02:00:24

Specify selected time range

~~~~~~~~~~~~~~~~~~~~~~~~~~~

*  If the selected time range is in the past hour, report data will be

*    retrieved from V$ views.

*  If the selected time range is over 1 hour ago, report data

*    will be retrieved from AWR.

*  If the dbid selected is not for the current database, only AWR data

*    is available.

*

*  Valid input formats:

*      To specify absolute time:

*        [mm/dd[/yyyy]] hh24:mi[:ss]

*        Examples: 02/23/14 14:30:15

*                  02/23 14:30:15

*                  14:30:15

*                  14:30

*      To specify relative time: (start with '-' sign)

*        -[hh24:]mi

*        Examples: -1:15  (SYSDATE - 1 Hr 15 Mins)

*                  -25    (SYSDATE - 25 Mins)

Please enter start time [05/07/2019 12:05:18]: -24:00

Please enter end time [05/07/2019 12:10:18]: -2:00

Generating report for 05/06/2019 12:10:34 - 05/07/2019 10:10:34

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

Please enter report name [perfhub_ht_05071010.html]:

Generating report perfhub_ht_05071010.html ...

Report written to perfhub_ht_05071010.html

Some of the sample tabs from the generated performance hub report.

The Performance Hub shows performance data into various different tabs. Each tab addresses a specific aspect of database performance. Details for each tab display 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. Clicking a SQL_ID displays the SQL Details page with more information about that SQL statement.

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.

Database Time - this tab is available in historical mode only and enables to view wait events by category for various metrics, and to view time statistics for various metrics for the selected time period.

Resources - this tab is available only in historical mode only and enables to view operating system resource usage statistics, I/O resource usage statistics, and memory usage statistics for the selected time period.

System Statistics - this tab is available only in historical mode only and enables to view database statistics by value, per transaction, or per second for the selected time period.

For more information, please refer below MOS notes.

• Monitoring Database Performance Using Performance Hub Report (Doc ID 2436566.1)
• Not Able to Select Historical Time in Perfhubrpt to Show Report of 24 Hours (Doc ID 2205403.1)

Please write your comments if this helped you and let me know, if I can help you further.