Web ASH : W-ASH

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)

./cash.sh
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 172.16.100.250 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 172.16.100.250
The database I am monitoring is on host 172.16.100.250 with Oracle SID “orcl”

http://172.16.100.250/ash.html?q=172.16.100.250:orcl

 

 

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


Uncategorized

  1. Trackbacks

  2. No trackbacks yet.
  1. Comments

  2. Johnny
    February 27th, 2014 at 03:03 | #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?

    Thanks

  3. khailey
    February 27th, 2014 at 18:40 | #2

    @Johnny
    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
    March 19th, 2014 at 12:28 | #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
    March 19th, 2014 at 15:28 | #4

    @Kevin
    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
    June 4th, 2014 at 00:13 | #5

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

  7. khailey
    June 4th, 2014 at 04:27 | #6

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

    select
    (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’ )
    from
    v\$session s,
    all_users u
    where
    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
    June 5th, 2014 at 04:04 | #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
    June 5th, 2014 at 17:05 | #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


five − = 2