Money for nothing and ASH for free

  • From: "kyle Hailey" <kylelf@xxxxxxxxx>
  • To: Oracle-L@xxxxxxxxxxxxx
  • Date: Wed, 20 Jun 2007 16:38:47 -0700

If you are on 10g and already have the performance pack, you can probably
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 pack
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
   http://www.perfvision.com/ash.php
Active Session History is an amazingly rich sort of peformance data and half
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 running
SQL, find the top SQL by cpu or wait, or see the resource breakdown for a
single SQL etc etc

This paper explains a bit of ASH power
http://www.perfvision.com/papers/Sampling.pdf

Examples of some SQL and output from querying ASH data are on
http://www.perfvision.com/sql/ash/note.html
One cool thing is, even if you are on 10g, the queries from the simulated
ASH should be roughly compatible with v$active_session_history since it's
basically the same structure and columns. There are a few differences
though.

Best Wishes
Kyle Hailey
http://perfvision.com

Other related posts: