Active Session History (ASH) tool

Active Session History (ASH) tool: "

Introduction:

From Oracle documentation, 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. The data present in ASH can be rolled up on various dimensions that it captures, including the following:

* 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

Active Session History (ASH) tool:

From the OTN forum we learn a lot of things. In answer of one question about free ASH tool. The OP asked the following question:

“

Hi

We are having many Oracle 10.2.0.3 instances on Unix and Windows .

We do not have Enterprise Manager configured and we do not have licence for the diagnostic packs.

Requirement : My manager wants a Graphical Report for each database instance showing its performance details . He needs the reports monthly .

I tried to convince him by generating AWR reports , however he said he is not satisfied with it because its too technical . He needs something in a graphical format or a presentation format where a non technical person can easily understand some things like :

1) When was peak load on the database

2) Which Application was using most resources

3) Which Table/Object was most queried

4) Statistics in regards to Disk/Network/CPU

Actually the report should be similar or a kind of Oracle EM dbcontrol’s Performance Tab where we can see all the graphs at any point of time.

I have to create a dynamic report every month with the details mentioned above for any point of Time with in the last one month.

The resulting report can be in Excel or PPT

Any pointers or Templates to get the result is highly appreciated .

Thanks

”

An OTN user provided him a link from Jonathan Lewis ‘s blog (see the link below in the references).

In his blog, Jonathan advised to use the free ASH tool in the case of you are running an older version of Oracle than 10g, or you don’t just want to pay the Diagnostic Pack licence.

The tool cam be downloaded from http://sourceforge.net/projects/ashv/ page.

In my Fedora Linux, I had to change a little bit the configuration files so I can then run the tool. In this post, I am going to show you how to successfully run the tool in Linux. So these are the steps to follow, the read me file of the tool is helpful but, I think it’s not really complete.

1- You have to download the tool from http://sourceforge.net/projects/ashv/

2- Make sure your database is running and your listener is up. My Oracle version is 11.2.0.1.0.

3- Copy the download tool under your Oracle system user home. (/home/oracle)

4- Unpack the archive .zip using the following command:

unzip ashv-3.4-bin.zip

5- Make sure you give the Oracle system user the privileges in this folder:

chown -R oracle:oinstall /home/oracle/ashv-3.4-bin/

6- Download the Oracle JDBC driver ojdbc14.jar from http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-10201-088211.html

Put the ojdbc14.jar under the lib folder /home/oracle/ashv-3.4-bin/lib

7- Create a new folder called “profile” /home/oracle/ashv-3.4-bin/profile

8- Make the run.sh file executable.

9- Convert the run.sh file in unix readable folder using the command:

dos2unix run.sh

10- Edit the run.sh file and make the following changes:

#!/bin/ksh
# ----------------------------------------------------------------------------
# Licensed to the GNU GENERAL PUBLIC LICENSE Version 3
# ----------------------------------------------------------------------------

# ----------------------------------------------------------------------------
# ASH Viewer start up batch script
#
# Required ENV vars:
# JAVA_HOME - location of a JDK home dir
#

export JAVA_HOME=/usr/lib/jvm/java-1.6.0-openjdk-1.6.0.0.x86_64
export CLASSPATH=/home/oracle/ashv-3.4-bin/lib
export JAVA_EXE=$JAVA_HOME/bin/java

$JAVA_EXE -cp /home/oracle/ashv-3.4-bin/lib  -Xmx128m  -jar ASHV.jar

I have changed the JAVA_HOME variable to reflect my environment and also I have added the Classpath.

If you don’t do that you will receive an error like this:

SQL Exception occured while connection pool initialize: No suitable driver found for jdbc:oracle:thin:@//192.168.2.104:1522/ORAWISS

11- Now, you have just to run the file ./run.sh and you will get the following screens.

References:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/autostat.htm#i35568

http://forums.oracle.com/forums/thread.jspa?threadID=2205096&tstart=0

http://jonathanlewis.wordpress.com/2011/03/06/free-ash/

Scridb filter"