6 Gathering Database Statistics

6.1.3.1 Fixed Baselines

A fixed baseline corresponds to a fixed, contiguous time period in the past that you specify. Before creating a fixed baseline, carefully consider the time period you choose as a baseline, because the baseline should represent the system operating at an optimal level. In the future, you can compare the baseline with other baselines or snapshots captured during periods of poor performance to analyze performance degradation over time.

6.1.3.2 Moving Window Baselines

A moving window baseline corresponds to all AWR data that exists within the AWR retention period. This is useful when using adaptive thresholds because the database can use AWR data in the entire AWR retention period to compute metric threshold values.

Oracle Database automatically maintains a system-defined moving window baseline. The default window size for the system-defined moving window baseline is the current AWR retention period, which by default is 8 days. If you are planning to use adaptive thresholds, then consider using a larger moving window—such as 30 days—to accurately compute threshold values. You can resize the moving window baseline by changing the number of days in the moving window to a value that is equal to or less than the number of days in the AWR retention period. Therefore, to increase the size of a moving window, you must first increase the AWR retention period accordingly.

6.1.3.3 Baseline Templates

Baseline templates enable you to create baselines for a contiguous time period in the future. There are two types of baseline templates:

• Single Baseline Templates

• Repeating Baseline Templates

6.1.5.1 Percentage of Maximum Thresholds

The threshold value for percentage of maximum thresholds is computed as a percentage multiple of the maximum value observed for the data in the moving window baseline.

Percentage of maximum thresholds are most useful when a system is sized for peak workloads, and you want to be alerted when the current workload volume approaches or exceeds previous high values. Metrics that have an unknown but definite limiting value are prime candidates for these settings. For example, the redo generated per second metric is typically a good candidate for a percentage of maximum threshold.

6.1.5.2 Significance Level Thresholds

The threshold value for significance level thresholds is set to a statistical percentile that represents how unusual it is to observe values above the threshold value based the data in the moving window baseline.

Significance level thresholds are most useful for metrics that exhibit statistically stable behavior when the system is operating normally, but might vary over a wide range when the system is performing poorly. For example, the response time per transaction metric should be stable for a well-tuned OLTP system, but may fluctuate widely when performance issues arise. Significance level thresholds are meant to generate alerts when conditions produce both unusual metric values and unusual system performance.

Significance level thresholds can be set to one of the following levels:

• High (.95)

  Only 5 in 100 observations are expected to exceed this value.

• Very High (.99)

  Only 1 in 100 observations are expected to exceed this value.

• Severe (.999)

  Only 1 in 1,000 observations are expected to exceed this value.

• Extreme (.9999)

  Only 1 in 10,000 observations are expected to exceed this value.

When you specify a significance level threshold, Oracle Database performs an internal calculation to set the threshold value. In some cases, Oracle Database cannot establish the threshold value at higher significance levels using the data in the baseline, and the significance level threshold is not set.

If you specified a Severe (.999) or Extreme (.9999) significance level threshold and are not receiving alerts as expected, try setting the significance level threshold to a lower value, such as Very High (.99) or High (.95). Alternatively, consider using a percentage of maximum threshold instead. If you change the threshold and find that you are receiving too many alerts, try increasing the number of occurrences to trigger an alert.

6.2.2.1 User Interfaces for Managing Snapshots

The primary interface for managing snapshots is Oracle Enterprise Manager. Whenever possible, you should manage snapshots using Oracle Enterprise Manager.

If Oracle Enterprise Manager is unavailable, then manage snapshots using the DBMS_WORKLOAD_REPOSITORY package in the command-line interface. The DBA role is required to invoke the DBMS_WORKLOAD_REPOSITORY procedures.

6.2.2.2 Creating Snapshots

By default, Oracle Database automatically generates snapshots once every hour. However, you may want to manually create snapshots to capture statistics at times different from those of the automatically generated snapshots.

This section describes how to create snapshots and contains the following topics:

• Creating Snapshots Using the Command-Line Interface

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

6.2.2.3 Dropping Snapshots

By default, Oracle Database automatically purges snapshots that have been stored in AWR for over 8 days. However, you may want to manually drop a range of snapshots to free up space.

This section describes how to drop snapshots and contains the following topics:

• Dropping Snapshots Using the Command-Line Interface

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 22, 
                           high_snap_id => 32, dbid => 3310949047);
END;
/

6.2.2.4 Modifying Snapshot Settings

You can adjust the interval, retention, and captured Top SQL of snapshot generation for a specified database ID, but note that this can affect the precision of the Oracle Database diagnostic tools.

This section describes how to modify snapshot settings and contains the following topics:

• Modifying Snapshot Settings Using the Command-Line Interface

BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( retention => 43200, 
                 interval => 30, topnsql => 100, dbid => 3310949047);
END;
/

6.2.3.1 User Interface for Managing Baselines

The primary interface for managing baselines is Oracle Enterprise Manager. Whenever possible, manage baselines using Oracle Enterprise Manager.

If Oracle Enterprise Manager is unavailable, then manage baselines using the DBMS_WORKLOAD_REPOSITORY package in the command-line interface. The DBA role is required to invoke the DBMS_WORKLOAD_REPOSITORY procedures.

6.2.3.2 Creating a Baseline

By default, Oracle Database automatically maintains a system-defined moving window baseline. However, you may want to manually create a fixed baseline that represents the system operating at an optimal level, so you can compare it with other baselines or snapshots captured during periods of poor performance.

This section describes how to create a baseline using an existing range of snapshots and contains the following topics:

• Creating a Baseline Using the Command-Line Interface

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 270, 
                   end_snap_id => 280, baseline_name => 'peak baseline', 
                   dbid => 3310949047, expiration => 30);
END;
/

6.2.3.3 Dropping a Baseline

To conserve disk space, consider periodically dropping a baseline that is no longer being used. The snapshots associated with a baseline are retained indefinitely until you explicitly drop the baseline or the baseline has expired.

This section describes how to drop an existing baseline and contains the following topics:

• Dropping a Baseline Using the Command-Line Interface

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => 'peak baseline',
                  cascade => FALSE, dbid => 3310949047);
END;
/

6.2.3.4 Renaming a Baseline

This section describes how to rename a baseline and contains the following topics:

• Renaming a Baseline Using the Command-Line Interface

BEGIN
    DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE (
                   old_baseline_name => 'peak baseline', 
                   new_baseline_name => 'peak mondays', 
                   dbid => 3310949047);
END;
/

6.2.3.5 Displaying Baseline Metrics

When used with adaptive thresholds, a baseline contains AWR data that the database can use to compute metric threshold values. This section describes how to display metric threshold settings during the time period captured in a baseline and contains the following topics:

• Displaying Baseline Metrics Using the Command-Line Interface

DBMS_WORKLOAD_REPOSITORY.SELECT_BASELINE_METRICS (
    baseline_name IN VARCHAR2,
             dbid IN NUMBER DEFAULT NULL,
     instance_num IN NUMBER DEFAULT NULL)
RETURN awr_baseline_metric_type_table PIPELINED;

6.2.3.6 Resizing the Default Moving Window Baseline

By default, Oracle Database automatically maintains a system-defined moving window baseline. The default window size for the system-defined moving window baseline is the current AWR retention period, which by default is 8 days. In certain circumstances, you may want to modify the window size of the default moving window baseline, such as increasing its size to more accurately compute threshold values for adaptive thresholds.

This section describes how to resize the window size of the default moving window baseline and contains the following topics:

• Resizing the Default Moving Window Using the Command-Line Interface

BEGIN
    DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (
                   window_size => 30, 
                   dbid => 3310949047);
END;
/

6.2.4.1 User Interfaces for Managing Baseline Templates

The primary interface for managing baseline templates is Oracle Enterprise Manager. Whenever possible, manage baseline templates using Oracle Enterprise Manager.

If Oracle Enterprise Manager is unavailable, then manage baseline templates using the DBMS_WORKLOAD_REPOSITORY package in the command-line interface. The DBA role is required to invoke the DBMS_WORKLOAD_REPOSITORY procedures.

6.2.4.2 Creating a Single Baseline Template

You can use a single baseline template to create a baseline during a single, fixed time interval in the future. For example, you can create a single baseline template to generate a baseline that is captured on April 2, 2012 from 5:00 p.m. to 8:00 p.m.

This section describes how to create a single baseline template and contains the following topics:

• Creating a Single Baseline Template Using the Command-Line Interface

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
                   start_time => '2012-04-02 17:00:00 PST', 
                   end_time => '2012-04-02 20:00:00 PST', 
                   baseline_name => 'baseline_120402', 
                   template_name => 'template_120402', expiration => 30, 
                   dbid => 3310949047);
END;
/

6.2.4.3 Creating a Repeating Baseline Template

You can use a repeating baseline template to automatically create baselines that repeat during a particular time interval over a specific period in the future. For example, you can create a repeating baseline template to generate a baseline that repeats every Monday from 5:00 p.m. to 8:00 p.m. for the year 2012.

This section describes how to create a repeating baseline template and contains the following topics:

• Creating a Repeating Baseline Template Using the Command-Line Interface

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
                   day_of_week => 'monday', hour_in_day => 17,
                   duration => 3, expiration => 30,
                   start_time => '2012-04-02 17:00:00 PST', 
                   end_time => '2012-12-31 20:00:00 PST', 
                   baseline_name_prefix => 'baseline_2012_mondays_', 
                   template_name => 'template_2012_mondays',
                   dbid => 3310949047);
END;
/

6.2.4.4 Dropping a Baseline Template

Periodically, you may want to remove baselines templates that are no longer used to conserve disk space.

This section describes how to drop an existing baseline template and contains the following topics:

• Dropping a Baseline Template Using the Command-Line Interface

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE_TEMPLATE (
                   template_name => 'template_2012_mondays',
                   dbid => 3310949047);
END;
/

6.2.5.1 Extracting AWR Data

The awrextr.sql script extracts AWR data for a range of snapshots from the database into a Data Pump export file. After it is created, you can transport this dump file to another database where you can load the extracted data. To run the awrextr.sql script, you must be connected to the database as the SYS user.

To extract AWR data: 

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/awrextr.sql
   

   A list of the databases in the AWR schema is displayed.

2. Specify the database from which AWR data will be extracted:

   Enter value for db_id: 1377863381
   

   In this example, the database with the database identifier of 1377863381 is selected.

3. Specify the number of days for which you want to list snapshot IDs.

   Enter value for num_days: 2
   

   A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.

4. Define the range of snapshots for which AWR data will be extracted by specifying a beginning and ending snapshot ID:

   Enter value for begin_snap: 30
   Enter value for end_snap: 40
   

   In this example, the snapshot with a snapshot ID of 30 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 40 is selected as the ending snapshot.

   A list of directory objects is displayed.

5. Specify the directory object pointing to the directory where the export dump file will be stored:

   Enter value for directory_name: DATA_PUMP_DIR
   

   In this example, the directory object DATA_PUMP_DIR is selected.

6. Specify a prefix for the name of the export dump file (the .dmp suffix will be automatically appended):

   Enter value for file_name: awrdata_30_40
   

   In this example, an export dump file named awrdata_30_40 will be created in the directory corresponding to the directory object you specified:

   Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
   C:\ORACLE\PRODUCT\12.1.0.1\DB_1\RDBMS\LOG\AWRDATA_30_40.DMP
   Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 08:58:20
   

   Depending on the amount of AWR data that must be extracted, the AWR extract operation may take a while to complete. After the dump file is created, you can use Data Pump to transport the file to another system.

6.2.5.2 Loading AWR Data

After the export dump file is transported to the target system, load the extracted AWR data using the awrload.sql script. The awrload.sql script will first create a staging schema where the snapshot data is transferred from the Data Pump file into the database. The data is then transferred from the staging schema into the appropriate AWR tables. To run the awrload.sql script, you must be connected to the database as the SYS user.

To load AWR data: 

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/awrload.sql
   

   A list of directory objects is displayed.

2. Specify the directory object pointing to the directory where the export dump file is located:

   Enter value for directory_name: DATA_PUMP_DIR
   

   In this example, the directory object DATA_PUMP_DIR is selected.

3. Specify a prefix for the name of the export dump file (the .dmp suffix will be automatically appended):

   Enter value for file_name: awrdata_30_40
   

   In this example, the export dump file named awrdata_30_40 is selected.

4. Specify the name of the staging schema where AWR data will be loaded:

   Enter value for schema_name: AWR_STAGE
   

   In this example, a staging schema named AWR_STAGE will be created where AWR data will be loaded.

5. Specify the default tablespace for the staging schema:

   Enter value for default_tablespace: SYSAUX
   

   In this example, the SYSAUX tablespace is selected.

6. Specify the temporary tablespace for the staging schema:

   Enter value for temporary_tablespace: TEMP
   

   In this example, the TEMP tablespace is selected.

7. A staging schema named AWR_STAGE will be created where AWR data will be loaded. After AWR data is loaded into the AWR_STAGE schema, the data will be transferred into AWR tables in the SYS schema:

   Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
   Completed 113 CONSTRAINT objects in 11 seconds
   Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
   Completed 1 REF_CONSTRAINT objects in 1 seconds
   Job "SYS"."SYS_IMPORT_FULL_03" successfully completed at 09:29:30
   ... Dropping AWR_STAGE user
   End of AWR Load
   

   Depending on the amount of AWR data that must be loaded, the AWR load operation may take a while to complete. After AWR data is loaded, the staging schema will be dropped automatically.

6.3.2.1 Generating an AWR Report for the Local Database

The awrrpt.sql SQL script generates an HTML or text report that displays statistics from a range of snapshot IDs.

To generate an AWR report on the local database instance using the command-line interface: 

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/awrrpt.sql
   

2. Specify whether you want an HTML or a text report:

   Enter value for report_type: text
   

   In this example, a text report is chosen.

3. Specify the number of days for which you want to list snapshot IDs.

   Enter value for num_days: 2
   

   A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.

4. Specify a beginning and ending snapshot ID for the workload repository report:

   Enter value for begin_snap: 150
   Enter value for end_snap: 160
   

   In this example, the snapshot with a snapshot ID of 150 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 160 is selected as the ending snapshot.

5. Enter a report name, or accept the default report name:

   Enter value for report_name: 
   Using the report name awrrpt_1_150_160
   

   In this example, the default name is accepted and an AWR report named awrrpt_1_150_160 is generated.

6.3.2.2 Generating an AWR Report for a Specific Database

The awrrpti.sql SQL script generates an HTML or text report that displays statistics from a range of snapshot IDs using a specific database instance. This script enables you to specify a database identifier and instance for which the AWR report will be generated.

To generate an AWR report on a specific database instance using the command-line interface: 

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/awrrpti.sql
   

2. Specify whether you want an HTML or a text report:

   Enter value for report_type: text
   

   In this example, a text report is chosen.

   A list of available database identifiers and instance numbers are displayed:

   Instances in this Workload Repository schema
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      DB Id    Inst Num DB Name      Instance     Host
   ----------- -------- ------------ ------------ ------------
    3309173529        1 MAIN         main         examp1690
    3309173529        1 TINT251      tint251      samp251
   

3. Enter the values for the database identifier (dbid) and instance number (inst_num):

   Enter value for dbid: 3309173529
   Using 3309173529 for database Id
   Enter value for inst_num: 1
   

4. Specify the number of days for which you want to list snapshot IDs.

   Enter value for num_days: 2
   

   A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.

5. Specify a beginning and ending snapshot ID for the workload repository report:

   Enter value for begin_snap: 150
   Enter value for end_snap: 160
   

   In this example, the snapshot with a snapshot ID of 150 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 160 is selected as the ending snapshot.

6. Enter a report name, or accept the default report name:

   Enter value for report_name: 
   Using the report name awrrpt_1_150_160
   

   In this example, the default name is accepted and an AWR report named awrrpt_1_150_160 is generated on the database instance with a database ID value of 3309173529.

6.3.2.3 Generating an Oracle RAC AWR Report for the Local Database

The awrgrpt.sql SQL script generates an HTML or text report that displays statistics from a range of snapshot IDs using the current database instance in an Oracle Real Application Clusters (Oracle RAC) environment.

To generate an AWR report for Oracle RAC on the local database instance using the command-line interface: 

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/awrgrpt.sql
   

2. Specify whether you want an HTML or a text report:

   Enter value for report_type: html
   

   In this example, an HTML report is chosen.

3. Specify the number of days for which you want to list snapshot IDs.

   Enter value for num_days: 2
   

   A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last day are displayed.

4. Specify a beginning and ending snapshot ID for the workload repository report:

   Enter value for begin_snap: 150
   Enter value for end_snap: 160
   

   In this example, the snapshot with a snapshot ID of 150 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 160 is selected as the ending snapshot.

5. Enter a report name, or accept the default report name:

   Enter value for report_name: 
   Using the report name awrrpt_rac_150_160.html
   

   In this example, the default name is accepted and an AWR report named awrrpt_rac_150_160.html is generated.

6.3.2.4 Generating an Oracle RAC AWR Report for a Specific Database

The awrgrpti.sql SQL script generates an HTML or text report that displays statistics from a range of snapshot IDs using specific databases instances running in an Oracle RAC environment. This script enables you to specify database identifiers and a comma-delimited list of database instances for which the AWR report will be generated.

To generate an AWR report for Oracle RAC on a specific database instance using the command-line interface: 

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/awrgrpti.sql
   

2. Specify whether you want an HTML or a text report:

   Enter value for report_type: html
   

   In this example, an HTML report is chosen.

   A list of available database identifiers and instance numbers are displayed:

   Instances in this Workload Repository schema
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      DB Id    Inst Num DB Name      Instance     Host
   ----------- -------- ------------ ------------ ------------
    3309173529        1 MAIN         main         examp1690
    3309173529        1 TINT251      tint251      samp251
    3309173529        2 TINT251      tint252      samp252
   

3. Enter the value for the database identifier (dbid):

   Enter value for dbid: 3309173529
   Using 3309173529 for database Id
   

4. Enter the value for the instance numbers (instance_numbers_or_all) of the Oracle RAC instances you want to include in the report:

   Enter value for instance_numbers_or_all: 1,2
   

5. Specify the number of days for which you want to list snapshot IDs.

   Enter value for num_days: 2
   

   A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the last 2 days are displayed.

6. Specify a beginning and ending snapshot ID for the workload repository report:

   Enter value for begin_snap: 150
   Enter value for end_snap: 160
   

   In this example, the snapshot with a snapshot ID of 150 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 160 is selected as the ending snapshot.

7. Enter a report name, or accept the default report name:

   Enter value for report_name: 
   Using the report name awrrpt_rac_150_160.html
   

   In this example, the default name is accepted and an AWR report named awrrpt_rac_150_160.html is generated on the database instance with a database ID value of 3309173529.

6.3.2.5 Generating an AWR Report for a SQL Statement on the Local Database

The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement from a range of snapshot IDs. Run this report to inspect or debug the performance of a SQL statement.

To generate an AWR report for a SQL statement on the local database instance using the command-line interface: 

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
   

2. Specify whether you want an HTML or a text report:

   Enter value for report_type: html
   

   In this example, an HTML report is chosen.

3. Specify the number of days for which you want to list snapshot IDs.

   Enter value for num_days: 1
   

   A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the previous day are displayed.

4. Specify a beginning and ending snapshot ID for the workload repository report:

   Enter value for begin_snap: 146
   Enter value for end_snap: 147
   

   In this example, the snapshot with a snapshot ID of 146 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 147 is selected as the ending snapshot.

5. Specify the SQL ID of a particular SQL statement to display statistics:

   Enter value for sql_id: 2b064ybzkwf1y
   

   In this example, the SQL statement with a SQL ID of 2b064ybzkwf1y is selected.

6. Enter a report name, or accept the default report name:

   Enter value for report_name: 
   Using the report name awrrpt_1_146_147.html
   

   In this example, the default name is accepted and an AWR report named awrrpt_1_146_147 is generated.

6.3.2.6 Generating an AWR Report for a SQL Statement on a Specific Database

The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement from a range of snapshot IDs using a specific database instance.This script enables you to specify a database identifier and instance for which the AWR report will be generated. Run this report to inspect or debug the performance of a SQL statement on a specific database and instance.

To generate an AWR report for a SQL statement on a specific database instance using the command-line interface: 

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/awrsqrpi.sql
   

2. Specify whether you want an HTML or a text report:

   Enter value for report_type: html
   

   In this example, an HTML report is chosen.

   A list of available database identifiers and instance numbers are displayed:

   Instances in this Workload Repository schema
   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      DB Id    Inst Num DB Name      Instance     Host
   ----------- -------- ------------ ------------ ------------
    3309173529        1 MAIN         main         examp1690
    3309173529        1 TINT251      tint251      samp251
   

3. Enter the values for the database identifier (dbid) and instance number (inst_num):

   Enter value for dbid: 3309173529
   Using 3309173529 for database Id
   Enter value for inst_num: 1
   Using 1 for instance number
   

4. Specify the number of days for which you want to list snapshot IDs.

   Enter value for num_days: 1
   

   A list of existing snapshots for the specified time range is displayed. In this example, snapshots captured in the previous day are displayed.

5. Specify a beginning and ending snapshot ID for the workload repository report:

   Enter value for begin_snap: 146
   Enter value for end_snap: 147
   

   In this example, the snapshot with a snapshot ID of 146 is selected as the beginning snapshot, and the snapshot with a snapshot ID of 147 is selected as the ending snapshot.

6. Specify the SQL ID of a particular SQL statement to display statistics:

   Enter value for sql_id: 2b064ybzkwf1y
   

   In this example, the SQL statement with a SQL ID of 2b064ybzkwf1y is selected.

7. Enter a report name, or accept the default report name:

   Enter value for report_name: 
   Using the report name awrrpt_1_146_147.html
   

   In this example, the default name is accepted and an AWR report named awrrpt_1_146_147 is generated on the database instance with a database ID value of 3309173529.

6.4.1.1 About Performance Hub Active Report Tabs

Performance Hub active report contains interactive tabs that enable you to view and navigate through performance data categorized into various performance areas.

The tabs contained in a Performance Hub active report include the following:

• Summary

  The Summary tab provides an overview of system performance, including resource consumption, average active sessions, and load profile information. This tab is available for real-time data as well as historical data.

• Activity

  The Activity tab displays ASH analytics. This tab is available for real-time data as well as historical data.

• Workload

  The Workload tab displays metric information about the workload profile, such as call rates, logon rate, and top SQL. This tab is available for real-time data as well as historical data.

• RAC

  The RAC tab displays metrics specific to Oracle RAC, such as the number of global cache blocks received and the average block latency. This tab is only available in Oracle RAC environments. This tab is available for real-time data as well as historical data.

• Monitored SQL

  The Monitored SQL tab displays information about monitored SQL statements. This tab is available for real-time data as well as historical data.

• ADDM

  The ADDM tab displays information for ADDM analysis tasks and Real-Time ADDM analysis reports. This tab is available for real-time data as well as historical data.

• Current ADDM Findings

  The Current ADDM Findings tab displays a real-time analysis of system performance for the past 5 minutes. This tab is only available if the specified time period for the Performance Hub active report is within the past hour. This tab is available only for real-time data.

• Database time

  The Database Time tab displays wait events by category for various metrics. This tab is available only for historical data.

• Resources

  The Resources tab displays operating system and I/O usage statistics. This tab is available only for historical data.

• System Statistics

  The System Statistics tab displays database and system statistics. This tab is available only for historical data.

6.4.1.2 About Performance Hub Active Report Types

You can choose the level of details displayed within each tab of the Performance Hub active report by selecting the report type.

The available report types for the Performance Hub active report include the following:

• Basic

  Only the basic information for all the tabs is saved to the report.

• Typical

  In addition to the information saved in the basic report type, the SQL Monitor information for the top SQL statements contained in the Monitored SQL tab and the ADDM reports are saved to the report.

• All

  In addition to the information saved in the typical report type, the SQL Monitor information for all SQL statements contained in the Monitored SQL tab and all detailed reports for all tabs are saved to the report.