Active session History (ASH) is a great enhancement came with oracle 10g. It takes session-state snapshots in every second so you can analyze what is going on your DB easily. Oracle Enterprise Manager Console and AWR reports mostly use ASH to provide all that kind of analysis, problem resolution and tuning tips to you. Altough these front-ends are great tools for general administration, they are not very cutomizable enough to do more specific analysis.


Let me give an example. Assume that you have a hot block problem on a specific object. You may want to list top 10 sqls cousing this contention. Furthermore, you may want to list top 10 application server machines from which opened connections cause this contention. This is an information that you can get from system tables but not from EM or AWR.