Oracle Scratchpad

March 27, 2015


Filed under: Infrastructure,Oracle,Troubleshooting — Jonathan Lewis @ 9:41 am GMT Mar 27,2015

There was a little conversation on Oracle-L about ASH (active session history) recently which I thought worth highlighting – partly because it raised a detail that I had got wrong until Tim Gorman corrected me a few years ago.

Once every second the dynamic performance view v$active_session_history copies information about active sessions from v$session. (There are a couple of exceptions to the this rule – for example if a session has called dbms_lock.sleep() it will appear in v$session as state = ‘ACTIVE’, but it will not be recorded in v$active_session_history.) Each of these snapshots is referred to as a “sample” and may hold zero, one, or many rows.

The rows collected in every tenth sample are flagged for copying into the AWR where, once they’ve been copied into the underlying table, they can be seen in the view dba_hist_active_sess_history.  This is where a common misunderstanding occurs: it is not every 10th row in v$active_session_history it’s every 10th second; and if a sample happens to be empty that’s still the sample that is selected (which means there will be a gap in the output from dba_hist_active_sess_history). In effect dba_hist_active_sess_history holds copies of the information you’d get from v$session if you sampled it once every 10 seconds instead of once per second.

It’s possible to corroborate this through a fairly simple query as the rows from v$active_session_history that are going to be dumped to the AWR are as they are created:

        distinct case is_awr_sample when 'Y' then 'Y' end flag,
        sample_time > sysdate - 1/1440
order by

- ---------- --------------------------------
     3435324 26-MAR-15 PM
     3435325 26-MAR-15 PM
     3435326 26-MAR-15 PM
     3435327 26-MAR-15 PM
     3435328 26-MAR-15 PM
     3435329 26-MAR-15 PM
     3435330 26-MAR-15 PM
     3435331 26-MAR-15 PM
Y    3435332 26-MAR-15 PM
     3435333 26-MAR-15 PM
     3435334 26-MAR-15 PM
     3435335 26-MAR-15 PM
     3435336 26-MAR-15 PM
     3435337 26-MAR-15 PM
     3435338 26-MAR-15 PM
     3435339 26-MAR-15 PM
     3435340 26-MAR-15 PM
     3435341 26-MAR-15 PM
Y    3435342 26-MAR-15 PM
     3435343 26-MAR-15 PM
     3435344 26-MAR-15 PM
     3435345 26-MAR-15 PM
     3435346 26-MAR-15 PM
     3435347 26-MAR-15 PM
     3435348 26-MAR-15 PM
     3435349 26-MAR-15 PM
     3435350 26-MAR-15 PM
     3435351 26-MAR-15 PM
Y    3435352 26-MAR-15 PM
     3435355 26-MAR-15 PM
     3435358 26-MAR-15 PM
     3435361 26-MAR-15 PM
     3435367 26-MAR-15 PM
     3435370 26-MAR-15 PM
     3435371 26-MAR-15 PM
     3435373 26-MAR-15 PM
     3435380 26-MAR-15 PM
     3435381 26-MAR-15 PM
Y    3435382 26-MAR-15 PM
     3435383 26-MAR-15 PM

40 rows selected.

As you can see at the beginning of the output the samples have a sample_time that increases one second at a time (with a little slippage), and the flagged samples appear every 10 seconds at 5.53.01, 5.53.11 and 5.53.21; but then the instance becomes fairly idle and there are several sample taken over the next 20 seconds or so where we don’t capture any active sessions; in particular there are no rows in the samples for 5.53.31, and 5.53.41; but eventually the instance gets a little busy again and we see that we’ve had active sessions in consecutive samples for the last few seconds, and we can see that we’ve flagged the sample at 5.53.51 for dumping into the AWR.

You’ll notice that I seem to be losing about 1/100th second every few seconds; this is probably a side effect of virtualisation and having a little CPU-intensive work going on in the background. If you see periods where the one second gap in v$active_session_history or 10 second gap in dba_hist_active_sess_history has been stretched by several percent you can assume that the CPU was under pressure over that period. The worst case I’ve seen to date reported gaps of 12 to 13 seconds in dba_hist_active_sess_history.  The “one second” algorithm is “one second since the last snapshot was captured” so if the process that’s doing the capture doesn’t get to the top of the runqueue in a timely fashion the snapshots slip a little.

When the AWR snapshot is taken, the flagged rows from v$active_session_history are copied to the relevant AWR table. You can adjust the frequency of sampling for both v$active_session_history, and dba_hist_active_sess_history, of course – there are hidden parameters to control both: _ash_sampling_interval (1,000 milliseconds) and _ash_disk_filter_ratio (10). There’s also a parameter controlling how much memory should be reserved in the shared pool to hold v$active_session_history.: _ash_size (1048618 bytes per session in my case).  The basic target is to keep one hour’s worth of data in memory, but if there’s no pressure for memory you can find that the v$active_session_history holds more than the hour; conversely, if there’s heavy demand for memory and lots of continuously active sessions you may find that Oracle does “emergency flushes” of v$active_session_history between the normal AWR snapshots. I have heard of people temporarily increasing the memory and reducing the interval and ratio – but I haven’t yet felt the need to do it myself.



  1. […] Oracle uses to capture a snapshot once every second of what each currently active session is doing; every 10th snapshot is then echoed down into the Automatic Workload Repository (AWR) by a process that runs every hour […]

    Pingback by Trouble-shooting | Oracle Scratchpad — December 4, 2015 @ 7:22 am GMT Dec 4,2015 | Reply

  2. If we are taking hundreds or thousands of calls per second, So ASH will missing information by the same rule?
    Is the tracing with 10046 event will help for in-depth analysis from a process monitoring perspective?
    What are ASH pro’s and cons’s over trace?

    Comment by Anil — October 6, 2016 @ 6:26 pm GMT Oct 6,2016 | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

You are commenting using your 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 )

Google+ photo

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

Connecting to %s

Blog at