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
