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,
|
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
|
|
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
No comments:
Post a Comment