Tuesday, June 30, 2009

INSERT/UPDATE/DELETE will generate redo log on NOLOGGING mode ?

After I read about Redo & Undo in Expert Oracle Database Architecture book by Kyte, He told about NOLOGGING...

Nologging doesn't mean all operations in that object will not generate redo log.

And Francisco Munoz A 's Paper told ... Nologging will generate a minimal number of redo log entries in order to protect the data dictionary.

That make my curious about nologging mode with DML. I use it on some tables and some indexes... So I should know a real thing about it by myself.

Francisco Munoz A 's Paper and Oracle Docs Idea ... INSERT/UPDATE/DELETE will generate a real redo with table/index NOLOGGING mode, except INSERT /*+APPEND+/

Begin test... NOLOGGING + DML on 11g Archivelog Mode.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled

Script:

--new.sql
column OLD_VALUE new_value OLD_VALUE
select value OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';


--diff.sql
select (value - &OLD_VALUE) OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';


- Create TABLE -

SQL> @begin

OLD_VALUE
----------
0

SQL> create table T_NOLOG nologging as select * from all_objects;

Table created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 0) OLD_VALUE

OLD_VALUE
----------
133120

REDO SIZE=133120

SQL> @begin

OLD_VALUE
----------
133120

SQL> create table T_LOG logging as select * from all_objects;

Table created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 133120) OLD_VALUE

OLD_VALUE
----------
8748596

REDO SIZE=8748596

Create table with NOLOGGING... not generate redo log (just generate redo log for data dictionary)
After create table... It's time to test with DML:

- DELETE -

SQL> @begin

OLD_VALUE
----------
8881716

SQL> DELETE FROM T_NOLOG ;

70999 rows deleted.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 8881716) OLD_VALUE

OLD_VALUE
----------
27076168

REDO SIZE=27076168

SQL> @begin

OLD_VALUE
----------
35958052

SQL> DELETE FROM T_LOG;

71000 rows deleted.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 35958052) OLD_VALUE

OLD_VALUE
----------
27076692

REDO SIZE=27076692

- INSERT -

SQL> @begin

OLD_VALUE
----------
63034912

SQL> INSERT INTO T_NOLOG SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 63034912) OLD_VALUE

OLD_VALUE
----------
8493412

REDO SIZE=8493412

SQL> @begin

OLD_VALUE
----------
71528324

SQL>INSERT INTO T_LOG SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 71528324) OLD_VALUE

OLD_VALUE
----------
8493360

REDO SIZE=8493360

- UPDATE -

SQL> @begin

OLD_VALUE
----------
80021684

SQL> UPDATE T_NOLOG SET OBJECT_ID=1;

71000 rows updated.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 80021684) OLD_VALUE

OLD_VALUE
----------
24671048

REDO SIZE=24671048

SQL> @begin

OLD_VALUE
----------
104692732

SQL> UPDATE T_LOG SET OBJECT_ID=1;

71000 rows updated.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 104692732) OLD_VALUE

OLD_VALUE
----------
20911424

REDO SIZE=20911424

On DML INSERT/UPDATE/DELETE ... Oracle generated redo log on nologging mode not difference... on logging mode.
And I need to know about it on INSERT /*+ APPEND */:

- INSERT "APPEND" hints -


- table NOLOGGING mode and not use APPEND hints

SQL> @begin

OLD_VALUE
----------
125604156

SQL> INSERT INTO T_NOLOG SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 125604156) OLD_VALUE

OLD_VALUE
----------
8586036

REDO SIZE=8586036

SQL> @begin

OLD_VALUE
----------
142830588

SQL> INSERT /*+ APPEND */ INTO T_NOLOG SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 142830588) OLD_VALUE

OLD_VALUE
----------
29448

REDO SIZE=29448


- table LOGGING mode, and use APPEND hints

SQL> @begin

OLD_VALUE
----------
134190192

SQL> INSERT /*+ APPEND */ INTO T_LOG SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 134190192) OLD_VALUE

OLD_VALUE
----------
8640396

REDO SIZE=8640396

*** make table logging to nologging ***


SQL> alter table t_log nologging ;

Table altered.

SQL> @begin

OLD_VALUE
----------
142874676

SQL> INSERT /*+ APPEND */ INTO T_LOG SELECT * FROM ALL_OBJECTS;

71000 rows created.

SQL> @diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 142874676) OLD_VALUE

OLD_VALUE
----------
27956

REDO SIZE=27956
APPEND hints on table "LOGGING" mode "select logging from dba_tables" (NO) ... not difference (generate redo)

If "alter table nologging" before, and then insert (append)
So, Oracle will generate a minimal number of redo log... with INSERT /*+ APPEND */ when table be nologging ... select logging from dba_tables (NO)
That just testing... But helpful to understand something ;)

3 comments:

Tonguç said...

hi, you may also check out this one for indexes' additional cost even tables are nologging - http://tonguc.wordpress.com/2007/01/20/direct-path-inserts-nologging-option-and-index-cost/

Surachart said...

thank you ... good idea and good example ;)

Susan Graham said...

Beautifully done! Thanks for sharing how you did it. So lovely I'm sure your guest will love it. My personal favorite is the addition of the ornaments. Thanks for posting.