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
  • Blocking Sessions

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. The Average Active Session chart has a control to select the displayed resolution of ASH data (low, medium, high, or maximum). For more information on ASH, see Active Session History (ASH) in Oracle Database Concepts.

ASH Sample Resolution

The ASH Sample Resolution menu gives users the ability to control the sampling of ASH data displayed in the Average Active Sessions chart. Data resolution means displaying more or fewer data points in the sample data in given time period. Lower resolution displays coarser data with better performance and less impact on the database, while higher resolution aggregates more data to display finer detail, but can have a corresponding cost in latency and impact on the database.

The Sample Resolution menu is displayed at the right side of the chart. The data resolution selections are:

  • Low - the chart displays the fewest data points in the selected data sample.
  • Medium - the chart displays more data points in the selected data sample.
  • High - the chart displays more data points in the selected data sample.
  • Maximum - the chart displays the most data points in the selected data sample.

To use this feature, see To view the average active sessions data by a selected dimension

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 both Shared Exadata infrastructureand Dedicated 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 navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database
To view the average active sessions data by a selected dimension
  1. Go to the Performance Hub page of the Oracle Cloud Infrastructure Console for the database which you wan to manage. See To navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database for more information.

    • The database name is displayed at the top of the Performance Hub page.
    • The time period for which information is available on the Performance Hub is displayed in the Time Range field.

    • The selected time period is indicated on the time slider graph by the adjustable time slider box.

    • The ASH Analytics tab is displayed with the top activity for a selected dimension in the selected time period.

  2. Use the Quick Select selector to set the exact time period for which data is displayed in the ASH Analytics tables and graphs. By default, the last hour is selected. The time range is the total amount of time available for analysis.
  3. Use the box on the time slider to further narrow down the time period for which performance data is displayed on the ASH Analytics tab.
  4. Select a dimension in the Average Active Sessions drop-down list to display ASH analytics by that dimension. When the Consumer Group dimension is selected, the data is categorized by default to the High, Medium, or Low service name that is associated with the Autonomous Database.

    Optionally, you can:

    • Click the Maximum Threads check box to view the number of Max CPU Threads. The red line on the chart shows this limit.
    • Click the Total Activity check box to view a black border that denotes total activity of all the components of the selected dimension on the chart. This option is selected by default when you use the filtering capabilities to only view the data for a particular component within a dimension. For information on filtering Average Active Sessions data, see Filter Average Active Sessions Data.

  5. Use the Sample Resolution menu to select the sampling of ASH data displayed in the Average Active Sessions chart. To select a resolution, click Sample Resolution to display the following menu and click the desired resolution to display the data.

    • Low - the graph displays the fewest data points available in the selected data sample.
    • Medium - the graph displays more data points in the selected data sample.
    • High - the graph displays more data points in the selected data sample.
    • Maximum - the graph displays the most data points available in the selected data sample.
  6. For the dimension selected in the Average Active Sessions drop-down list, you can further drill down into session details by selecting dimensions in the two sections at the bottom of the ASH Analytics tab. By default, the following dimensions are selected:

    • SQL ID by Consumer Group, which displays the SQL statements with the top average active sessions activity for consumer groups for the selected time period. You can right-click the bar charts to sort the SQL statements in ascending or descending order or click the SQL ID to go the SQL Details page.
    • User Session by Consumer Group, which displays the user sessions with the top average active sessions activity for consumer groups for the selected time period. You can right-click the bar charts to sort the user sessions in ascending or descending order or click the user session to go to the User Session page.
To filter average active sessions data
  1. Go to the Performance Hub page of the Oracle Cloud InfrastructureConsole for the database that you want to manage. See To navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database for more information.

    • The database name is displayed at the top of the Performance Hub page.
    • The time period for which information is available on the Performance Hub is displayed in the Time Range field. The selected time period is indicated on the time slider graph by the adjustable time slider block.

      The ASH Analytics tab is displayed with the top activity for a selected dimension in the selected time period.

  2. Use the Quick Select selector to set the exact time period for which data is displayed in the ASH Analytics tables and graphs. By default, the last hour is selected. The time range is the total amount of time available for analysis.
  3. Use the adjustable time slider box to further narrow down the time period for which performance data is displayed on the ASH Analytics tab.
  4. In the ASH Analytics tab, select a dimension in the Average Active Sessions by drop-down list. By default, Consumer Group is selected.

    The chart is displayed. Each color in the chart denotes a component of the selected dimension.For example, the Consumer Group dimension has High, Medium, and Low, which are predefined service names assigned to your Autonomous Database to provide different levels of concurrency and performance.

  5. Click a component in the legend. The selected component is displayed in the Applied Filters field and the chart is updated to only display data pertaining to that component. The total activity, which includes all the components of the dimension, is defined by a black outline and is displayed by default when you filter data.

To download an AWR report
  1. Go to the Performance Hub page of the Oracle Cloud Infrastructure Console for the database which you want to manage. See To navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database for more information.

    • The database name is displayed at the top of the Performance Hub page.
    • The time period for which information is available on the Performance Hub is displayed in the Time Range field. The selected time period is indicated on the time slider graph by the adjustable time slider box.

  2. Click the Reports drop-down to display a list of available reports.
  3. Click AWR to display the Generate AWR Report dialog.
  4. You can choose to generate a report either from two snapshots closest to the current time and date or from a custom time range of your choice.

    • If you choose the default time range, then select Use the two snapshots closest to date and click Download.
    • If you choose to generate a report from a custom time range, then select Custom and select start and end times for your range. Click Download.
  5. Oracle Database generates a report named AWRReport_date_range.html that downloads to the default download folder for your browser. View the report after the download completes.
To view the SQL Monitoring report
  1. Go to the Performance Hub page of the Oracle Cloud Infrastructure Console for the database which you want to manage. See To navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database for more information.

    • The database name is displayed at the top of the Performance Hub page.
    • The time period for which information is available on the Performance Hub is displayed in the Time Range field. The selected time period is indicated on the time slider graph by the adjustable time slider box.

  2. Click SQL Monitoring to display the SQL monitoring tab.
  3. Optionally, you can get detailed information on a specific SQL statement by clicking an ID number in the SQL ID column. When you click an ID number, the Real-time SQL Monitoring page is displayed.
  4. Click Download Report to download the report data for your selected SQL statement.
To view the Workload metrics
  1. Go to the Performance Hub page of the Oracle Cloud Infrastructure Console for the database that you want to manage. See To navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database for more information. The database name is displayed at the top of the Performance Hub page.

  2. Use the Quick Select selector to set the exact time period for which data is displayed in the ASH Analytics tables and graphs. By default, the last hour is selected. The time range is the total amount of time available for analysis.
  3. Use the time slider to further narrow down the time period for which performance data is displayed on the Workload tab. All charts show data for the entire specified time range if within 24 hours.
  4. Click Workload to view the Workload tab. The four regions and their associated charts are displayed.
  • CPU Statistics The CPU Statistics region contains two charts, CPU Time and CPU Utilization (%).

    • To display how much CPU Time is being consumed by the foreground sessions per second, select CPU Time in the menu in this region. This identifies where the CPU time is mostly spent in the workload and pinpoints any unusual CPU spikes. When CPU time is selected optionally click the Maximum Threads check box to show the maximum CPU time available. This shows the CPU time component of Average Active Sessions. .
    • To display the CPU Utilization (%) chart, select CPU Utilization (%) in the menu. This chart displays the percentage of CPU time aggregated by consumer group, as calculated by the resource manager.
  • Wait Time Statistics The Wait Time Statistics region contains one 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.
  • Workload Profile To change the metrics displayed in the Workload Profile, click the menu and select the metric that you want to view.
    • Select User Calls to display the combined number of logons, parses, and execute calls per second.
    • Select Executions to display the combined number of user and recursive calls that executed SQL statements per second.
    • Select Transactions to display the combined number of user commits and user rollbacks per second.
    • Select Parses to display the combined number of hard and soft parses per second
    • Select Running Statements to display the number of running SQL statements, aggregated by consumer group.
    • Select Queued Statements to display the number of queued parallel SQL statements, aggregated by consumer group.
  • Sessions To change the metrics displayed in the Sessions region, click the menu and select the metric that you want to view:
    • Select Current Logons to display the number of current successful logons.
    • Select Sessions to display the number of sessions.

To view blocking and waiting sessions
  1. Go to the Performance Hub page of the Oracle Cloud Infrastructure Console for the database that you want to manage. See To navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database for more information.

    • The database name is displayed at the top of the Performance Hub page.
    • The time period for which information is available on the Performance Hub is displayed in the Time Range field. The selected time period is indicated on the time slider graph by the adjustable time slider box. See the Time Range information in Performance Hub Features to learn how to set the duration of the time to be monitored.

  2. Click Blocking Sessions to display details about current blocking and waiting sessions. Analysis of historical sessions is not supported.
  3. Click the link in each column of the table to view the details of the listed blocking and waiting sessions, as shown in the following table.
Note

If you see an error message that says the server failed to get performance details for the selected session at the selected time, try the selection again. If the same error message is displayed, try a different time selection. If that fails, contact Oracle Support.
Tab Column Description

User Name

This is the name of the user.
Status The status indicates whether the session is active, inactive, or expired.
Lock

This is the lock type for the session. Click the lock type to display a table with more information about the session lock. It lists the Lock Type, Lock Mode, Lock Request, Object Type, Subobject Type, Time, ID1, ID2, Lock Object Address, and Lock Address of the selected session.

User Session

The user session lists the Instance, SID, and Serial number.

SQL ID

This is the ID of the SQL associated with the session.

Wait Event

This is the wait event for the session. Click the wait event to show additional wait event details.

Object Name

This is the name of the locked database object.
Blocking Time This is the time that a blocking session has been blocking a session.
Wait Time This is the time that a session has been waiting.

Setting the Minimum Wait Time

The minimum wait time works like a filter for the Blocking Sessions information. It sets the minimum time that a session must wait before it is displayed in the tab. For example, if the minimum wait time is set to three seconds, and a session has waited only two seconds, it is not displayed in the table. But if you change the minimum wait time to one second, the session that waited only two seconds is added to the display.

Note

The minimum wait time default setting is three seconds.

Killing a Session

  1. Click the check box at the left of the session User Name to select a session. The Kill Session button is enabled.
  2. Click Kill Session. The Kill Session confirmation dialog box is displayed.
  3. Click Kill Session to end the session.

Displaying Lock Details

  1. In the session Lock column, click the name of the lock type (Lock or Exclusive Lock) for the session. The Wait Event Details message box is displayed.
  2. Note the information in the table and use as needed to determine any action to take.

Displaying Wait Event Information

  1. In the session Wait Event column, click the name of the wait event for the selected session. The Session Lock Information table is displayed.
  2. Note the information in the message box and use as needed to determine any action to take.

Displaying Session Details

  1. In the session User Session column, click the session identifier for the session. The Performance Hub Session Details page is displayed.
  2. Optionally move the time slider to display a specific time range of the session.
  3. Use the Session Details page to explore additional details about the session.

Displaying SQL Details

  1. In the session SQL ID column, click the SQL ID associated with the session. The Performance Hub SQL Details page is displayed.
  2. Optionally move the time slider to display a specific time range of the session.
  3. Select one or more of the following tabs, note the information in them, and take any action needed.
  • Summary. This tab displays the SQL Overview and Source details.
  • ASH Analytics. This tab displays the SQL average active sessions.
  • Execution Statistics. This tab displays the SQL plans and plan details.
  • SQL Monitoring. This tab displays information about monitored SQL executions.
  • SQL Text. This tab displays the SQL.