Take / Generate Oracle 10g 11g Active Session History Report (ASH) Manually

Oracle ASH report introduced on Oracle 10GR2. It is kind of statistical data collected by Oracle Sampled data. The sampling is done on the current active sessions. Sampled data collected to circular buffer in SGA and the same can be accessed through V$ views. V$ACTIVE_SESSION_HISTORY view provides the sampled session activity for the instance.
Using the Active Session History you can examine and perform the detailed analysis on the current data in the V$ACTIVE_SESSION_HISTORY and the past data in the DBA_HIST_ACTIVE_SESS_HISTORY view.

ASH report is a small report compared to the AWR report which will provide the db/instance details for a short period of time. Using this report you can analyze and diagnose the problems of the database.  ASH report captures the following things.

  • SQL identifier of 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
  • Service hash identifier

Major ASH report sections are:
Top User Events
Top Background Events
Top Cluster Events
Top Service/Module
Top SQL Command Types
Top Phases of Execution
Top Remote Instances
Top SQL with Top Events
Etc.
How to Generate ASH report Manually?
Connect to sqlplus and run the ashrpt.sql.
@$ORACLE_HOME/rdbms/admin/ashrpt.sql

You have to give the following inputs.
Specify the Report Type
Enter 'html' for an HTML report, or 'text' for plain text
Defaults to 'html'
Enter value for report_type: text

Type Specified:  text

Enter begin time for report:
Defaults to -15 mins
Enter value for begin_time: -30
Report begin time specified: -30

Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Press Enter to analyze till current time
Enter value for duration: 30
Report duration specified:   30

Specify the Report Name
The default report file name is ashrpt_1_0329_0314.txt.  To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name:
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google