Active Session History (ASH)

Oracle 10gr2 Introduce new option for capture performance problem.ASH Active session History What is ASH ?Whenever you want to know information about blocker and waiter identifiers and their associated transaction IDs and SQL.About V$ACTIVE_SESSION_HISTORY1. The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance. 2. Active sessions are sampled every second and are stored in a circular buffer in SGA. 3. 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. 4. This includes any session that was on the CPU at the time of sampling.5. Each session sample is a set of rows and the V$ACTIVE_SESSION_HISTORY view returns one row for each active session per sample, returning the latest session sample rows first. Because the active session samples are stored in a circular buffer in SGA, the greater the system activity, the smaller the number of seconds of session activity that can be stored in the circular buffer. This means that the duration for which a session sample appears in the V$ view, or the number of seconds of session activity that is displayed in the V$ view, is completely dependent on the database activity.6. Using the Active Session History enables you to examine and perform detailed analysis on both current data in the V$ACTIVE_SESSION_HISTORY view 7. Historical data in the DBA_HIST_ACTIVE_SESS_HISTORY view, SQL identifier of SQL statementWhat information provide ASH view ?1. Object number, file number, and block number2. Wait event identifier and parameters3. Session identifier and session serial number4. Module and action name5. Client identifier of the session6. Service hash identifierHow to generate ASH report ?It is New feature of 10GR2(10.2.0.1.0)For report creation we have to use ASHRPT.SQL script.Located: In $ORACLE_HOME/rdbms/admin folderHow to run ASHRPT.SQL scriptTo generate a text report of ASH information, run the ashrpt.sql script at the SQL prompt:@$ORACLE_HOME/rdbms/admin/ashrpt.sqlFirst, you need to specify whether you want an HTML or a text report.Enter value for report_type: textSpecify the time frame to collect ASH information by first specifying the begin time in minutes prior to the system date.Enter value for begin_time: -10Note: here you have to just put number in minutes eg: 10 for 10 minutesNext, enter the duration in minutes that the report for which you want to capture ASH information from the begin time. The default duration of system date minus begin time is accepted in the following example:Enter value for duration:Note: left blank for default value. Default value is SYSDATEThe report in this example will gather ASH information beginning from 10 minutes before the current time and ending at the current time. Next, accept the default report name or enter a report name. The default name is accepted in the following example:Enter value for report_name: Using the report name ashrpt_1_0310_0131.txtNote: Left it blank for default value.The session history report is generated.For Futhere Detail please check metalink note: 243132.1