Get more information about waits and events to improve performance

When you've got a problem you often need to know what's going on under the hood of your database. Oracle has a ton of features where you can get lots of information, sometimes even too much information, especially for someone without a lot of experience in that field.

ASH (Active Session History) might be one of those features. Every second Oracle monitors the session activity and keeps it in V$ACTIVE_SESSION_HISTORY. After about 30 minutes, this information is saved in AWR (Automatic Workload Repository).

Without much explanation, look at the result of this query:

SELECT * FROM v$active_session_history;

Recipe #1 - Identify the top 5 waiting sessions

As you can see, you'll see lots of things by running this query. You'll probably need some time to understand it completely. To start with, here's one example which shows the top 5 waiting sessions during the last 10 minutes:

SELECT session_id, COUNT(*)
FROM v$active_session_history
WHERE session_state = 'WAITING'
  AND sample_time > SYSDATE - 10/1440
GROUP BY session_id
ORDER BY COUNT(*) DESC;

Recipe #2 - ASH report

Oracle also has functions which return a complete report to get a nice overview in no time. Run the following query to see what's happening in your database:

SELECT output FROM TABLE(
  dbms_workload_repository.ash_report_text(
    (SELECT dbid FROM v$database),
    (SELECT instance_number FROM v$instance),
    SYSDATE - 30/1440,
    SYSDATE
  )
);

This article only scratches the surface of ASH and its related tools but it should give you some information to start with. But please note: You need to have the proper Oracle license if you want to use ASH!