Oracle 12c: Can we disable logging for DML?

By Franck Pachot

.
If we don’t mind about loosing our changes, then can we disable logging for DML? This is a question I’ve heard a lot. Ok, you don’t need to recover your changes but Oracle may want to recover the consistency of its datafiles anyway. And that’s why datafiles blocks changed though the buffer cache always generate redo.

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.

Here is the idea: all changes to datafiles done through the buffer cache must generate redo. So we will:

• Do our DML only in a Global Temporary Tables, which are in tempfiles – not protected by redo
• Copy the data to our permanent table with direct path operation only, which can be done with NOLOGGING

The test case

Here is our table with its indexes:

SQL> create table DEMO_PER (id constraint DEMO_PER_PK primary key,n) as select rownum n , 0 from (select * from dual connect by level ≤ 1000),(select * from dual connect by level  ≤ 1000)

SQL> create index DEMO_PER_IX on DEMO_PER(n)
Index DEMO_PER_IX created.

The data is about 44MB of redo:

SQL> select name,value from v$mystat join v$statname using(statistic#) where name like 'redo size' and value>0

NAME                      VALUE
-------------------- ----------
redo size              46178212

And I save it’s DDL in order to check later than it has not changed during the operation:

SQL> spool ddl1.txt
SQL> ddl DEMO_PER

  CREATE TABLE "DEMO"."DEMO_PER"
   (    "ID" NUMBER,
        "N" NUMBER,
         CONSTRAINT "DEMO_PER_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

  CREATE INDEX "DEMO"."DEMO_PER_IX" ON "DEMO"."DEMO_PER" ("N")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
SQL> spool off

If you wonder what is that ‘ddl’ command, I’m just enjoying sdsql.

GTT

I create a GTT with same structure that will be used for my updates:

SQL> create global temporary table DEMO_TMP on commit preserve rows as select * from DEMO_PER where rownum=0
Global temporary TABLE created.

SQL> create index DEMO_TMP_IX on DEMO_TMP (id,n)
Index DEMO_TMP_IX created.

Copy all needed data in the GTT and do our DML on it

I use a direct-path insert that generates no undo:

SQL> insert /*+ append */ into DEMO_TMP select * from DEMO_PER
1,000,000 rows inserted.
SQL> commit
committed.

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):

SQL> alter session set temp_undo_enabled=true
Session altered.

And then I’ll do a dome DML that yould have generated hundred of MB if there were done on the permanent table:

SQL> insert into DEMO_TMP select id , 1 from DEMO_TMP
1,000,000 rows inserted.

SQL> delete from DEMO_TMP where n=0
1,000,000 rows deleted.

SQL> update DEMO_TMP set id=id+1, n=n+1
1,000,000 rows updated.

Copy the changed data to the permanent table

In order to generate no redo we need to set the table in nologging and insert in direct-path, and avoid all index maintenance.
First we truncate the table:

SQL> truncate table DEMO_PER
Table DEMO_PER truncated.

and put table and indexes in NOLOGGING:

SQL> alter table DEMO_PER nologging
Table DEMO_PER altered.

SQL> alter index DEMO_PER_IX nologging
Index DEMO_PER_IX altered.

and disable indexes (the primary key has to be disabled and here the index created by the constraint is dropped when disabling it):

SQL> alter index DEMO_PER_IX unusable
Index DEMO_PER_IX altered.

SQL> alter table DEMO_PER disable constraint DEMO_PER_PK
Table DEMO_PER altered.

then we insert our data from the GTT:

SQL> insert /*+ append */ into DEMO_PER select * from DEMO_TMP
1,000,000 rows inserted.

And then we need to recreate indexes:

SQL> alter table DEMO_PER logging
Table DEMO_PER altered.

The indexes must be created in nologging (even for the one created with the primary key):

SQL> alter table DEMO_PER modify constraint DEMO_PER_PK using index nologging
Table DEMO_PER altered.

SQL> alter table DEMO_PER enable constraint DEMO_PER_PK
Table DEMO_PER altered.

SQL> alter index DEMO_PER_IX rebuild nologging
Index DEMO_PER_IX altered.

Finally, we must put back table and indexes in LOGGING:

SQL> alter index DEMO_PER_PK logging
Index DEMO_PER_PK altered.

SQL> alter index DEMO_PER_IX logging
Index DEMO_PER_IX altered.

SQL> alter table DEMO_PER logging
Table DEMO_PER altered.

How much redo?

Command=redosize
SQL> redosize
SQL> select name,value from v$mystat join v$statname using(statistic#) where name like 'redo size' and value>0

NAME                      VALUE
-------------------- ----------
redo size               8175384

this is less than 1MB.

Checking DDL

As I’m in sdsql I generate the DDL with the ‘ddl’ command and compare it with ‘diff’ to the initial one:

SQL> spool ddl2.txt
SQL> ddl DEMO_PER
SQL> spool off
SQL> host diff ddl1.txt ddl2.txt

No difference 🙂

Conclusion

We can avoid most of the redo even for DML operations. However, this is only for offline jobs and it’s always to implement the modifications in the ‘insert /*+ append */’ 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’t need recovery, so they don’t need redo.

Of course, you need to backup at the end. Look at the consequences of NOLOGGING