9 Generating Performance Hub Active Report

Performance Hub feature of EM provides an active report with a consolidated view of all performance data for a specified time period. The report is fully interactive; its contents are saved in a HTML file, which you can access offline using a web browser.

See Also: Oracle Database 2 Day DBA for more information about Performance Hub feature of EM.

This section describes how to generate Performance Hub active report and contains the following topics:

• Overview of Performance Hub Active Report

• Command-Line User Interface for Generating a Performance Hub Active Report

• Generating a Performance Hub Active Report Using a SQL Script

• Performance Workload Analysis

Overview of Performance Hub Active Report

Performance Hub active report enables you to view all performance data available for a time period that you specify. Different 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.

This section describes Performance Hub active report and contains the following topics:

• About Performance Hub Active Report Tabs

• About Performance Hub Active Report Types

About Performance Hub Active Report Tabs

Performance Hub active report contains interactive tabs that enable you to view and navigate through performance data categorized into various performance areas.

The tabs contained in a Performance Hub active report include the following:

• Summary

  The Summary tab provides an overview of system performance, including resource consumption, average active sessions, and load profile information. This tab is available for real-time data as well as historical data.

• Activity

  The Activity tab displays ASH analytics. This tab is available for real-time data as well as historical data.

• Workload

  The Workload tab displays metric information about the workload profile, such as call rates, logon rate, and top SQL. This tab is available for real-time data as well as historical data.

• RAC

  The RAC tab displays metrics specific to Oracle RAC, such as the number of global cache blocks received and the average block latency. This tab is only available in Oracle RAC environments. This tab is available for real-time data as well as historical data.

• Monitored SQL

  The Monitored SQL tab displays information about monitored SQL statements. This tab is available for real-time data as well as historical data.

• ADDM

  The ADDM tab displays information for ADDM analysis tasks and Real-Time ADDM analysis reports. This tab is available for real-time data as well as historical data.

• Current ADDM Findings

  The Current ADDM Findings tab displays a real-time analysis of system performance for the past 5 minutes. This tab is only available if the specified time period for the Performance Hub active report is within the past hour. This tab is available only for real-time data.

• Database time

  The Database Time tab displays wait events by category for various metrics. This tab is available only for historical data.

• Resources

  The Resources tab displays operating system and I/O usage statistics. This tab is available only for historical data.

• System Statistics

  The System Statistics tab displays database and system statistics. This tab is available only for historical data.

About Performance Hub Active Report Types

You can choose the level of details displayed within each tab of the Performance Hub active report by selecting the report type.

The available report types for the Performance Hub active report include the following:

• Basic

  Only the basic information for all the tabs is saved to the report.

• Typical

  In addition to the information saved in the basic report type, the SQL Monitor information for the top SQL statements contained in the Monitored SQL tab and the ADDM reports are saved to the report.

• All

  In addition to the information saved in the typical report type, the SQL Monitor information for all SQL statements contained in the Monitored SQL tab and all detailed reports for all tabs are saved to the report.

Command-Line User Interface for Generating a Performance Hub Active Report

You can generate a Performance Hub active report using the command-line interface in one of two ways:

• Using a SQL script, as described in “Generating a Performance Hub Active Report Using a SQL Script”.

• Using the DBMS_PERF package, as described in Oracle Database PL/SQL Packages and Types Reference.

Generating a Performance Hub Active Report Using a SQL Script

This section describes how to generate Performance Hub active report by running the perfhubrpt.sql SQL script in the command-line interface. The DBA role is required to run this script.

To generate a Performance Hub active report:

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/perfhubrpt.sql

2. Specify the desired report type:

   Please enter report type: typical

   For information about the available report types, see “About Performance Hub Active Report Types”.

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

   Please enter database ID: 3309173529

   To use the local database, enter a null value (the default value). If you specify a database identifier for a database other than the local database, then the Performance Hub active report is generated from imported AWR data.

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

   Please enter instance number: all instances

   To specify all instances, enter all instances (the default value).

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

   Please enter end time in format of dd:mm:yyyy hh24:mi:ss: 03:04:2014 17:00:00 Please enter start time in format of dd:mm:yyyy hh24:mi:ss: 03:04:2014 16:00:00

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

   Enter value for report_name: my_perfhub_report.html

   In this example, a Performance Hub active report named my_perfhub_report is generated on all database instances with a database ID value of 3309173529 for the specified time period from 4:00 p.m. to 5:00 p.m on April 3, 2014.

Performance Workload Analysis

Workload Analysis

The Workload Analysis is a tool to quickly identify issues with the workload by comparing a given workload with another reference, which could be fixed or rolling. The analysis itself can be a one time analysis, for instance comparing it two periods, or the analysis can be a re-occurring task that is scheduled with various flexible parameters. The time period for the analysis is selected from the View Data drop-down with the default ranges of Last 24 Hours, Last 7 Days, Last 30 Days, and All.

Scheduled Analysis

The Scheduled Analysis tab of the Workload Analysis page displays five boxes with counters to alert the number of differences to:

• Changed Workloads

• Monitored Workloads

• Regressed Metrics

• Improved Metrics

• Unchanged Metrics

The counter overview provides insight into how changes to workloads improved or regressed for the time period.

The Create Analysis Task for the Scheduled Analysis requires that you to provide additional information about the re-occurring task. Here are some settings, some of them optional, needed for establishing a re-occurring analysis in the Workload Capture form.

• SQL Tuning Set Name Prefix

• Load SQL Statements using AWR Snapshot,

  • Specify Custom Time Range with a Start Time and End Time

  • Quick Select from Snapshots created in the past with specified units of hours or days, and only up to 7 days

  Total number of SQL Statements Captured

  • Capture All

  • Capture Top N

• Filter Option

• Workload Comparison

  • Subsequent Comparisons: Compare using a rolling reference, or compare using a fixed reference

  • Optional Initial reference workload

  • Comparison Metric such as CPU Time, Elapsed Time, Buffer Gets, Disk Reads, Direct Writes, Physical I/Os, and Optimizer Cost.

  • Change Threshold (%)

  • Consumer Resource Group

  • Days to retain purge results

• Schedule

  • Time zone: Local (America/Denver), Database (GMT), or UTC

    Start Date: Immediate, or Later specified with date field

    End Date: None, or Specified Date

    Repeat Every: Select units first.

    • (1-24) Hours

    • (1-7) Days

    • (1-4) Weeks

    • (1-12) Months

    Repeat On:Specified Date, or Custom Time

When an analysis task is created, it appears under the Scheduled Analysis. The arrow next to the Analysis name in the table expands to a list of various instances when that scheduled analysis was run and links to the resulting Comparison Report (or Workload Analysis Report)

One-Time Analysis

The One-Time Analysis tab of the Workload Analysis page displays five boxes with counters to alert the number of differences to:

• Analysis With Difference

• Total Analyses

• Regressed Metrics

• Improved Metrics

• Unchanged Metrics

The counter overview provides insight into how changes to workloads improved or regressed for the time period.

Create Analysis Task is similar for One-Time Analysis. The task requires

• A unique name for the task

• Select the Reference Workload by name or searching for them.

• Select the Compared Workload by name or searching for them.

• The task needs one or more comparison metrics,such as CPU Time, Elapsed Time, Buffer Gets, Disk Reads, Direct Writes, Physical I/Os, and Optimizer Cost.

When an analysis task is created, it appears under the One-Time Analysis. The arrow next to the Analysis name in the table expands to a list of various instances when that scheduled analysis was run and links to the resulting Comparison Report (or Workload Analysis Report)

Workload Analysis Report

The Workload Analysis Report is available after an analysis task has been run, whether regularly or one-time. Each analysis has its own report.

The information in the report varies according to the criteria established from Create Analysis Task. The reports contain data about the reference and/or comparison followed by the summary using a comparison metric, such as CPU Time, Elapsed Time, Buffer Gets, Disk Reads, Direct Writes, Physical I/Os, Optimizer Cost.

The Summary area lists, for instance, the Workload Impact by Overall, Common SQL, Improved SQL, Regressed SQL, Missing SQL, and New SQL:

The Breakdown area provides graphs for various performance topics, such as:

• CPU Time is a graph with the Before and After to the workload.

• SQL Statement by Performance is a graph with Improved, Regressed, Unchanged, Missing, and New categories and a bar indicating their number.

• SQL Statement by Plan Change shows the New Plan and Same Plan impacts side by side.

Top SQL Statements By Workload Impacts area is a table for each impact by:

• Impact

• SQL ID

• SQL Test

• Plan Change

• Net Absolute Impact on Workload (%)

• CPU Time Change (ms)