ASHMON
- (originally hosted here)
Exploiting ASH data can be difficult because of the many possible dimensions one can filter and aggregate by not to mention the basic need of varying the begin and end time for the analysis period. The solution is to use graphics. Here is a graphic monitor that one can use on a Oracle 10g or 11 database to browse the data. Of course of you have Enterprise Monitor you already have this functionality. The advantages of ASHMON is that you can point it to a database running S-ASH (simulated ASH). The other advantage is that ASHMON is a “fat client” not a web base client and thus is more responsive. (also if your EM ever breaks, it’s nice to have an alternative way of getting the data graphically)
Install
Download: ashmon.zip – Windows Only (portable to UNIX with TCL/TK executable for UNIX – see discussion thread below)
http://www.perfvision.com/ftp/ashmon_2010_10_22_64bit.rar – for 64bit laptops (with this 64bit version, you have to edit the tnsnames.ora file in the ./ashmon/network/admin to add your targets, see http://ashmasters.com/2010/10/ashmon-on-64bit-oracle-11gr2. This version also works on 32 bit laptops as well )
Unzip ashmon.zip
Run ashmon.bat with ashmon\bin\ as the starting directory
Login as “system” , or user with select any dictionary priviledge, your connect id has to be in tnsnames.ora
Supports 10g or 11can run on earlier versions of Oracle with S-ASH repository S-ASH but need to create some veiws on SASH as well as wait groups
Features
- See http://dboptimizer.com/?p=764 for more screen shots
- Based on ASH
- System Load
- measured in AAS – Average Active Sessions
- broken down into CPU and wait group states
- window slider (mouse click drag, release)
- Top SQL in window
- ( Top Session in window – not implemented yet)
- Zoom in on Load (right click start, right click finish, clear with double click)
- SQL Text for selected SQL
- SQL execution time line (click twice on SQL id)
Notices
- 10.1 ASHMON gets errors because v$active_session_history is missing wait_class. workaround
- if copying ashmon.bat to your desktop or elsewhere, use a “shortcut” instead of moving the file. The file needs to be located in ashmon/bin
- only connect to Oracle 10g or 11 unless you have set up the S-ASH repository
- only connect as system or someone who has select on v$ tables unless you have set up the S-ASH repository. script to grant select on v$ views or grant “SELECT_CATALOG_ROLE” to the user.
- All connect errors return “invalid username/password” in Oratcl, thus to debug connection problems you need to test with sqlplus. If sqlplus connects, then the monitor will connect with the same connection string ie username, password and tnsnames.ora entry
- ashmon will use the tnsnames.ora located in ORACLE_HOME found in the PATH variable on windows. IE if sqlplus executes C:\oracle\bin\sqlplus then ashmon will use C:\oracle\network\admin\tnsnames.ora
- ashmon comes with a default oracle client libraries and will use these if it does not find Oracle already installed on the PC, and will use ashmon/oracle/network/admin/tnsnames.ora for tns alias lookups
Oracle L
Question :
hello — I’m hacking around w/ ashmon on linux and wondering
if you have any docs regarding installation/configuration on
this platform. Any pointers much appreciated.
Response:
TCL/TK is portable across windows, mac, unix, linux etc
but you have to either get a binary or compile a binary.
For windows, I got all the binarys off the web.
I’ve did a compile for solaris for ASHMON (but not Linux).
The versions of TCL/TK and packages that ASHMON depends on
are:
tcl 8.4
tk 8.4
blt 2.4
oratcl 4.4
thread 2.4
I can put the TCL source on FTP if you’d like to try and compile
it. TCL/TK needs to be compiled thread safe. I have the source for
tcl, tk, blt and thread. I don’t have oratcl but I’m sure it’s
easy to find on the web.
http://www.perfvision.com/ftp/tcl/tcl_tk_blt.tar.gz – should be tcl, tk and blt
http://www.perfvision.com/ftp/tcl/thread24.zip
oratcl
http://downloads.sourceforge.net/oratcl/oratcl44.tar.gz?modtime=1147947755&big_mirror=0
http://downloads.sourceforge.net/oratcl/oratcl-4.4-1.i386.rpm?modtime=1154519037&big_mirror=0
Kyle,
Hi, I much admired your session at Hotsos in March. You mentioned that in 9i it’s easy to get similar reports by joining v$session and v$session_wait….. I’ve scoured your site but I can’t find any example code. Is there any? I’d really love to see it!
Keep up the good work!
CH
Hi Charles,
The 9i collection to simulate ASH is outlined in detail on the “Simulation” tab on this site. Specifically, you’ll find the code to collect ASH data in this script: http://www.perfvision.com/ash/targ_1_pkg.sql
Here is a link to standalone query see what ash data looks like right now (ie no history)
http://www.perfvision.com/ash/ash9i.sql
Hi Kyle
This seems to be a great tool indead! Unfortunately I am not getting it to work against a 10.1.0.4 database.
I have applied the v$ash patch and modified dba_hist_active_sess_history to include the missing columns event, wait_class
and session_state, and modified the contents of session_type.
But most things still don’t work… Is 10.1.0.4 not supported?
Cheers,
Chris
Hi Kyle,
Congratulations for this very nice tool !
I always compare it with Indepth and in my opinion Ahsmon has better granularity than
Indepth (Indepth only shows slices of 15mn where we can’t see CPU peaks for instance).
It is still different in the way that Indepth is more complete regarding history of data
especially if you don’t use a repository. For testing environments that’s more than enough !
I’ve installed it under citrix but one problem that prevents us from using it more officially
is that it doesn’t encrypt passwords in config file and therefore anyone can see them.
Would it be possible to add such encryption ? I know it’s not an official commercial tool yet
but that would really help me convince other people to use it and participate (as end user) to its
enhancement.
I will as soon as possible try to provide you some feedback/suggestions about what could be done
in my opinion.
Was great to see you again in Dallas !
Jean-Marc
Hi Jean-Marc,
No plans to make ASHMON an official tool. I would love to do more work on it but lacking any other contributers prospects are unlikely.
For security, the easy solution is comment out the code that collects the passwords so that they are not stored at all. That way you have to type in the password everytime – incovenient but safe.
Adding encryption would be great and I encourage anyone interested to chip in on the code for things like this. My current code priorities would be to add SQLite as a data store for session sample data enabling ASHMON to point at any Oracle database and by pass v$active_session_history. My other priority would be to add wait event drilldowns that display and aggregate the ASH info in ways that address specific wait events.
Best
Kyle
> Hello Kyle,
>
> I would trully like to try the ASHMON v2 on my linux debian box,
> hovewer I have problem during launching it.
>
> I believe I have all the blt tcl8.4 tk8.4 oratcl 4.4 and thread 2.4
> but if I try to launch it I only get:
>
> $ cat ./ashmon.bat
> /usr/bin/wish8.4 ../funcs/begin.tcl -colormap new
>
> $ ./ashmon.bat
> Error in startup script: couldn’t load file
> “/home/remik/ashmon/bin/mpexpr1080.dll”:
> /home/remik/ashmon/bin/mpexpr1080.dll: invalid ELF header
> while executing
> “load $env(MON_SHARED_LIB)/mpexpr1080.dll”
> (file “../funcs/begin.tcl” line 165)
>
>
> Can You help?
>
> Greetings
> Remigiusz Boguszewicz
> If you have all the binaries for LINUX, I’d love to get a copy of them. Did
> you find all the binaries on the web or did you have to compile some of
> them? That would be really cool to have it running on LINUX.
> I’ve only run it on Solaris and Windows.
>
> Glancing at my code, looks like I stopped supporting both windows and UNIX,
> but this should be fixable with just some small changes to the
> file./funcs/begin.tcl
>
> You’ll need to delete these lines
>
> load $env(MON_SHARED_LIB)/mpexpr1080.dll
> load $env(MON_SHARED_LIB)/thread24.dll
> load $env(MON_SHARED_LIB)/tile078.dll
> set public(oratcl) $env(MON_SHARED_LIB)/oratcl40.dll }
>
> You’ll need to add a line for LINUX specifying where the oratcl 4.4 shared
> library is. On Solaris, using oratcl 4.0, the line looked like:
>
> set public(oratcl) $env(MON_SHARED_LIB)/liboratcl4.0.so
>
> You will also have to delete the lines on ORACLE_HOME for windows (this
> should work automatically on UNIX if ORACLE_HOME is set in the environment):
>
> # proc Text:insert { args } { puts $args }
> set orahome “unknown”
> set path [ split $env(PATH) “;” ]
> foreach i $path {
> if { [ file exists $i/oci.dll ] } {
> set orahome $i
> regsub “.bin.*” $orahome “” orahome
> Text:insert “ORACLE_HOME is $orahome (first location of oci.dll in
> PATH)”
> break
> }
> }
> if { $orahome == “unknown” } {
> Text:insert “ORACLE_HOME not found (no oci.dll in PATH)”
> Text:insert “setting ORACLE_HOME to $env(MON_HOME)/oracle”
> set env(ORACLE_HOME) $env(MON_HOME)/oracle
> }
>
> Best
> Kyle
Hi Kyle,
thank you for the answer.
I tried at first with standard debian provided packages.
apt-get install tk8.4 tcl8.4 blt tklib
then with the ones provided at your site. At both cases I end up in
the following error:
invalid command name “thread::names”
while executing
“thread::names ”
(procedure “connect” line 35)
invoked from within
“connect ”
invoked from within
“.m.f.o.connect.f.b.ok invoke”
(“uplevel” body line 1)
invoked from within
“uplevel #0 [list $w invoke]”
(procedure “tkButtonUp” line 7)
invoked from within
“tkButtonUp .m.f.o.connect.f.b.ok
”
(command bound to event)
To be honest I have no idea what could I try next.
I would greatly appreciate any suggestions.
Greetings
Remigiusz Boguszewicz
We’ll, that’s a little farther along. ASHMON now can’t find the thread procs.
Along with TK8.4, TCl8.4, BLT 2.4 and ORATCL you’ll need Thread 2.4 and tile 7.8
Thread 2.4
The thread source code is here:http://www.perfvision.com/ftp/tcl/thread24.zip
You need to compile it for your machine.
If you already have the thread 2.4 library and ASHMON isn’t finding it then you can force load it with
load full_path_and_name_of_thread_file
Like on Solaris I could do
load $env(MON_SHARED_LIB)/libthread2.4.so
Tile 7.8
Should be available here: http://rpm.pbone.net/index.php3/stat/4/idpl/4616762/com/tcl-tile-0.7.8-alt1.i586.rpm.html
Linux version of ASHMON available !!
http://www.perfvision.com/ftp/ashmon_linux32.tar.gz
still needs a small change to soft link tile0.7.8 to /usr/share/tcl :
cd /usr/share/tcl8.4
ln -s /opt/ashmon/lib/tile0.7.8 .
lrwxrwxrwx 1 root root 24 2008-10-14 12:27 tile0.7.8 -> /usr/share/tcl/tile0.7.8
Thanks to Simon Coter from Oracle Italy who got it working during my 2 day class this past week in Milan.
Hi Kyle,
thank you for all the help during the way.
I implemented the S-ASH repository according to described steps.
Separating major blocks into separate files indeed is easier to
understand and implement.
So far I decided to create a separate repository for each database
beeing monitored. That way it is very obvious which database is taking
how much resources on the repository server and I like the separation.
It is a little more fork, because for each new database I have to
create new schema and execute the repo_* scripts but I like it.
Regarding the ashmon utility it is a great tool. I was always feeling
that Oracle does not pays much attention to wait interface and I could
not understand why. That has changed in version 10g but was true in
version 9 and below. As I started my career on version 8 and 9 I was
miising that a lot. Around 5 or 6 years ago I found very easy
interface that visualise the wait statistics – rrdora –
http://home.zonnet.nl/r.vermeer/rrdora/ . It was already someting and
at least I could see the waits that were happening on the database,
with nice and simple pictures, without having to launch statspack, run
traces or looking at v$ views.
Although the rrdora could show me the waits it did not show which
actual statements were causing them. I could only guess, then run the
statspac, then guess again. Ashmon with s-ash is amazing tool that
actually lets me to see what SQLs are causing the waits. I believe it
should always be that way. It is great that we have the that
funcionality in 10g, but it is even better that I can have similar
capabilities on 9i. Thanks again.
What I find strange in ashmon is:
1) It often happens that I do not see the sqltext of the selected
querry. I have noticed that most of the time it helps to run
sash_pkg.get_all on the target host. As it is run every hour it is
sometimes too seldom to see the sqltext of recent sql.
2) It often happens that SQLs visible have 0 values in SQL_ID, SQL
type and plan_hash. It looks like those are the background processes
like LGWR, etc. but it is only a guess.
3) As I run the ashmon with s-ash on 9i databases I believe that the
explain plan functionality is not available for me (I do not see it
anyway)
4) Clicking on waits window gives me:
working …
wait_analysis: event :log file parallel write:
wait_analysis: id log file parallel write
wait_analysis:toto log file
or shows a SQL:
–ashmon
select
sum(cnt) over ( partition by ash.sql_id order by sql_id ) tcnt, (…)
what is the point of that?
5) Clicking on user_name window gives me:
working … session id 10,929
but nothing happens.
I suspect that some of the problems are related to 9i dtabase and the
fact, that I actually connect with ashmon to repository database and
not actual database, but just wonted to note the spoted issues.
Thanks again. It is great to know what is going on in my database
right now, and few hours before without the effort to go through
statspack guessing and tracing burden.
You are the man. If you ever visit Poland (where I am living) consider
yourself invited for a party.
Greetings
Remigiusz Boguszewicz
Hi Remiqiusz,
I’m glad you like S-ASH and ASHMON. I agree to that Oracle ignored the wait interface and my goal with the performance screens in OEM 10g were to expose the wait interface clearly (they call the “top activity” page the “Kyle Page”). I also wanted access to ASH on all versions without having to pay the license and I wanted a graphic interface in a simple executable instead of the slow big complicated web install of OEM 10g, thus I created ASHMON. Unfortunately I am no longer able to work on ASHMON .
All the problems/issues you stated are areas of development in ASHMON that have still been left to do. The main problem I think is that SQL Text is only collected once and hour. It should probably be collected every 15-30 seconds for new SQL. I did try changing the job period for collecting “get_all” to every 15 seconds but it looked like it used too much resources. What I think should be done is taking the get sql text part out of get_all and run it separately every 15-30 seconds.
There is no explain plan on 9i because I use the dbms_xplan.display_cursor which doesn’t exist on 9i. I think get_all collects the explain plan if I recall correctly, so you should be able to display the explain plan from the sash repository.
The output you see when clicking a wait event or session is the shell for having detailed information on a session or wait. If you click on an IO wait event or buffer busy waits you will see some extra output.
Best Wishes
Kyle Hailey
Hi Kyle;
I’ve tried to run ashmon against an Oracle 10.2.0 database. I’ve logged in as system and got the following error message:
graphsetup:ERROR: invalid command name “Blt_ZoomStack” !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
ash_init: can’t read “public(ash,groups)”: no such element in array
Connection seemed to be successful. What am I missing?
Thanks and best wishes.
Livio
Hi Livio,
Was this on Windows or Linux?
You can comment out (by starting the line with a pound sign, # )
Blt_ZoomStack $graph
In
ashmon/funcs/ash_display.tcl
Not sure why this would be missing unless you are on LINUX (which I haven’t personally tested). This function isn’t required. It allows zooming in and out on the graph. It’s a function that comes with the BLT install that is part of the ASHMON install in ashmon/lib/blt2.4/graph.tcl
Best
Kyle
Hi Kyle;
as a matter of fact, both the db and ashmon were running on Windows XP, SP2. I’ll try your suggestion and keep you posted.
Thank you for responding.
Best wishes
Livio
Hi,
i am an enthusiastic ASHMON PERFVISION tool (OEM LITE) user.
I got my own scripts that i execute through sqlplus to administer the Oracle databases, and i got OEM LITE to capture, isolate e tune the SQLs runing over the databases, leading in a proactive way the application impact over the enviroments (databases, nodes, HW).
But .. after i got to migrate my desktop to Linux Ubuntu, i am not able to install “oem lite” any more.
So, i need some help.
I am runing ubuntu desktop edition 8.10 x86_64:
Linux marinmedeiros-laptop 2.6.27-11-generic #1 SMP Fri Dec 19 16:29:35 UTC 2008 x86_64 GNU/Linux
I run Oracle Instant client 11.1.0.7 with sqlplus
When a execute ashmon.sh the following error is logged
marinmedeiros@marinmedeiros-laptop:/app/oracle/ashmon/bin$ ./ashmon.sh
Error in startup script: couldn’t load file “/app/oracle/ashmon/bin/../lib/thread2.6.3/libthread2.6.3.so”: /app/oracle/ashmon/bin/../lib/thread2.6.3/libthread2.6.3.so: wrong ELF class: ELFCLASS32
while executing
“load $env(MON_SHARED_LIB)/../lib/thread2.6.3/libthread2.6.3.so”
(file “../funcs/begin.tcl” line 168)
Hi,
i am an enthusiastic ASHMON PERFVISION tool (OEM LITE) user.
I got my own scripts that i execute through sqlplus to administer the Oracle databases, and i got OEM LITE to capture, isolate e tune the SQLs runing over the databases, leading in a proactive way the application impact over the enviroments (databases, nodes, HW).
But .. after i got to migrate my desktop to Linux Ubuntu, i am not able to install “oem lite” any more.
So, i need some help.
I am runing ubuntu desktop edition 8.10 x86_64:
Linux marinmedeiros-laptop 2.6.27-11-generic #1 SMP Fri Dec 19 16:29:35 UTC 2008 x86_64 GNU/Linux
I run Oracle Instant client 11.1.0.7 with sqlplus
When a execute ashmon.sh the following error is logged
marinmedeiros@marinmedeiros-laptop:/app/oracle/ashmon/bin$ ./ashmon.sh
Error in startup script: couldn’t load file “/app/oracle/ashmon/bin/../lib/thread2.6.3/libthread2.6.3.so”: /app/oracle/ashmon/bin/../lib/thread2.6.3/libthread2.6.3.so: wrong ELF class: ELFCLASS32
while executing
“load $env(MON_SHARED_LIB)/../lib/thread2.6.3/libthread2.6.3.so”
(file “../funcs/begin.tcl” line 168)
Hi Jutay,
Sounds like the libthread2.6 is incompatible. I’d recommend getting the source for it and recompiling
http://sourceforge.net/project/showfiles.php?group_id=10894&package_id=10438&release_id=453541
best
Kyle
Hi Kyle,
thank you for your response, i have downloaded the source code and i compiled it, without any modification, and i still got the same error.
Do i have to alter any code? Can you help me in this point?
Thank you again.
Hello Jutay,
after recompiled the libthread library you have to modify “funcs/begin.tcl” and change the row:
load $env(MON_SHARED_LIB)/../lib/thread2.6.3/libthread2.6.3.so
with the path where you have the “new” libthread 64bit library .
Hope this helps.
Ciao
Simon COTER
Hi Coter,
i have done what you suggested, and the first error has been solved. In the other hand now i got the following error:
marinmedeiros@marinmedeiros-laptop:~/orasoftware/ashmon/bin$ ./ashmon.sh
Error in startup script: couldn’t load file “/home/marinmedeiros/orasoftware/ashmon/bin/../lib/tile0.7.8/libtile0.7.8.so”: /home/marinmedeiros/orasoftware/ashmon/bin/../lib/tile0.7.8/libtile0.7.8.so: wrong ELF class: ELFCLASS32
while executing
“load $env(MON_SHARED_LIB)/../lib/tile0.7.8/libtile0.7.8.so”
(file “../funcs/begin.tcl” line 171)
I googled this library but i was not able to find anything (just referenced as a library installed automatically by a tool/plugin installation)
Can you still help me?
Thank you a lot
Hi,
i suppose i could sove the error before, of the following way:
load /usr/lib/tcltk/tile0.8.2/libtile0.8.2.so^M
# load $env(MON_SHARED_LIB)/../lib/tile0.7.8/libtile0.7.8.so^M
But now i got other error:
marinmedeiros@marinmedeiros-laptop:~/orasoftware/ashmon/bin$ ./ashmon.sh
Error in startup script: couldn’t load file “C:/Program Files/Tcl/lib/BLT24.so”: C:/Program Files/Tcl/lib/BLT24.so: cannot open shared object file: No such file or directory
while executing
“load $library BLT”
(procedure “LoadBLT” line 30)
invoked from within
“LoadBLT 2.4 ../lib/blt2.4″
(“package ifneeded” script)
invoked from within
“package require BLT 2.4″
(file “../funcs/begin.tcl” line 182)
I really couldn’t find where it took the value “C:/Program File …”
Anyone know how to address this exception?
Thank you
Hi all,
i solved the problem. I installed the blt library, and compiled oratcl4.4 sourcecode fo my 64bits platform. I copied the /usr/lib/Oratcl4.4 directory to ASHMON_HOME and replaces the original Oratcl4.4 folder in this directory (because an error was raising because of 32bits libraries incompatibilities).
Everthig seems to work fine, except when i try to connect and received the following erro:
marinmedeiros@marinmedeiros-laptop:~/orasoftware/ashmon/bin$ ./ashmon.sh
Oratcl_Init(): Failed to load /usr/lib/oracle/11.1/client64/lib/libclntsh.so with error /usr/lib/oracle/11.1/client64/lib/libclntsh.so: cannot open shared object file: No such file or directory
Error from thread tid0x41f6f950
while executing
“load [ tsv::set dll oratcl40 ]”
marinmedeiros@marinmedeiros-laptop:~/orasoftware/ashmon/bin$
Are there any incompatibility with oracle instant client 11g? If so, how can i bypass this problem?
Thnak you a lot.
Well,
about my last update, I solved (or initially solved) the error of the following way:
root@marinmedeiros-laptop:/usr/lib/oracle/11.1/client64/lib# ln -s libclntsh.so.11.1 libclntsh.so
root@marinmedeiros-laptop:/usr/lib/oracle/11.1/client64/lib# ls -lah
total 144M
drwxr-xr-x 2 root root 4.0K 2009-02-04 01:56 .
drwxr-xr-x 4 root root 4.0K 2008-12-08 19:27 ..
-rw-r–r– 1 root root 441 2008-12-10 17:24 glogin.sql
lrwxrwxrwx 1 root root 17 2009-02-04 01:56 libclntsh.so -> libclntsh.so.11.1
-rw-r–r– 1 root root 46M 2008-11-25 09:09 libclntsh.so.11.1
-rw-r–r– 1 root root 8.0M 2008-11-25 09:09 libnnz11.so
-rw-r–r– 1 root root 1.6M 2008-11-25 09:09 libocci.so.11.1
-rw-r–r– 1 root root 82M 2008-11-25 09:09 libociei.so
-rw-r–r– 1 root root 176K 2008-11-25 09:09 libocijdbc11.so
-rw-r–r– 1 root root 1.5M 2008-11-25 09:10 libsqlplusic.so
-rw-r–r– 1 root root 1.5M 2008-11-25 09:10 libsqlplus.so
-rw-r–r– 1 root root 1.9M 2008-11-25 09:09 ojdbc5.jar
-rw-r–r– 1 root root 1.9M 2008-11-25 09:09 ojdbc6.jar
But, when i execute the ashmon.sh and try to connect, a new exception raises:
marinmedeiros@marinmedeiros-laptop:~/orasoftware/ashmon/bin$ ./ashmon.sh
Error from thread tid0x7f9d8c9c06e0
can’t read “handle”: no such variable
while executing
“::callback_mesg “$cur_proc:$handle””
Any idea?
Hello Jutay,
maybe the problem is that you use some 32bit and some 64bit library.
You have to be shure to use only one type of library ( only 32 or only 64 ).
If you want to try to use 32bit library you can use the “linux32″ command on your laptop.
Bye
Simon
Hello Jutay,
maybe the problem is that you use some 32bit and some 64bit library.
You have to be shure to use only one type of library ( only 32 or only 64 ).
If you want to try to use 32bit library you can use the “linux32″ command on your laptop.
Bye
ps: to check if you are using a 32bit or 64 library file you can follow this example:
[oracle@area51 ~]$ file /app/oracle/APEX/11.1.0/lib/libclntsh.so
/app/oracle/APEX/11.1.0/lib/libclntsh.so: symbolic link to `/app/oracle/APEX/11.1.0/lib/libclntsh.so.11.1′
[oracle@area51 ~]$ file /app/oracle/APEX/11.1.0/lib/libclntsh.so.11.1
/app/oracle/APEX/11.1.0/lib/libclntsh.so.11.1: ELF 32-bit LSB shared object, Intel 80386, version 1 (SYSV), dynamically linked, not stripped
Simon