Using Performance Hub to Analyze Database Performance

Use the Performance Hub tool to analyze and tune the performance of a selected Oracle Exadata Database Service on Cloud@Customer 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
  • 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.

Figure 6-1 Time Range Selector

Description of Figure 6-1 follows

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.

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.

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.

Using the Oracle Cloud Infrastructure Console

Navigate to Performance Hub in the Oracle Cloud Infrastructure Console Interface of an Autonomous Database

  1. Open the navigation menu. Under Oracle Database, click Exadata Database Service on Cloud@Customer.
  2. Choose your Compartment.
  3. Click Autonomous Databases.
  4. In the list of Autonomous Databases, click the display name of the database you want to analyze using Performance Hub reports.
  5. Click Performance Hub.

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 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.

    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. 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.

Filter Average Active sessions Data

  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.

    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.

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.

View the Blocking and Waiting Sessions

  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 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.

    Table 6-8 Blocking and Waiting Sessions

    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.