Re: Performance queries

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: niall.litchfield@xxxxxxxxx
  • Date: Wed, 24 Aug 2011 08:39:35 -0600

Kyle had to stop working on Ashmon when he joined Embarcadero. Fortunately he 
could show/prove that he did not touch it once he started working for 
Embarcadero, else they would have claimed it as their property. You can 
download it from
Marcin Przepiorowski has made changes to Kyle's original V2 ( dated 2008-03-30 
) as V2.1 and V2.2 and posted those on the Ashmasters site
I have made extensive modifications as well and use it at two client sites. I 
have not published my modifications. Firstly because I am still not done with 
it ( I am afraid it will be a "work in progress" forever ) and when I 
contemplated posting what I had done, Marcin had posted V2.2 which, if I'm not 
mistaken is a 180˚ departure ( the repository polls the targets rather than the 
targets sending data to the repository ) and I did not want to confuse the 
situation by posting a 2.3 or 3.1 version which reverses that again. If anyone 
is interested in "my" version contact me by e-mail.
I shall present about Ashmon and my experiences with it at the UKOUG conference.

To answer Niall's question, Yes, it supports 9i although I do not have any 
experience with it on 9i.
On 2011-08-24, at 4:12 AM, Niall Litchfield wrote:

> Hi Bill
> It *sounds* like you are saying that the server is on CPU all the time 
> (though 100% utilized could mean other things). To troubleshoot this I start 
> with an OS tool to grab the top cpu consuming O/S processes (or threads in 
> windows). 
> I then run the following query to grab the active SQL 
> select
>      s.sid
> ,    s.serial#
> ,    p.spid ospid
> ,    q.sql_text
> ,    s.sql_child_number
> from
> v$process p
> ,   v$session s
> ,   v$sqlarea q
> where
> p.addr = s.paddr
> and     s.sql_id=q.sql_id(+)
> and     p.spid = &os_process_id;
> It may be that there isn't active SQL but this is rare in my experience (and 
> usually turns out to be session waiting on a PL/SQL loop anyway. You'll also 
> want to run statspack and take a look at the top sql there - though in 9i 
> unfortunately there isn't an 'ordered by elapsed time' section. Statspack 
> will also give you top 5 timed events as well. 
> Bear in mind that once you've identified your top sessions - either via 
> TOP/process monitor etc - or via the application or some other means ("how 
> come it's always Sue in accounts ringing to complain about performance?" ) 
> then 9i is just as susceptible as any other release since 7.3 to the Method-R 
> approach.(Identify sessions, gather diagnostic data, profile, resolve issue 
> and predict performance increase). or the other trace file 
> profilers out there will be useful here.   
> Finally I've had some success with Tanel Poder's session snapper on 9i 
> (though I've also had failures on 9i as well - tool ran but not in a timely 
> fashion), you can grab that tool at 
> I know that Kyle Hailey was working on a simulated Active Session History 
> tool ( but I'm not sure 
>  a)  if it supports 9i
>  b)  what the state of it currently is and 
>  c)  which of Kyle's sites are current. 
> This is a plea for Kyle to come out of the woodwork really and let us know 
> what he has made available non-commercially and where! 

Other related posts: