UPDATE: see Pythian’s Marc Fielding detailed write-up on
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 you r 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.
Simulating ASH has been released in following versions:
Versions 1 and 2 have collection job running on database being monitored.
Version 2.1 has a collection job running on repository database.
Data Mining S-ASH
When running ASH scripts or ASHMON on SASH data there are a couple of issues.
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+), so the wait groups need to be created in order to run ASHMON. Ideally there would be a version check to get wait_groups from v$event_name in 10g or higher, but I haven’t done that yet.
repo_4_waitgroups.sql – set 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 – change the DBID in SASH_TARGET