Analyze Automatic Workload Repository (AWR) Performance Data

The Operations Insights AWR Hub lets you consolidate and store detailed performance data from the Automatic Workload Repository (AWR) of your important Oracle Databases. This consolidated AWR Hub allows you to view and analyze historical performance data beyond the AWR retention period of the source database.

Operations Insights extracts data from one or more source database targets and transfers it into the AWR Hub Warehouse, which is maintained independent of the source databases. The AWR Hub lets you keep a long-term history of AWR data from the selected database targets. This enables long-term analysis of AWR data across databases without performance or storage impact on the source database targets. Therefore, by uploading AWR data to a centralized AWR Hub, you can free up space and improve performance on your source production systems.

To handle AWR data from multiple databases, Operations Insights provides AWR Hub, a cloud-based repository which collects and consolidates AWR snapshots from multiple databases in your OCI tenancy. The snapshots from source databases are retained for 25 months.

Note

AWR Hub Licensing
  • The use of Automatic Workload Repository Hub (AWR Hub) features in the database requires both an Oracle Diagnostics Pack license and an OCI Operations Insights Service license subscription.
  • The use of PL/SQL APIs through the DBMS_AWRHUB package requires both an Oracle Diagnostics Pack license and an OCI Operations Insights Service license subscription.

See Oracle Management Packs and Their Permitted Features for more information.

Supported Databases

AWR Hub provides support for the following databases:

Enabling AWR Hub for External Oracle Databases version 12.1

To enable AWR Hub for databases on version 12.1, ensure that you:

  • Apply 12c Release 12.1.0.2.211019DBBP Patch Set Update (PSU) 33114885 on the Oracle Database
  • Apply Patch 33397065: AWRHUB TO SUPPORT TRANSIENT SOURCE DB LOCAL MAILBOX

Enabling AWR Hub for External Oracle Databases 12.2 to 18c

  • Oracle Support will work with you and supply a one-off patch for DB 12.2 through 18c.
  • You will need to raise a work order request with Oracle Support via an SR to backport this feature in your release label.
  • Refer to the MOS note: Set up AWR Hub source for pre-19c (Doc ID 2877452.1) for instructions on registering the database with AWR Hub.

Enabling AWR Hub for External Oracle Databases 19c

For database version 19c (version 19.4) refer to the following:

  • Download the patch from My Oracle Support - Patch 33515361 to upgrade the base Oracle Database 19c to version 19.14.
  • Set up the DBMS Cloud package by using the instructions in MOS note 2748362.1.
  • Download and install Patch 33397065 for AWR Hub.
  • Configure AWR Hub using the steps mentioned in MOS note 2908865.1.

For detailed information, see MOS note 2908865.1

Configuring AWR Hub

Before using the AWR Hub feature, you must first configure Operations Insights to work with your existing database environment.

Configuration Steps


Graphic shows the AWR Hub configuration steps.

As shown in the illustration, to configure AWR Hub, you must:

  1. Create the Warehouse and AWR Hub
  2. Configure Database Sources
  3. Access the Operations Insights Warehouse
  4. Create an Operations Insights Warehouse Database User

Create the Warehouse and AWR Hub

The first step is to set up the AWR Hub and associate your target database Object Storage Bucket where the database's AWR snapshots are stored.

Prerequisite:

In order to create the AWR Warehouse, you MUST have the requisite permission to perform this operation at the root compartment.

Required Policies:


allow <group> to manage opsi-warehouses in tenancy
allow <group> to manage opsi-warehouse-users in tenancy
allow <group> to manage opsi-awr-hubs in tenancy

Alternatively, the above policies are covered by the following:

allow <group> to manage opsi-family in tenancy
  1. Open the navigation menu, click Observability & Management. Under Operations Insights, click Overview. Operations Insights console displays.
  2. From the Operation Insights menu, click Administration and then Operations Insights Warehouse. The Operations Insights Warehouse page displays.
  3. Enter a Warehouse Name and the number of OCPU cores to enable.
  4. Click Create Warehouse. A work request will be submitted to create the Operations Insights Warehouse. To view the progress of the work request, click the Work Requests tab.

    Once the work request completes and the warehouse has been created, the actions you can perform on the warehouse are activated. You can:

    • Increase or decrease the number of OCPU cores
    • Download or rotate the client credential wallet
    • Add Tags to the warehouse
    • Terminate the warehouse
    • Create database users
    • Create the AWR Hub
  5. To create a console connection to use an SQL Worksheet directly in the cloud for Operations Insights warehouse follow these sub-steps:
    1. Create the OPSI Warehouse user.
    2. Download the wallet.
    3. Create a vault and two secrets in the vault: Warehouse user password and contents of cwallet.sso (inside the wallet zip file).
    4. Navigate to Developer Services then Connections. For more information see: Creating a Connection.
    5. On the Create Connection page select Enter Database Information, this is required because of the Operations Insights Warehouse resource tenancy location.
    6. Obtain the connection string from the tnsnames.ora file from the wallet obtained in step b.
    7. Specify a name and use the two secrets established in step c.

      Figure 7-1 Create Connection

      Create Connection
    Once completed you can now use SQL Worksheets with Operations Insights.
  6. Click the AWR Hub tab. The Create AWR Hub page displays.

    Because the target database AWR data is uploaded to an Object Storage Bucket that resides in a different tenancy from the AWR Hub, you will need to define cross-tenancy policies that will allow the AWR Hub to pull AWR snapshots from the target database bucket.


    Graphic shows the Create AWR Hub page with the policy template highlighted.

  7. Enter Hub Name and then select the Object Storage Bucket where the source databases will upload snapshots. Once you select a bucket from the drop-down list, the required policy statements will appear already populated with the correct parameters (highlighted in red above).

    At this point, the policy statements are ready to use.

    Note

    The policy must exist in the hub tenancy before you can create the AWR Hub. You can alternatively create the policy statements manually outside the Operations Insights console. The policy statements have the following format:
    define tenancy opsitenancy as <opsi-tenancy-id>
    define dynamic-group opsiwarehousedg as <opsi-dynamic-group>
    admit dynamic-group opsiwarehousedg of tenancy opsitenancy to manage object-family in tenancy where ANY{target.bucket.name=’<bucket-name>’
    

    opsi-tenancy-id and opsi-dynamic-group can be determined from the warehouse resource directly via SDK/API. Fields on this resource are:

    "dynamicGroupId": "string"
    "operationsInsightsTenancyID": "string"

    bucket-name is user-defined.

  8. Set up the policy in the bucket tenancy using the policy statements generated when you selected the Object Storage Bucket in the previous step.
    Note

    This policy MUST be created in the root compartment of the tenancy. For instructions on creating an OCI policy, see To create a policy.
  9. Once the policy has been created, you can create the AWR Hub. On the Create AWR Hub page, enter the Bucket Name and then click Create AWR Hub.

    A work request to create the AWR Hub is submitted and may take a few minutes to complete.

Configure Database Sources

For any source database that will be uploading AWR snapshots to the AWR Hub, you need to register that database with the AWR Hub.

The prerequisites for the registration process differ depending on whether the database you are registering is an external Oracle Database (resides outside OCI), Autonomous Database shared (ADB-S), or Autonomous Database dedicated (ADB-D).

For external Oracle Database prerequisites, see Enabling AWR Hub for External Oracle Databases version 12.1 or Enabling AWR Hub for External Oracle Databases 12.2 to 18.

Once the prerequisites have been met, you register the database(s) by running the PL/SQL scripts provided in the Register Database Information pop-up.

ADB-S Prerequisites

The source Autonomous Databases on which AWR Hub is being enabled need to have manage permissions on the Object Storage bucket into which AWR snapshots will be uploaded. To grant these permissions, do the following:

Note

The following two policies need to be created for each of the compartments in which your source databases reside.

  1. Create a user-defined dynamic group for the Autonomous Databases in the compartment from the OCI console.
    ALL {resource.type = 'autonomousdatabase', resource.compartment.id = '<compartmentId>'}

    Replace the compartmentId placeholder with the appropriate value in the above policy

    This policy may require multiple lines if more than one compartment is involved (one line for each compartment).

  2. Give permission to the dynamic group defined in step 1 to manage the Object Storage bucket.
    allow dynamic-group '<UserDefinedDynamicGroupName>' to manage object-family in compartment <Compartment name of the bucket> where ANY{target.bucket.name='<Bucket Name>'} 

    Replace the dynamic group name, compartment name and object storage bucket name placeholders with the appropriate values in the above policy.

ADB-D Prerequisites

Prior to registering an ADB-D database, you need to perform the following steps:

  1. Create an Identity and Access Management (IAM) user and write policies to provide access to the AWR Hub Object Storage Bucket by the IAM user.

    Add the IAM user to an IAM group and give permission to the group to manage the Object Storage bucket by writing the following policy:

    allow group '<UserDefinedGroupName>' to manage object-family in compartment <Compartment name of the bucket> where ANY{target.bucket.name='<Bucket Name>'}
  2. Generate API keys for the IAM user using the Add API Key option and download the private and public key for future reference.
    Graphic shows API Keys


    Graphic shows the API Key dialog.

  3. Once the API key is added for the IAM user, click View Configuration File and copy and save the contents locally.
    Graphic shows the View Configuration file menu option.

  4. Run the following script to create an OCI User principal based credential in ADB-D (ATP-D/ ADW-D) that you want to register as an AWR source database.
    
    BEGIN                                                                                                                     
      DBMS_CLOUD.CREATE_CREDENTIAL(
        credential_name => 'AWRHUB_KEY_NEW',                                                                                                                                                                               
        user_ocid       => '<user_ocid>',                                                                                                                           
        tenancy_ocid    => '<tenancy_ocid>',                                                                                                                         
        private_key     => '<content of the private key file (excluding -----BEGIN RSA PRIVATE KEY----- / -----END RSA PRIVATE KEY----->',
        fingerprint     => '<finger_print>') ;
     END ;/

Configure the Source Database

External Databases

For information on registering external databases with AWR Hub, refer to the MOS note: Set up AWR Hub source for pre-19c (Doc ID 2877452.1).

ADB-S and ADB-D

  1. From the Operations Insights menu, select Administration and then Warehouse.
  2. From the AWR Hub tab, click Register Database Information. The Register Database Information pop-up displays.
    Graphic shows the Register Database Information dialog.

    Here, you'll find PL/SQL scripts used to register the source database with the AWR Hub.

  3. Click Copy to copy the PL/SQL code blocks.

    For ADB-S, the following PL/SQL block is used to register the source database with AWR Hub:

    DECLARE
      -- Ask for database source name (max 10 characters).
      -- The value entered must be unique for each database registered to the same AWR Hub.
      db_src_name VARCHAR2(10) := '&SourceName';
    BEGIN
      -- Enable the resource principal in the database.
      -- This allows uploading AWR snapshots to OCI Object Storage using resource principal credentials.
      DBMS_CLOUD_ADMIN.ENABLE_RESOURCE_PRINCIPAL();
      -- Register the database as an AWR source.
      dbms_awrhub.register_source(
        hub_name => 'HUB_CLOUD',
        source_name => db_src_name,
        hub_mailbox => 'http://the.awr.hub.mailbox.Url.returned.by.the.restUrl',
        mailbox_type => 'OBJECT_STORE',
        mailbox_cred => 'OCI$RESOURCE_PRINCIPAL');
      -- Setup a cache at the source side to upload AWR snapshots via data pump directory.
      dbms_awrhub.setup_source_cache_mailbox(
        hub_name => 'HUB_CLOUD',
        source_name => db_src_name,
        cache_mailbox => 'DATA_PUMP_DIR');
    END;
    /

    For ADB-D, the following PL/SQL block is used to register the source database with AWR Hub:

    DECLARE
      -- Ask for database source name (max 10 characters).
      -- The value entered must be unique for each database registered to the same AWR Hub.
      db_src_name VARCHAR2(10) := '&SourceName';
    BEGIN
      
      -- Register the database as an AWR source.
      dbms_awrhub.register_source(
        hub_name => 'HUB_CLOUD',
        source_name => db_src_name,
        hub_mailbox => 'http://the.awr.hub.mailbox.Url.returned.by.the.restUrl',
        mailbox_type => 'OBJECT_STORE',
        mailbox_cred => 'AWRHUB_KEY_NEW');
      -- Setup a cache at the source side to upload AWR snapshots via data pump directory.
      dbms_awrhub.setup_source_cache_mailbox(
        hub_name => 'HUB_CLOUD',
        source_name => db_src_name,
        cache_mailbox => 'DATA_PUMP_DIR');
    END;
    /
  4. Execute the PL/SQL on each target database using any SQL editor, such as SQL Plus or SQL Developer.

    The source database name is user-provided and can be anything up to 10 characters in length. When you run the script, you will be prompted for the source database name. Running the script uploads the AWR snapshot from the source DB to the AWR Hub.

    It may take some time to run the blocks. Data transfer may take up to 24 hours.

Viewing Source Databases

To view the source databases that are uploading AWR snapshots, click AWR Hub in the Operations Insights menu

The process above registers a database. Similarly, you can unregister a database by clicking Unregister Database Information.

Create an Operations Insights Warehouse Database User

You need to create a warehouse database user who will have read-only access and access to the dbms_workload_repository PL/SQL package to run AWR reports.

  1. Click the Warehouse Database Users tab.
  2. Click Create User. The Create Warehouse Database User page displays.
  3. Enter a User Name and Password.
    Note

    Password must be 12 to 30 characters and contain at least one uppercase letter, one lowercase letter, and one number. The password cannot contain the double quote (") character or the username entered above. It must be different than the last four passwords. You cannot reuse a password within 24 hours.
  4. Click Create User.

Access the Operations Insights Warehouse

In order to access the Operations Insights Warehouse, which is an Autonomous Database, a wallet is needed.

  1. From the Operations Insights menu, select Administration and then Warehouse. The Warehouse screen displays.
  2. Click DB Connection. The Database Connection region displays.
  3. Click Download Wallet. Enter and confirm a password for this wallet.
    Note

    Some database clients will require that you provide both the wallet and password to connect to your database, while other clients will auto-login using the wallet without a password.
  4. Click Download. The OPSIWarehouseWallet.zip file is downloaded to your machine.
  5. Connect to the Operations Insights Warehouse using the downloaded cloud wallet.

Using AWR Hub

Once you've set up AWR Hub, you're ready to use the feature.

Using AWR Explorer

AWR Explorer in Operations Insights lets you compare database performance data stored in AWR Hub without having to toggle between hourly AWR reports.

From a single interface that integrates performance and data visualization tools, you can view historical performance data from AWR snapshots in easy-to-interpret charts, thus allowing you to quickly analyze performance trends and detect issues.

Using AWR Explorer in Operations Insights lets you:

  • View and analyze AWR data across different database systems
  • Easily identify performance trends without needing to toggle between hourly AWR reports
  • Visualize different aspects of Oracle Database performance data which can be helpful in detecting performance issues

Accessing AWR Explorer

To access AWR Explorer:

  1. Open the navigation menu and click Observability & Management. Under Operations Insights, click AWR Hub. The AWR Hub page is displayed.
    Note

    All databases providing AWR snapshots are shown in the table. If you have not yet set up AWR Hub or configured source databases to send AWR snapshots to AWR Hub, see Configuring AWR Hub.
  2. Click More (vertical ellipses) in the last column to display the pop-up menu and select Launch AWR Explorer.
    Graphic shows the launch AWR Explorer menu item

    AWR Explorer is displayed showing performance charts for the chosen database.


    Image shows the AWR Explorer page with the Load tab selected.

The following fields are displayed in AWR Explorer. You can select the options in these fields to determine the data to be displayed:

  • Database: The name used when registering the database with AWR Hub.
  • Time Range: Duration of the snapshot range for which AWR data is displayed. By default, the duration of the latest AWR snapshot range is selected. However, if the snapshot range has more than six snapshots, then the default time range is the duration of the most recent six snapshots in the snapshot range. For example, if the duration of each snapshot (snapshot interval) is 10 minutes, then the time range is 6 * 10 minutes = 1 hour and the data within this time range is displayed.

    Click the Time Range field to view the Custom Time Range dialog and change the duration within the selected time range, if required.

  • Time Zone: Time zone in which AWR data is displayed. By default, the UTC (Coordinated Universal Time) time zone is selected. Click this field to replace the default time zone with your local browser time zone.

AWR Explorer Tabs

AWR Explorer displays the following tabs, which provide information on the important aspects of database performance within the selected time range. These tabs display charts that greatly simplify the ­interpretation and comparison of AWR data.

  • Load Profile: This tab displays historical system statistics from AWR tables such as DBA_HIST_SYSSTAT. The charts display the default SYSSTAT categories, such as Time Model Overview, Connections, and Logons, which provide an overview of the global health of the database.

    On the Load Profile tab, you can add charts to display other supported system statistics. To do so:

    1. Ensure that Custom is selected in the System Statistics drop-down list.
    2. Click Add Load Profile Chart.
    3. In the Add Custom Load Profile Chart dialog, enter a title for the new chart, select the system statistics that you want to display, and click Add Load Profile Chart.
  • Wait Events: This tab displays the top ten wait events sorted by wait time from AWR tables such as DBA_HIST_SYSTEM_EVENT. The charts display the wait event and the corresponding Waits per Second and Average Wait Time information.

    On the Wait Events tab, you can also:

    1. Add new charts to display more wait events. To do so, select a wait event in the Wait Event drop-down list and click Add Wait Event Chart.
    2. View a histogram of the wait event with a breakdown of the percentage of waits by their duration, within a selected snapshot range. To do so, click the Show Histogram option available for each wait event chart. Note that you can also click on the chart to navigate to the Show Histogram dialog.
  • Activity: This tab enables you to view the performance trend of the database by selecting an Average Active Sessions dimension and viewing the top activity for that dimension in the selected time range. You can also scroll down to view the top activity by additional dimensions based on the dimension selected in the Average Active Sessions chart. For example, if the default dimension, Wait Class is selected in the Average Active Sessions chart, then you can view the top SQL ID and Wait Event by Wait Class or select other dimensions in the menus on the top left of the tables.
  • Database Parameters: This tab displays all the database parameters that were changed and you can click the link in the Changed column to view change history. The change history information includes details such as the ID of the snapshot when the database parameter was changed, the start and end time of the snapshot, and the changed value.

    On this tab, you can also view all the database parameters, including those that were not changed. To do so, deselect the Hide Unchanged Parameters check box.

AWR Chart Visualization Options

AWR Explorer provides the following data visualization options to enable you to filter data and make changes to how data is displayed in the charts.

  • Show data iconsShow the data: Click to view raw data in the chart. You can use this option to copy and analyze raw data.
  • Icons for bar and lin charts Change to bar chart or Change to line chart Click to toggle between a bar chart and a line chart. Note that a change made to the visualization option in one chart applies to all the charts on the tab.
  • icon zoom in/outZoom In/Out: Click to alter the scope of what is displayed in the chart. Note that after clicking the Zoom in icon, you can use mouse and touch pad to scroll to the right or left of the chart, and zoom in and out. Note that a change made using the zoom options in one chart applies to all the charts on the tab.
  • restore iconRestore: Click to undo any changes made to the charts using the other options and return to the default display.
  • save chart as iconSave chart as: Click to save the chart in .png format.
  • remove chart iconRemove chart: Click to remove the chart from the tab. Note that you can add the chart again by selecting it in the drop-down list on the tab and clicking Add <name of the tab> Chart.

Generate Reports

You can generate and download various reports from the database in AWR Explorer.

To do so, click Reports and select one of the following options:

  • AWR: In the Generate AWR Report dialog, select the snapshot range and click Generate Report to generate and download an AWR report with additional performance data.
  • SQL: In the Generate SQL Report dialog, select the snapshot range, enter a SQL ID, and click Generate Report to generate and download a SQL Health Check report with additional performance data for the specified SQL statement.
  • ASH: In the Generate ASH Report dialog, specify the start and end time and click Generate Report to generate and download an ASH report with additional Active Session History data.

Generate an AWR Hub Report

An AWR report contains performance statistics on database activity between two points in time – two snapshots. This can be used for problem detection and self-tuning purposes. For example, you can compare database statistics captured during a period of poor performance and compare it with normal baseline performance to identify and diagnose problems. Because AWR Hub stores AWR snapshots for multiple databases, you can easily compare performance statistics across your database fleet.

Typical Use Cases

  • Track and analyze SQL plan changes for workloads within a single database
  • Track and analyze SQL plan changes for workloads across multiple databases
  • Understand past and present workload (SQL) performance over a period of two years or above.
  • Build custom dashboards to plot SQL performance statistics, database waits, CPU usage history, connections established, etc.

Create an AWR Report

  1. Click AWR Hub from the left navigation menu to access the AWR Hub dashboard.
  2. From the Databases providing AWR snapshots table, locate the database for which you want to generate an AWR report.
  3. Click More (vertical ellipses) in the last column to display the pop-up menu and select Generate AWR Report. The Generate Automatic Workload Repository Report dialog displays.
    Graphic displays the Generate Automatic Workload Repository Report dialog.

    Selecting a different Snapshot Start Time changes the list of available snapshots for the Start Snapshot and End Snapshots drop-down menus.

  4. Click Download. The report is generated in HTML.
    Graphic shows a generated AWR report.

Examples: SQL Use Cases

The following SQL examples are useful for analyzing the data ingested in the AWR Hub server and identifying potential problems in the source databases.

To connect to ADW with the user you created in Create an Operations Insights Warehouse Database User and the wallet you downloaded in Access the Operations Insights Warehouse, you'll need a tool such as Oracle SQL Developer.

For information about connecting to ADW with SQL Developer, see:

Example 7-1 SQL Plan Changes for a SQLID on a Single Database

select sql_id, plan_hash_value,
       sum(EXECUTIONS_DELTA) executions,
       sum(ROWS_PROCESSED_delta) crows,
       round(sum(CPU_TIME_delta)/1000000/60) cpu_mins,
       round(sum(ELAPSED_TIME_delta)/1000000/60)  ela_mins
  from dba_hist_sqlstat
where  SQL_ID=trim('&sqlid')
  AND  dbid='&dbid'
group by sql_id, plan_hash_value
order by sql_id, cpu_mins

Example 7-2 SQL Plan Changes for a SQLID Across Multiple AWR Source Databases


select ar.source_name source_name, ar.source_awrid dbid, s.sql_id, s.plan_hash_value,
       sum(EXECUTIONS_DELTA) executions,
       sum(ROWS_PROCESSED_delta) crows,
       round(sum(CPU_TIME_delta)/1000000/60) cpu_mins,
       round(sum(ELAPSED_TIME_delta)/1000000/60)  ela_mins
  from dba_hist_sqlstat s, awrhub_registration ar
where s.SQL_ID=trim(:sqlid)
  AND s.dbid=ar.source_awrid
  and s.dbid in (:dbid1, :dbid2)
  AND ar.registration_state = 'ACTIVE'
group by ar.source_name, ar.source_awrid, s.sql_id, s.plan_hash_value
order by s.sql_id, cpu_mins

Example 7-3 SQL Past and Present Performance for a Given Time Range for a SQLID on a Single Database

SELECT to_char(Min(s.end_interval_time), 'DD-MON-YYYY DY HH24:MI') sample_end,
q.sql_id,
q.plan_hash_value,
Sum(q.executions_delta) executions,
Round(Sum(disk_reads_delta) / greatest(Sum(executions_delta), 1), 1)
pio_per_exec,
Round(Sum(buffer_gets_delta) / greatest(Sum(executions_delta), 1), 1)
lio_per_exec,
Round(( Sum(elapsed_time_delta) / greatest(Sum(executions_delta), 1) /
1000 ), 1
) msec_exec
FROM dba_hist_sqlstat q,
dba_hist_snapshot s
WHERE q.sql_id = trim('&sqlid')
AND s.snap_id = q.snap_id
AND s.dbid = q.dbid
AND s.dbid = '&dbid'
AND s.instance_number = q.instance_number
AND s.end_interval_time >= to_date(trim('&start_time.'),
'dd-mon-yyyy hh24:mi')
AND s.begin_interval_time <= to_date(trim('&end_time.'),
'dd-mon-yyyy hh24:mi')
GROUP BY s.snap_id,
q.sql_id,
q.plan_hash_value
ORDER BY s.snap_id,
q.sql_id,
q.plan_hash_value