ASH Report in Oracle

 ASH Report in Oracle

Introduction

The ASH Report in Oracle contains recent information on active sessions sampled every second,The AWR and ADDM report will taken every one hour genetates and one hour old report will not generate and will not help in diagnosis issues that are in the current database

The ASH Report in Oracle report is an integral part of the oracle database self-management framework and is extremely useful for diagnosing performance problems

The ASH Report in Oracle gathers sampled data at the session level rather than at the instance level by capturing statastics for only active sessions ,ASH report collects a manageable set of data

ASH Report in Oracle these are the parameters below are the following

• SQL identifier of a SQL statement
• 
  
• 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

Let us start the process

In this blog i am going to explain ASH Report in Oracle  in single instance database and Rac database and also i am going to tell detailed in ASH report manually 

The AWR and ADDM report will taken every one hour genetates Report

Here i am using  ORCL database and ORCL instance in this example

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

To generate an ASH report:

Login to sqlplus / as sysdba

Step 1: At the SQL prompt, enter:

SQL>@$ORACLE_HOME/rdbms/admin/ashrpt.sql

or 

SQL@?/rdbms/admin/ashrpt.sql

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

Enter value for report_type: html

In this example, a html report is chosen.

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

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

Step 5: Specify the Report  name, or accept the default report name:

Enter value for report_name:

Using the report name : / u01/ash.html

In this example, ASH report ash.html is generated. The report will gather ASH information beginning from 10 minutes before the current time and ending at the current time.

To generate an ASH report in an Oracle RAC environment:

Step1: At the SQL prompt, enter:

SQL@$ORACLE_HOME/rdbms/admin/ashrpti.sql

Step2: Specify whether you want an HTML or a text report:

Enter value for report_type: html

In this example, an HTML report is chosen.

Step 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

----------- -------- ------------ ------------ ------------

2206262418 2    ORCLorcl localhost1660

2206262418 1    OMALL341 omallt341 cha341

2206262418 2    OMALL341 omallt342 cha342

2206262418 3   OMALL341 omallt343 cha343

2206262418 4    OMALL341 omallt344 cha344

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

Enter value for dbid: 2206262418

Using database id: 2206262418

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

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

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

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

Step 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

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

Enter value for report_name:

Using the report name ashrpt_rac_0210_0232.txt

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

Note: Info on ASH Report in Oracle itmay be differ in your environment like production,testing,development etc

THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME