Monday, January 21, 2013

Benefits and consequences of the NOLOGGING option

I still find confusion out there about the LOGGING and NOLOGGING clauses when performing DML and DDL operations, the reality is that the NOLOGGING clause will work only on particular conditions, but all regular inserts,updates and deletes will still log the operations.
The benefits of the NOLOGGING option are:
  • Will save disk space when the archive option is enabled.
  • Will largely reduce I/O on the redologs.
  • Will reduce the time it takes to complete the operation.
Please note that NOLOGGING operations will only reduce -not eliminate- the logging.
Lets see an example -
01-- First: we create an empty table with the nologging clause
02SQL> create table logging_example nologging as select * from dba_objects where 1=2;
03
04Table created.
05
06--Now let's enable the statistics and perform a couple of tests:
07
08SQL> set autotrace on statistics
09SQL> set timing on
10
11-- insert the records the traditional way
12SQL> alter system flush buffer_cache; --clean the cache to compare the speeds in equal conditions
13
14System altered.
15
16Elapsed: 00:00:01.49
17
18SQL> insert into logging_example select * from dba_objects;
19
2050864 rows created.
21
22Elapsed: 00:00:01.59
23
24Statistics
25----------------------------------------------------------
26 0 recursive calls
27 5250 db block gets
28 6766 consistent gets
29 982 physical reads
305636712 redo size --without the APPEND hint
31 670 bytes sent via SQL*Net to client
32 586 bytes received via SQL*Net from client
33 3 SQL*Net roundtrips to/from client
34 1 sorts (memory)
35 0 sorts (disk)
36 50864 rows processed
37
38-- insert the records with the APPEND hint (nologging)
39SQL> alter system flush buffer_cache; --clean the cache to compare the speeds in equal conditions
40
41System altered.
42
43Elapsed: 00:00:01.06
44
45SQL> insert /*+ append */ into logging_example select * from dba_objects;
46
4750864 rows created.
48
49Elapsed: 00:00:00.59
50
51Statistics
52----------------------------------------------------------
53 0 recursive calls
54 743 db block gets
55 5374 consistent gets
56 944 physical reads
572200 redo size --with the APPEND hint
58 654 bytes sent via SQL*Net to client
59 604 bytes received via SQL*Net from client
60 3 SQL*Net roundtrips to/from client
61 1 sorts (memory)
62 0 sorts (disk)
63 50864 rows processed

We can see that there is a big difference on the redo size generated by each insert, there are many post and articles on the internet that show the speed benefits when using the NOLOGGING option, but here I mainly want to clarify that a regular insert (no APPEND hint) will still generate redologs even if the table have been created with the NOLOGGING option.
What happens to the data after a restore when a nologging operation was performed on it?

I will present some scenarios to show the consequences when we need to perform a restore after a nologging transaction, this way we will know what to expect and we can better prepare ourselves in case of a disaster.
I took a full database backup, now I will create several tables with different options to see what happens after a restore, you might see some surprises here!
Scenarios:
  • Table “create as select” with the nologging option (table_ctas_nologging).
  • Regular table “create as select” (table_ctas_logging)
  • A nologging table created empty, and a regular (logging) insert (table_ctas_nologging_insert)
  • Table created with nologging, then two inserts, one with and one without logging (table_insert_mixed)
  • Regular logging table, with a nologging index (table_ctas_index_nologging)
01SQL> create table table_ctas_nologging nologging as select * from dba_objects;
02
03Table created.
04
05SQL> create table table_ctas_logging as select * from dba_objects;
06
07Table created.
08
09SQL> create table table_ctas_nologging_insert nologging as select * from dba_objects where 1=2;
10
11Table created.
12
13SQL> insert into table_ctas_nologging_insert select * from dba_objects;
14
1550864 rows created.
16
17SQL> commit;
18
19
20Commit complete.
21
22SQL> create table table_insert_mixed nologging as select * from dba_objects where 1=2;
23
24Table created.
25
26SQL> insert into table_insert_mixed select * from dba_objects;
27
2850866 rows created.
29
30SQL> insert into table_insert_mixed select /*+ append */ * from dba_objects;
31
3250866 rows created.
33
34SQL> commit;
35
36Commit complete.
37
38SQL> select count(*) from table_insert_mixed;
39
40 COUNT(*)
41----------
42 101732
43
44SQL> create table table_ctas_index_nologging as select * from dba_objects;
45
46Table created.
47
48SQL> create index IDXNOLOG on table_ctas_index_nologging (object_id) nologging;
49
50Index created.

Now I will shutdown the database and restore the tablespace from the backup.
Next is an extract from RMAN
01rman target /
02
03Recovery Manager: Release 10.2.0.4.0 - Production on Wed Aug 25 17:32:20 2010
04
05Copyright (c) 1982, 2007, Oracle. All rights reserved.
06
07connected to target database: ORCL (DBID=1247573001)
08
09RMAN> shutdown immediate
10
11using target database control file instead of recovery catalog
12database closed
13database dismounted
14Oracle instance shut down
15
16RMAN> startup mount;
17
18Oracle instance started
19database mounted
20
21Total System Global Area 285212672 bytes
22
23Fixed Size 1267068 bytes
24Variable Size 155191940 bytes
25Database Buffers 125829120 bytes
26Redo Buffers 2924544 bytes
27
28RMAN> restore tablespace users;
29
30Starting restore at 25-AUG-10
31using target database control file instead of recovery catalog
32allocated channel: ORA_DISK_1
33channel ORA_DISK_1: sid=152 devtype=DISK
34
35channel ORA_DISK_1: starting datafile backupset restore
36channel ORA_DISK_1: specifying datafile(s) to restore from backup set
37restoring datafile 00004 to +DATA/orcl/datafile/users.259.719792191
38channel ORA_DISK_1: reading from backup piece +DATA/orcl/backupset/2010_08_25/nnndf0_tag20100825t171657_0.272.727982219
39channel ORA_DISK_1: restored backup piece 1
40piece handle=+DATA/orcl/backupset/2010_08_25/nnndf0_tag20100825t171657_0.272.727982219 tag=TAG20100825T171657
41channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
42Finished restore at 25-AUG-10
43
44RMAN> recover tablespace users;
45
46Starting recover at 25-AUG-10
47using channel ORA_DISK_1
48
49starting media recovery
50media recovery complete, elapsed time: 00:00:05
51
52Finished recover at 25-AUG-10
53
54RMAN> alter database open;
55
56database opened

Now lets see the status of the tables:
1SQL> select count(*) from table_ctas_nologging ;
2 select count(*) from table_ctas_nologging
3 *
4 ERROR at line 1:
5 ORA-01578: ORACLE data block corrupted (file # 4, block # 404)
6 ORA-01110: data file 4: '+DATA/orcl/datafile/users.259.719792191'
7 ORA-26040: Data block was loaded using the NOLOGGING option

That doesn’t look good, lets see the next table
1SQL> select count(*) from table_ctas_logging ;
2
3COUNT(*)
4----------
550863

Good, no problem here, the next scenario is more interesting, the table was created with the NOLOGGING option, but the inserts were done without the APPEND hint
1SQL> select count (*) from table_ctas_nologging_insert;
2
3COUNT(*)
4----------
550864

Good, no problem here, now let’s see our table with half data inserted with logging and half with nologging
1SQL> select count(*) from table_insert_mixed;
2select count(*) from table_insert_mixed
3*
4ERROR at line 1:
5ORA-01578: ORACLE data block corrupted (file # 4, block # 4363)
6ORA-01110: data file 4: '+DATA/orcl/datafile/users.259.719792191'
7ORA-26040: Data block was loaded using the NOLOGGING option

Wow, the whole table is unredable!
Now lets see the table with the NOLOGGING index .
1<pre>SQL> select count(*) from table_ctas_index_nologging;
2
3COUNT(*)
4----------
550865

Ok, thats nice, the table is accessible, but what happend if we try to use the index?
1SQL> select object_id from table_ctas_index_nologging where object_id=1;
2select object_id from table_ctas_index_nologging where object_id=1
3*
4ERROR at line 1:
5ORA-01578: ORACLE data block corrupted (file # 4, block # 2821)
6ORA-01110: data file 4: '+DATA/orcl/datafile/users.259.719792191'
7ORA-26040: Data block was loaded using the NOLOGGING option

I tried to rebuil the index but I was still getting the same error message, at the end I was forced to drop it and recreate it.
Conclusions:
  • Use the NOLOGGING option only on temporary/working/staging tables.
  • Always perform a backup after a NOLOGGING operation.
  • Unless explicitly indicated, DDLs like CTAS and DMLs like inserts will log all operations.
FROM ORACLE DOCUMENTATION:
NOLOGGING is supported in only a subset of the locations that support LOGGING. Only the following operations support the NOLOGGING mode:

DML:
  • Direct-path INSERT (serial or parallel) resulting either from an INSERT or a MERGE statement. NOLOGGING is not applicable to any UPDATE operations resulting from the MERGE statement.
  • Direct Loader (SQL*Loader)

DDL:
  • CREATE TABLEAS SELECT
  • CREATE TABLELOB_storage_clauseLOB_parametersNOCACHE | CACHE READS
  • ALTER TABLELOB_storage_clauseLOB_parametersNOCACHE | CACHE READS (to specify logging of newly created LOB columns)
  • ALTER TABLEmodify_LOB_storage_clausemodify_LOB_parametersNOCACHE | CACHE READS (to change logging of existing LOB columns)
  • ALTER TABLEMOVE
  • ALTER TABLE … (all partition operations that involve data movement)
    • ALTER TABLEADD PARTITION (hash partition only)
    • ALTER TABLEMERGE PARTITIONS
    • ALTER TABLESPLIT PARTITION
    • ALTER TABLEMOVE PARTITION
    • ALTER TABLEMODIFY PARTITIONADD SUBPARTITION
    • ALTER TABLEMODIFY PARTITIONCOALESCE SUBPARTITION
  • CREATE INDEX
  • ALTER INDEXREBUILD
  • ALTER INDEXREBUILD [SUB]PARTITION
  • ALTER INDEXSPLIT PARTITION
For objects other than LOBs, if you omit this clause, then the logging attribute of the object defaults to the logging attribute of the tablespace in which it resides.
For LOBs, if you omit this clause, then:
  • If you specify CACHE, then LOGGING is used (because you cannot have CACHE NOLOGGING).
  • If you specify NOCACHE or CACHE READS, then the logging attribute defaults to the logging attribute of the tablespace in which it resides.
NOLOGGING does not apply to LOBs that are stored internally (in the table with row data). If you specify NOLOGGING for LOBs with values less than 4000 bytes and you have not disabled STORAGE IN ROW, then Oracle ignores the NOLOGGING specification and treats the LOB data the same as other table data.

 
Source : http://oracleexamples.wordpress.com/2010/08/28/benefits-and-consequences-of-the-nologging-option/

No comments:

Post a Comment