NOLOGGING Vs Redo entries.

Title: NOLOGGING Vs Redo entries.
Comments: This document is related to the redo log generation of the scripts in LOGGING mode and the NOLOGGING mode.

Checking the redo generation for table creation:
Ø Checking the record count and the current Redo log size of the session.

SQL> select count(*) from all_objects;



COUNT(*)
----------
27189
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 01 row selected.
Ø Creating the table CTAS (Create Table As Select) table with the NOLOGGING mode. It is not creating the Redo logs for the data.

SQL> create table t NOLOGGING as select * from all_objects;

Table created.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 56732
Ø Creating the table CTAS table with the LOGGING mode. It is creating the Redo logs for the data.

SQL> create table tt as select * from all_objects;
Table created.
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 31768281 row selected.
Ø Creating the table CTAS table with the NOLOGGING mode (only table structure) and INSERT the records. It is creating the Redo logs for the data.

SQL> create table ttt nologging as select * from all_objects where 1=2;
Table created.
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 3212880
1 row selected.

SQL> insert into ttt
2 select * from all_objects;

27192 rows created.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 6257108

1 row selected.
Ø Creating the table CTAS table with the LOGGING mode (only table structure) and INSERT records with /*+ APPEND */ Hint. The Redo logs are not generated for the data.

SQL> create table tttt nologging as select * from all_objects where 1=2;
Table created.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 6293312
1 row selected.
SQL> INSERT /*+ APPEND */ INTO tttt select * from all_objects;
27193 rows created.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 6311384
1 row selected.

Redo Log generation table:
The Below table shows when the log generates.




----------- ------------- --------------- ---------------
Checking the redo generation for indexes:
Ø Drop the Redo_check table.

SQL> drop table redo_check;
Table dropped.

Ø Checks the current redo size of the session.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 25016
Ø Create a table and insert the records.

SQL> create table redo_check (
2 num number,
3 data1 varchar2(20)
4 );

Table created.
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 32460
SQL> declare
2 i int;
3 begin
4 for i in 1 .. 200000
5 loop
6 insert into redo_check values(i,'Redo check ' i);
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 49555748
Ø Create the index with NOLOGGING mode.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 495558601 row selected.
SQL> create index dat_idx on redo_check (data1) nologging;
Index created.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 49633080

1 row selected.
Ø We try to update the index column and check the Redo generation.




SQL> update redo_check set data1 = ' redo check' where num <100001 em="">
Ø Yes. The updates are generating the Redo logs for the table entry and index if we create a table in the NOLOGGING mode also.

SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 127536500
1 row selected.SQL> commit;Commit complete.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 127536592
1 row selected.
Ø We drop the index and try to update the records.

SQL> drop index dat_idx;
Index dropped.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 127548252
1 row selected.
SQL> update redo_check set data1 = ' redo check' where num < 100001;Ø Now it has created the redo only for the table entry.
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 152859364
1 row selected.
Ø After that creating the index with NOLOGGING. This is one of the best ways to increase the performance of the scripts.
SQL> create index dat_idx on redo_check (data1) nologging;
Index created.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 152906760
1 row selected.SQL> commit;
Ø Now we check the redo generation with unusable the index and rebuilding the index way.
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 0Ø Make the index as an Unusable and update the records in the table.
SQL> alter index dat_idx unusable;
Index altered.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 2224
SQL> update redo_check set data1 ='reupdate' where num < data1 ="'reupdate'">
Ø The update statement is giving the error because the index is in the unusable state. To void this error message alter the current session with below statement.
SQL> alter session set skip_unusable_indexes=true;
Session altered.SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 2876
Ø Execute the update statement now and check the redo log. It has created the redo generation only for the table entries.
SQL> update redo_check set data1 ='reupdate' where num < 100001;>SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;
NAME VALUE
------------ ----------
redo size 24896420
Ø Rebuild the index and if we check the redo generation. It did not create any redo for the index entries.

SQL> alter index data1_idx rebuild;
Index altered.
SQL> select name,a.value
2 from v$sesstat a, v$sysstat b
3 where b.statistic#=a.statistic#
4 and b.name like 'redo size' and sid = 9;

NAME VALUE
------------ ----------
redo size 24934104