| ----- 5.1.1.3 Active Session History (ASH) The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance. Active sessions are sampled every second and are stored in a circular buffer in SGA. 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. 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. 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. By capturing only active sessions, a manageable set of data is represented with the size being directly related to the work being performed rather than the number of sessions allowed on the system. 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, often avoiding the need to replay the workload to gather additional performance tracing information. 10g and above Active Session History (Ash) And Analysis Of Ash Online And Offline [ID 243132.1] ----- The flushing and purging policies of ASH, including the way ASH respects SWRF baselines, are completely tied with SWRF policies. Still, flushing the entire content of ASH on to disk could be too populous to be feasible, and therefore, only one out of every ten active session samples will be flushed to disk. The ASH infrastructure will provide a single V$ view (V$ACTIVE_SESSION_HISTORY), to view the in-memory contents of ASH. The ASH infrastructure will also provide a way to dump the in-memory contents of its circular buffer onto an external file in a human readable format. The ASH dump file can be transported to another system, imported onto a user table, and analyzed using the ADDM in that system. ASH Memory Size ============== Size of ASH Circular Buffer = Max [Min [ #CPUs * 2 MB, 5% of Shared Pool Size, 30MB ], 1MB ] Let start with ONLINE analysis ======================== Lets check the info in V$ACTIVE_SESSION_HISTORY. Lets do the OFFLINE analysis of ASH ============================== Command would be like below: where level means minute. lets dump for 10 minutes history 1. SQL> alter session set events 'immediate trace name ashdump level 10'; or 2. SQL> alter system set events 'immediate trace name ashdump level 10'; or 3. SQL> oradebug setmypid SQL> oradebug dump ashdump 10; So you will get the trace file in udump. Oracle has provide an utility under $ORACLE_HOME/rdbms/demo (Location may change), by which you can upload the ASH trace dump to a database table and do the analysis. Please see the following Note Note 555303.1 ashdump* scripts and post-load processing of MMNL traces for the scripts. Script Details ============ 1. "ashdump_loader" -> Main script to run with one argument. Argument is the the name of tracefile. (available in 10g only) 2. "ashdump_table.sql" -> It would be called by the main script to create the table called SYSTEM.ACTIVE_SESSION_HISTORY_DUMP. 3. "ashdump_sqlldr.ctl" -> It is the third script called by main script to load the trace into table called SYSTEM.ACTIVE_SESSION_HISTORY_DUMP. You must use the same oracle version to compare the OFFLINE trace with the other view. |