{"id":4245,"date":"2014-12-15T13:38:00","date_gmt":"2014-12-15T12:38:00","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/"},"modified":"2014-12-15T13:38:00","modified_gmt":"2014-12-15T12:38:00","slug":"can-we-disable-logging-for-dml","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/","title":{"rendered":"Oracle 12c: Can we disable logging for DML?"},"content":{"rendered":"<h2>By Franck Pachot<\/h2>\n<p>.<br \/>\nIf we don&#8217;t mind about loosing our changes, then can we disable logging for DML? This is a question I&#8217;ve heard a lot. Ok, you don&#8217;t need to recover your changes but Oracle may want to recover the consistency of its datafiles anyway. And that&#8217;s why datafiles blocks changed though the buffer cache always generate redo.<\/p>\n<p>But yes, in 12c you can do DML and generate only minimal redo. All DML: even updates and deletes. And that post is not about underscore parameters that allows corruption.<\/p>\n<p>Here is the idea: all changes to datafiles done through the buffer cache must generate redo. So we will:<\/p>\n<ul>\n<li>Do our DML only in a Global Temporary Tables, which are in tempfiles &#8211; not protected by redo<\/li>\n<li>Copy the data to our permanent table with direct path operation only, which can be done with NOLOGGING<\/li>\n<\/ul>\n<div>However, that is appropriate only when you have exclusive access to the table for the operation. Think of it as an ETL step that must do some updates for example. It&#8217;s not ACID. ACID needs undo for the ACI and redo for the D , but here we want to avoid redo and undo as much as possible.<\/div>\n<h3>The test case<\/h3>\n<p>Here is our table with its indexes:<\/p>\n<pre><code>SQL&gt; create table DEMO_PER (id constraint DEMO_PER_PK primary key,n) as select rownum n , 0 from (select * from dual connect by level \u2264 1000),(select * from dual connect by level  \u2264 1000)\n\nSQL&gt; create index DEMO_PER_IX on DEMO_PER(n)\nIndex DEMO_PER_IX created.\n<\/code><\/pre>\n<p>The data is about 44MB of redo:<\/p>\n<pre><code>SQL&gt; select name,value from v$mystat join v$statname using(statistic#) where name like 'redo size' and value&gt;0\n\nNAME                      VALUE\n-------------------- ----------\nredo size              46178212\n<\/code><\/pre>\n<p>And I save it&#8217;s DDL in order to check later than it has not changed during the operation:<\/p>\n<pre><code>SQL&gt; spool ddl1.txt\nSQL&gt; ddl DEMO_PER\n\n  CREATE TABLE \"DEMO\".\"DEMO_PER\"\n   (    \"ID\" NUMBER,\n        \"N\" NUMBER,\n         CONSTRAINT \"DEMO_PER_PK\" PRIMARY KEY (\"ID\")\n  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS\n  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645\n  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1\n  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)\n  TABLESPACE \"USERS\"  ENABLE\n   ) SEGMENT CREATION IMMEDIATE\n  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255\n NOCOMPRESS LOGGING\n  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645\n  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1\n  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)\n  TABLESPACE \"USERS\"\n\n  CREATE INDEX \"DEMO\".\"DEMO_PER_IX\" ON \"DEMO\".\"DEMO_PER\" (\"N\")\n  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS\n  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645\n  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1\n  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)\n  TABLESPACE \"USERS\"\nSQL&gt; spool off\n<\/code><\/pre>\n<p>If you wonder what is that &#8216;ddl&#8217; command, I&#8217;m just enjoying <a href=\"http:\/\/krisrice.blogspot.ch\/2014\/12\/what-is-sdsql.html\">sdsql<\/a>.<\/p>\n<h3>GTT<\/h3>\n<p>I create a GTT with same structure that will be used for my updates:<\/p>\n<pre><code>SQL&gt; create global temporary table DEMO_TMP on commit preserve rows as select * from DEMO_PER where rownum=0\nGlobal temporary TABLE created.\n\nSQL&gt; create index DEMO_TMP_IX on DEMO_TMP (id,n)\nIndex DEMO_TMP_IX created.\n<\/code><\/pre>\n<p>&nbsp;<\/p>\n<h3>Copy all needed data in the GTT and do our DML on it<\/h3>\n<p>I use a direct-path insert that generates no undo:<\/p>\n<pre><code>SQL&gt; insert \/*+ append *\/ into DEMO_TMP select * from DEMO_PER\n1,000,000 rows inserted.\nSQL&gt; commit\ncommitted.\n<\/code><\/pre>\n<p>Then, and this is where I need to be in 12c, I can do any DML on my GTT without generating any redo (because undo is generated into the tempfiles instead of the undo tablespace):<\/p>\n<pre><code>SQL&gt; alter session set temp_undo_enabled=true\nSession altered.\n<\/code><\/pre>\n<p>And then I&#8217;ll do a dome DML that yould have generated hundred of MB if there were done on the permanent table:<\/p>\n<pre><code>SQL&gt; insert into DEMO_TMP select id , 1 from DEMO_TMP\n1,000,000 rows inserted.\n\nSQL&gt; delete from DEMO_TMP where n=0\n1,000,000 rows deleted.\n\nSQL&gt; update DEMO_TMP set id=id+1, n=n+1\n1,000,000 rows updated.\n<\/code><\/pre>\n<p>&nbsp;<\/p>\n<h3>Copy the changed data to the permanent table<\/h3>\n<p>In order to generate no redo we need to set the table in nologging and insert in direct-path, and avoid all index maintenance.<br \/>\nFirst we truncate the table:<\/p>\n<pre><code>SQL&gt; truncate table DEMO_PER\nTable DEMO_PER truncated.\n<\/code><\/pre>\n<p>and put table and indexes in NOLOGGING:<\/p>\n<pre><code>SQL&gt; alter table DEMO_PER nologging\nTable DEMO_PER altered.\n\nSQL&gt; alter index DEMO_PER_IX nologging\nIndex DEMO_PER_IX altered.\n<\/code><\/pre>\n<p>and disable indexes (the primary key has to be disabled and here the index created by the constraint is dropped when disabling it):<\/p>\n<pre><code>SQL&gt; alter index DEMO_PER_IX unusable\nIndex DEMO_PER_IX altered.\n\nSQL&gt; alter table DEMO_PER disable constraint DEMO_PER_PK\nTable DEMO_PER altered.\n<\/code><\/pre>\n<p>then we insert our data from the GTT:<\/p>\n<pre><code>SQL&gt; insert \/*+ append *\/ into DEMO_PER select * from DEMO_TMP\n1,000,000 rows inserted.\n<\/code><\/pre>\n<p>And then we need to recreate indexes:<\/p>\n<pre><code>SQL&gt; alter table DEMO_PER logging\nTable DEMO_PER altered.\n<\/code><\/pre>\n<p>The indexes must be created in nologging (even for the one created with the primary key):<\/p>\n<pre><code>SQL&gt; alter table DEMO_PER modify constraint DEMO_PER_PK using index nologging\nTable DEMO_PER altered.\n\nSQL&gt; alter table DEMO_PER enable constraint DEMO_PER_PK\nTable DEMO_PER altered.\n\nSQL&gt; alter index DEMO_PER_IX rebuild nologging\nIndex DEMO_PER_IX altered.\n<\/code><\/pre>\n<p>Finally, we must put back table and indexes in LOGGING:<\/p>\n<pre><code>SQL&gt; alter index DEMO_PER_PK logging\nIndex DEMO_PER_PK altered.\n\nSQL&gt; alter index DEMO_PER_IX logging\nIndex DEMO_PER_IX altered.\n\nSQL&gt; alter table DEMO_PER logging\nTable DEMO_PER altered.\n<\/code><\/pre>\n<h3>How much redo?<\/h3>\n<pre><code>Command=redosize\nSQL&gt; redosize\nSQL&gt; select name,value from v$mystat join v$statname using(statistic#) where name like 'redo size' and value&gt;0\n\nNAME                      VALUE\n-------------------- ----------\nredo size               8175384\n<\/code><\/pre>\n<p>this is less than 1MB.<\/p>\n<h3>Checking DDL<\/h3>\n<p>As I&#8217;m in sdsql I generate the DDL with the &#8216;ddl&#8217; command and compare it with &#8216;diff&#8217; to the initial one:<\/p>\n<pre><code>SQL&gt; spool ddl2.txt\nSQL&gt; ddl DEMO_PER\nSQL&gt; spool off\nSQL&gt; host diff ddl1.txt ddl2.txt\n<\/code><\/pre>\n<p>No difference \ud83d\ude42<\/p>\n<h3>Conclusion<\/h3>\n<p>We can avoid most of the redo even for DML operations. However, this is only for offline jobs and it&#8217;s always to implement the modifications in the &#8216;insert \/*+ append *\/&#8217; statement rather than doing updates and deletes. This is specific to 12c when we can avoid redo generated to protect undo. This is not the default as it has been introduced by Oracle to be able to update GTT in an Active Data Guard configuration, but we can use it on the primary. GGT don&#8217;t need recovery, so they don&#8217;t need redo.<\/p>\n<p>Of course, you need to backup at the end. Look at the <a href=\"\/the-consequences-of-nologging-in-oracle\">consequences of NOLOGGING<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>By Franck Pachot . If we don&#8217;t mind about loosing our changes, then can we disable logging for DML? This is a question I&#8217;ve heard a lot. Ok, you don&#8217;t need to recover your changes but Oracle may want to recover the consistency of its datafiles anyway. And that&#8217;s why datafiles blocks changed though the [&hellip;]<\/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":[198,59],"tags":[221,96,209],"type_dbi":[],"class_list":["post-4245","post","type-post","status-publish","format-standard","hentry","category-database-management","category-oracle","tag-data-guard","tag-oracle","tag-oracle-12c"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Oracle 12c: Can we disable logging for DML? - dbi Blog<\/title>\n<meta name=\"description\" content=\"How to achieve NOLOGGING in 12c\" \/>\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\/can-we-disable-logging-for-dml\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle 12c: Can we disable logging for DML?\" \/>\n<meta property=\"og:description\" content=\"How to achieve NOLOGGING in 12c\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2014-12-15T12:38: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=\"6 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\/can-we-disable-logging-for-dml\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle 12c: Can we disable logging for DML?\",\"datePublished\":\"2014-12-15T12:38:00+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/\"},\"wordCount\":611,\"commentCount\":0,\"keywords\":[\"Data Guard\",\"Oracle\",\"Oracle 12c\"],\"articleSection\":[\"Database management\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/\",\"name\":\"Oracle 12c: Can we disable logging for DML? - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2014-12-15T12:38:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee\"},\"description\":\"How to achieve NOLOGGING in 12c\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle 12c: Can we disable logging for DML?\"}]},{\"@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 12c: Can we disable logging for DML? - dbi Blog","description":"How to achieve NOLOGGING in 12c","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\/can-we-disable-logging-for-dml\/","og_locale":"en_US","og_type":"article","og_title":"Oracle 12c: Can we disable logging for DML?","og_description":"How to achieve NOLOGGING in 12c","og_url":"https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/","og_site_name":"dbi Blog","article_published_time":"2014-12-15T12:38:00+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle 12c: Can we disable logging for DML?","datePublished":"2014-12-15T12:38:00+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/"},"wordCount":611,"commentCount":0,"keywords":["Data Guard","Oracle","Oracle 12c"],"articleSection":["Database management","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/","url":"https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/","name":"Oracle 12c: Can we disable logging for DML? - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2014-12-15T12:38:00+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"description":"How to achieve NOLOGGING in 12c","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/can-we-disable-logging-for-dml\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle 12c: Can we disable logging for DML?"}]},{"@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\/4245","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=4245"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4245\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4245"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4245"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4245"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=4245"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}