Manually Getting Active Session Information

Problem
You need to do performance analysis on sessions that run too frequently or are too short to be available on available AWR snapshots. The AWR snapshots are not taken often enough to capture the information that you need.

Solution
You can use the Oracle Active Session History (ASH) information in order to get real-time or near real time session information. While the AWR information is very useful, it is bound by the reporting periods, which are by default run every hour on your database. The ASH information has active session information, and is sampled every second from V$SESSION, and can show more real-time or near real time session information to assist in doing performance analysis on your database. There are a few ways to get active session information from the database:

• Running the canned ASH report
• Running an ASH report from within Enterprise Manager
• Getting ASH information from the data dictionary

The easiest method to get information on active sessions is to run the ashrpt.sql script, which is similar in nature to the awrrpt.sql script that is run when generating an AWR report. 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

There are many sections to the ASH report. See Table 4-1 for a brief description of each section. See the following snippet from many of the sections of the ASH report. Some sections have been shortened for brevity.

How It Works

Retrieving ASH information is necessary if you need to get session information more current than you can retrieve from the AWR report. Again, AWR information is generated only hourly by default. ASH information is gathered every second from V$SESSION, and stores the most useful session information to help gauge database performance at any given moment.

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. You may need to view the DBA_HIST_ACTIVE_SESS_HISTORY historical view in order to get the ASH information you need if your database is very active. For an example of querying the DBA_HIST_ACTIVE_SESS_HISTORY view, see Recipe 4-13. 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:

SELECT min(sample_time) FROM dba_hist_active_sess_history;
MIN(SAMPLE_TIME)
---------------------------------------------------------------------------
20-MAR-11 11.00.27.433 PM

The MMON background process, which manages the AWR hourly snapshots, also flushes ASH information to the historical view at the same time. If there is heavy activity on the database, and the buffer fills between the hourly AWR snapshots, the MMNL background process will wake up and flush the ASH data to the historical view.

The V$ACTIVE_SESSION_HISTORY and DBA_HIST_ACTIVE_SESS_HISTORY views contain much more detailed information than just the samples shown within this recipe, and you can drill down and get much more information at the session level, if desired, including information regarding actual SQL statements, the SQL operations, blocking session information, and file I/O information.