Monday, 17 March 2014

AWR reports generating in different ways

Oracle 11g AWR (Automatic Workload Repository)

Oracle has provided many performance monitoring tools over the years. They started off with UTLBSTAT/UTLESTAT scripts followed by Statspack. Starting from Oracle 10g Automatic Workload Repository (AWR) is the new comprehensive tool for statistics gather and reporting

Database Statistics using AWR:

Gathering database statistics like wait events, time model statistics, ASH and system/session stats is enabled by defult using a STATISTICS_LEVEL parameter:

SQL> show parameter statistics_level

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
statistics_level                     string      TYPICAL

the parameter should be set to either TYPICAL or ALL to enable statistics gathering by AWR. If the parameter is set to BASIC, it will disable many database features. AWR can still be executed when the parameter is set to BASIC using DBMS_WORKLOAD_REPOSITORY pacakge but the statistics gathered will not be complete

Snapshots:

Snapshots are sets of historical data for specific time periods that are used for performance comparisons by ADDM. See below mentioned link for ADDM details.
Oracle Database 11g Automatic Database Diagnostic Monitor (ADDM)


By default, Oracle Database automatically generates snapshots of the performance data once every hour and retains the statistics in the workload repository for 8 days. 

Creating Snapshots:

To  manually create a snapshots in order to capture statistics at times different than those of the automatically generated snapshots. Use the below script

BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT ();
END;

query the below SQL and go at the end to view the newly created snapshot

SQL> select * from DBA_HIST_SNAPSHOT

 Dropping Snapshots:

To view a list of the snapshot Ids along with database Ids, check the DBA_HIST_SNAPSHOT view

BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE (low_snap_id => 11024,
                           high_snap_id => 11036, dbid => 1544388080);
END;

Baselines (Fixed):

A baseline contains performance data from a specific time period that is preserved for comparison with other similar workload periods when performance problems occur. The snapshots contained in a baseline are excluded from the automatic AWR purging process and are retained indefinitely.

Creating a Baseline:
Based on the snapshots we created above, we'll create the baseline.

1. get the snap id to create the baseline on using DBA_HIST_SNAPSHOT
2. Create the base line as below 

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE (start_snap_id => 11022, 
                   end_snap_id => 11023, baseline_name => 'peak baseline', 
                   dbid => 1544388080);
END;

view the baseline create using the script below

SQL> select dbid,baseline_id,baseline_name from DBA_HIST_BASELINE;

      DBID BASELINE_ID BASELINE_NAME
---------- ----------- ----------------------------------------------------------------
1544388080           1 peak baseline
1544388080           0 SYSTEM_MOVING_WINDOW


Renaming a Baseline:

To rename a baseline:

1. Review the existing baselines in the DBA_HIST_BASELINE view as mentioned above to determine the baseline that you want to rename.
2. Use the scripte below the rename the baseline

BEGIN 
 DBMS_WORKLOAD_REPOSITORY.RENAME_BASELINE ( old_baseline_name => 'peak baseline', new_baseline_name => 'off peak baseline', dbid => 1544388080 );
 END;

Dropping the Baseline:

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

Moving Window Baseline:

Oracle 11g introduces the concept of a moving window baseline, which is used to calculate metrics for the adaptive thresholds. The window is a view of the AWR data within the retention period.

Viewing AWR Retention Period:

SQL> SELECT retention FROM dba_hist_wr_control;

RETENTION
---------------------------------------------------------------------------
+00008 00:00:00.0

 The default size of the window matches the default AWR retention period of 8 days, but it can be set as a subset of this value. Before you can increase the size of the window you must first increase the size of the AWR retention period.
Modifying the AWR retention period:
BEGIN
    DBMS_WORKLOAD_REPOSITORY.MODIFY_BASELINE_WINDOW_SIZE (
                   window_size => 30,
                   dbid => 1544388080);
END;
Baseline Templates:

Baseline Templates are used for creating the baselines any time in the future. Baseline Templates can be single baseline and repeating baseline.

Single baseline:

Single baseline is created when the AWR data is to be collected at any time in the future e.g. you are testing a system over a week-end and want to capture the statistic data during the testing.

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
                   start_time => TO_DATE('12-MAY-12 00:00', 'DD-MON-YY HH24:MI'),
                   end_time => TO_DATE('12-MAY-12 03:00', 'DD-MON-YY HH24:MI'),
                   baseline_name => 'baseline_120518',
                   template_name => 'template_120818', expiration => 30,
                   dbid => 1544388080);
END;

Repeating baseline:

A repeating baseline template can be used to create and drop baselines based on a repeating time schedule. 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.

BEGIN
    DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE (
    day_of_week => 'monday', hour_in_day => 17,
     duration => 3, expiration => 30,
     start_time => TO_DATE('13-MAY-12 00:00', 'DD-MON-YY HH24:MI'),
     end_time => TO_DATE('13-MAY-12 03:00', 'DD-MON-YY HH24:MI'),
      baseline_name_prefix => 'baseline_2012_mondays_',
      template_name => 'template_2012_mondays',
      dbid => 1544388080);
END;



The START_TIME and END_TIME parameters define when the template is activated and deactivated. The DAY_OF_WEEK, HOUR_IN_DAY and DURATION parameters define the day (MONDAY - SUNDAY or ALL) the baselines are generated on and the start and end point of the baseline.

Viewing Baseline templates:

SQL> select template_name, template_type,expiration  from DBA_HIST_BASELINE_TEMPLATE;

TEMPLATE_NAME                  TEMPLATE_ EXPIRATION
------------------------------ --------- ----------
template_120818                SINGLE            30
template_2012_mondays          REPEATING         30


Transporting Automatic Workload Repository Data

Oracle Database enables you to transport AWR data between systems. This is useful in cases where you want to use a separate system to perform analysis of the AWR data. To transport AWR data, you must first extract the AWR snapshot data from the database on the source system, then load the data into the database on the target system, as described in the following sections:
·         Extracting AWR Data
·         Loading AWR Data

 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 must be connected to the database as the SYSuser.
To extract AWR data:
1.      At the SQL prompt, enter:
2.      @$ORACLE_HOME/rdbms/admin/awrextr.sql
A list of the databases in the AWR schema is displayed.
3.      Specify the database from which the AWR data will be extracted:
4.      Enter value for db_id: 1377863381
In this example, the database with the database identifier of 1377863381 is selected.
5.      Specify the number of days for which you want to list snapshot Ids.
6.      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.
7.      Define the range of snapshots for which AWR data will be extracted by specifying a beginning and ending snapshot ID:
8.      Enter value for begin_snap: 30
9.      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.
10.  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.
11.  Specify the prefix for name of the export dump file (the .dmp suffix will be automatically appended):
12.  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.

 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 must be connected to the database as the SYS user.
To load AWR data:
1.      At the SQL prompt, enter:
2.      @$ORACLE_HOME/rdbms/admin/awrload.sql
A list of directory objects is displayed.
3.      Specify the directory object pointing to the directory where the export dump file is located:
4.      Enter value for directory_name: DATA_PUMP_DIR
In this example, the directory object DATA_PUMP_DIR is selected.
5.      Specify the prefix for name of the export dump file (the .dmp suffix will be automatically appended):
6.      Enter value for file_name: awrdata_30_40
In this example, the export dump file named awrdata_30_40 is selected.
7.      Specify the name of the staging schema where the AWR data will be loaded:
8.      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.
9.      Specify the default tablespace for the staging schema:
10.  Enter value for default_tablespace: SYSAUX
In this example, the SYSAUX tablespace is selected.
11.  Specify the temporary tablespace for the staging schema:
12.  Enter value for temporary_tablespace: TEMP
In this example, the TEMP tablespace is selected.
13.  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:
14.  Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
15.  Completed 113 CONSTRAINT objects in 11 seconds
16.  Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
17.  Completed 1 REF_CONSTRAINT objects in 1 seconds
18.  Job "SYS"."SYS_IMPORT_FULL_03" successfully completed at 09:29:30
19.  ... Dropping AWR_STAGE user
20.  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.

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:
2.      @$ORACLE_HOME/rdbms/admin/awrrpt.sql
3.      Specify whether you want an HTML or a text report:
4.      Enter value for report_type: text
In this example, a text report is chosen.
5.      Specify the number of days for which you want to list snapshot Ids.
6.      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.
7.      Specify a beginning and ending snapshot ID for the workload repository report:
8.      Enter value for begin_snap: 150
9.      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.
10.  Enter a report name, or accept the default report name:
11.  Enter value for report_name: 
12.  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.

 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:
2.      @$ORACLE_HOME/rdbms/admin/awrrpti.sql
3.      Specify whether you want an HTML or a text report:
4.      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
5.      Enter the values for the database identifier (dbid) and instance number (inst_num):
6.      Enter value for dbid: 3309173529
7.      Using 3309173529 for database Id
8.      Enter value for inst_num: 1
9.      Specify the number of days for which you want to list snapshot Ids.
10.  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.
11.  Specify a beginning and ending snapshot ID for the workload repository report:
12.  Enter value for begin_snap: 150
13.  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.
14.  Enter a report name, or accept the default report name:
15.  Enter value for report_name: 
16.  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.

 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:
2.      @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
3.      Specify whether you want an HTML or a text report:
4.      Enter value for report_type: html
In this example, an HTML report is chosen.
5.      Specify the number of days for which you want to list snapshot Ids.
6.      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.
7.      Specify a beginning and ending snapshot ID for the workload repository report:
8.      Enter value for begin_snap: 146
9.      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.
10.  Specify the SQL ID of a particular SQL statement to display statistics:
11.  Enter value for sql_id: 2b064ybzkwf1y
In this example, the SQL statement with a SQL ID of 2b064ybzkwf1y is selected.
12.  Enter a report name, or accept the default report name:
13.  Enter value for report_name: 
14.  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:
2.      @$ORACLE_HOME/rdbms/admin/awrsqrpi.sql
3.      Specify whether you want an HTML or a text report:
4.      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
5.      Enter the values for the database identifier (dbid) and instance number (inst_num):
6.      Enter value for dbid: 3309173529
7.      Using 3309173529 for database Id
8.      Enter value for inst_num: 1
9.      Using 1 for instance number
10.  Specify the number of days for which you want to list snapshot Ids.
11.  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.
12.  Specify a beginning and ending snapshot ID for the workload repository report:
13.  Enter value for begin_snap: 146
14.  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.
15.  Specify the SQL ID of a particular SQL statement to display statistics:
16.  Enter value for sql_id: 2b064ybzkwf1y
In this example, the SQL statement with a SQL ID of 2b064ybzkwf1y is selected.
17.  Enter a report name, or accept the default report name:
18.  Enter value for report_name: 
19.  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.

 

 

Generating Automatic Workload Repository Compare Periods Reports

While an AWR report shows AWR data between two snapshots (or two points in time), the AWR Compare Periods report shows the difference between two periods (or two AWR reports, which equates to four snapshots). Using the AWR Compare Periods report helps you to identify detailed performance attributes and configuration settings that differ between two time periods. For example, if the application workload is known to be stable between 10:00 p.m. and midnight every night, but the performance on a particular Thursday was poor between 10:00 p.m. and 11:00 p.m., generating an AWR Compare Periods report for Thursday from 10:00 p.m. to 11:00 p.m. and Wednesday from 10:00 p.m. to 11:00 p.m. should identify configuration settings, workload profile, and statistics that were different in these two time periods. Based on the differences identified, the cause of the performance degradation can be more easily diagnosed. The two time periods selected for the AWR Compare Periods Report can be of different durations, because the report normalizes the statistics by the amount of time spent on the database for each time period, and presents statistical data ordered by the largest difference between the periods.
The AWR Compare Periods reports are divided into multiple sections. The HTML report includes links that can be used to navigate quickly between sections. The content of the report contains the workload profile of the system for the selected range of snapshots.
The primary interface for generating AWR Compare Periods reports is Oracle Enterprise Manager. Whenever possible, you should generate AWR Compare Periods reports using Oracle Enterprise Manager, as described in Oracle Database 2 Day + Performance Tuning Guide. If Oracle Enterprise Manager is unavailable, you can generate AWR Compare Periods reports by running SQL scripts, as described in the following sections:
To run these scripts, you must be granted the DBA role.

 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:
2.      @$ORACLE_HOME/rdbms/admin/awrddrpt.sql
3.      Specify whether you want an HTML or a text report:
4.      Enter value for report_type: html
In this example, an HTML report is chosen.
5.      Specify the number of days for which you want to list snapshot Ids in the first time period.
6.      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.
7.      Specify a beginning and ending snapshot ID for the first time period:
8.      Enter value for begin_snap: 102
9.      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.
10.  Specify the number of days for which you want to list snapshot Ids in the second time period.
11.  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.
12.  Specify a beginning and ending snapshot ID for the second time period:
13.  Enter value for begin_snap2: 126
14.  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.
15.  Enter a report name, or accept the default report name:
16.  Enter value for report_name: 
17.  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.

 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:
2.      @$ORACLE_HOME/rdbms/admin/awrddrpi.sql
3.      Specify whether you want an HTML or a text report:
4.      Enter value for report_type: text
In this example, a text report is chosen.
5.      A list of available database identifiers and instance numbers are displayed:
6.      Instances in this Workload Repository schema
7.      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
8.         DB Id    Inst Num DB Name      Instance     Host
9.      ----------- -------- ------------ ------------ ------------
10.   3309173529        1 MAIN         main         examp1690
11.   3309173529        1 TINT251      tint251      samp251
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
12.  Specify the number of days for which you want to list snapshot Ids in the first time period.
13.  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.
14.  Specify a beginning and ending snapshot ID for the first time period:
15.  Enter value for begin_snap: 102
16.  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.
17.  Enter the values for the database identifier (dbid) and instance number (inst_num) for the second time period:
18.  Enter value for dbid2: 3309173529
19.  Using 3309173529 for Database Id for the second pair of snapshots
20.  Enter value for inst_num2: 1
21.  Using 1 for Instance Number for the second pair of snapshots
22.  Specify the number of days for which you want to list snapshot Ids in the second time period.
23.  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.
24.  Specify a beginning and ending snapshot ID for the second time period:
25.  Enter value for begin_snap2: 126
26.  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.
27.  Enter a report name, or accept the default report name:
28.  Enter value for report_name: 
29.  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.

 Generating Active Session History Reports

Use Active Session History (ASH) reports to perform analysis of:
·         Transient performance problems that typically last for a few minutes
·         Scoped or targeted performance analysis by various dimensions or their combinations, such as time, session, module, action, or SQL_ID
Transient performance problems are short-lived and do not appear in the Automatic Database Diagnostics Monitor (ADDM) analysis. ADDM tries to report the most significant performance problems during an analysis period in terms of their impact on DB time. If a particular problem lasts for a very short duration, its severity might be averaged out or minimized by other performance problems in the entire analysis period; therefore, the problem may not appear in the ADDM findings. Whether or not a performance problem is captured by ADDM depends on its duration compared to the interval between the Automatic Workload Repository (AWR) snapshots.
If a performance problem lasts for a significant portion of the time between snapshots, it will be captured by ADDM. For example, if the snapshot interval is set to one hour, a performance problem that lasts for 30 minutes should not be considered as a transient performance problem because its duration represents a significant portion of the snapshot interval and will likely be captured by ADDM.
On the other hand, a performance problem that lasts for only 2 minutes could be a transient performance problem because its duration represents a small portion of the snapshot interval and will likely not show up in the ADDM findings. For example, if the user notifies you that the system was slow between 10:00 p.m. and 10:10 p.m., but the ADDM analysis for the time period between 10:00 p.m. and 11:00 p.m. does not show a performance problem, it is likely that a transient performance problem occurred that lasted for only a few minutes of the 10-minute interval reported by the user.
The ASH reports are divided into multiple sections. The HTML report includes links that can be used to navigate quickly between sections. The content of the report contains ASH information used to identify blocker and waiter identities and their associated transaction identifiers and SQL for a specified duration. For more information on ASH, see "Active Session History".
The primary interface for generating ASH reports is Oracle Enterprise Manager. Whenever possible, you should generate ASH reports using Oracle Enterprise Manager, as described in Oracle Database 2 Day + Performance Tuning Guide. If Oracle Enterprise Manager is unavailable, you can generate ASH reports by running SQL scripts, as described in the following sections:

 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:
2.      @$ORACLE_HOME/rdbms/admin/ashrpt.sql
3.      Specify whether you want an HTML or a text report:
4.      Enter value for report_type: text
In this example, a text report is chosen.
5.      Specify the begin time in minutes before the system date:
6.      Enter value for begin_time: -10
In this example, 10 minutes before the current time is selected.
7.      Enter the duration in minutes that the report for which you want to capture ASH information from the begin time.
8.      Enter value for duration:
In this example, the default duration of system date minus begin time is accepted.
9.      Enter a report name, or accept the default report name:
10.  Enter value for report_name: 
11.  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.

 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:
2.      @$ORACLE_HOME/rdbms/admin/ashrpti.sql
3.      Specify whether you want an HTML or a text report:
4.      Enter value for report_type: html
In this example, an HTML report is chosen.
5.      A list of available database Ids and instance numbers are displayed:
6.      Instances in this Workload Repository schema
7.      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
8.         DB Id    Inst Num DB Name      Instance     Host
9.      ----------- -------- ------------ ------------ ------------
10.   3309173529        1 MAIN         main         examp1690
11.   3309173529        1 TINT251      tint251      samp251
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
12.  Specify the begin time in minutes before the system date:
13.  Enter value for begin_time: -10
In this example, 10 minutes before the current time is selected.
14.  Enter the duration in minutes that the report for which you want to capture ASH information from the begin time.
15.  Enter value for duration:
In this example, the default duration of system date minus begin time is accepted.
16.  Enter a report name, or accept the default report name:
17.  Enter value for report_name: 
18.  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.


Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)