How to use ASH report to pinpoint issues in Oracle?

Oracle has another nifty report that helps you dive into more granularity on the happenings within a DB that AWR reports cannot. ASH stands really for Active Sessions History, a mechanism within Oracle that stores history about sessions in a circular cache, meaning older stuff in the cache gets overwritten with new session history. So this is not something you can query to get what the active session history was say 2 weeks ago.

You can run ASH from SQL *Plus using a simple call:

SQL>@?/rdbms/admin/ashrpt.sql

Where ? = your ORACLE_HOME directory.

When it prompts you for begin time you can give an actual date, and time to the minute and second, or if you wanted to start from a begin time 20 minutes ago, just type -20 (yes, negative 20). And for duration give the number of minutes you want the report to cover, I usually pick a couple of minutes before an incident and couple of minutes after the incident. Usually I try not to go beyond 10-15 mins duration.

Once the report is generated, look for the below:

1. Average Active Sessions: the more this statistics the more your DB was busy. So usually if you see this in the several tens or hundreds then you have a problem!

2. Top User Events: this gives you an idea as to what the most waited on event was within the time span you ran the report for. The % Event gives you an idea as to how much a particular wait event contributed to the overall wait.

3. Top SQL Command Types: this will give you an idea of what operation was being run the most, usually UPDATE/DELETES etc may show you locks etc. So pay attention.

4. Top SQL with TOP Events: this is the main section that shows you the most expensive SQL for the time period in question along with their SQL ID and the database wait event that contributed to their latency.

There are other details, but these 4 above will give you a very quick idea on what slowed the database and these ASH reports are much less verbose than the AWR reports and more importantly, ASH is much more granular.

Once you have identified the SQL ID, you can delve deeper to look at the plan and maybe craft a solution to the problematic SQL.