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
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!