Using Performance Hub to Analyze Database Performance

This topic describes how to use the Performance Hub tool to analyze and tune the performance of a selected Oracle Cloud Infrastructure Autonomous Database. With this tool, you can view real-time and historical performance data. When you view historical data in the Performance Hub, you are viewing statistics collected as part of the hourly snapshots of your database.

Note

Performance Hub supports only Autonomous Databases.

Performance Hub Features

The Performance Hub window consists of a graphical Time Range display that you use to select the time period of all data to be displayed. It includes the following tabs that display performance data:

  • ASH Analytics
  • SQL Monitoring
  • Workload (available for databases using shared Exadata infrastructure)
  • Blocking Sessions (available for databases using dedicated Exadata infrastructure)

These tabs, described in detail below, provide information that you can use to analyze the performance of a selected database, including the following:

  • How much of the database is waiting for a resource, such as CPU or disk I/O
  • Whether database performance degraded over a given time period and what could be the likely cause.
  • Which specific modules may be causing a load on the system, and where most of database time is being spent on this module.
  • Which SQL statements are the key contributors to changes in database performance, and which executions are causing them.
  • Which user sessions are causing performance bottlenecks.
  • Which sessions are currently blocking and if there are outstanding requests for a lock.

Time Range Selector

The time range selector is displayed at the top of the Performance Hub page. It consists of a graphically displayed time field as shown in the following illustration. Note that the selected time range applies to all charts and graphs in the Performance Hub window.

Performance Hub Time Range and time slider

The time range field (#1 in the above illustration) shows database activity in chart form for the specified Time Range period. The time range is the amount of time being monitored.

Use the Quick Select selector to set the time range. The menu includes five time choices, Last Hour, Last 8 Hours, Last 24 Hours, Last Week, and Custom. The default time range is Last Hour. To specify a custom time range, you can also click the Time Range field. This opens the Custom Time Range dialog, allowing you to specify a custom range.

The Activity graph displays the average number of active sessions broken down by CPU, User I/O, and Wait. Maximum threads are shown as a red line above the time field.

The sliding box (circled at right in the above illustration) on the time range chart is known as the time slider. The time slider selects a section of the time range (#2 in the above illustration) shown in the time range field. It shows the time being analyzed. In the illustration, the arrows inside the time slider point to the vertical 'handle' elements on the left and right boundaries of the slider box. The time slider works as follows:

  • To change the start and end time of the analysis while keeping the same amount of time between them, left click anywhere inside the box. Then slide the box left or right along the time range without changing its size. The selected times are displayed below the time graph.
  • To increase or decrease the length of time being analyzed, left click either one of the handles and drag it left or right to expand or contract the box.
  • To refresh the data in Performance Hub according to the time range chosen, click Refresh (upper right corner of the window).

Note

The time slider provides an extra display feature in the Workload tab. See the description in the Workload section of this page.

Use the Quick Select menu to set the time duration. The menu includes the following five time choices: Last Hour, Last 8 Hours, Last 24 Hours, Last Week, and Custom. The default Time Range is Last Hour. The time slider selects the time period of the data displayed in Performance Hub. The time slider has a different default time period based on the selected Time Range.

Time Zone Selector

The Time Zone selector is located above the time range field, beside the Quick Select and Time Range selectors. By default, when you open Performance Hub, the tool displays data in UTC (Coordinated Universal Time) time. You can use the time zone selector to change the time zone to either your local web browser time, or the time zone setting of the database you are working with. When you change the time zone, Performance Hub's reports display data in your specified time zone.

ASH Analytics Tab

Displayed by default, the ASH (Active Session History) Analytics tab shows ASH analytics charts to explore ASH data. You use it to drill down into database performance across multiple dimensions such as Consumer Group, Wait Class, SQL ID, and User Name. In the ASH Analytics tab, you can select an Average Active Sessions dimension and view the top activity for that dimension for the selected time period. For more information on ASH, see Active Session History (ASH) in Oracle Database Concepts.

SQL Monitoring Tab

The SQL Monitoring tab is not displayed by default. To view it, click SQL Monitoring on the Performance Hub page.

SQL statements are only monitored if they have been running for at least five seconds or if they are run in parallel. The table in this section displays monitored SQL statement executions by dimensions including Last Active Time, CPU Time, and Database Time. The table displays currently running SQL statements and SQL statements that completed, failed, or were terminated. The columns in the table provide information for monitored SQL statements including Status, Duration, and SQL ID.

The Status column has the following icons:

  • A spinning icon indicates that the SQL statement is executing.
  • A green check mark icon indicates that the SQL statement completed its execution during the specified time period.
  • A red cross icon indicates that the SQL statement did not complete. The icon displays when an error occurs because the session was terminated.
  • A clock icon indicates that the SQL statement is queued.

To terminate a running or queued SQL statement, click Kill Session.

You can also click an SQL ID to go to the corresponding Real-time SQL Monitoring page. This page provides extra details to help you tune the selected SQL statement.

Workload Tab

The Workload tab in the Performance Hub graphically displays four sets of statistics that you can use to monitor the database workload and identify spikes and bottlenecks. Each set of statistics is displayed in a separate region, as described below. The Workload tab supports Autonomous Databases using Shared Exadata Infrastructure.

Monitored and analyzed time indications

The time slider has more functionality in the Workload tab than it does in the Active Session History and SQL Monitoring tabs. Note the following about the Quick Select time range options:

  • Last Hour, Last 8 Hours, and Last 24 Hours - The charts in the Workload tab display data for the entire time period of specified time range. A shadowed area is displayed in each chart that corresponds to the position of the time slider in the time range.

  • Last Week - The charts in the Workload tab display data for the selected time period of the time slider in the time range. There is no shadowed area displayed in this case.
  • Custom - The shadowed area display depends on whether the time period is up to and including 24 hours, or greater than 24 hours.

Regions

The tab contains four regions: CPU Statistics, Wait Time Statistics, Workload Profile, and Sessions. Each region contains one or more charts that indicate the characteristics of the workload and the distribution of the resources. The data displayed on all the charts is for the same time period, as selected by the Time Range and time slider at the top of the window.

  • The CPU Statistics region contains two charts:
    • CPU Time - This chart shows how much CPU time is being used by the foreground sessions every second. It identifies where the CPU time is mostly spent in the workload and pinpoints any unusual CPU spikes.
    • CPU Utilization (%) - This chart indicates the percentage of CPU time aggregated by consumer group as calculated by the resource manager.
  • The Wait Time Statistics region contains a chart that displays the time used in different wait classes. To see the total average active sessions, select the DB Time check box. The activities are broken down by the 13 wait classes.
  • The Workload Profile region contains a group of charts that indicate patterns of user calls, executions, transactions, and parses, as well as the number of running statements and queued statements. This region includes a menu that you can use to select the data to display. It contains the following options.

    • User Calls - This option displays the combined number of logons, parses, and execute calls per second.
    • Executions - This option displays the combined number of user and recursive calls that executed SQL statements per second.
    • Transactions - This option displays the combined number of user commits and user rollbacks per second.
    • Parses - This option displays the combined number of hard and soft parses per second.
    • Running Statements - This option displays the number of running SQL statements, aggregated by consumer group.
    • Queued Statements - This option displays the number of queued parallel SQL statements, aggregated by consumer group.
  • The Sessions region contains charts that show the number of current logons and sessions. It contains a menu that includes the following options:
    • Current Logons - This option displays the number of current successful logons.
    • Sessions - This option displays the number of sessions.

Blocking Sessions Tab

The Performance Hub blocking sessions tab displays the current blocking and waiting sessions in a hierarchical display. You can view detailed information about each blocking session, and can view the sessions blocked by each blocking session. You can also use the tab to inspect or drill down into the SQL involved, to determine the cause of the blocking. You can perform several operations in the tab, including killing one or more of the listed sessions to resolve a waiting session problem. Instructions for the tab functions are located in this topic under Using the Oracle Cloud Infrastructure Console

The hierarchical display nests waiting sessions underneath the session that they are blocked by in an easily viewable parent-child relationship. The hierarchy can contain any number of levels to correctly represent the structure of the sessions involved,

The sessions listed include sessions that are waiting for a resource and sessions that hold a resource that is being waited on that creates the blocking condition.

Automatic Workload Repository Reports

The Automatic Workload Repository (AWR) collects, processes, and maintains performance statistics for problem detection and self-tuning purposes. This data is both in memory and stored in the database. From the Performance Hub you can generate and download a report of the gathered data.

An AWR report shows data captured between two points in time (or snapshots). AWR reports are divided into multiple sections. The content of the report contains the workload profile of the system for the selected range of snapshots. The HTML report includes links that you can use to navigate quickly between sections.

The statistics collected and processed by AWR include:

  • Object statistics that determine both access and usage statistics of database segments
  • Time model statistics based on time usage for activities, displayed in the V$SYS_TIME_MODEL and V$SESS_TIME_MODEL views
  • Some of the system and session statistics collected in the V$SYSSTAT and V$SESSTAT views
  • SQL statements that are producing the highest load on the system, based on criteria such as elapsed time and CPU time
  • ASH statistics, representing the history of recent sessions activity

To generate and download an AWR report, see To download an AWR report.

Using the Oracle Cloud Infrastructure Console

To view the average active sessions data by a selected dimension
To filter average active sessions data
To download an AWR report
To view the SQL Monitoring report
To view the Workload metrics
To view blocking and waiting sessions