This was first published on https://blog.dbi-services.com/can-we-disable-logging-for-dml (2014-12-15)
Republishing here for new followers. The content is related to the the versions available at the publication date
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:
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.
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.
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.
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.
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.
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
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