ASH simulation Scripts (SASH and SASHMON)

(see http://sites.google.com/site/embtdbo/wait-event-documentation/sash-1)

I've gotten a few questions on SASH and SASHMON recently, so I decided to repost the following info:

Active Sessoin history, aka ASH, is only available in Oracle 10g and higher and also requires the diagnostic package license. If you are are an older version of Oracle or don't have the diagnostic package license then you have the option of simulating ASH (S-ASH) yourself. Your milage may vary on the scripts below. No guarentees on them working correctly on your systems. Make sure you test and understand them.

For the scripts below it is highly recommended to put the repository on a different database than the database being monitored. I've only tested resource consumption for gathering data (less than 1% of 1 CPU) and not the resource usage of actually storing the data.

--------------------------------------------

(v1 is available at http://ashmasters.com/ash-simulation/)

V2 Installs on Windows or Unix on Standard Edition

The scripts below are newer so there will probably be some hickups in them (some modules such as SQL and Session statistics collection aren't implemented). Please comment on the blog on any issues and/or solutions you find. I wrote these scripts as both an option for installing on Windows (since the first version was a shell script) and for allowing Standard Edition as a repository while still supporting data purging using "poor man's partitioning", ie having separate tables for each day of the week, and using view with union all of the 7 tables in the data mining scripts. I find the following scripts easier to follow, read, understand and change than the single script above.

SASH creates a view called v$active_session_history and scripts that run on the "real" v$active_session_history should also work on SASH.v$active_session_history.

Repository Creation -

repo_0_user.sql - run as SYS or SYSTEM. Creates SASH user
repo_1_tables.sql - run as SASH only !! ( WARNING - Installs the SASH schema on repository database including simulated DBA_ and V$ views. Will cause problems if run as SYS or SYSTEM)

repo_2_pkg.sql - run as SASH, optional, create an automatic purge procedure on repository machine
repo_3_jobs.sql - run as SASH, optional, start a job on repository machine to purge oldest day of data

Monitored Database Setup (do this on each database to be monitored)

targ_1_pkg.sql - install collection package on each database to be monitored
targ_2_jobs.sql - start up collection in a job on each database to be monitored

Data Mining S-ASH

When running scripts written explicity for ASH on SASH data there are a couple of issues. (or running ASHMON)

1) WAIT GROUPS : SASH doesn't collect wait groups because its set up mainly for version 8 and 9 (since ASH is already on 10g+). The wait groups are required to run ASHMON or scripts that use "wait_class" in v$active_session_history.

repo_4_waitgroups.sql - run as SASH, sets up wait groups

2) CURRENT DATABASE : SASH collects data for multiple databases into the same schema, thus scripts and ASHMON have to filter by the correct DBID. I do this by having a table SASH_TARGET that contains the DBID that I'm interested in. Then views like v$active_session_history include a filter on DBID from SASH_TARGET. Thus to change databases, I just change the DBID in SASH_TARGET.

repo_5_curdb.sql - run as SASH, changes the DBID in SASH_TARGET (shows a list of monitored databases and prompts for the one to filter for)

ASHMON - graphical monitor

When running ASHMON, the user to connect to is SASH, ie the owner of the repository data