Monitoring Database Performance Using Performance Hub Report (Doc ID 2436566.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.
PURPOSE
Purpose of this document is to explain the new feature called Performance Hub from 12.1 onwards.
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 Database Express page, and the detail tabs will display the available performance data for the selected time range. This EM express page can be generated without logging into Database Express as active database report called Performance Hub Report.
DBAs can generate and check the active real time performance hub report that will have summary information on overall IO, CPU and Memory usage by the DB and would give clear picture using HTML interface.
The Performance Hub Report is very useful to diagnose the performance problems effectively using the holistic information it contains.
REMEMBER: Usage of Performance Hub requires Oracle Diagnostics + Tuning pack license.
Refer Options and Packs
SCOPE
For DBAs or Users using Database 12.1 onwards.
DETAILS
- The Performance Hub Report can be generated both in Single-Instance Database and RAC.
- The report can be generated in real-time or historical mode depending upon the range of time interval.
- The Performance Hub Report can be manually generated as Database Active Report without using DB Express using the following command:
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
Example:
SQL> conn <USERNAME>/<Password> as sysdba
SQL> @?/rdbms/admin/perfhubrpt.sql
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ 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]: <<<<<<<<<<<<<<< Select typical or all
Using typical for report level
Available Databases and Instances. <<<<<<<<<<<<<< Select the Database
The database with * is current database
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance
------------ -------- ------------ ------------
* 1975797631 1 X12102 x12102
Specify the database ID
~~~~~~~~~~~~~~~~~~~~~~~
Please enter database ID [1975797631]:
Using 1975797631 for database ID
Specify the Instance Number
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Single Instance Database. Please press Enter
Default Start Time Default End Time AWR Start Time
------------------- ------------------- -------------------
08/16/2018 23:35:49 08/16/2018 23:40:49 08/08/2018 16:00:57
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 [08/16/2018 23:35:49]: -1:00 <<<<<<<<<<<<< Specify start time as -1:00 (sysdate -1 hr)
Please enter end time [08/16/2018 23:40:49]: <<<<<<<<<<<<<< Leave it as null to generate the real time report for the last 1 hour
Generating report for 08/16/2018 22:40:49 - 08/16/2018 23:40:49
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
Please enter report name [perfhub_rt_08162340.html]:
Generating report perfhub_rt_08162340.html ...
Report written to perfhub_rt_08162340.html
- Performance Hub Report provides single unified view of DB performance.
- New interactive report for analyzing AWR data.
- It is RAC, Exadata and Multitenant aware.
- The Performance Hub report can be used to view both historical and real-time data.
- Supports historical view of SQL Monitoring reports.
In real-time mode, performance data is retrieved from in-memory views. 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. The default selected time range is one day;
The Performance Hub organizes performance data by dividing it into 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.
In addition to DB performance metrics, it also collects the SQL details for the top SQLs which would be very useful to get the SQL execution metrics.
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. Clicking the SQL_ID under Activity tab of Performance hub report displays the SQL details page for that particular SQL_ID.
