Sunday, January 24, 2010

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


8 comments:

  1. Kyle,

    Thanks a lot for your reply! you even made a blog post for it..

    Though I have a follow up question, What does this variables mean? and When do I need to tweak or change these values?

    public(sleep,fast) - 15
    public(sleep,slow) - 60
    public(debug_level) - 0
    public(debug_to_file) - 0
    public(debug_thread_to_file) - 0
    public(ash,bucket_secs) - 15
    public(ash,keep_secs) - 7200
    public(collect,sevt) - 1
    public(collect,stat) - 1
    public(ash,loadhours) - 2
    public(sevt,keep) - 1000
    public(sevt,ticks) - 100

    ReplyDelete
  2. These variables are for the graphical monitor ASHMON which reads SASH data. Since SASH looks just like the real tables, ie v$active_session_history, this means ASHMON works on the the real table as well.
    the most important variables are:
    UI:
    bucket_secs - width of a bar on the graph
    keep_secs - width of the graph
    loadhours - how much historic data to load into the graph when starting.
    Debugging:
    The debug_level I believe goes to 10, so 0 turns it off.
    The debug_to_file writes output to a file instead of the debug window. Good if program crashes and then there is a record.
    THe debug_thread_to_file is whether to debug the data collection thread. By default only the GUI is debugged.
    OTHER STUFF:
    ASHMON also collects system statistics and system wait events but it does this on the database you connect to, so if you connect to a repository machine, these won't be of much interest. If the sash collector was updated to collected this data then ASHMON could get it form the repo.
    sleep,fast - normal collection interval
    sleep,slow - background collection interval
    collect,sevt - collect system events 0=no, 1=yes
    collect,stat - collect system stats 0=n0, 1=yes
    sevt,keep - how many points to keep in the buffer
    sevt,ticks - how many points between graph tick marks
    -
    if any, or all of this sounds confusing, rememeber it's free. My current project DB Optimizer does all of this and much more with simplicity, but it's pay for.

    ReplyDelete
  3. PS - there is a bug where the saved connection strings can get extra double quotes and get message:
    connect_diag:ERROR:extra characters after close-quote
    The connections are saved in
    ashmon\funcs\logons.tcl
    which you can edit by hand and replace any "" with "

    ReplyDelete
  4. Kyle,

    Thanks for all your replies :) SASH and ASHMON will definitely be on my performance toolkits for 9i below..



    - Karl Arao

    ReplyDelete
  5. Hi Kyle,

    I'm testing SASH now and I have two questions:
    - what is a license model for SASH ?
    - can I add additional stuff to SASH to enhance functionality (ex. add session statistic gathering) which is missing ?

    regards,
    Marcin

    ReplyDelete
  6. SASH is free for use, but copyright for redistribution.
    If you want to improve the code, I'd be happy to post your new code improvements and welcome you posting the new code for free use if you include copy right information and a log of changes.
    I should officially GPL it to clarify this.
    At the time being I can't change the code myself.

    ReplyDelete
  7. Hi Kyle,

    I have implemented SASH in opposite way - there is only SASH_NOW view defined on target and all jobs are running on repository DB and are accessing that target via DB link. It was a firewall limitation and business requirement to limit changes on target. It's working on 9i with repository on 10g. I need to document all changes and I will be able to send it to you if you are interested in. I'm going to blog about this too.

    regards,
    Marcin

    ReplyDelete
  8. Hi Marcin,

    Sounds great - would love to see your new code and post it on ashmasters.com.

    Best
    Kyle

    ReplyDelete