How to Generate an ASH Report

How to Generate an ASH Report

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, then its severity might be averaged out or minimized by other performance problems in the analysis period. Therefore, the problem may not appear in the ADDM findings. Whether a performance problem is captured by ADDM depends on its duration compared to the interval between the 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.

However, 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, a transient performance problem probably occurred that lasted for only a few minutes of the 10-minute interval reported by the user.

How to generate an ASH Report for a single instance/RAC database.

SOLUTION :

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

Generating an Oracle RAC ASH Report

The ashrpti.sql SQL script generates an HTML or text report that displays ASH information for a specified duration for specified databases and instances in an Oracle RAC environment. 

To generate an ASH report in an Oracle RAC environment:

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 examp1690
3309173529 1 TINT251 tint251 samp251
3309173529 2 TINT251 tint252 samp252
3309173529 3 TINT251 tint253 samp253
3309173529 4 TINT251 tint254 samp254

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

Enter value for dbid: 3309173529
Using database id: 3309173529
Enter instance numbers. Enter 'ALL' for all instances in an Oracle
RAC cluster or explicitly specify list of instances (e.g., 1,2,3).
Defaults to current instance.
Enter value for inst_num: ALL
Using instance number(s): ALL

4. Specify the begin time in minutes before the system date:

Enter value for begin_time: -1:10

In this example, 1 hour and 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: 10

In this example, the duration is set to 10 minutes.

6. Specify the slot width in seconds that will be used in the Activity Over Time section of the report:

Enter value for slot_width:

In this example, the default value is accepted.

7. Follow the instructions as explained in the subsequent prompts and enter values for the following report targets:
* target_session_id
* target_sql_id
* target_wait_class
* target_service_hash
* target_module_name
* target_action_name
* target_client_id
* target_plsql_entry

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

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

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

Thanks 

comments plz.