Exploiting ASH data can be difficult because of the many possible dimensions one can filter and aggregate by not to mention the basic need of varying the begin and end time for the analysis period. The solution is to use graphics. Here is a graphic monitor that one can use on a Oracle 10g or 11 database to browse the data. Of course of you have Enterprise Monitor you already have this functionality. The advantages of ASHMON is that you can point it to a database running S-ASH (simulated ASH). The other advantage is that ASHMON is a “fat client” not a web base client and thus is more responsive. (also if your EM ever breaks, it’s nice to have an alternative way of getting the data graphically)
Install
Download:
ashmon.zip
Windows Only (portable to UNIX with TCL/TK executable for UNIX)
Unzip ashmon.zip
Run ashmon.bat
with ashmon\bin\ as the starting directory
Login as “system”
Supports 10g or 11
can run on earlier versions of Oracle with S-ASH repository S-ASH
need to create some veiws on SASH as well as wait groups
Features
- Based on ASH
- System Load
- measured in AAS - Average Active Sessions
- broken down into CPU and wait group states
- window slider (mouse click drag, release)
- Top SQL in window
- ( Top Session in window - not implemented yet)
- Zoom in on Load (right click start, right click finish, clear with double click)
- SQL Text for selected SQL
- SQL execution time line (click twice on SQL id)
Notices
- 10.1 ASHMON gets errors because v$active_session_history is missing wait_class. workaround
- if copying ashmon.bat to your desktop or elsewhere, use a “shortcut” instead of moving the file. The file needs to be located in ashmon/bin
- only connect to Oracle 10g or 11 unless you have set up the S-ASH repository
- only connect as system or someone who has select on v$ tables unless you have set up the S-ASH repository. script to grant select on v$ views or grant “SELECT_CATALOG_ROLE” to the user.
- All connect errors return “invalid username/password” in Oratcl, thus to debug connection problems you need to test with sqlplus. If sqlplus connects, then the monitor will connect with the same connection string ie username, password and tnsnames.ora entry
- ashmon will use the tnsnames.ora located in ORACLE_HOME found in the PATH variable on windows. IE if sqlplus executes C:\oracle\bin\sqlplus then ashmon will use C:\oracle\network\admin\tnsnames.ora
- ashmon comes with a default oracle client libraries and will use these if it does not find Oracle already installed on the PC, and will use ashmon/oracle/network/admin/tnsnames.ora for tns alias lookups
Question :
hello — I’m hacking around w/ ashmon on linux and wondering
if you have any docs regarding installation/configuration on
this platform. Any pointers much appreciated.
Response:
TCL/TK is portable across windows, mac, unix, linux etc
but you have to either get a binary or compile a binary.
For windows, I got all the binarys off the web.
I’ve did a compile for solaris for ASHMON (but not Linux).
The versions of TCL/TK and packages that ASHMON depends on
are:
tcl 8.4
tk 8.4
blt 2.4
oratcl 4.4
thread 2.4
I can put the TCL source on FTP if you’d like to try and compile
it. TCL/TK needs to be compiled thread safe. I have the source for
tcl, tk, blt and thread. I don’t have oratcl but I’m sure it’s
easy to find on the web.
http://www.perfvision.com/ftp/tcl/tcl_tk_blt.tar.gz - should be tcl, tk and blt
http://www.perfvision.com/ftp/tcl/thread24.zip
oratcl
http://downloads.sourceforge.net/oratcl/oratcl44.tar.gz?modtime=1147947755&big_mirror=0
http://downloads.sourceforge.net/oratcl/oratcl-4.4-1.i386.rpm?modtime=1154519037&big_mirror=0
Kyle,
Hi, I much admired your session at Hotsos in March. You mentioned that in 9i it’s easy to get similar reports by joining v$session and v$session_wait….. I’ve scoured your site but I can’t find any example code. Is there any? I’d really love to see it!
Keep up the good work!
CH
Hi Charles,
The 9i collection to simulate ASH is outlined in detail on the “Simulation” tab on this site. Specifically, you’ll find the code to collect ASH data in this script: http://www.perfvision.com/ash/targ_1_pkg.sql
Here is a link to standalone query see what ash data looks like right now (ie no history)
http://www.perfvision.com/ash/ash9i.sql
Hi Kyle
This seems to be a great tool indead! Unfortunately I am not getting it to work against a 10.1.0.4 database.
I have applied the v$ash patch and modified dba_hist_active_sess_history to include the missing columns event, wait_class
and session_state, and modified the contents of session_type.
But most things still don’t work… Is 10.1.0.4 not supported?
Cheers,
Chris
Hi Kyle,
Congratulations for this very nice tool !
I always compare it with Indepth and in my opinion Ahsmon has better granularity than
Indepth (Indepth only shows slices of 15mn where we can’t see CPU peaks for instance).
It is still different in the way that Indepth is more complete regarding history of data
especially if you don’t use a repository. For testing environments that’s more than enough !
I’ve installed it under citrix but one problem that prevents us from using it more officially
is that it doesn’t encrypt passwords in config file and therefore anyone can see them.
Would it be possible to add such encryption ? I know it’s not an official commercial tool yet
but that would really help me convince other people to use it and participate (as end user) to its
enhancement.
I will as soon as possible try to provide you some feedback/suggestions about what could be done
in my opinion.
Was great to see you again in Dallas !
Jean-Marc
Hi Jean-Marc,
No plans to make ASHMON an official tool. I would love to do more work on it but lacking any other contributers prospects are unlikely.
For security, the easy solution is comment out the code that collects the passwords so that they are not stored at all. That way you have to type in the password everytime - incovenient but safe.
Adding encryption would be great and I encourage anyone interested to chip in on the code for things like this. My current code priorities would be to add SQLite as a data store for session sample data enabling ASHMON to point at any Oracle database and by pass v$active_session_history. My other priority would be to add wait event drilldowns that display and aggregate the ASH info in ways that address specific wait events.
Best
Kyle