Using Performance Hub to Analyze Database Performance

This topic describes how to use Performance Hub to analyze and tune the performance of Oracle Cloud Infrastructure Shared and Dedicated Autonomous Databases, and Virtual Machine, Bare Metal, Oracle Exadata Cloud Service, and external Oracle databases. With this tool, you can view real-time and historical performance data.

To use Performance Hub for Virtual Machine, Bare Metal, Oracle Exadata Cloud Service, and external Oracle databases, Database Management must be enabled for the database. When enabling a database, the database administrator can choose from two database management options: Basic Management and Full Management. For information about using Performance Hub with Virtual Machine, Bare Metal, Oracle Exadata Cloud Service, and external Oracle databases see Enable Database Management.

Note

Using Identity and Access Management (IAM), you can create a policy that grants users access to Performance Hub while limiting actions they can take on an Autonomous, Virtual Machine, Bare Metal, Oracle Exadata Cloud Service, Oracle Database Cloud Service, and external Oracle databases. For more information, about policies and how to use them, see How Policies Work. The following example shows a policy that grants access only to performance data without allowing general use access on Autonomous Databases.
Allow group <groupname> to inspect autonomous-database-family in compartment <name> 
Allow group <groupname> to use autonomous-database-family in compartment <name> where
request.operation = 'RetrieveAutonomousDatabasePerformanceBulkData'

Performance Hub Features

Note

The number of tabs displayed in Performance Hub depends on the database management option that is enabled for the database you are monitoring. When a database is set to Basic Management, only the Performance Hub ASH Analytics and SQL Monitoring tabs are displayed. When a database is set to Full Management, all the Performance Hub tabs described on this page are displayed. For more information, see About Management Options in Enable Database Management.
Note

Performance Hub is not currently supported in Pluggable Databases in BMVM,

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. See Performance Hub User Interface on this page for details. Performance Hub includes the following tabs:

These tabs, described in detail in this topic, 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 are 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 outstanding requests exist for a lock

  • Which databases might be interfering with the current database
  • Identify databases, disks, and cells causing high I/O load and classify the load as Backup, Rebalance, or User I/O
  • Identify CPU bound cells and determine the cell offload
  • Identify configuration information such as version and disk status
Performance Hub User Interface

The Performance Hub user interface consists of a time range selector, a time zone selector, and several tabs that access the Performance Hub features. They are described in the following paragraphs.

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. The selected time range applies to all charts and graphs in the Performance Hub window.

Using the Time range selector, you can view real-time and historical performance data.

  • In real-time mode, performance data is retrieved from in-memory views. You can display data in any time range from within the time selection shown by the date picker.
  • In historical mode, data is retrieved from the Automatic Workload Repository (AWR). You can select any time period, provided sufficient data exists in the AWR. When you view historical data in the Performance Hub, you are viewing statistics collected as part of the snapshots of your database.

You can hide the Activity Summary chart to save space and display only the main tab content. To do so, click the Hide Activity Summary check box that is located directly above the graph.

Figure 1. Performance Hub Activity Summary

Performance Hub Activity Summary

The time range field (#1 in the previous 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. You can also click the Time Range field to specify a custom time range. Clicking the Time Range field opens the Custom Time Range dialog box, allowing you to specify a custom range.

The Activity Summary 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 previous 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 previous 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 (in the 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 Tab 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, the Performance Hub 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 that you can use to explore ASH data. You can use this tab 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 at the right end of the chart 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. 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 available in the selected data sample.

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

Activity tables

By default, the two tables located below the Average Active Sessions graph display the top SQLs and user sessions for the time period covered by the Average Activity Sessions graph. To view activities by other dimensions, use the menus at the top left of each of the two tables. The ASH Analytics tab can be used to monitor real time SQL activity. See To view real time SQL activity for details.

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 includes the following icons:

  • A spinning icon indicates that the SQL statement is running.
  • 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.

Real time SQL monitoring

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.

Graphical Explain Plan

The Real-time SQL Monitoring page provides different options for viewing plan statistics for a SQL execution. The default Tabular Execution Plan option presents a tabular view of the execution plan. The Graphical Explain Plan option presents a graphical tree representation of the explain plan. In the Graphical Explain Plan, you can display as much or as little data as needed, and can rotate the tree to display the information either horizontally or vertically.

Workload Tab

The Workload tab 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 in the following sections.

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. A shadowed area is not 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 executed calls per second.
    • Executions: This option displays the combined number of user and recursive calls that displayed 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 included in this topic under. Using the Oracle Cloud Infrastructure Console

The hierarchical display nests waiting sessions underneath the session that is blocking them 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.

ADDM Tab

The Performance Hub Automatic Database Diagnostic Monitor (ADDM) tab includes controls to access the information stored by ADDM. ADDM analyzes the Automatic Workload Repository (AWR) data regularly, then locates the root causes of performance problems, provides recommendations for correcting any problems, and identifies non-problem areas of the application. Because AWR is a repository of historical performance data, ADDM can be used to analyze performance issues after the event, often saving time and resources that would be needed to reproduce a problem.

ADDM provides the following benefits:

  • Time-based quantification of application problem impacts and recommendation benefits
  • Provides recommendations for treating the root causes of problems
  • Identification of non-problem areas of the application

In addition to the benefits ADDM provides for production systems, it can be used on development and test systems to provide early warnings of application performance issues.

Instructions to use the ADDM tab are located below in Using the Oracle Cloud Infrastructure Console.

Exadata Tab

The Exadata tab is available for external databases that use Exadata infrastructure, and Exadata Cloud deployments. It provides a unified view of Oracle Exadata hard disk and flash performance statistics with deep insight into the health and performance of all components such as the Oracle databases, Oracle Exadata storage cells, and Automatic Storage Management (ASM). You can use the Exadata tab for enhanced performance diagnostics for databases deployed on Exadata systems. The Exadata tab is available only in the historical mode and supports only container databases. It does not support real-time views or pluggable databases.

The Exadata tab allows you to:

  • Analyze outliers that affect database performance, including finding a slow disk component that is affecting the system
  • Analyze performance characteristics of multiple databases deployed in an Exadata system
  • Identify a high I/O load and classify the load as Backup, Rebalance, User I/O, and others
  • Identify CPU bound cells and determine cell offload
  • Identify configurations such as versions and disk status

It provides many types of statistics about databases running on Exadata systems, including:

  • Operating system statistics
  • Storage server software statistics
  • Smart scan statistics
  • Database, disk, and cell statistics
  • Exadata-specific ADDM recommendations
  • Exadata system statistics

Exadata sub tabs

The Exadata tab contains nine subtabs. See To view Exadata statistics for instructions to use these tabs.

  • Performance - shows database performance, including a summary, latency statistics, flash I/O statistics, and Hard Disk I/O statistics
  • Health - shows the number and type of open alerts (Information, Warning, and Critical), the number of offline disks, open alert details, and offline disk details
  • ADDM (Automatic Database Diagnostic Monitor ) - displays ADDM performance findings and details by task in the selected time period, and makes specific recommendations to correct performance problems. See ADDM Tab on this page and To view ADDM data for details about viewing ADDM data.
  • Top Consumers - identifies workload distribution across all the databases and databases that could be consuming a significant amount of the I/O bandwidth. The data is aggregated by I/O requests and I/O throughput to report the top databases captured. The tab shows the number of requests, I/O speed, and usage of the top databases per cell in the Exadata system. It also displays the IORM Queue Time, the details of the top databases per cell , and the details of the IORM queue time.
  • Cells and Disks - used to compare the operational statistics of the flash drives and hard disks in the system. It shows how I/O is distributed between flash drives and hard disks allowing you to see how much benefit the system is getting from Flash Cache, how much disk I/O is caused by Flash Cache, and how I/O correlates to database single block reads. The tab displays statistics by cell, including the Top Cells, Outlier Cells, OS I/O Throughput, Cell Server I/O Requests, Cell Server I/O Throughput, % Disk Utilization, I/O Latency, and Cell Latency. You can select the default column group by which to display the data. Hover the cursor over a statistic to view details about it.
  • Smart I/O - displays summaries and details of Flash log and Flash Cache statistics by cell
  • I/O Reasons - displays the number of requests and bytes for the set of selected cells to show the reasons for a high I/O load by category. Categories include backup, rebalance, and user I/O. The Requests and Bytes statistics bars for each cell are broken into sections that show the number of database control file reads per second, voting files I/O operations per second, and xrov I/O operations per second, and others.
  • Configuration - displays the configuration of the storage server model, the storage server version, and the objective of each cell.
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.

Performance Hub Report

Performance Hub includes the ability to create, view, and download an active report that contains database performance data. The report contains the Performance Hub UI backed by data that has been collected and bundled with the report at the time you created it. You can set the report to contain one of three levels of data:

  • Basic: Includes only the tab contents
  • Typical: Includes the tab contents with details for the top SQL statements
  • All: Includes the tab contents with details for all SQL statements
Note

When the database is set to Full Management, Performance Hub Report is available for Bare Metal (BM), Virtual Machine (VM), Exadata Cloud Service, and external databases.

For more information and instructions to generate a Performance Hub report, see To download a Performance Hub Report.

Active Session History Report

Performance Hub includes the ability to create, view, and download Active Session History (ASH) reports for a database. An ASH report contains data that allows you to:

  • Triage transient performance problems that typically last for a few minutes
  • Perform scoped or targeted performance analysis by various dimensions or their combinations, such as time, session, module, action, or SQL identifier.
Note

An ASH Report is only available for external databases.
Note

When the database is set to Full Management, Active Session History Report is available for Bare Metal (BM), Virtual Machine (VM), Exadata Cloud Service, and external databases.
For more information and instructions to generate an ASH report, see To download an Active Session History Report.

Using the Oracle Cloud Infrastructure Console

To navigate to Performance Hub in the External Database Service

This topic provides the steps to navigate to the page that explains how to use Performance Hub with external databases.

  1. Open the navigation menu. Click Oracle Database, then click External Database.
  2. Choose your Compartment.
  3. Under External Databases, click either Pluggable Database, Container Databases, or Non-Container Databases, depending on the type of external database handle you are managing.
    Note

    The Associated Services section of the database details page shows whether the Database Management service is enabled for the database.
    • If Database Management is Enabled, click Disable to disable it.
    • If Database Management is Disabled, click Enable to enable it.
  4. In the database details page, click Performance Hub.
    Note

    Performance Hub is enabled only under the following conditions.
    • The Database Management service must be enabled.
    • The database must be an Enterprise Edition, version 12.1.0.0.0 or higher.
To navigate to Performance Hub for the Bare Metal, Virtual Machine and Exadata databases
  1. Open the navigation menu, then click Oracle Database, then click Bare Metal, VM, and Exadata.
  2. Choose a Compartment.
  3. For databases running on virtual machine or bare metal systems:
    1. Click a Database System name. The Database System page is displayed.
    2. In the list of databases, click a database name. The database details page is displayed.
  4. For Exadata databases, click the VM Cluster, then click Databases. The database summary page is displayed.
  5. Click Performance Hub.
  6. The Associated Services section of the database details page shows whether the Database Management service is enabled for the database. It also shows which database management option (Basic or Full) is enabled.
    • If Database Management is Enabled, click Disable to disable it.
    • If Database Management is Disabled, click Enable to enable it.
    • Click Edit to display the Edit Database Management page. For information about changing database management settings, see About Management Options in Enable Database Management.
  7. In the database details page, click Performance Hub.
    Note

    Performance Hub is enabled only under the following conditions.
    • The Database Management service must be enabled.
    • The database must be an Enterprise Edition, version 12.1.0.0.0 or higher.
To view the average active sessions data by a selected dimension
  1. To display Performance Hub for the database you are managing, go to the details or summary page of that database and click Performance Hub. See To navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database or To navigate to Performance Hub in the External Database Service for more information, or To navigate to Performace Hub for the Bare Metal, VM and Exadata databases.

    • 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 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. To display Performance Hub for the database you are managing, go to the details or summary page of that database and click Performance Hub. See To navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database or To navigate to Performance Hub in the External Database Service for more information, or To navigate to Performace Hub for the Bare Metal, VM and Exadata databases.

    • 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 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 view the SQL Monitoring report
  1. To display Performance Hub for the database you are managing, go to the details or summary page of that database and click Performance Hub. See To navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database or To navigate to Performance Hub in the External Database Service for more information, or To navigate to Performance Hub for the Bare Metal, VM and Exadata databases.

    • 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 Graphical Explain Plan
  1. To display Performance Hub for the database you are managing, go to the details or summary page of that database and click Performance Hub. See To navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database or To navigate to Performance Hub in the External Database Service for more information, or To navigate to Performace Hub for the Bare Metal, VM and Exadata databases.

    • 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. In the SQL Monitoring tab, click an ID number in the SQL ID column to display detailed information on a specific SQL statement. This action displays the Real-time SQL Monitoring page. The Plan Statistics tab is displayed by default. In the Plan Statistics tab, the default Tabular Execution Plan option displays the plan statistics for the SQL execution.
  4. To display the Graphical Explain Plan, locate the menu in the upper right corner of the tab. Expand the menu and click Graphical Explain Plan. The SQL explain plan is presented as a collapsible tree of nodes where each node represents an operation.
Using the Graphical Explain Plan

In the Graphical Explain Plan, the operation nodes are displayed horizontally by default, with the root node of the tree on the right side of the screen. Sections of the tree below a node can be collapsed or expanded by clicking the minus or plus sign for the node.

  1. Use the mouse to scroll or use the keyboard +/- keys to zoom in or zoom out on the graphical explain plan. You can also use the mouse to click and drag the plan within the viewport. An overview section is located in the lower right corner of the content area. It provides a visual representation of the positioning of the viewport with respect to the overall content of the plan.
  2. Click a plus sign to expand a node and display information for the operation sub-tree. Click a minus sign to collapse the operation sub-tree beneath a node.
  3. Hover over a node to view the operation name and estimated row count. Click a node to display a message box that contains additional information about the operation.
  4. Click Rotate (to the right of the Graphical Explain menu) to rotate the entire explain plan 90 degrees counterclockwise. Subsequent clicks on Rotate rotate the explain plan another 90 degrees counterclockwise. In any position of the tree, click the plus or minus sign to expand or contract the subtree of an operation node.
To view real-time SQL activity

This topic explains how to use the ASH Analytics and SQL Monitoring tabs to view and monitor real-time-SQL activity for databases in the Oracle Cloud Infrastructure, and in Virtual Machine, Bare Metal, Oracle Exadata Cloud Service, Oracle Database Cloud Service, and external databases. These tabs are available when the database is set to either Basic Management of Full Management.

  1. To display Performance Hub for the database you are managing, go to the details or summary page of that database and click Performance Hub. See To navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database or To navigate to Performance Hub in the External Database Service for more information, or To navigate to Performance Hub for the Bare Metal, Virtual Machine, and Exadata databases.

    • 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. Use the Quick Select, Time Range, Time Zone, and the time slider to set the time period of the SQL activity that you want to monitor.

Using the ASH Analytics tab to view real-time SQL data.

  1. Click ASH Analytics to display the ASH Analytics page
  2. Scroll down to the table containing the list of SQL queries.
  3. Click an ID number in the SQL ID column to display detailed information about a specific SQL statement. This action displays the Performance Hub SQL Details page.
  4. The Performance Hub SQL Details page contains the following tabs:
    • Summary, which shows the overall information about the SQL statement and the SQL plans.
    • ASH Analytics, which shows the active sessions and two tables. The default information shown for the SQL query is Wait Class and User Session. As needed, click the menu containing the Wait Class option and select the wanted parameter and sub parameter to display the associated SQL data. Click a User Session ID to display the SQL activity during the selected user session.
    • Execution Statistics, which shows the Tabular Execution Plan and Graphical Explain Plan. See To view the Graphical Explain Plan for more information.
    • SQL Monitoring, which displays SQL data for the selected SQL statement. Click the SQL ID to display detailed data about the SQL statement. Depending on the database selected, the Details section includes Plan Statistics, Parallel, SQL Text, and Activity tabs.

      In the Details section of the page:

      • Click Plan Statistics to view the Tabular Execution Plan and Graphical Explain Plan. See To view the Graphical Explain Plan for more information.
      • Click Parallel to view the Parallel Server and the Instances and Parallel Groups it contains.
      • Click SQL Text to view the text of the active SQL query.
      • Click Activity to view the active sessions of the database that is selected. Use the menu on the Details page to view either the Active Sessions Resource Type or Plan Line of the selected SQL operation.

Using the SQL Monitoring tab to view real-time SQL data

  1. On the database details page of the database you have selected, click Performance Hub.
  2. Click SQL Monitoring to display the SQL monitoring page.
  3. In the SQL ID column, click an SQL ID to display detailed information about a specific SQL statement. This action displays the Performance Hub Real-time SQL Monitoring page.
    Note

    To return to the Database Details Page, click Back at the upper left corner of the Real-time SQL Monitoring page.
  4. Scroll down to display the SQL activities. Depending on the database selected, the Details section includes Plan Statistics, Parallel, SQL Text, and Activity tabs.

    In the Details section of the page:

    • Click Plan Statistics to view the Tabular Execution Plan and Graphical Explain Plan. See To view the Graphical Explain Plan for more information.
    • Click Parallel to view the Parallel Server and the Instances and Parallel Groups it contains.
    • Click SQL Text to view the text of the active SQL query.
    • Click Activity to view the active sessions of the database that is selected. Use the menu on the Details page to view either the Active Sessions Resource Type or Plan Line of the selected SQL operation.
To download an AWR report

For databases using Oracle Database 18c and older:

  1. To display Performance Hub for the database you are managing, go to the details or summary page of that database and click Performance Hub. See To navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database or To navigate to Performance Hub in the External Database Service for more information, or To navigate to Performace Hub for the Bare Metal, VM and Exadata databases.

    • 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. In the upper right corner, click Reports, and then click Automatic Workload Repository.

    The Generate Automatic Workload Repository dialog box is displayed

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

  4. 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 download folder for your browser.
  6. Open the download folder for your browser on your system and view the report from there.

For databases using Oracle Database 19c and newer:

  1. To display Performance Hub for the database you are managing, go to the details or summary page of that database and click Performance Hub. See To navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database or To navigate to Performance Hub in the External Database Service for more information, or To navigate to Performace Hub for the Bare Metal, VM and Exadata databases.

    • 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. In the Quick Select menu, choose a time period for which an AWR report will be generated.
  3. In the upper right corner, click Reports, and then click Automatic Workload Repository.

    The Generate Automatic Workload Repository dialog box is displayed.

  4. Use the Start Snapshot and End Snapshot menus to select the beginning and end of the snapshot time range to generate the report.
  5. Click Download. The system generates the report named AWRReport_date_range.html. When the report is complete, the report name is displayed at the top of the screen, and the report is automatically downloaded to the download folder for your browser.
  6. Open the download folder for your browser on your system and view the report from there.
To download a Performance Hub Report
  1. To display Performance Hub for the database you are managing, go to the details or summary page of that database and click Performance Hub. See To navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database or To navigate to Performance Hub in the External Database Service for more information, or To navigate to Performace Hub for the Bare Metal, VM and Exadata databases.

    The database name is displayed at the top of the Performance Hub page.

  2. In the upper right corner of the window, select Reports, and in the menu that is displayed, select Performance Hub.

    The Generate Performance Hub Report dialog box is displayed.

  3. In the dialog box:
    1. Select the type of report
      • Basic
      • Typical
      • All
    2. Click Download to generate the report and download it.
  4. While the report is being generated, a message ("Report generation is in progress.") appears at the upper right corner of the screen. When the report is complete and downloaded, a confirmation message "Report generated successfully" and the name of the report file appear at the upper right corner of the screen. The report is automatically downloaded to the download folder for your browser.

  5. Open the download folder for your browser on your system and view the report from there.

To download an Active Session History Report
  1. To display Performance Hub for the database you are managing, go to the details or summary page of that database and click Performance Hub. See To navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database or To navigate to Performance Hub in the External Database Service for more information, or To navigate to Performace Hub for the Bare Metal, VM and Exadata databases.

    The database name is displayed at the top of the Performance Hub page.

  2. In the upper right corner of the window, select Reports, and in the menu that is displayed, select Active Session History. The Generate ASH Report dialog box is displayed.
  3. In the dialog box, click the date of the Start Time, and in the calendar and time dialog boxes that are displayed, select the start date and time for the report. Repeat this procedure for the End Time date and time.
  4. Click Download to generate the report and download it.

    While the report is being generated, a message ("Report generation is in progress.") appears at the upper right corner of the screen. When the report is complete and downloaded, a confirmation message "Report generated successfully" and the name of the report file appear at the upper right corner of the screen. The report is automatically downloaded to the download folder for your browser.

  5. Open the download folder for your browser on your system and view the report from there.
To view the Workload metrics
  1. To display Performance Hub for the database you are managing, go to the details or summary page of that database and click Performance Hub. See To navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database or To navigate to Performance Hub in the External Database Service for more information, or To navigate to Performace Hub for the Bare Metal, VM and Exadata databases.

  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. To display Performance Hub for the database you are managing, go to the details or summary page of that database and click Performance Hub. See To navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database or To navigate to Performance Hub in the External Database Service for more information, or To navigate to Performace Hub for the Bare Metal, VM and Exadata databases.

    • 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.
To view ADDM data

This procedure explains how to view Automatic Database Diagnostic Monitor (ADDM) information with Performance Hub.

  1. To display Performance Hub for the database you are managing, go to the details or summary page of that database and click Performance Hub. See To navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database or To navigate to Performance Hub in the External Database Service for more information, or To navigate to Performace Hub for the Bare Metal, VM and Exadata databases.

  2. Click the ADDM tab to open it.
  3. Use the menu located below Quick Select to select a time range. The data for that time range is displayed.
  4. In the Activity Summary area, just below the data, click one of the gray AWR snapshot icons to display findings for the associated ADDM task. A white check mark in the gray icon indicates that there are problem findings available. When selected, the gray icon changes to blue.
    Note

    You can alternatively select an ADDM task from the menu below the ADDM tab or by positioning the time slider above an icon.
    Note

    When you manually change the ADDM task selection, either by clicking the gray icon for an associated AWR snapshot, or by selecting an option from the ADDM task menu, the time slider position and size are adjusted to cover the analysis period for the ADDM task.
  5. Hover over the icon to display a message about the AWR snapshot and ADDM task, including the number of findings for the ADDM task. The findings are displayed in two tables:
    • Findings table. When there are findings, the Findings table shows the Name of the finding, the Impact, Number of recommendations, and Average Active Sessions for that finding. If there are no findings available, the table displays a message that says no findings are available for the selected analysis period.
    • Warnings and Information table. The Warnings and Information table is displayed below the Findings table. It lists messages related to the findings.
      • Warning messages identify issues such as missing data in the AWR that may affect the completeness or accuracy of the ADDM analysis.
      • Information messages provide information that is relevant to understanding the performance of the database but does not represent a performance problem. This may include identification of non-problem areas of the database and automatic database maintenance activity.
    Note

    Both the Findings table and the Warnings and Information table are collapsible to save space when many findings are found. Click the minus icon (-) to collapse a table. Click the plus icon (+) to expand the table again.
  6. If a finding has ADDM recommendations available, the name of the finding is displayed as a link. Click the name of the finding to display more information about the finding, including a table of recommendations for corrective actions. Each recommendation includes the problem area, the suggested action to take to solve it, and the estimated benefit that will result when the action is taken.
  7. Click the expand icon at the end of a row in the recommendations table to view a rationale for the recommendation.
To view Exadata statistics

This procedure explains how to use Performance Hub to view statistics of databases running on an Exadata system.

  1. To display Performance Hub for the database you are managing, go to the details or summary page of that database and click Performance Hub. For more information, see To navigate to Performance Hub in the Oracle Cloud Infrastructure Console interface of an Autonomous Database, To navigate to Performance Hub in the External Database Service, or To navigate to Performance Hub for the Bare Metal, VM and Exadata databases.

  2. Click the Exadata tab. The Exadata summary page is displayed. It includes the following sections:
    • Summary - displays open alerts and type, I/O requests per disk, cell server I/O throughput per disk, and disk utilization for the database you have selected
    • Latency - displays the latency of OS and cell I/O, small reads and writes, and large reads and writes
    • Flash I/O - System Total - displays I/O requests, database throughput, and the maximum capacity of the flash drives used by the database
    • Hard Disk I/O - System Total - displays I/O requests, database throughput, and the maximum capacity of the hard disks used by the database
  3. Click Exadata Details to display the Exadata sub tabs. These tabs are:
    • Performance (displayed by default)
    • Health
    • ADDM
    • Top Consumers
    • Cells
    • Disks
    • Smart I/O
    • I/O Reasons
    • Configuration
  4. Click a sub tab to display the statistics you are interested in. For a description of each sub tab and the information it provides, see Exadata Tab in Performance Hub Features.