Sunday, April 24, 2011

Active Session History (ASH) in oracle

Befor 10g database, we would STATPAK report but after 10g it is better to use ACTIVE SESSION HISTORY report.

What is Active session :-
Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session. This includes any session that was on the CPU at the time of sampling.

Active sessions are sampled every second and are stored in a circular buffer in SGA.
As part of the Automatic Workload Repository (AWR) snapshots, the content of V$ACTIVE_SESSION_HISTORY is also flushed to disk. Because the content of this V$ view can get quite large during heavy system activity, only a portion of the session samples is written to disk.

Using the Active Session History enables you to examine and perform detailed analysis on both current data in the V$ACTIVE_SESSION_HISTORY view and historical data in the DBA_HIST_ACTIVE_SESS_HISTORY view .

the following are stayed in this ASH report

  • 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

How to Run ASH report from SQLPLUS
connect with sys user then run

SQL> @oracle_home/RDBMS/admin/ashrpt.sql (for single node database)

SQL> @oracle_home/RDBMS/admin/ashrpti.sql (for RAC database)

after execution... pass the parameter as a requirement basis .

No comments: