.
\nWhen an Oracle Database spends a high percentage of its DB time in User I\/O, I usually check the wait event histograms in order to see if the storage system is working well. But today, with storage going to SSD, most I\/O are less than 1 milliseconds and we have no details about those wait times.<\/p>\n
Here is what is exposed by V$WAIT_EVENT_HISTOGRAM:<\/p>\n
<\/p>\n
select event,wait_time_milli,wait_count from v$event_histogram where event like 'db file sequential read' order by event,wait_time_milli;\nEVENT WAIT_TIME_MILLI WAIT_COUNT\n------------------------------ --------------- ----------\ndb file sequential read 1 27140\ndb file sequential read 2 6\ndb file sequential read 4 1\n<\/code><\/pre>\n <\/p>\n
The latest Oracle 12c patchset, 12.1.0.2, besides changing the future of the database world with the In-Memory option, comes with a small new feature that helps us in our day-to-day tasks: the introduction of the V$EVENT_HISTOGRAM_MICRO view:<\/p>\n
<\/p>\n
select event,wait_time_micro,wait_count,wait_time_format from v$event_histogram_micro where event like 'db file sequential read' order by event,wait_time_micro;\nEVENT WAIT_TIME_MICRO WAIT_COUNT WAIT_TIME_FORMAT\n------------------------------ --------------- ---------- ----------------\ndb file sequential read 1 120 1 microsecond\ndb file sequential read 2 24 2 microseconds\ndb file sequential read 4 51 4 microseconds\ndb file sequential read 8 212 8 microseconds\ndb file sequential read 16 19600 16 microseconds\ndb file sequential read 32 5958 32 microseconds\ndb file sequential read 64 550 64 microseconds\ndb file sequential read 128 492 128 microseconds\ndb file sequential read 256 98 256 microseconds\ndb file sequential read 512 14 512 microseconds\ndb file sequential read 1024 21 1 millisecond\ndb file sequential read 2048 6 2 milliseconds\ndb file sequential read 4096 1 4 milliseconds\n<\/code><\/pre>\n <\/p>\n
Here it is: the wait event are detailed up to microseconds. It’s good for I\/O when on SSD. It’s good for In-Memory events as well.<\/p>\n
Unfortunately, this has not been yet introduced in the AWR reports (I made an enhancement request for that).<\/p>\n
Now, if you wonder which disk I’m using to get the microsecond i\/o above…<\/p>\n
…<\/p>\n
…<\/p>\n
…<\/p>\n
…<\/p>\n
…<\/p>\n
…<\/p>\n
…<\/p>\n
…<\/p>\n
…<\/p>\n
…<\/p>\n
…<\/p>\n
…<\/p>\n
…<\/p>\n
…<\/p>\n
…<\/p>\n
…<\/p>\n
… here is how I created that database:<\/p>\n
<\/p>\n
mkdir -p \/mnt\/ramdisk\nmount -t tmpfs -o size=8G tmpfs \/mnt\/ramdisk\n$ORACLE_HOME\/bin\/dbca -createDatabase -templateName General_Purpose.dbc -gdbName RAMDB -sid RAMDB -sysPassword oracle -systemPassword oracle -storageType FS -characterSet AL32UTF8 -listeners LISTENER -sampleSchema true -totalMemory 600 -databaseType MULTIPURPOSE -silent -datafileDestination \/mnt\/ramdisk\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"By Franck Pachot . When an Oracle Database spends a high percentage of its DB time in User I\/O, I usually check the wait event histograms in order to see if the storage system is working well. But today, with storage going to SSD, most I\/O are less than 1 milliseconds and we have no […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,59],"tags":[224,222,96,209,230],"type_dbi":[],"class_list":["post-2172","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-oracle","tag-12-1-0-2","tag-in-memory","tag-oracle","tag-oracle-12c","tag-wait-event"],"acf":[],"yoast_head":"\n
Oracle 12.1.0.2: Wait event histograms in \u03bcs - dbi Blog<\/title>\n<meta name=\"description\" content=\"Oracle wait event histograms in microseconds: Oracle 12.1.0.2 comes with a small new feature that helps us in our day-to-day tasks - the V$EVENT_HISTOGRAM_MICRO view.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle 12.1.0.2: Wait event histograms in \u03bcs\" \/>\n<meta property=\"og:description\" content=\"Oracle wait event histograms in microseconds: Oracle 12.1.0.2 comes with a small new feature that helps us in our day-to-day tasks - the V$EVENT_HISTOGRAM_MICRO view.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2014-08-04T10:01:00+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle 12.1.0.2: Wait event histograms in \u03bcs\",\"datePublished\":\"2014-08-04T10:01:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/\"},\"wordCount\":189,\"commentCount\":0,\"keywords\":[\"12.1.0.2\",\"In-memory\",\"Oracle\",\"Oracle 12c\",\"wait event\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/\",\"name\":\"Oracle 12.1.0.2: Wait event histograms in \u03bcs - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2014-08-04T10:01:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"Oracle wait event histograms in microseconds: Oracle 12.1.0.2 comes with a small new feature that helps us in our day-to-day tasks - the V$EVENT_HISTOGRAM_MICRO view.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle 12.1.0.2: Wait event histograms in \u03bcs\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle 12.1.0.2: Wait event histograms in \u03bcs - dbi Blog","description":"Oracle wait event histograms in microseconds: Oracle 12.1.0.2 comes with a small new feature that helps us in our day-to-day tasks - the V$EVENT_HISTOGRAM_MICRO view.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/","og_locale":"en_US","og_type":"article","og_title":"Oracle 12.1.0.2: Wait event histograms in \u03bcs","og_description":"Oracle wait event histograms in microseconds: Oracle 12.1.0.2 comes with a small new feature that helps us in our day-to-day tasks - the V$EVENT_HISTOGRAM_MICRO view.","og_url":"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/","og_site_name":"dbi Blog","article_published_time":"2014-08-04T10:01:00+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle 12.1.0.2: Wait event histograms in \u03bcs","datePublished":"2014-08-04T10:01:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/"},"wordCount":189,"commentCount":0,"keywords":["12.1.0.2","In-memory","Oracle","Oracle 12c","wait event"],"articleSection":["Database Administration & Monitoring","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/","url":"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/","name":"Oracle 12.1.0.2: Wait event histograms in \u03bcs - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2014-08-04T10:01:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"Oracle wait event histograms in microseconds: Oracle 12.1.0.2 comes with a small new feature that helps us in our day-to-day tasks - the V$EVENT_HISTOGRAM_MICRO view.","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/12102-wait-event-histograms-in-s\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle 12.1.0.2: Wait event histograms in \u03bcs"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2172","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=2172"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/2172\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=2172"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=2172"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=2172"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=2172"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}