{"id":11396,"date":"2018-07-03T20:19:48","date_gmt":"2018-07-03T18:19:48","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/"},"modified":"2018-07-03T20:19:48","modified_gmt":"2018-07-03T18:19:48","slug":"18c-some-optimization-about-redo-size","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/","title":{"rendered":"18c: some optimization about redo size"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nSome years ago, at the time of 12.1 release, I published in the SOUG newsletter<\/a> some tests to show the amount of redo generated by different operations on a 10000 rows table. I had run it on 12.2 without seeing the differences and now on 18.1
\nI get the statistics from mystat using a script<\/a> that displays them as columns, with the value being the difference from the previous run. I’ve run the same as in the article, and most of the statistics were in the same ballpark.<\/p>\n

12.2<\/h3>\n

In 12.2 I get the same numbers. I was surprised about that because there is an optimization on 12.2 when updating a column to the same value. But this optimization may not occur for all situations. This reduction of redo generation has been analyzed by Maris Elsins with redo log dumps and by Sayan Malakshinov on triggers. And then the origin of this optimization has been exposed by Bryn Llewellyn. All info and links from the following Twitter conversation:
\nhttps:\/\/twitter.com\/MarisDBA\/status\/1011644517416620034<\/p>\n

Here are the numbers I have in 18c, which are very similar to those from 12.1<\/p>\n

\nSQL> --- update with different values (the original ones were all lowercase)\nSQL>\nSQL> update TEST_TABLE set a=upper(a),b=upper(b),c=upper(c),d=upper(d);\n10000 rows updated.\n \nSQL> commit;\nCommit complete.\n \nSQL> @ _mystat_diff.sql\nSQL> set termout off verify off\nSQL> \/\n \ndb block changes        redo size undo change vector size     redo entries\n---------------- ---------------- ----------------------- ----------------\n         190,485       32,226,368              13,288,940           95,258\n \n1 row selected.\n \nSQL>\nSQL> --- update with same valus\nSQL>\nSQL> update TEST_TABLE set a=upper(a),b=upper(b),c=upper(c),d=upper(d);\n10000 rows updated.\n \nSQL> commit;\nCommit complete.\n \nSQL> @ _mystat_diff.sql\nSQL> set termout off verify off\nSQL> \/\n \ndb block changes        redo size undo change vector size     redo entries\n---------------- ---------------- ----------------------- ----------------\n          20,346        4,594,528               1,844,012           10,085\n \n1 row selected.\n<\/code><\/pre>\n
The second update has updated the 4 columns with the same value. For about 1MB of data (10000 rows with avg_row_len=100), we have 1.8MB of undo and 4.4MB of redo (which covers the new data and the undo). I have 4 indexes there but Oracle do not update index entries when the old and new values are the same.<\/p>\n
The first update changes all values, and then, in addition to the changes in the table block, the indexes must be updated.<\/p>\n
So, here, on my test, it seems that the 12.2 optimization, referenced in the tweet above, did not occur because the redo generated for the table blocks is stull full logging when the old and new values are the same. I can check from a block dump that I have the same value in undo and redo:<\/p>\n
\nREDO RECORD - Thread:1 RBA: 0x000008.00002444.0010 LEN: 0x01c8 VLD: 0x01 CON_UID: 1008806272\nSCN: 0x00000000002cb8a4 SUBSCN:3047 07\/03\/2018 12:23:22\nCHANGE #1 CON_ID:4 TYP:0 CLS:36 AFN:14 DBA:0x02405a20 OBJ:4294967295 SCN:0x00000000002cb8a4 SEQ:34 OP:5.1 ENC:0 RBL:0 FLG:0x0000\nktudb redo: siz: 184 spc: 2020 flg: 0x0022 seq: 0x0147 rec: 0x22\n            xid:  0x000a.009.000002bd\nktubu redo: slt: 9 rci: 33 opc: 11.1 objn: 77968 objd: 77978 tsn: 0\nUndo type:  Regular undo       Undo type:  Last buffer split:  No\nTablespace Undo:  No\n             0x00000000\nKDO undo record:\nKTB Redo\nop: 0x02  ver: 0x01\ncompat bit: 4 (post-11) padding: 1\nop: C  uba: 0x02405a20.0147.21\nKDO Op code: URP row dependencies Disabled\n  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x0040a994  hdba: 0x0040a7d8\nitli: 3  ispac: 0  maxfr: 4863\ntabn: 0 slot: 3(0x3) flag: 0x2c lock: 0 ckix: 0\nncol: 4 nnew: 4 size: 0\nVector content:\ncol  0: [24]\n 31 30 30 30 30 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58\ncol  1: [24]\n 31 30 30 30 30 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58\ncol  2: [24]\n 31 30 30 30 30 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58\ncol  3: [24]\n 31 30 30 30 30 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58\nCHANGE #2 CON_ID:4 TYP:0 CLS:1 AFN:12 DBA:0x0040a994 OBJ:77978 SCN:0x00000000002cb8a4 SEQ:3 OP:11.5 ENC:0 RBL:0 FLG:0x0000\nKTB Redo\nop: 0x02  ver: 0x01\ncompat bit: 4 (post-11) padding: 1\nop: C  uba: 0x02405a20.0147.22\nKDO Op code: URP row dependencies Disabled\n  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x0040a994  hdba: 0x0040a7d8\nitli: 3  ispac: 0  maxfr: 4863\ntabn: 0 slot: 3(0x3) flag: 0x2c lock: 3 ckix: 0\nncol: 4 nnew: 4 size: 0\nVector content:\ncol  0: [24]\n 31 30 30 30 30 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58\ncol  1: [24]\n 31 30 30 30 30 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58\ncol  2: [24]\n 31 30 30 30 30 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58\ncol  3: [24]\n 31 30 30 30 30 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58 58\n<\/code><\/pre>\n
The redo record has the old and new values even when they are the same. I hope that we will have more documentation about the 12.2 redo optimization so that it can be predictable and tunable.<\/p>\n
18.1<\/h3>\n
So, I didn’t see the 12.2 optimizations I expected here. However, but it seems that we have one with deletes on 18c.<\/p>\n
In 12.2 the delete of all 10000 rows without index generates 2MB of undo and 3.5MB of redo:<\/p>\n
\nSQL> --- delete all rows\nSQL>\nSQL> delete from TEST_TABLE;\n10000 rows deleted.\n \nSQL> commit;\nCommit complete.\n \nSQL> @ _mystat_diff.sql\nSQL> set termout off verify off\nSQL> \/\n \ndb block changes        redo size undo change vector size     redo entries\n---------------- ---------------- ----------------------- ----------------\n          20,690        3,670,476               2,053,292           10,145\n \n1 row selected.\n<\/code><\/pre>\n
The same in 18.1 generates only 1MB of undo and redo:<\/p>\n
\nSQL> --- delete all rows\nSQL>\nSQL> delete from TEST_TABLE;\n10000 rows deleted.\n \nSQL> commit;\nCommit complete.\n \nSQL> @ _mystat_diff.sql\nSQL> set termout off verify off\nSQL> \/\n \ndb block changes        redo size undo change vector size     redo entries\n---------------- ---------------- ----------------------- ----------------\n             872        1,187,120               1,116,812              293\n \n1 row selected.\n<\/code><\/pre>\n
So in 12.2 I had one block change, and one redo record per row deleted. In 18.1 it seems that I have one redo record per block where all rows are deleted. Still in the same Twitter conversation, Tanel Poder had the idea to do a sparse delete leaving one row in each block:<\/p>\n
\nSQL> delete from TEST_TABLE where dbms_rowid.rowid_row_number(rowid)!=42;\n9849 rows deleted.\n \nSQL> commit;\nCommit complete.\n \nSQL> @ _mystat_diff.sql\nSQL> set termout off verify off\nSQL> \/\n \ndb block changes        redo size undo change vector size     redo entries\n---------------- ---------------- ----------------------- ----------------\n          20,431        3,660,204               2,102,584           10,011\n<\/code><\/pre>\n
Now, as there is no blocks that are fully emptied, I’m back to one redo entry per row deleted.<\/p>\n
Update 4-JUL-18<\/h3>\n
I got a comment from Jonathan Lewis that the delete optimization occurs when using mod() to filter one row per block instead of dbms_rowid. Actually it seems that this optimization is more related to the way the rows are accessed.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . Some years ago, at the time of 12.1 release, I published in the SOUG newsletter some tests to show the amount of redo generated by different operations on a 10000 rows table. I had run it on 12.2 without seeing the differences and now on 18.1 I get the statistics from […]<\/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":[59],"tags":[61,96,874],"type_dbi":[],"class_list":["post-11396","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-18c","tag-oracle","tag-redo"],"acf":[],"yoast_head":"\n18c: some optimization about redo size - 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\/18c-some-optimization-about-redo-size\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"18c: some optimization about redo size\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . Some years ago, at the time of 12.1 release, I published in the SOUG newsletter some tests to show the amount of redo generated by different operations on a 10000 rows table. I had run it on 12.2 without seeing the differences and now on 18.1 I get the statistics from […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-07-03T18:19:48+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=\"5 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\/18c-some-optimization-about-redo-size\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"18c: some optimization about redo size\",\"datePublished\":\"2018-07-03T18:19:48+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/\"},\"wordCount\":525,\"commentCount\":0,\"keywords\":[\"18c\",\"Oracle\",\"redo\"],\"articleSection\":[\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/\",\"name\":\"18c: some optimization about redo size - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2018-07-03T18:19:48+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"18c: some optimization about redo size\"}]},{\"@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":"18c: some optimization about redo size - 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\/18c-some-optimization-about-redo-size\/","og_locale":"en_US","og_type":"article","og_title":"18c: some optimization about redo size","og_description":"By Franck Pachot . Some years ago, at the time of 12.1 release, I published in the SOUG newsletter some tests to show the amount of redo generated by different operations on a 10000 rows table. I had run it on 12.2 without seeing the differences and now on 18.1 I get the statistics from […]","og_url":"https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/","og_site_name":"dbi Blog","article_published_time":"2018-07-03T18:19:48+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"18c: some optimization about redo size","datePublished":"2018-07-03T18:19:48+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/"},"wordCount":525,"commentCount":0,"keywords":["18c","Oracle","redo"],"articleSection":["Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/","url":"https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/","name":"18c: some optimization about redo size - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-07-03T18:19:48+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/18c-some-optimization-about-redo-size\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"18c: some optimization about redo size"}]},{"@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\/11396","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=11396"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11396\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11396"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11396"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11396"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11396"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}