jump to navigation

Sessions and Events in the Database April 9, 2013

Posted by tamnau in AWR, General, Performance, Scripts.
Tags: , , , , , ,

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



No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: