MMON Sampling ASH Data

When I was working on the course, I noticed this White Paper (PDF) on Oracle 10g Self-Management Framework Internals: Exploring the Automatic Workload Repository. The paper describes the way that MMON selects 1 in 10 of the ASH samples for storage in DBA_HIST_ACTIVE_SESS_HISTORY (I suppose it would be more correct to say WRH$_ACTIVE_SESSION_HISTORY_BL) :-

"The in-memory data that is selected for writing is randomly chosen (sampling of the samples)."

This phrase confused me but, rather than digging around to resolve it (it was late), I took the paper at face value and repeated the phrase during the class the next day. Someone questioned it (as I said before, they were a smart bunch) and I found myself unable to justify it, particularly as it didn't really make sense to me either. So that evening I sent a mail to a couple of people who would know and, as well as getting a quick answer, was able to sit back and enjoy a debate about whether the selection could be described as 'random' or not. The answer first :-

Not random, think MOD(sample_id, 10)

Initially I thought that meant that MMON selects every tenth sample from the ASH buffers^*. That would be the case if the sample_id was incremented every time there was data in a sample but it's incremented even if there are no active sessions. For example, here is a selection of ASH samples from an active system. (Note that a 'sample' includes all of the sessions that were Active at the sample point, so the volume of data and number of sessions in each sample will vary.)

SYS@TEST1020> SELECT sample_id, TO_CHAR(sample_time, 'HH24:MI:SS'), COUNT(*)
  2  FROM v$active_session_history
  3  WHERE sample_id > 1238065
  4  GROUP BY sample_id, TO_CHAR(sample_time, 'HH24:MI:SS')
  5  ORDER BY 1
  6  /

 SAMPLE_ID TO_CHAR(   COUNT(*)
---------- -------- ----------
   1238073 20:57:17          1
   1238074 20:57:18          2

Straightforward so far; there was one active session at 20:57:17 and two at 20:57:18. So let's look at the next bunch of samples that appear in the output.

   1238162 20:58:47          1
   1238242 21:00:08          1
   1238245 21:00:11          1
   1238266 21:00:32          1
   1238278 21:00:45          1
   1238286 21:00:53          1
   1238358 21:02:06          1
   1238371 21:02:19          2
   1238483 21:04:12          1
   1238510 21:04:40          1
   1238527 21:04:57          1
   1238554 21:05:24          1
   1238560 21:05:31          1
   1238563 21:05:34          1
   1238575 21:05:46          1
   1238667 21:07:19          1

Because there were no active sessions between 20:57:19 and 20:58:46, there's no ASH data (as expected), but SAMPLE_ID is incremented every second. If we apply MOD(SAMPLE_ID) to determine which samples should be written to the workload repository, we'd end up with something like this.

   1238073 20:57:17          1
   1238074 20:57:18          2
   1238162 20:58:47          1
   1238242 21:00:08          1
   1238245 21:00:11          1
   1238266 21:00:32          1
   1238278 21:00:45          1
   1238286 21:00:53          1
   1238358 21:02:06          1
   1238371 21:02:19          2
   1238483 21:04:12          1
   1238510 21:04:40          1 << IN AWR   
   1238527 21:04:57          1
   1238554 21:05:24          1
   1238560 21:05:31          1 << IN AWR   
   1238563 21:05:34          1
   1238575 21:05:46          1
   1238667 21:07:19          1

I can check this by forcing a manual AWR snapshot.

SYS@TEST1020> exec dbms_workload_repository.create_snapshot

PL/SQL procedure successfully completed.

SYS@TEST1020> SELECT ash.sample_id, TO_CHAR(ash.sample_time, 'HH24:MI:SS'),
  2     DECODE(awr.sample_id, NULL, 'NO', 'YES') in_awr
  3  FROM v$active_session_history ash, dba_hist_active_sess_history awr
  4  WHERE ash.sample_id=awr.sample_id  (+)
  5  AND ash.sample_id > 1238065
  6  GROUP BY ash.sample_id, TO_CHAR(ash.sample_time, 'HH24:MI:SS'),
  7     DECODE(awr.sample_id, NULL, 'NO', 'YES')
  8  ORDER BY 2;

 SAMPLE_ID TO_CHAR( IN_
---------- -------- ---
   1238073 20:57:17 NO
   1238074 20:57:18 NO
   1238162 20:58:47 NO
   1238242 21:00:08 NO
   1238245 21:00:11 NO
   1238266 21:00:32 NO
   1238278 21:00:45 NO
   1238286 21:00:53 NO
   1238358 21:02:06 NO
   1238371 21:02:19 NO
   1238483 21:04:12 NO
   1238510 21:04:40 YES
   1238527 21:04:57 NO
   1238554 21:05:24 NO
   1238560 21:05:31 YES
   1238563 21:05:34 NO
   1238575 21:05:46 NO
   1238667 21:07:19 NO

Bear in mind that this is on a very quiet instance running on my laptop so it's possible to get large holes in the AWR data. The reality is that any reasonably busy system is likely to have ASH data for every second and so will have an AWR sample for every ten seconds.

As for the debate, I'm no mathematician, but although the sample time gaps in DBA_HIST_ACTIVE_SESS_HISTORY might appear random in isolation, it seems clear to me that they aren't. I think the term 'sampling of the samples' is perfect, though.

* I suppose it does select every tenth sample really, but there might be no data there.