Active Sessions
When you want to do a verification of current activity in the database, the following sos_sessions_active.sql script will list information on active sessions, blockers, associated events, impacted objects, users, program and services that are currently running across all instance(s) in the database (i.e. this script can run against a single instance or RAC database).An example of this report can be found here:
(Click on image to zoom in)
Historical Sessions
At times, identifying performance issues in real time can be challenging. By the time the DBA is called upon to investigate the issue disappears. Rather then waiting for the issue to resurface, with Active Session History (ASH) introduced in 10g, the feature has become invaluable source to allow DBAs to perform post analysis and of sessions captured in the past.Using the sos_sessions_history.sql script, by specifying the time period you wish to review, the report will return details on sessions and associated events.
NOTE: The columns “machine” and “sql_opname” is not available to be reported against DBA_HIST_ACTIVE_SESS_HISTORY in version 10g and 11.1. So the sos_sessions_history.sql script will be required to edited to remove these columns from the query is executing against versions earlier then 11.2.
A snippet from a sample output can be found here: (Click on image to zoom in)
