Tuesday, September 27, 2011

Manually Getting Active Session Information

So first we need to know what is ASH ???

Well, You can use the Oracle Active Session History (ASH) information in order to get real-time or near real-time session information. AWR report is also very useful, however, its usually based  on reporting periods like it is normally scheduled to run after every one hour.
The ASH information  has active session information, and is sampled every second from V$SESSION. There are a few ways to get active session information from the database:

The easiest method to get information on active sessions is to run the ashrpt.sql script in SQL PLUS as below

SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql

When you run the ashrpt.sql script, it asks you for the following:
•  Report type (text or HTML)
•  Begin time for report (defaults to current time minus 15 minutes)
•  End time for report (defaults to current time)
•  Report name

How It Works ?

The ASH information is stored within a circular buffer in the SGA. Oracle documentation states that
the buffer size is calculated as follows:
Max [Min [ #CPUs * 2 MB, 5% of Shared Pool Size, 30MB ], 1MB ]
The amount of time that the information is stored within the data dictionary depends on the activity
within your database.

To quickly see how much data is held in your historical view, you could simply get the earliest SAMPLE_TIME from the DBA_HIST_ACTIVE_SESS_HISTORY view:

SQL>SELECT min(sample_time) FROM dba_hist_active_sess_history; 

18-SEP-11 PM

No comments:

Post a Comment