Money for nothing and ASH for free

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

--
http://www.freelists.org/webpage/oracle-l