5 Automatic Performance Statistics

5.1.1.1 Wait Events

Wait events are statistics that are incremented by a server process/thread to indicate that it had to wait for an event to complete before being able to continue processing. Wait event data reveals various symptoms of problems that might be impacting performance, such as latch contention, buffer contention, and I/O contention.

To enable easier high-level analysis of the wait events, the events are grouped into classes. The wait event classes include: Administrative, Application, Cluster, Commit, Concurrency, Configuration, Idle, Network, Other, Scheduler, System I/O, and User I/O.

The wait classes are based on a common solution that usually applies to fixing a problem with the wait event. For example, exclusive TX locks are generally an application level issue and HW locks are generally a configuration issue.

The following list includes common examples of the waits in some of the classes:

• Application: locks waits caused by row level locking or explicit lock commands

• Commit: waits for redo log write confirmation after a commit

• Idle: wait events that signify the session is inactive, such as SQL*Net message from client

• Network: waits for data to be sent over the network

• User I/O: wait for blocks to be read off a disk

Wait event statistics for an instance include statistics for both background and foreground processes. Because you would typically focus your effort in tuning foreground activities, overall instance activity is broken down into foreground and background statistics in the relevant V$ views to facilitate tuning.

The V$SYSTEM_EVENT view provides wait event statistics for the foreground activities of an instance as well as the wait event statistics for the instance as a whole. The V$SYSTEM_WAIT_CLASS view provides these wait event statistics (foreground and instance statistics) after aggregating to wait classes.

The V$SESSION_EVENT and V$SYSTEM_WAIT_CLASS views provide wait event and wait class statistics at session level.

5.1.1.2 Time Model Statistics

When tuning an Oracle system, each component has its own set of statistics. To look at the system as a whole, it is necessary to have a common scale for comparisons. Because of this, most Oracle advisories and reports describe statistics in terms of time. In addition, the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views provide time model statistics. Using the common time instrumentation helps to identify quantitative effects on the database operations.

The most important of the time model statistics is DB time. This statistics represents the total time spent in database calls and is an indicator of the total instance workload. It is calculated by aggregating the CPU and wait times of all sessions not waiting on idle wait events (non-idle user sessions).

DB time is measured cumulatively from the time that the instance was started. Because DB time it is calculated by combining the times from all non-idle user sessions, it is possible that the DB time can exceed the actual time elapsed since the instance started up. For example, an instance that has been running for 30 minutes could have four active user sessions whose cumulative DB time is approximately 120 minutes.

The objective for tuning an Oracle system could be stated as reducing the time that users spend in performing some action on the database, or simply reducing DB time. Other time model statistics provide quantitative effects (in time) on specific actions, such as logon operations and hard and soft parses.

5.1.1.3 Active Session History

The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance. Active sessions are sampled every second and are stored in a circular buffer in SGA. Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session. This includes any session that was on the CPU at the time of sampling.

Each session sample is a set of rows and the V$ACTIVE_SESSION_HISTORY view returns one row for each active session per sample, returning the latest session sample rows first. Because the active session samples are stored in a circular buffer in SGA, the greater the system activity, the smaller the number of seconds of session activity that can be stored in the circular buffer. This means that the duration for which a session sample appears in the V$ view, or the number of seconds of session activity that is displayed in the V$ view, is completely dependent on the database activity.

As part of the Automatic Workload Repository (AWR) snapshots, the content of V$ACTIVE_SESSION_HISTORY is also flushed to disk. Because the content of this V$ view can get quite large during heavy system activity, only a portion of the session samples is written to disk.

By capturing only active sessions, a manageable set of data is represented with the size being directly related to the work being performed rather than the number of sessions allowed on the system. Using the Active Session History enables you to examine and perform detailed analysis on both current data in the V$ACTIVE_SESSION_HISTORY view and historical data in the DBA_HIST_ACTIVE_SESS_HISTORY view, often avoiding the need to replay the workload to gather additional performance tracing information. Active Session History also contains execution plan information for each SQL statement that is captured. This information can be used to identify which part of the SQL execution contributed most significantly to the SQL elapsed time. The data present in ASH can be rolled up on various dimensions that it captures, including the following:

• SQL identifier of SQL statement

• SQL plan identifier and hash value of the SQL plan used to execute the SQL statement

• SQL execution plan information

• Object number, file number, and block number

• Wait event identifier and parameters

• Session identifier and session serial number

• Module and action name

• Client identifier of the session

• Service hash identifier

• Consumer group identifier

Active Session History information over a specified duration can be gathered into a report. For more information, see "Generating Active Session History Reports".

5.1.1.4 System and Session Statistics

A large number of cumulative database statistics are available on a system and session level through the V$SYSSTAT and V$SESSTAT views.

5.1.2.1 CPU Statistics

CPU utilization is the most important operating system statistic in the tuning process. Get CPU utilization for the entire system and for each individual CPU on multi-processor environments. Utilization for each CPU can detect single-threading and scalability issues.

Most operating systems report CPU usage as time spent in user space or mode and time spent in kernel space or mode. These additional statistics allow better analysis of what is actually being executed on the CPU.

On an Oracle data server system, where there is generally only one application running, the server runs database activity in user space. Activities required to service database requests (such as scheduling, synchronization, I/O, memory management, and process/thread creation and tear down) run in kernel mode. In a system where all CPU is fully utilized, a healthy Oracle system runs between 65% and 95% in user space.

The V$OSSTAT view captures machine level information in the database, making it easier for you to determine if there are hardware level resource issues. The V$SYSMETRIC_HISTORY view shows a one-hour history of the Host CPU Utilization metric, a representation of percentage of CPU usage at each one-minute interval. The V$SYS_TIME_MODEL view supplies statistics on the CPU usage by the Oracle database. Using both sets of statistics enable you to determine whether the Oracle database or other system activity is the cause of the CPU problems.

5.1.2.2 Virtual Memory Statistics

Virtual memory statistics should mainly be used as a check to validate that there is very little paging or swapping activity on the system. System performance degrades rapidly and unpredictably when paging or swapping occurs.

Individual process memory statistics can detect memory leaks due to a programming failure to deallocate memory taken from the process heap. These statistics should be used to validate that memory usage does not increase after the system has reached a steady state after startup. This problem is particularly acute on shared server applications on middle tier machines where session state may persist across user interactions, and on completion state information that is not fully deallocated.

5.1.2.3 Disk I/O Statistics

Because the database resides on a set of disks, the performance of the I/O subsystem is very important to the performance of the database. Most operating systems provide extensive statistics on disk performance. The most important disk statistics are the current response time and the length of the disk queues. These statistics show if the disk is performing optimally or if the disk is being overworked.

Measure the normal performance of the I/O system; typical values for a single block read range from 5 to 20 milliseconds, depending on the hardware used. If the hardware shows response times much higher than the normal performance value, then it is performing badly or is overworked. This is your bottleneck. If disk queues start to exceed two, then the disk is a potential bottleneck of the system.

Oracle Database also maintains a consistent set of I/O statistics for the I/O calls it issues. These statistics are captured for both single and multi block read and write operations in the following dimensions:

• Consumer group

  When Oracle Database Resource Manager is enabled, I/O statistics for all consumer groups that are part of the currently enabled resource plan are captured in the V$IOSTAT_CONSUMER_GROUP view. These cumulative statistics will be sampled every hour and stored as historical statistics in the AWR.

• Database file

  I/O statistics of database files that are or have been accessed are captured in the V$IOSTAT_FILE view.

• Database function

  I/O statistics for database functions (such as the LGWR and DBWR) are captured in the V$IOSTAT_FUNCTION view

For information about using views in Oracle Database to identify I/O problems, see "Identifying I/O Problems Using V$ Views".

5.1.2.4 Network Statistics

Network statistics can be used in much the same way as disk statistics to determine if a network or network interface is overloaded or not performing optimally. In today's networked applications, network latency can be a large portion of the actual user response time. For this reason, these statistics are a crucial debugging tool.

Oracle Database maintains a set of network I/O statistics in the V$IOSTAT_NETWORK view. For information about using the V$IOSTAT_NETWORK view in Oracle Database to identify network issues, see "Identifying Network Issues".

5.1.2.5 Operating System Data Gathering Tools

Table 5-1 shows the various tools for gathering operating statistics on UNIX. For Windows, use the Performance Monitor tool.

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

For information about managing fixed baselines, see "Managing Baselines".

5.2.2.2 Moving Window Baseline

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 AWR data in the entire AWR retention period can be used 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, 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 will first need to increase the AWR retention period accordingly.

For information about resizing the moving window baseline, see "Modifying the Window Size of the Default Moving Window Baseline".

5.2.2.3 Baseline Templates

You can also create baselines for a contiguous time period in the future using baseline templates. There are two types of baseline templates: single and repeating.

A single baseline template can be used to create a baseline for a single contiguous time period in the future. This is useful if you know beforehand of a time period that you want to capture in the future. For example, you may want to capture the AWR data during a system test that is scheduled for the upcoming weekend. In this case, you can create a single baseline template to automatically capture the time period when the test will take place.

A repeating baseline template can be used to create and drop baselines based on a repeating time schedule. This is useful if you want Oracle Database to automatically capture a contiguous time period on an ongoing basis. For example, you may want to capture the AWR data during every Monday morning for a month. In this case, you can create a repeating baseline template to automatically create baselines on a repeating schedule for every Monday, and automatically remove older baselines after a specified expiration interval such as one month.

For information about managing baseline templates, see "Managing Baseline Templates".

5.3.1.1 Creating Snapshots

You can manually create snapshots with the CREATE_SNAPSHOT procedure if you want to capture statistics at times different than those of the automatically generated snapshots. For example:

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;
/

In this example, a snapshot for the instance is created immediately with the flush level specified to the default flush level of TYPICAL. You can view this snapshot in the DBA_HIST_SNAPSHOT view.

5.3.1.2 Dropping Snapshots

You can drop a range of snapshots using the DROP_SNAPSHOT_RANGE procedure. To view a list of the snapshot Ids along with database Ids, check the DBA_HIST_SNAPSHOT view. For example, you can drop the following range of snapshots:

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

In the example, the range of snapshot Ids to drop is specified from 22 to 32. The optional database identifier is 3310949047. If you do not specify a value for dbid, the local database identifier is used as the default value.

Active Session History data (ASH) that belongs to the time period specified by the snapshot range is also purged when the DROP_SNAPSHOT_RANGE procedure is called.

5.3.1.3 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 diagnostic tools.

The INTERVAL setting affects how often in minutes that snapshots are automatically generated. The RETENTION setting affects how long in minutes that snapshots are stored in the workload repository. The TOPNSQL setting affects the number of Top SQL to flush for each SQL criteria (Elapsed Time, CPU Time, Parse Calls, Shareable Memory, and Version Count). The value for this setting will not be affected by the statistics/flush level and will override the system default behavior for the AWR SQL collection. It is possible to set the value for this setting to MAXIMUM to capture the complete set of SQL in the cursor cache, though by doing so (or by setting the value to a very high number) may lead to possible space and performance issues since there will more data to collect and store. To adjust the settings, use the MODIFY_SNAPSHOT_SETTINGS procedure. For example:

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

In this example, the retention period is specified as 43200 minutes (30 days), the interval between each snapshot is specified as 30 minutes, and the number of Top SQL to flush for each SQL criteria as 100. If NULL is specified, the existing value is preserved. The optional database identifier is 3310949047. If you do not specify a value for dbid, the local database identifier is used as the default value. You can check the current settings for your database instance with the DBA_HIST_WR_CONTROL view.

5.3.2.1 Creating a Baseline

This section describes how to create a baseline using an existing range of snapshots.

To create a baseline:

1. Review the existing snapshots in the DBA_HIST_SNAPSHOT view to determine the range of snapshots that you want to use.

2. Use the CREATE_BASELINE procedure to create a baseline using the desired range of snapshots:

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

   In this example, 270 is the start snapshot sequence number and 280 is the end snapshot sequence. The name of baseline is peak baseline. The optional database identifier is 3310949047. If you do not specify a value for dbid, the local database identifier is used as the default value. The optional expiration parameter is set to 30, so the baseline will expire and be dropped automatically after 30 days. If you do not specify a value for expiration, the baseline will never expire.

The system automatically assign a unique baseline Id to the new baseline when the baseline is created. The baseline Id and database identifier are displayed in the DBA_HIST_BASELINE view.

5.3.2.2 Dropping a Baseline

This section describes how to drop an existing baseline. Periodically, you may want to drop a baseline that is no longer used to conserve disk space. The snapshots associated with a baseline are retained indefinitely until you explicitly drop the baseline or the baseline has expired.

To drop a baseline:

1. Review the existing baselines in the DBA_HIST_BASELINE view to determine the baseline that you want to drop.

2. Use the DROP_BASELINE procedure to drop the desired baseline:

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

   In the example, the name of baseline is peak baseline. The cascade parameter is set to FALSE, which specifies that only the baseline is dropped. Setting this parameter to TRUE specifies that the drop operation will also remove the snapshots associated with the baseline. The optional dbid parameter specifies the database identifier, which in this example is 3310949047. If you do not specify a value for dbid, the local database identifier is used as the default value.

5.3.2.3 Renaming a Baseline

This section describes how to rename a baseline.

To rename a baseline:

1. Review the existing baselines in the DBA_HIST_BASELINE view to determine the baseline that you want to rename.

2. Use the RENAME_BASELINE procedure to rename the desired baseline:

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

   In this example, the name of the baseline is renamed from peak baseline, as specified by the old_baseline_name parameter, to peak mondays, as specified by the new_baseline_name parameter. The optional dbid parameter specifies the database identifier, which in this example is 3310949047. If you do not specify a value for dbid, the local database identifier is used as the default value.

5.3.2.4 Displaying Baseline Metrics

This section describes how to display metric threshold settings during the time period captured in a baseline. When used with adaptive thresholds, a baseline contains AWR data that can be used to compute metric threshold values. The SELECT_BASELINE_METRICS function enables you to display the summary statistics for metric values in a baseline period.

To display metric information in a baseline:

1. Review the existing baselines in the DBA_HIST_BASELINE view to determine the baseline for which you want to display metric information.

2. Use the SELECT_BASELINE_METRICS function to display the metric information for the desired baseline:

   BEGIN
       DBMS_WORKLOAD_REPOSITORY.SELECT_BASELINE_METRICS (
                      baseline_name => 'peak baseline', 
                      dbid => 3310949047,
                      instance_num => '1');
   END;
   /
   

   In this example, the name of baseline is peak baseline. The optional dbid parameter specifies the database identifier, which in this example is 3310949047. If you do not specify a value for dbid, the local database identifier is used as the default value. The optional instance_num parameter specifies the instance number, which in this example is 1. If you do not specify a value for instance_num, the local instance is used as the default value.

5.3.2.5 Modifying the Window Size of the Default Moving Window Baseline

This section describes how to modify the window size of the default moving window baseline. For information about the default moving window baseline, see "Moving Window Baseline".

To resize the default moving window baseline, use the MODIFY_BASELINE_WINDOW_SIZE procedure:

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

The window_size parameter is used to specify the new window size, in number of days, for the default moving window size. In this example, the window_size parameter is set to 30. The window size must be set to a value that is equal to or less than the value of the AWR retention setting. To set a window size that is greater than the current AWR retention period, you will first need to increase the value of the retention parameter, as described in "Modifying Snapshot Settings".

In this example, the optional dbid parameter specifies the database identifier is 3310949047. If you do not specify a value for dbid, the local database identifier is used as the default value.

5.3.3.1 Creating a Single Baseline Template

This section describes how to create a single baseline template. A single baseline template can be used 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, 2007 from 5:00 p.m. to 8:00 p.m.

To create a single baseline template, use the CREATE_BASELINE_TEMPLATE procedure:

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

The start_time parameter specifies the start time for the baseline to be created. The end_time parameter specifies the end time for the baseline to be created. The baseline_name parameter specifies the name of the baseline to be created. The template_name parameter specifies the name of the baseline template. The optional expiration parameter specifies the expiration, in number of days, for the baseline. If unspecified, the baseline will never expire. The optional dbid parameter specifies the database identifier. If unspecified, the local database identifier is used as the default value.

In this example, a baseline template named template_070402 is created that will generate a baseline named baseline_070402 for the time period from 5:00 p.m. to 8:00 p.m. on April 2, 2007 on the database with a database Id of 3310949047. The baseline will expire after 30 days.

5.3.3.2 Creating a Repeating Baseline Template

This section describes how to create a repeating baseline template. A repeating baseline template can be used 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 2007.

To create a repeating baseline template, use the CREATE_BASELINE_TEMPLATE procedure:

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

The day_of_week parameter specifies the day of the week on which the baseline will repeat. The hour_in_day parameter specifies the hour in the day when the baseline will start. The duration parameter specifies the duration, in number of hours, that the baseline will last. The expiration parameter specifies the number of days to retain each created baseline. If set to NULL, the baselines will never expire. The start_time parameter specifies the start time for the baseline to be created. The end_time parameter specifies the end time for the baseline to be created. The baseline_name_prefix parameter specifies the name of the baseline prefix that will be appended to the data information when the baseline is created. The template_name parameter specifies the name of the baseline template. The optional dbid parameter specifies the database identifier. If unspecified, the local database identifier is used as the default value.

In this example, a baseline template named template_2007_mondays is created that will generate a baseline on every Monday from 5:00 p.m. to 8:00 p.m. beginning on April 2, 2007 at 5:00 p.m. and ending on December 31, 2007 at 8:00 p.m. on the database with a database Id of 3310949047. Each of the baselines will be created with a baseline name with the prefix baseline_2007_mondays_ and will expire after 30 days.

5.3.3.3 Dropping a Baseline Template

This section describes how to drop an existing baseline template. Periodically, you may want to remove baselines templates that are no longer used to conserve disk space.

To drop a baseline template:

1. Review the existing baselines in the DBA_HIST_BASELINE_TEMPLATE view to determine the baseline template you want to drop.

2. Use the DROP_BASELINE_TEMPLATE procedure to drop the desired baseline template:

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

   The template_name parameter specifies the name of the baseline template that will be dropped. In the example, the name of baseline template that will be dropped is template_2007_mondays. The optional dbid parameter specifies the database identifier, which in this example is 3310949047. If you do not specify a value for dbid, the local database identifier is used as the default value.

5.3.4.1 Extracting AWR Data

The awrextr.sql script extracts the AWR data for a range of snapshots from the database into a Data Pump export file. Once created, this dump file can be transported to another system where the extracted data can be loaded. To run the awrextr.sql script, you need to 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 the 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.

5. A list of directory objects is displayed.

   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 the prefix for 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\11.1.0.5\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 needs to be extracted, the AWR extract operation may take a while to complete. Once the dump file is created, you can use Data Pump to transport the file to another system.

5.3.4.2 Loading AWR Data

Once the export dump file is transported to the target system, you can 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 need to 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 the prefix for 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 the 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 the 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 the AWR data will be loaded. After the AWR data is loaded into the AWR_STAGE schema, the data will be transferred into the 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 needs to be loaded, the AWR load operation may take a while to complete. After the AWR data is loaded, the staging schema will be dropped automatically.

5.3.6.1 Generating an AWR Report for a Snapshot Range

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

To generate an AWR report for a range of snapshots:

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.

5.3.6.2 Generating an AWR Report for a Snapshot Range on a Specified Database Instance

The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance. This script enables you to specify a database and instance before entering a range of snapshot Ids.

To generate an AWR report for a range of snapshots on a specific database instance:

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         dlsun1690
    3309173529        1 TINT251      tint251      stint251
   

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.

5.3.6.3 Generating an AWR Report for a SQL Statement

The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for 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 particular SQL statement:

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.

5.3.6.4 Generating an AWR Report for a SQL Statement on a Specified Database Instance

The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified database and instance. This script enables you to specify a database and instance before selecting a SQL statement. 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 particular SQL statement on a specified database instance:

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         dlsun1690
    3309173529        1 TINT251      tint251      stint251
   

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.

5.3.7.1 Generating an AWR Compare Periods Report

The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.

To generate an AWR Compare Periods report:

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/awrddrpt.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 in the first time period.

   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 first time period:

   Enter value for begin_snap: 102
   Enter value for end_snap: 103
   

   In this example, the snapshot with a snapshot Id of 102 is selected as the beginning snapshot, and the snapshot with a snapshot Id of 103 is selected as the ending snapshot for the first time period.

5. Specify the number of days for which you want to list snapshot Ids in the second time period.

   Enter value for num_days2: 1
   

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

6. Specify a beginning and ending snapshot Id for the second time period:

   Enter value for begin_snap2: 126
   Enter value for end_snap2: 127
   

   In this example, the snapshot with a snapshot Id of 126 is selected as the beginning snapshot, and the snapshot with a snapshot Id of 127 is selected as the ending snapshot for the second time period.

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

   Enter value for report_name: 
   Using the report name awrdiff_1_102_1_126.txt
   

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

5.3.7.2 Generating an AWR Compare Periods Report on a Specified Database Instance

The awrddrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance. This script enables you to specify a database and instance before selecting time periods to compare.

To generate an AWR Compare Periods report on a specified database instance:

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/awrddrpi.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. 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         dlsun1690
    3309173529        1 TINT251      tint251      stint251
   

   Enter the values for the database identifier (dbid) and instance number (inst_num) for the first time period:

   Enter value for dbid: 3309173529
   Using 3309173529 for Database Id for the first pair of snapshots
   Enter value for inst_num: 1
   Using 1 for Instance Number for the first pair of snapshots
   

4. Specify the number of days for which you want to list snapshot Ids in the first time period.

   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 first time period:

   Enter value for begin_snap: 102
   Enter value for end_snap: 103
   

   In this example, the snapshot with a snapshot Id of 102 is selected as the beginning snapshot, and the snapshot with a snapshot Id of 103 is selected as the ending snapshot for the first time period.

6. Enter the values for the database identifier (dbid) and instance number (inst_num) for the second time period:

   Enter value for dbid2: 3309173529
   Using 3309173529 for Database Id for the second pair of snapshots
   Enter value for inst_num2: 1
   Using 1 for Instance Number for the second pair of snapshots
   

7. Specify the number of days for which you want to list snapshot Ids in the second time period.

   Enter value for num_days2: 1
   

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

8. Specify a beginning and ending snapshot Id for the second time period:

   Enter value for begin_snap2: 126
   Enter value for end_snap2: 127
   

   In this example, the snapshot with a snapshot Id of 126 is selected as the beginning snapshot, and the snapshot with a snapshot Id of 127 is selected as the ending snapshot for the second time period.

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

   Enter value for report_name: 
   Using the report name awrdiff_1_102_1_126.txt
   

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

5.3.8.1 Generating an ASH Report

The ashrpt.sql SQL script generates an HTML or text report that displays ASH information for a specified duration.

To generate an ASH report:

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/ashrpt.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 begin time in minutes prior to the system date:

   Enter value for begin_time: -10
   

   In this example, 10 minutes before the current time is selected.

4. Enter the duration in minutes that the report for which you want to capture ASH information from the begin time.

   Enter value for duration:
   

   In this example, the default duration of system date minus begin time is accepted.

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

   Enter value for report_name: 
   Using the report name ashrpt_1_0310_0131.txt
   

   In this example, the default name is accepted and an ASH report named ashrpt_1_0310_0131 is generated. The report will gather ASH information beginning from 10 minutes before the current time and ending at the current time.

5.3.8.2 Generating an ASH Report on a Specified Database Instance

The ashrpti.sql SQL script generates an HTML or text report that displays ASH information for a specified duration for a specified database and instance. This script enables you to specify a database and instance before setting the time frame to collect ASH information.

To generate an ASH report on a specified database instance:

1. At the SQL prompt, enter:

   @$ORACLE_HOME/rdbms/admin/ashrpti.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. A list of available database Ids and instance numbers are displayed:

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

   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 begin time in minutes prior to the system date:

   Enter value for begin_time: -10
   

   In this example, 10 minutes before the current time is selected.

5. Enter the duration in minutes that the report for which you want to capture ASH information from the begin time.

   Enter value for duration:
   

   In this example, the default duration of system date minus begin time is accepted.

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

   Enter value for report_name: 
   Using the report name ashrpt_1_0310_0131.txt
   

   In this example, the default name is accepted and an ASH report named ashrpt_1_0310_0131 is generated. The report will gather ASH information on the database instance with a database Id value of 3309173529 beginning from 10 minutes before the current time and ending at the current time.

5.3.9.1 Top Events

The Top Events section describes the top wait events of the sampled session activity categorized by user, background, and priority. Use the information in this section to identify the wait events that may be the cause of the transient performance problem.

The Top Events section contains the following subsections:

• Top User Events

• Top Background Events

• Top Event P1/P2/P3 Values

5.3.9.2 Load Profile

The Load Profile section describes the load analyzed in the sampled session activity. Use the information in this section to identify the service, client, or SQL command type that may be the cause of the transient performance problem.

The Load Profile section contains the following subsections:

• Top Service/Module

• Top Client IDs

• Top SQL Command Types

• Top Phases of Execution

5.3.9.3 Top SQL

The Top SQL section describes the top SQL statements of the sampled session activity. Use this information to identify high-load SQL statements that may be the cause of the transient performance problem.

The Top SQL section contains the following subsections:

• Top SQL with Top Events

• Top SQL with Top Row Sources

• Top SQL Using Literals

• Top Parsing Module/Action

• Complete List of SQL Text

5.3.9.4 Top PL/SQL

The Top PL/SQL section lists the PL/SQL procedures that accounted for the highest percentages of sampled session activity. The PL/SQL Entry Subprogram column lists the application's top-level entry point into PL/SQL. The PL/SQL Current Subprogram column lists the PL/SQL subprogram being executed at the point of sampling. If the value of this column is SQL, then the % Current column shows the percentage of time spent executing SQL for this subprogram.

5.3.9.5 Top Java

The Top Java section describes the top Java programs in the sampled session activity.

5.3.9.6 Top Sessions

The Top Sessions section describes the sessions that were waiting for a particular wait event. Use this information to identify the sessions that accounted for the highest percentages of sampled session activity, which may be the cause of the transient performance problem.

The Top Sessions section contains the following subsections:

• Top Sessions

• Top Blocking Sessions

• Top Sessions Running PQs

5.3.9.7 Top Objects/Files/Latches

The Top Objects/Files/Latches section provides additional information about the most commonly-used database resources and contains the following subsections:

• Top DB Objects

• Top DB Files

• Top Latches

5.3.9.8 Activity Over Time

The Activity Over Time section is one of the most informative sections of the ASH report. This section is particularly useful for longer time periods because it provides in-depth details about activities and workload profiles during the analysis period. The Activity Over Time section is divided into 10 time slots. The size of each time slot varies based on the duration of the analysis period. The first and last slots are usually odd-sized. All inner slots are equally sized and can be compared to each other. For example, if the analysis period lasts for 10 minutes, then all time slots will 1 minute each. On the other hand, if the analysis period lasts for 9 minutes and 30 seconds, then the outer slots may be 15 seconds each and the inner slots will be 1 minute each.

Each of the time slots contains information regarding that particular time slot, as described in Table 5-2.

When comparing the inner slots, perform a skew analysis by identifying spikes in the Event Count and Slot Count columns. A spike in the Event Count column indicates an increase in the number of sampled sessions waiting for a particular event. A spike in the Slot Count column indicates an increase in active sessions, because ASH data is sampled from active sessions only and a relative increase in database workload. Typically, when the number of active session samples and the number of sessions associated with a wait event increases, the slot may be the cause of the transient performance problem.

To generate the ASH report with a user-defined slot size, run the ashrpti.sql script, as described in "Generating an ASH Report on a Specified Database Instance".