Re: Money for nothing and ASH for free

  • From: "kyle Hailey" <kylelf@xxxxxxxxx>
  • To: "John Kanagaraj" <john.kanagaraj@xxxxxxxxx>
  • Date: Thu, 21 Jun 2007 13:47:15 -0700

SGA attach from a performance point of view is probably better than Oracle
10g ASH. I've work on at least one site where the database hung, including
ASH collection. If they had used external SGA attach, then they would have
been able to collect the ASH data.
On the other hand 10g ASH includes some nice extras that I don't know how to
calculate with SGA attatch and SGA attach is much harder to package into an
easy install.
Installing a PLSQL package, though much less efficient, is much much easier
to write and maintain and distribute.
If someone has motivation and experience then of course the SGA attach could
get them the same info as the PLSQL package for extremely cheap.

On 6/21/07, John Kanagaraj <john.kanagaraj@xxxxxxxxx> wrote:

Thanks Kyle. I know you advocated Direct SGA access for the same data,
but this is different, correct?

I have the issue of having to manage older 8i/9i databases so I
adapted 10g's ASH and Metrics ideas in a slightly different way and
wrote some code that could be used as the basis of a 'backport' of
these tools. Actually presented these ideas as a paper at COLLAB 06
and OOW 06, but stopped there and did not actually develop that into a
tool (I just display/spool the simulated ASH output and interpret it
as I see it). Let me know if you would like to see that paper. You can
probably use that to expand this concept to ADDM....


On 6/20/07, kyle Hailey <kylelf@xxxxxxxxx> wrote:
> If you are on 10g and already have the performance pack, you can
> skip this email.
> Some people have been complaining about the licensing of the performance
> packs. I agree it's weird that v$active_session_history is there but you
> aren't suppose to query it. On the other hand I think the performance
> is definitely worth it if you are working on an important database where
> slowdowns or outages have financial impact.
> On the other hand, if you don't want to pay, can't pay  or if you are on
> version 7, 8 or 9  then you can simulate ASH.
> I've put together a plsql package that simulates ASH on
> Active Session History is an amazingly rich sort of peformance data and
> the fun is just thinking of new ways to mine the data.
> Things you can do are graph system load over time, find the longest
> SQL, find the top SQL by cpu or wait, or see the resource breakdown for
> single SQL etc etc
> This paper explains a bit of ASH power
> Examples of some SQL and output from querying ASH data are on
> One cool thing is, even if you are on 10g, the queries from the
> ASH should be roughly compatible with v$active_session_history since
> basically the same structure and columns. There are a few differences
> though.
> Best Wishes
> Kyle Hailey

John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)

** The opinions and facts contained in this message are entirely mine
and do not reflect those of my employer or customers **

Other related posts: