February 23rd, 2014

I’m excited about the ease of creating rich user applications  that are web enabled  given the state of technology now. JavaScript and JQuery have gone from being disdained as “not a very serious” language to moving towards the limelight of front and center.

Here is a small example.

Download the following file:  W-ASH (web enabled ASH, file is wash.tar.gz )

Source is also on github at https://github.com/khailey/wash

Go to your apache web server root, in my case on redhat Linux is

# cd /usr/local/apache2
# gzip -d wash.tar.gz
# tar xvf wash.tar
-rwxr-xr-x  21956  14:08:21 cash.sh
-rw-r--r--  30881  11:52:10 htdocs/ash.html
drwxr-xr-x      0  15:40:52 htdocs/js/
-rwxr-xr-x  10958  14:04:42 cgi-bin/json_ash.sh

(the directory htdocs/js has a number of files put into it from Highcharts. I edited them out to make the output cleaner)

There are 3 basic files

  1. cash.sh  – collect ASH like data from Oracle into a flat file, it  runs in a continual loop
  2. ash.html  – basic web page using Highcharts
  3. json_ash.sh – cgi to read ASH like data and give it to the web page via JSON

Now you are almost ready to go. You just need to start the data collection with “cash.sh”  (collect ASH)

Usage: usage <username> <password> <host> [sid] [port]

The script “cash.sh” requires “sqlplus” be in the path and that is all. It’s probably easiest to

  • move/copy cash.sh to an ORACLE_HOME/bin
  • su oracle
  • kick it off as in:
nohup cash.sh system change_on_install orcl &

The script “cash.sh” will create a directory in /tmp/MONITOR/day_of_the_week for each day of the week, clearing out any old files, so there are only maximum 7 days of data. (to stop the  collection run “rm /tmp/MONITOR/clean/*end” )

To view the data go to your web server address and add “ash.html?q=machine:sid
For example my web server is on
The database I am monitoring is on host with Oracle SID “orcl”



See video at : http://screencast.com/t/sZrFxZkTrmn


  1. Trackbacks

  2. No trackbacks yet.

  2. Johnny
    | #1

    Very interesting

    Kyle, what do you think about to use the total CPU threads instead of CPU cores as a threshold to an Average Active Sessions graph? Some versions of OEM the user can to choose. Does this make sense?


  3. khailey
    | #2

    Good question
    i haven’t done hands on tests my self but as far as I can gather from the industry papers and friends is that CPU cores is a much better metric for horsepower than threads. Threads may or may not help throughput depending on they of workload and even when it helps it doesn’t help at a 2x factor but more of a percentage increase in head room.

  4. Kevin Zhang
    | #3

    Hello Kyle,
    Thanks for your great tool. I try to use it. It’s really good.
    Two questions:
    1. Is there any overhead on the db instance when running to cash.sh? If yes, how big is the effect?
    2. I see there’s another file vdb.html which can fetch info by sql_id. Do you plan to share it? Or it’s still working in progress.

    Thanks again for your tool and inspiration.

  5. khailey
    | #4

    Yes, there is a lot I want to do with this package. The SQL drill down is one, and Tyler Muth has written an awesome web enabled SQL drilldown that would be a perfect complement to this. I, or someone, just has to hook them together.
    I also want to have a landing page with available database instances, and whether they are up or down. Putting the DB info in the URL is just not pretty.
    As for load, the cash.sql script should be less than 1% of 1 CPU core on the database side. Very low.

  6. sam
    | #5

    You say it’s ‘ash-like’ but in cash.sh I see you’re selecting from v$active_session_history.

  7. khailey
    | #6

    @Sam: need to read a little closer. Here is the query that is used

    (cast(sysdate as date)-to_date(’01-JAN-1970′,’DD-MON-YYYY’))*(86400) ||’,’||
    1 ||’,’||
    concat(s.sid,concat(‘_’,s.serial#)) ||’,’||
    decode(type,’BACKGROUND’,substr(program,-5,4),u.username) ||’,’||
    s.sql_id ||’,’||
    — sql_plan_hash_value is not in v$session but in x$ksusea KSUSESPH
    s.SQL_CHILD_NUMBER ||’,’||
    s.type ||’,’||
    decode(s.WAIT_TIME,0,replace(s.event,’ ‘,’_’) , ‘ON CPU’) ||’,’||
    decode(s.WAIT_TIME,0,replace(s.wait_class,’ ‘,’_’) , ‘CPU’ )
    v\$session s,
    all_users u
    u.user_id=s.user# and
    s.sid != ( select distinct sid from v\$mystat where rownum < 2 ) and ( ( s.wait_time != 0 and /* on CPU */ s.status=’ACTIVE’ /* ACTIVE */) or s.wait_class != ‘Idle’ ) ; yes v$active_session_history is in the file cash.sh but it’s not used by default

  8. sam
    | #7

    Thanks, Kyle. Yep, I commented out the 2 blocks of code in cash.sh that reference v$active_session_history and it still works.

    Is there a way to go back historically beyond 5 minutes? I had a look through ash.html and json_ash.sh but didn’t see anything to allow for that.

  9. khailey
    | #8

    Sure, it can be modified in the code. I put the code together pretty quick so there is a lot more that could be done with it.
    Ideally the interface would allow one to zoom in and out or browse backwards and forwards.
    I had that kind of interface in Ashmon http://www.oraclerealworld.com/ash-masters/ashmon/ which I spent more time on

3 − = one