Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Manjunath.

Asked: February 25, 2020 - 6:39 pm UTC

Answered by: Connor McDonald - Last updated: March 03, 2020 - 6:27 am UTC

Category: SQL - Version: 18c

Viewed 100+ times

You Asked

I was trying to understand how redo works in nologging mode. Here is a interesting situation i encountered in XE.

SQL> @redo_question.sql

Table dropped.


Table created.


LOG_MODE
------------
NOARCHIVELOG


Statistics
----------------------------------------------------------
        437  recursive calls
          6  db block gets
        912  consistent gets
         18  physical reads
        340  redo size
        852  bytes sent via SQL*Net to client
       1052  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
        112  sorts (memory)
          0  sorts (disk)
          9  rows processed


Statistics
----------------------------------------------------------
         44  recursive calls
         26  db block gets
         83  consistent gets
          0  physical reads
          0  redo size
        870  bytes sent via SQL*Net to client
       1039  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          9  rows processed


Here is the script i used to generate the above. As you can see, i was not expecting the redo log as i acreate a nologging table and append hint. But, i see the redo blocks. At the same time when i create table in normal mode and do not use append hint, there is zero redo generated. What am I missing?

set  autotrace off
set timing off
set linesize 2000
drop table t1;

create table t1 nologging as select object_name,object_id,owner from all_objects where object_id<10;
/*
alter table t1 add constraint t1_pk primary key(object_name);

create index t1_idx on t1(object_id);

begin
for i in (select constraint_name, table_name from user_constraints where table_name='T1') LOOP
execute immediate 'alter table '||i.table_name||' disable constraint '||i.constraint_name||'';
end loop;
end;
/
begin
for i in (select index_name from user_indexes where table_name='T1') LOOP
execute immediate 'alter index '||i.index_name||' unusable';
end loop;
end;
/
*/
select log_mode from v$database;
set define off
set feedback off
set autotrace traceonly statistics
insert/*+ append */ into t1 select object_name,object_id,owner from all_objects where object_id>10 and object_id<20;
commit;

set autotrace off

drop table t1;
create table t1 logging as select object_name,object_id,owner from all_objects where object_id<10;
alter table t1 add constraint t1_pk primary key(object_name);
create index t1_idx on t1(object_id);
set autotrace traceonly statistics
insert into t1 select object_name,object_id,owner from all_objects where object_id>10 and object_id<20;
commit;


and we said...

I've made a slight adjustment to your script to demonstrate the true difference

SQL> set  autotrace off
SQL> set timing off
SQL> set linesize 2000
SQL> drop table t1;

Table dropped.

SQL>
SQL> create table t1 nologging as select object_name,object_id,owner from all_objects where object_id<10;

Table created.

SQL>
SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

1 row selected.

SQL> set define off
SQL> set feedback off
SQL> set autotrace traceonly statistics
SQL> insert/*+ append */ into t1 select object_name,object_id,owner from all_objects;

Statistics
----------------------------------------------------------
        252  recursive calls
       1070  db block gets
      91776  consistent gets
          0  physical reads
      38788  redo size
        848  bytes sent via SQL*Net to client
       1000  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
        840  sorts (memory)
          0  sorts (disk)
      80907  rows processed

SQL> commit;
SQL>
SQL> set autotrace off
SQL>
SQL> drop table t1;
SQL> create table t1 logging as select object_name,object_id,owner from all_objects where object_id<10;
SQL> create index t1_idx on t1(object_id);
SQL> set autotrace traceonly statistics
SQL> insert into t1 select object_name,object_id,owner from all_objects;

Statistics
----------------------------------------------------------
        179  recursive calls
      13298  db block gets
      94490  consistent gets
          1  physical reads
   12740264  redo size
        864  bytes sent via SQL*Net to client
        987  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
        840  sorts (memory)
          0  sorts (disk)
      80908  rows processed

SQL> commit;
SQL>
SQL>


So insert-append = 34k redo, normal insert = 127meg of redo !

A direct mode operation in nologging mode means we do not have log redo for the *data* that you are inserting. However, we still need to log redo for anything that happens in the dictionary because of this insert. For example, we might allocate some extents, we might need to adjust your remaining quota for the tablespace etc etc etc. These *dictionary* operations must be logged.

In your "no redo was used" example, because we created so little data, we didn't need to do dictionary work.

and you rated our response

  (2 ratings)

Reviews

Thanks!

February 26, 2020 - 9:59 am UTC

Reviewer: Manjunath

Thanks Connor for quick response! So this means such insert append operations are only useful when one is loading large amount of data. Other than bench marking ourselves, is there any other way to find out weather to use direct path or normal loading given my table and the amount of data one is loading?
Connor McDonald

Followup  

March 03, 2020 - 6:27 am UTC

Like any and every feature - Cost vs Benefit.

I used your question to explore this a little further in my Office Hours session last week. Here's the video



which talks about the pros and cons.

February 27, 2020 - 11:09 pm UTC

Reviewer: A reader

does any sub-sequent DML on nologging table produce redo?

1) create table t1 nologging as select object_name,object_id,owner from all_objects where object_id<10;

2) insert into t1 select object_name,object_id,owner from all_objects where object_id<10;

my question is does 2 produce redo?

thanks
Chris Saxon

Followup  

February 28, 2020 - 10:21 am UTC

The NOLOGGING clause also specifies that subsequent direct loads using SQL*Loader and direct load INSERT operations are not logged. Subsequent DML statements (UPDATE, DELETE, and conventional path insert) are unaffected by the NOLOGGING attribute of the table and generate redo.

https://docs.oracle.com/en/database/oracle/oracle-database/20/admin/managing-tables.html#GUID-E9EB5D85-49D8-4A29-9DF3-A7CBBA484EE3

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database