Statistical Data about Database Events, Waits and more using ASH


Problem

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 tun 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 that feature. 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

Solution

Recipe #1 - Identity 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 session 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 on the surface of ASH and it's related tools but it should give you some information to start with it. But please note: You need to have the proper Oracle license if you want to use ASH!

Comments




Please sign-in to post a comment