{"id":1402,"date":"2008-04-17T12:00:00","date_gmt":"2008-04-17T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1402"},"modified":"2008-04-17T12:00:00","modified_gmt":"2008-04-17T12:00:00","slug":"mmon-sampling-ash-data","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2008\/04\/17\/mmon-sampling-ash-data\/","title":{"rendered":"MMON Sampling ASH Data"},"content":{"rendered":"

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<\/a>. 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) :-<\/p>\n

“The in-memory data that is selected for writing is randomly chosen (sampling of the samples).”<\/em><\/p><\/blockquote>\n

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 :-<\/em><\/p>\n

Not random, think MOD(sample_id, 10)<\/em><\/p><\/blockquote>\n

Initially I thought that meant that MMON selects every tenth sample from the ASH buffers*<\/sup>. 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
\nthe sample point, so the volume of data and number of sessions in each
\nsample will vary.)<\/p>\n

SYS@TEST1020> SELECT sample_id, TO_CHAR(sample_time, 'HH24:MI:SS'), COUNT(*)\n  2  FROM v$active_session_history\n  3  WHERE sample_id > 1238065\n  4  GROUP BY sample_id, TO_CHAR(sample_time, 'HH24:MI:SS')\n  5  ORDER BY 1\n  6  \/\n\n SAMPLE_ID TO_CHAR(   COUNT(*)\n---------- -------- ----------\n   1238073 20:57:17          1\n   1238074 20:57:18          2<\/pre>\n
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.<\/p>\n
   1238162 20:58:47          1\n   1238242 21:00:08          1\n   1238245 21:00:11          1\n   1238266 21:00:32          1\n   1238278 21:00:45          1\n   1238286 21:00:53          1\n   1238358 21:02:06          1\n   1238371 21:02:19          2\n   1238483 21:04:12          1\n   1238510 21:04:40          1\n   1238527 21:04:57          1\n   1238554 21:05:24          1\n   1238560 21:05:31          1\n   1238563 21:05:34          1\n   1238575 21:05:46          1\n   1238667 21:07:19          1<\/pre>\n
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.<\/p>\n
   1238073 20:57:17          1\n   1238074 20:57:18          2\n   1238162 20:58:47          1\n   1238242 21:00:08          1\n   1238245 21:00:11          1\n   1238266 21:00:32          1\n   1238278 21:00:45          1\n   1238286 21:00:53          1\n   1238358 21:02:06          1\n   1238371 21:02:19          2\n   1238483 21:04:12          1\n   1238510 21:04:40          1 << IN AWR<\/strong>   \n   1238527 21:04:57          1\n   1238554 21:05:24          1\n   1238560 21:05:31          1 << IN AWR<\/strong>   \n   1238563 21:05:34          1\n   1238575 21:05:46          1\n   1238667 21:07:19          1<\/pre>\n
I can check this by forcing a manual AWR snapshot.<\/p>\n
SYS@TEST1020> exec dbms_workload_repository.create_snapshot\n\nPL\/SQL procedure successfully completed.\n\nSYS@TEST1020> SELECT ash.sample_id, TO_CHAR(ash.sample_time, 'HH24:MI:SS'),\n  2     DECODE(awr.sample_id, NULL, 'NO', 'YES') in_awr\n  3  FROM v$active_session_history ash, dba_hist_active_sess_history awr\n  4  WHERE ash.sample_id=awr.sample_id  (+)\n  5  AND ash.sample_id > 1238065\n  6  GROUP BY ash.sample_id, TO_CHAR(ash.sample_time, 'HH24:MI:SS'),\n  7     DECODE(awr.sample_id, NULL, 'NO', 'YES')\n  8  ORDER BY 2;\n\n SAMPLE_ID TO_CHAR( IN_\n---------- -------- ---\n   1238073 20:57:17 NO\n   1238074 20:57:18 NO\n   1238162 20:58:47 NO\n   1238242 21:00:08 NO\n   1238245 21:00:11 NO\n   1238266 21:00:32 NO\n   1238278 21:00:45 NO\n   1238286 21:00:53 NO\n   1238358 21:02:06 NO\n   1238371 21:02:19 NO\n   1238483 21:04:12 NO\n   1238510 21:04:40 YES<\/strong>\n   1238527 21:04:57 NO\n   1238554 21:05:24 NO\n   1238560 21:05:31 YES<\/strong>\n   1238563 21:05:34 NO\n   1238575 21:05:46 NO\n   1238667 21:07:19 NO<\/pre>\n
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. <\/p>\n
As for the debate, I’m no mathematician, but although the sample time
\ngaps in DBA_HIST_ACTIVE_SESS_HISTORY might appear random in isolation,
\nit seems clear to me that they aren’t. I think the term ‘sampling of
\nthe samples’ is perfect, though.<\/p>\n
* I suppose it does select every tenth sample really, but there might be no data there.<\/p>\n","protected":false},"excerpt":{"rendered":"
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… Continue reading MMON Sampling ASH Data<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1402","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1480,"url":"http:\/\/orcldoug.com\/blog\/2009\/03\/31\/diagnosing-locking-problems-using-ash-part-3\/","url_meta":{"origin":1402,"position":0},"title":"Diagnosing Locking Problems using ASH – Part 3","date":"March 31, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.In the last part I described a locking scenario where the blocking session had only executed one SQL statement that was quick enough to avoid being sampled by ASH and is now inactive. As a result, there is no ASH data\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1395,"url":"http:\/\/orcldoug.com\/blog\/2008\/03\/25\/ash-and-the-psychology-of-hidden-parameters\/","url_meta":{"origin":1402,"position":1},"title":"ASH and the psychology of Hidden Parameters","date":"March 25, 2008","format":false,"excerpt":"Time for a quick break from the final push to complete the course slides. I've (probably foolishly) decided to apply the 10.2.0.4 patch to my test database.As I was confirming the details of when Oracle starts to flush information from the ASH Buffer to the workload repository, I thought I'd\u2026","rel":"","context":"With 5 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1478,"url":"http:\/\/orcldoug.com\/blog\/2009\/03\/30\/diagnosing-locking-problems-using-ash-part-2\/","url_meta":{"origin":1402,"position":2},"title":"Diagnosing Locking Problems using ASH – Part 2","date":"March 30, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.I tried the graphical approach to tracking down the root cause of a locking problem in the last part. Now let's look at the underlying ASH data. I ran the same test after bouncing my test instance to start from a\u2026","rel":"","context":"With 3 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1505,"url":"http:\/\/orcldoug.com\/blog\/2009\/07\/02\/session-level-ash-reports\/","url_meta":{"origin":1402,"position":3},"title":"Session Level ASH Reports","date":"July 2, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.I noticed a post on H.Tongu\u00e7 Y\u0131lmaz's blog about filtering ASH data to look at the actions of a specific instrumented query. There are a few strange things that I was going to comment on but the blog requires me to\u2026","rel":"","context":"With 1 comment","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/18.133.199.212\/wp-content\/uploads\/recovered\/ash_report.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1432,"url":"http:\/\/orcldoug.com\/blog\/2008\/09\/01\/time-matters-db-cpu\/","url_meta":{"origin":1402,"position":4},"title":"Time Matters – DB CPU","date":"September 1, 2008","format":false,"excerpt":"In his comment on the last post, John Beresniewicz mentioned the following property of DB CPU :-\"... 'DB time' fully contains 'DB CPU' and both are measured values (which means in particular that 'DB CPU' is not inflated by runq time)\"I'd like to talk about the end of that sentence\u2026","rel":"","context":"With 24 comments","img":{"alt_text":"","src":"https:\/\/i0.wp.com\/18.133.199.212\/wp-content\/uploads\/recovered\/DBCPU4.png?resize=350%2C200","width":350,"height":200},"classes":[]},{"id":1477,"url":"http:\/\/orcldoug.com\/blog\/2009\/03\/30\/diagnosing-locking-problems-using-ash-part-1\/","url_meta":{"origin":1402,"position":5},"title":"Diagnosing Locking Problems using ASH – Part 1","date":"March 30, 2009","format":false,"excerpt":"Some features in this post require a Diagnostics Pack license.There are few better illustrations of the utility of ASH data than the ability to detect and diagnose locking problems*, even after they've occurred. Because ASH is sampling all active sessions all of the time, you should have information both for\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1402","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1402"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1402\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1402"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1402"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1402"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}