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:
select distinct case is_awr_sample when 'Y' then 'Y' end flag, sample_id, sample_time from v$active_session_history where sample_time > sysdate - 1/1440 order by 2,1 ; F SAMPLE_ID SAMPLE_TIME - ---------- -------------------------------- 3435324 26-MAR-15 05.52.53.562 PM 3435325 26-MAR-15 05.52.54.562 PM 3435326 26-MAR-15 05.52.55.562 PM 3435327 26-MAR-15 05.52.56.562 PM 3435328 26-MAR-15 05.52.57.562 PM 3435329 26-MAR-15 05.52.58.562 PM 3435330 26-MAR-15 05.52.59.562 PM 3435331 26-MAR-15 05.53.00.562 PM Y 3435332 26-MAR-15 05.53.01.562 PM 3435333 26-MAR-15 05.53.02.572 PM 3435334 26-MAR-15 05.53.03.572 PM 3435335 26-MAR-15 05.53.04.572 PM 3435336 26-MAR-15 05.53.05.572 PM 3435337 26-MAR-15 05.53.06.572 PM 3435338 26-MAR-15 05.53.07.572 PM 3435339 26-MAR-15 05.53.08.572 PM 3435340 26-MAR-15 05.53.09.572 PM 3435341 26-MAR-15 05.53.10.582 PM Y 3435342 26-MAR-15 05.53.11.582 PM 3435343 26-MAR-15 05.53.12.582 PM 3435344 26-MAR-15 05.53.13.582 PM 3435345 26-MAR-15 05.53.14.582 PM 3435346 26-MAR-15 05.53.15.582 PM 3435347 26-MAR-15 05.53.16.582 PM 3435348 26-MAR-15 05.53.17.582 PM 3435349 26-MAR-15 05.53.18.592 PM 3435350 26-MAR-15 05.53.19.592 PM 3435351 26-MAR-15 05.53.20.592 PM Y 3435352 26-MAR-15 05.53.21.602 PM 3435355 26-MAR-15 05.53.24.602 PM 3435358 26-MAR-15 05.53.27.612 PM 3435361 26-MAR-15 05.53.30.622 PM 3435367 26-MAR-15 05.53.36.660 PM 3435370 26-MAR-15 05.53.39.670 PM 3435371 26-MAR-15 05.53.40.670 PM 3435373 26-MAR-15 05.53.42.670 PM 3435380 26-MAR-15 05.53.49.700 PM 3435381 26-MAR-15 05.53.50.700 PM Y 3435382 26-MAR-15 05.53.51.700 PM 3435383 26-MAR-15 05.53.52.700 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.