Home > Tips > Is redo size affected by nologging indexes?

Is redo size affected by nologging indexes?

Yury recently raised a topic about nologging segments in e-Business Suite database. The discussion later evolved to on how indexes are affected by the nologging setting (is it worth putting indexes in nologging mode to reduce the redo generation). I wasn’t quite sure about the answer, so I decided to test it myself. Read on for results of 22 short testcases (I think the results turned out to be quite interesting and worth knowing).

Shortly about the test, it’s 11.2.0.1 database, 2 tablespaces (nologging and force logging) – created similarilly to those in eBS OATM setup. Redo size for following operations will be tested: insert, insert with append hint, update for one of the columns, index rebuild online. Each of operations will be performed on table with indexes (in different logging/nologging combinations). Let’s start with the results:

I marked red the results where one could have expected a decrease of redo size, and green where the expectations fulfilled.

The thing with the red ones is that the index maintenance during DML operations is always a LOGGING operation, we can see a partial redo size reductin for insert with append hint operations which is explained by the fact that index maintenance is deferred, so in these cases it’s carried out only when the table has been loaded.

If we check the “insert_append” column some relations can be drawn, which means the nologging setting is nicely applied to the table each time the insert with append is done

  • “log_table + no_index” – “nolog_table + no_index” = ~43Mb
  • “log_table + log_index” – “nolog_table + log_index” = ~43Mb
  • “log_table + nolog_index” – “nolog_table + nolog_index” = ~43Mb

The most surprising result here in my opinion is that the index maintenance still generates redo even if redo is not generated for the table. What’s the point of it? It does not help me in case of recovery at all. (The redo generation for the nologging indexes can be avided by setting the index in UNUSABLE state before the DML operation, and by a rebuild followed after the DML – but this has to be specifically coded)

The only way how nologging affects the redo generation for index operations is during rebuild (online or not) and also create index statements (not tested here), those are marked green in the table above along with the loading data into the table without any indexes.

Click on the “show source” below if you want to see what I was actually doing to perform the test, including the tablespace creation, way of checking the redo size, etc.:

SQL>
SQL> SELECT LOG_MODE, FORCE_LOGGING FROM V$DATABASE;

LOG_MODE     FOR
------------ ---
ARCHIVELOG   NO

Elapsed: 00:00:00.02
SQL>
SQL> SET HEAD OFF VERIFY OFF FEED OFF TIMING OFF
SQL>
SQL> alter session set optimizer_dynamic_sampling=0;
SQL>
SQL> DROP TABLESPACE T_LOG_TBS INCLUDING CONTENTS AND DATAFILES;
SQL> DROP TABLESPACE T_NOLOG_TBS INCLUDING CONTENTS AND DATAFILES;
SQL>
SQL> CREATE TABLESPACE T_LOG_TBS DATAFILE SIZE 500M
  2  AUTOEXTEND OFF FORCE LOGGING
  3  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT AUTO;
SQL>
SQL> CREATE TABLESPACE T_NOLOG_TBS
  2  DATAFILE SIZE 500M
  3  AUTOEXTEND OFF NOLOGGING
  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT AUTO;
SQL>
SQL> DROP TABLE TEST_SOURCE;
SQL>
SQL> CREATE TABLE TEST_SOURCE TABLESPACE USERS AS
  2  SELECT * FROM DBA_OBJECTS,
  3    (SELECT rownum rn FROM dual CONNECT BY level<=5
  4    );
SQL>
SQL> SELECT bytes FROM user_segments WHERE segment_name='TEST_SOURCE';

  45088768
SQL>
SQL> col R1 NOPRINT NEW_VALUE R1
SQL> col R2 NOPRINT NEW_VALUE R2
SQL>
SQL>
SQL> DROP table TEST_DEST;
DROP table TEST_DEST
           *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> CREATE table TEST_DEST tablespace T_LOG_TBS as select * from TEST_SOURCE where 1=0;
SQL> insert into TEST_DEST select * from TEST_SOURCE;
SQL>
SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_noappend log_table no_index: '||(&R2-&R1) T1 from dual;

REDO SIZE insert_noappend log_table no_index: 44585428
SQL>
SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert /*+ append */into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_append log_table no_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_append log_table no_index 45005652
SQL>
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> update TEST_DEST set object_name=substr(to_char(length(owner||object_name))||object_name,1,30);
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE update log_table no_index: '||(&R2-&R1) T1 from dual;

REDO SIZE update log_table no_index: 183455584
SQL>
SQL> create index TEST_LOG_IDX on TEST_DEST (owner, object_name, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME) tablespace T_LOG_TBS;
SQL>
SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_noappend log_table log_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_noappend log_table log_index 245471824
SQL>
SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert /*+ append */into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_append log_table log_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_append log_table log_index 159382864
SQL>
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> update TEST_DEST set object_name=substr(to_char(length(owner||object_name))||object_name,1,30);
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE update log_table log_index: '||(&R2-&R1) T1 from dual;

REDO SIZE update log_table log_index: 420821388
SQL>
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> alter index TEST_LOG_IDX rebuild online;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE log_table rebuild_online log_index: '||(&R2-&R1) T1 from dual;

REDO SIZE log_table rebuild_online log_index: 32297556
SQL>
SQL> drop index TEST_LOG_IDX;
SQL> create index TEST_NOLOG_IDX on TEST_DEST (owner, object_name, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME) tablespace T_NOLOG_TBS;
SQL>
SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_noappend log_table nolog_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_noappend log_table nolog_index 245564848
SQL>
SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert /*+ append */into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_append log_table nolog_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_append log_table nolog_index 159186324
SQL>
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> update TEST_DEST set object_name=substr(to_char(length(owner||object_name))||object_name,1,30);
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE update log_table nolog_index: '||(&R2-&R1) T1 from dual;

REDO SIZE update log_table nolog_index: 420795224
SQL>
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> alter index TEST_NOLOG_IDX rebuild online;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE log_table rebuild_online nolog_index: '||(&R2-&R1) T1 from dual;

REDO SIZE log_table rebuild_online nolog_index: 846412
SQL>
SQL> DROP table TEST_DEST;
SQL> CREATE table TEST_DEST tablespace T_NOLOG_TBS as select * from TEST_SOURCE where 1=0;
SQL> insert into TEST_DEST select * from TEST_SOURCE;
SQL>
SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_noappend nolog_table no_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_noappend nolog_table no_index 44606672
SQL>
SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert /*+ append */into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_append nolog_table no_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_append nolog_table no_index 767684
SQL>
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> update TEST_DEST set object_name=substr(to_char(length(owner||object_name))||object_name,1,30);
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE update nolog_table no_index: '||(&R2-&R1) T1 from dual;

REDO SIZE update nolog_table no_index: 188704492
SQL>
SQL> create index TEST_LOG_IDX on TEST_DEST (owner, object_name, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME) tablespace T_LOG_TBS;
SQL>
SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_noappend nolog_table log_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_noappend nolog_table log_index 245396952
SQL>
SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert /*+ append */into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_append nolog_table log_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_append nolog_table log_index 115176588
SQL>
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> update TEST_DEST set object_name=substr(to_char(length(owner||object_name))||object_name,1,30);
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE update nolog_table log_index: '||(&R2-&R1) T1 from dual;

REDO SIZE update nolog_table log_index: 420744288
SQL>
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> alter index TEST_LOG_IDX rebuild online;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE nolog_table rebuild_online log_index: '||(&R2-&R1) T1 from dual;

REDO SIZE nolog_table rebuild_online log_index: 32312396
SQL>
SQL> drop index TEST_LOG_IDX;
SQL> create index TEST_NOLOG_IDX on TEST_DEST (owner, object_name, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME) tablespace T_NOLOG_TBS;
SQL>
SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_noappend nolog_table nolog_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_noappend nolog_table nolog_index 245784828
SQL>
SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert /*+ append */into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_append nolog_table nolog_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_append nolog_table nolog_index 115763064
SQL>
SQL>
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> update TEST_DEST set object_name=substr(to_char(length(owner||object_name))||object_name,1,30);
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE update nolog_table nolog_index: '||(&R2-&R1) T1 from dual;

REDO SIZE update nolog_table nolog_index: 420846428
SQL>
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> alter index TEST_NOLOG_IDX rebuild online;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE nolog_table rebuild_online nolog_index: '||(&R2-&R1) T1 from dual;

REDO SIZE nolog_table rebuild_online nolog_index: 846532

Here are the explanations of the abbreviatures used above:

  • insert_noappend: simple insert-as-select statement into an empty table:
  • insert into TEST_DEST select * from TEST_SOURCE;
    
  • insert_append: insert-as-select statement with append hint into an empty table:
  • insert /*+ append */into TEST_DEST select * from TEST_SOURCE;
    
  • update: update of the 2nd column in the index, update statement is written so that it would update the same rows the same way in each test case:
  • update TEST_DEST set object_name=substr(to_char(length(owner||object_name))||object_name,1,30);
    
  • rebuild_online: rebuild online operation of the index:
  • alter index TEST_LOG_IDX rebuild online;
    alter index TEST_NOLOG_IDX rebuild online;
    
  • log_table: table used for tests created in the FORCE_LOGGING enabled tablespace:
  • CREATE table TEST_DEST tablespace T_LOG_TBS as select * from TEST_SOURCE where 1=0;
    
  • nolog_table: table used for tests created in the NOLOGGING enabled tablespace:
  • CREATE table TEST_DEST tablespace T_NOLOG_TBS as select * from TEST_SOURCE where 1=0;
    
  • no_index: there is no index on the table TEST_DEST
  • log_index: index on TEST_DEST is created in the FORCE_LOGGING enabled tablespace:
  • create index TEST_LOG_IDX on TEST_DEST (owner, object_name, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME) tablespace T_LOG_TBS;
    
  • log_index: index on TEST_DEST is created in the NOLOGGING enabled tablespace:
  • create index TEST_LOG_IDX on TEST_DEST (owner, object_name, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME) tablespace T_NOLOG_TBS;
    
About these ads
Categories: Tips Tags: , , , , ,
  1. Yury Velikanov
    September 13, 2010 at 04:29 | #1

    Hey Maris,

    Nice post. Keep those going. IMHO: The notable thing in you tests is the amount of redo taken by tables (we can save during direct load operations) compared to redo generated by indexes. The ratio is 3-4 (indexes redo) to 1 (table redo). I think this is very typical ration generally in the applications. Indexes redo neglects the savings we do during table direct operation.

    The reason for this behaviour (table no REDO, indexes generates REDO) is quite simple. Please remember the way Oracle does the direct load operations. It allocates am extent above the object’s high water mark, load table’s data there and at the commit time it just adds the extend to the segment.
    In case DMLs again indexes (and IOT btw ;) it isn’t possible. Any particular entry MUST be placed somewhere in between existing entries (existing extents). Therefore Oracle can’t use the same technique are it uses in direct table load case.

    Lets keep the posts going,
    Yury

    • June 16, 2011 at 12:53 | #2

      Yury,
      Thanks for a great comment and insight on why indexes generate redo in the described cases. I think it’s very important for people to understand this, because I’ve used to hear phrases like “do direct data loads with NOLOGGING tables and you won’t generate redo” which is misleading because it’s only partially true (on tables, but not on indexes).
      This is something that has to be kept in mind.
      Maris

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: