Tuesday, May 3, 2016

Oracle Database 12c: EM Express Performance Hub

Oracle Database 12c: EM Express Performance Hub




The Performance Hub is the new feature of Oracle Database 12c. This provides a new consolidated view of all performance data in single page. The user only needs to select a time range using timepicker at the top of Enterprise Manager page and the details tabs will the performance data for that time period.

Two Modes to generate Performance Hub Report.
1) Real-time Mode
2) Historical Mode


In real-time mode, the Performance Hub only displays performance data for the past hour.  This data is retrieved from in-memory views and gives more granularities 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 real-time 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.


Performance Hub report can be generated in Two ways,
1)      Using Enterprise Manger
2)      Using SQL Script (Similar to AWR report.)



The Performance Hub organizes performance data by dividing it into different tabs.  Each tab addresses a specific aspect of database performance.



Performance Hub Tab Name
Description
Summary
The Summary tab provides an overall view of the performance of the system for the specified time period.

It will show the below things,
  • Load Profile
  • Active Sessions
  • Host CPU
  • I/O Requests
  • Memory
  • Wait Class

RAC
·         The RAC tab appears only when EM Express is being used with an Oracle RAC database (or cluster database).
·         This tab shows global cache activity information and a breakdown of activity (average active sessions) and resource usage (CPU, I/O, memory) per instance.
Activity
  • It allows detailed drilldown into average active sessions for ASH over the selected time period.
  • It allows detailed drill-down into average active sessions for ASH over the selected time period.
  • You can view the SQL statements and user sessions that had the top average active session activity.
Workload
·         You can view the SQL statements and user sessions that had the top average active session activity.
·         The Sessions chart show the logon rate, current logons and open cursors.
·         It also shows Top SQLs, by clicking a SQL_ID displays the SQL Details page with more information about that SQL statement.
Monitored SQL
·         Shows information about monitored SQL statements that were executing or that completed during the selected time period.

·         The table displays information about monitored SQL statement executions. If there is a green spinning icon in the Status column, then the monitored statement did not complete during the selected time period. A red cross indicates that the SQL did not complete either due to an error or due to the session getting terminated. If there is a check mark in the Status column, then the statement completed its execution during the selected time period.

·         SQL statements are monitored only if they have consumed at least 5 seconds of CPU or I/O time.

·         It shows, SQL ID, its SQL plan hash, user, serial or parallel statement, Database time, I/O, or other , SQL statement.
ADDM
The ADDM tab shows the performance findings and recommendations that have been found by Automatic Database Diagnostics Monitor (ADDM) for tasks performed in the database during the selected time period.
Current ADDM Findings
The Current ADDM Findings tab enables you to view Real-time ADDM findings for the past five minutes.
Database Time
The Database Time tab enables you to view wait events by category for various metrics, and to view time statistics for various metrics for the selected time period.
Resources
The Resources tab enables you to view operating system resource usage statistics, I/O resource usage statistics, and memory usage statistics for the selected time period.
System Statistics
The System Statistics tab enables you to view database statistics by value, per transaction, or per second for the selected time period.
Exadata
Navigate to Exadata Detail page you can see below tabs
Per flash type, the total I/O requests and the total I/O throughput is shown on the charts.
Per hard disk type, the total I/O requests and the total I/O throughput is shown on the charts.
The CPU metric indicates the average CPU usage across all cells.

  he I/O metrics indicate the average I/O performance across all disks for the given disk type.  Disk types with a mean I/O latency of 0 are not shown.


Here I am listing how to generate Performance Hub report using SQL Script.
Below is the listing for the same.



SYS@ORCL121>  @$ORACLE_HOME/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]: all

Using all for report level

Available Databases and Instances.
The database with * is current database
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
   DB Id     Inst Num DB Name      Instance
------------ -------- ------------ ------------
  1945550938        2 ORCL12        ORCL122
* 1945550938        1 ORCL12        ORCL121

Specify the database ID
~~~~~~~~~~~~~~~~~~~~~~~
Please enter database ID [1945550938]:

Using 1945550938 for database ID

Specify the Instance Number
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please enter instance number [all instances]:

Using all instances for instance
Default Start Time  Default End Time    AWR Start Time
------------------- ------------------- -------------------
04/29/2016 02:09:36 04/29/2016 02:14:36 04/20/2016 03:30:33

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 [04/29/2016 02:09:36]: 04/27/2016 06:00:00
Please enter end time [04/29/2016 02:14:36]: 04/27/2016 07:00:00
Generating report for 04/27/2016 06:00:00 - 04/27/2016 07:00:00

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
Please enter report name [perfhub_ht_04270700.html]:
Generating report perfhub_ht_04270700.html ...

Report written to perfhub_ht_04270700.html

SYS@ORCL121>

 Few Screenshots of Performance Hub report.














Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert
Post a Comment