FLASHBACK OFF<\/b> 100 0.85 1.08 0 6908 7421 100000\nFLASHBACK ON <\/b> 100 1.02 1.68 502<\/b> 6912 7945 100000\n<\/code><\/pre>\nSo it’s clear. In FLASHBACK ON we read the same number of blocks, but some of them involve a physical read, where it’s not the case when we don’t do flashback logging.<\/p>\n
When Oracle creates new block that overwrite entirely what was stored before into it, then there is no need to read the previous values. The block is directly written in the buffer cache. They are counted as a buffer get, but there is nothing to read – so no disk read at all.<\/p>\n
But when database is in FLASHBACK ON, or has a guaranteed restore point, then all overwritten blocks must be logged into the flashback logs because a flashback database has to write them back. So it must be read before being overwritten.<\/p>\n
That concern blocks from dropped (or truncated) objects. And that concerns also the expired undo blocks that are reused. And in that case, because they expired after a while (undo_retention) there are good chance that they are not anymore in buffer cache. Consequence is disk reads from UNDO tablespace.<\/p>\n
physical reads for flashback new<\/h3>\n
Here are the statistics of the 3 insert cases from previous blog, where I’ve added the ‘physical reads for flashback new’ one:<\/p>\n
NAME VALUE\n--------------------------------- ----------\nphysical reads for flashback new 2924\nredo entries 203086\nredo size 36492404\nundo change vector size 20673192\n \nNAME VALUE\n--------------------------------- ----------\nphysical reads for flashback new 1204\nredo entries 31769\nredo size 9647772\nundo change vector size 6867248\n \nNAME VALUE\n--------------------------------- ----------\nphysical reads for flashback new 506\nredo entries 6506\nredo size 4105060\nundo change vector size 2997752\n<\/code><\/pre>\nWhen we generate 20673192 bytes into 8k blocks, we expect to write at least to 20673192\/8192=2524 block. This matches the 2924 ‘physical reads for flashback new’. And it’s exactly the 2924 ‘disk’ reads that we see in the tkprof above.<\/p>\n
Measuring the impact<\/h3>\n
Now back to the initial issue where I had lot of ‘db file sequential read’ on UNDO (file#=2)<\/p>\n
Here are some statistics from a 1 hour Statspack:<\/p>\n
Top 5 Timed Events Avg %Total\n~~~~~~~~~~~~~~~~~~ wait Call\nEvent Waits Time (s) (ms) Time\n----------------------------------------- ------------ ----------- ------ ------\ndb file sequential read 9,896,083 14,338 1 32.8\n \nStatistic Total per Second per Trans\n--------------------------------- ------------------ -------------- ------------\nphysical reads for flashback new 1,196,359 208.9 14.3\nundo change vector size 7,100,240,724 1,240,000.1 84,960.6\nflashback log write bytes 16,034,947,072 2,800,375.0 191,872.2\nflashback log writes 45,350 7.9 0.5\n<\/code><\/pre>\nSo from that we know that:<\/p>\n
\n- 1,196,359\/9,896,083=12% of ‘db file sequential read’ is for ‘physical reads for flashback new’<\/li>\n
- and 16,034,947,072\/7,100,240,724= half of flashback logging is for undo<\/li>\n<\/ul>\n
This is the overhead of flashback logging. Reducing the undo generated (see previous block) can be a good recommendation. And besides the flashback logging issue, it will reduce the generated redo as well.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . in my previous post I’ve reproduced an issue where some bulk insert into a GTT was generating too much undo. But the real case I encountered was worse: the insert was reading lot of UNDO. And lot of this undo was read from disk. Jonathan Lewis has pointed me to a […]<\/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":[524,96],"type_dbi":[],"class_list":["post-4266","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-oracle","tag-flashback","tag-oracle"],"acf":[],"yoast_head":"\n
Flashback logging overhead: 'db file sequential read' on UNDO tablespace - dbi Blog<\/title>\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\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Flashback logging overhead: 'db file sequential read' on UNDO tablespace\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . in my previous post I’ve reproduced an issue where some bulk insert into a GTT was generating too much undo. But the real case I encountered was worse: the insert was reading lot of UNDO. And lot of this undo was read from disk. Jonathan Lewis has pointed me to a […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-01-09T16:09: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=\"3 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\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Flashback logging overhead: ‘db file sequential read’ on UNDO tablespace\",\"datePublished\":\"2015-01-09T16:09:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/\"},\"wordCount\":447,\"commentCount\":0,\"keywords\":[\"flashback\",\"Oracle\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/\",\"name\":\"Flashback logging overhead: 'db file sequential read' on UNDO tablespace - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-01-09T16:09:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Flashback logging overhead: ‘db file sequential read’ on UNDO tablespace\"}]},{\"@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":"Flashback logging overhead: 'db file sequential read' on UNDO tablespace - dbi Blog","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\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/","og_locale":"en_US","og_type":"article","og_title":"Flashback logging overhead: 'db file sequential read' on UNDO tablespace","og_description":"By Franck Pachot . in my previous post I’ve reproduced an issue where some bulk insert into a GTT was generating too much undo. But the real case I encountered was worse: the insert was reading lot of UNDO. And lot of this undo was read from disk. Jonathan Lewis has pointed me to a […]","og_url":"https:\/\/www.dbi-services.com\/blog\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/","og_site_name":"dbi Blog","article_published_time":"2015-01-09T16:09:00+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Flashback logging overhead: ‘db file sequential read’ on UNDO tablespace","datePublished":"2015-01-09T16:09:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/"},"wordCount":447,"commentCount":0,"keywords":["flashback","Oracle"],"articleSection":["Database Administration & Monitoring","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/","url":"https:\/\/www.dbi-services.com\/blog\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/","name":"Flashback logging overhead: 'db file sequential read' on UNDO tablespace - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-01-09T16:09:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/flashback-logging-overhead-db-file-sequential-read-on-undo-tablespace\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Flashback logging overhead: ‘db file sequential read’ on UNDO tablespace"}]},{"@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\/4266","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=4266"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4266\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4266"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4266"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4266"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4266"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}