You can view real-time and historical performance data from the Performance Hub. Performance Hub shows Active Session History (ASH) analytics, SQL monitoring and workload information.
Perform the following prerequisite steps as necessary:
Open the Oracle Cloud Infrastructure console by clicking the next to Oracle Cloud.
- From the Oracle Cloud Infrastructure left navigation list click either Autonomous Data Warehouse, Autonomous Transaction Processing, or Autonomous JSON Database depending on the workload type of your Autonomous Database.
From the Autonomous Database landing pad select an instance.
From the Details page click Performance Hub.
The Performance Hub page is displayed. This page has the following sections:
The time selector.
The Reports drop-down list, containing the option to create and download an Automatic Workload Repository (AWR) report.
The tabbed data area, with the tabs ASH Analytics, SQL Monitoring, ADDM, Workload and Blocking Sessions.
The Time Selector
The time selector is a set of controls at the top of the Performance Hub page. You use these controls to set the time range for Performance Hub to monitor.
You can use the Quick Select list to quickly set the time range to Last Hour, Last 8 Hours, Last 24 Hours or Last Week, or you can click the Time Range field and specify a custom time range. Additionally, you can use the Time Zone list to have all times based on UTC (Coordinated Universal Time) time, your local web browser time, or the time zone setting of the database.
The Activity timeline shows active sessions during the selected time range. It displays the average number of active sessions broken down by CPU, User I/O, and Wait. It also shows the Max CPU usage.
The sliding box on the timeline is the time slider. Use the time slider to select a section of the time range.
You can slide the box to the left or the right to shift the time selection, and you can widen or narrow the box to increase or decrease the section's timespan. To slide the entire box, left-click anywhere inside the box and drag the box to the left or the right. To widen or narrow the box, left-click and hold the handlebar on either side of the box, then drag to the left or the right to increase or decrease the width of the time slider.
Click Refresh to refresh the data in Performance Hub according to the time range chosen.
The Automatic Workload Repository Report
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.
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
- Some of the system and session statistics collected in the
- 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
Follow these steps to generate and download an AWR report from the Performance Hub:
- Use the time selector controls to specify a time range that includes the period for which you want to generate the AWR report.
- Click Reports and then choose Automatic Workload Repository to display the Generate Automatic Workload Repository Report dialog.
- Choose the Start Snapshot and End Snapshot for the report and then click Download.
- Performance Hub generates the report, displays the name of the report file, and, depending on your browser settings, automatically downloads it to your default download location or prompts you to specify a download location.
For more information about using an AWR report, see Using the AWR Compare Periods Reports.
The ASH Analytics Tab
This tab, which is displayed by default, shows Active Session History (ASH) analytics charts to explore Active Session History data. You can drill down into database performance across multiple dimensions such as Consumer Group, Wait Class, SQL ID, and User Name. Select an Average Active Sessions dimension and view the top activity for that dimension for the selected time period.
You can use the ASH Sample Resolution drop-down list to control the data resolution of the chart, from the coarsest setting of Low, which shows the fewest data points and has the largest interval between data points, to the finest setting of Maximum, which shows the most data points and has the smallest interval between data points. Note that the higher the setting you choose, the longer it takes Performance Hub to retrieve data and display the chart.
See Active Session History (ASH) in Oracle Database Concepts for more information on Active Session History.
The SQL Monitoring Tab
The SQL statements are only monitored if they've been running for at least five seconds or if they're run in parallel. The table 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, either due to an error, or due to the session being terminated.
- A clock icon indicates that the SQL statement is queued.
To terminate a running or queued SQL statement, click Kill Session.
Select the link in the SQL ID column to go to the corresponding Real-time SQL Monitoring page. This page provides additional details to help you tune the selected SQL statement.
The ADDM Tab
This tab provides access to analysis information gathered by the Automatic Database Diagnostic Monitor (ADDM) tool. ADDM analyzes AWR (Automatic Workload Repository) snapshots on a regular basis, locates root causes of any performance problems, provides recommendations for correcting the problems, and identifies non-problem areas of the system. Because AWR is a repository of historical performance data, ADDM can analyze performance issues after the event, often saving time and resources in reproducing a problem.
After selecting the ADDM tab, use the time selector controls to specify a time range that includes the period for which you want to view ADDM info. Then, select which ADDM analysis dataset you want to view from the Task Name list.
Performance Hubs retrieves the ADDM analysis information, presenting it in two tables:
- Findings. This table lists performance symptoms and problems. For more information about a finding, you can click its name to display details and recommendations regarding it.
- Warnings and Information. This table lists findings that can help you understand the analysis:
- Warning messages identify issues such as missing data in an AWR that may affect the completeness or accuracy of the ADDM analysis.
- Information messages are not performance issues, but help you understand how the database is performing. This may include identification of non-problem areas of the database and automatic database maintenance activity.
For more information about ADDM findings and how to interpret them, see ADDM Analysis Results in Oracle Database Performance Tuning Guide.
The Workload Tab
This tab shows four chart areas that show the workload on the database in various ways:
CPU Statistics: Charts CPU usage as measured by the statistic you select:
CPU Time: Shows how many CPU seconds are being used per second by the database's foreground sessions.
CPU Utilization (%): Shows the CPU usage of all the database's consumer groups as a percentage of the number of CPUs the database is allowed to use.
Wait Time Statistics: Shows the wait time across the database's foreground sessions, divided by wait classes.
Workload Profile: Charts user (client) workload on the database as measured by the statistic you select:
User Calls and Transactions: Shows the User Calls, Executions and Transactions statistics in a single, consolidated chart.
User Calls: Shows the number of user calls (such as login, parse, fetch, or execute) per second.
Executions: Shows the number of executed SQL statements per second, whether initiated directly by a user or recursively.
Transactions: Shows the combined number of user commits and user rollbacks per second.
Parses: Shows the combined number of hard and soft parses per second.
Running Statements: Shows the number of running SQL statements across all the database's consumer groups.
Queued Statements: Shows the number of queued parallel SQL statements across all the database's consumer groups.
Current Logons: Shows the number of current successful logons.
Sessions: Shows the number of sessions.
The Blocking Sessions Tab
This tab lists sessions that are waiting or are blocked by sessions that are waiting. You can set the minimum wait time required for sessions to be displayed in the list, and you can view a variety of information about a session to determine whether to let it continue or to kill it. You access this information by clicking the links in columns of the table row for the session:
Lock column: click the name of the lock type to display the Wait Event Details dialog box.
Wait Event column: click the name of the wait event to display the Session Lock Information dialog box.
User Session column: click the session identifier to display the Session Details page.
SQL ID column: click the SQL ID to display the SQL Details page.
After researching a session, you can kill it by selecting the checkbox at the start of the session's table row and then clicking Kill Session. A confirmation dialog is displayed, requesting you to confirm the kill operation.