Hi J.J,
This is part of a material I'm preparing about this topic:
Redo generation is a vital part of the Oracle recovery mechanism. Without it crashed instances will not recover and will not start in a consistent state. Excessive LOGGING is the result of excessive work on the database.
The Oracle® Database Administrator's Guide 10g Release 2 say regarding the main benefits of the NOLOGGING option:
• Space is saved in the redo log files
• The time it takes to create the table is decreased
• Performance improves for parallel creation of large tables
“A very important rule with respect to data is to never put yourselft into an unrecoverable situation. The importance of this guideline cannot be stressed enough, but it does not mean that you can never use time saving or performance enhancing options. “
Oracle gave the user the ability to limit redo generation on tables and indexes by setting them in NOLOGGING mode. NOLOGGING affect the recoverability and before going into how to limit the redo generation it is important to clear the misunderstanding that NOLOGGING is the way out of redo generation, this are some points regarding it:
• NOLOGGING is designed to handle bulk inserts for data which can be re-produced.
• Writing to undo blocks causes generation of redo regardless of LOGGING status.
• LOGGING should not be disabled on a primary database if it has one or more standby databases. For this reason oracle introduced the ALTER DATABASE FORCE LOGGING command in Oracle 9i R2. This means that the NOLOGGING attribute will not have any effect on the segments if the database is in FORCE LOGGING MODE. NOLOGGING can be also override at tablespace level using ALTER TABLESPACE … FORCE LOGGING.
• Any change to the database dictionary will cause LOGGING. This will happen to protect the data dictionary. One example is if we allocated a space above the HWM for a table, and the system fail in the middle of one INSERT /*+ APPEND */ , the Oracle will need to rollback that data dictionary update. There will be redo generated but it is to protect the data dictionary, not your newly inserted data (Oracle just undo the space allocation if it fails, your data will disappear).
• Data which are not logged can not be recovered. The data should be backed up after the modification.
• Tables and indexes should be set back to LOGGING mode when the NOLOGGING is no longer needed.
• NOLOGGING is not needed for Direct Path Insert if the database is in NO Archive log mode.
• NOLOGGING should not be used for the data which can not be reproduced. If data which can not be reloaded was loaded using NOLOGGING and the database crashes before backing this data up, the data can not be recovered.
• NOLOGGING does not apply to UPDATE and DELETE.
• NOLOGGING will work during certain situations but subsequent DML will generate redo. Some of these situations are: direct load INSERT (using APPEND hint), CREATE TABLE ... AS SELECT, CREATE INDEX.
• If the LOGGING or NOLOGGING clause is not specified when creating a table, partition, or index the default to the LOGGING attribute will be the LOGGING attribute of the tablespace in which it resides.
Disabling Logging (NOLOGGING)
Logging can be disabled at the table level or the tablespace level. If it is done at the tablespace level then every newly created index or table in this tablespace will be in NOLOGGING mode you can have logging tables inside a NOLOGGING tablespace). A table or an index can be created with NOLOGGING mode or it can be altered using ALTER TABLE/INDEX NOLOGGING. It is important to note that just because an index or a table was created with NOLOGGING does not mean that redo generation has been stopped for this table or index. NOLOGGING is active in the following situations and while running one of the following commands but not after that. This is not a full list:
• DIRECT LOAD (SQL*Loader)
• DIRECT LOAD INSERT (using APPEND hint)
• CREATE TABLE ... AS SELECT
• CREATE INDEX
• ALTER TABLE MOVE
• ALTER TABLE ... MOVE PARTITION
• ALTER TABLE ... SPLIT PARTITION
• ALTER TABLE … ADD PARTITION (if HASH partition)
• ALTER TABLE … MERGE PARTITION
• ALTER TABLE … MODIFY PARTITION
o ADD SUBPARTITON
o COALESCE SUBPARTITON
o REBUILD UNUSABLE INDEXES
• ALTER INDEX ... SPLIT PARTITION
• ALTER INDEX ... REBUILD
• ALTER INDEX ... REBUILD PARTITION
Logging is stopped only while one of the commands above is running, so if a user runs this:
• ALTER INDEX new_index NOLOGGING.
The actual rebuild of the index does not generate redo (all data dictionary changes associated with the rebuild will do) but after that any DML on the index will generate redo this includes direct load insert on the table which the index belongs to.
Here is another example to make this point more clear:
• CREATE TABLE new_table_nolog_test NOLOGGING(….);
All the following statements will generate redo despite the fact the table is in NOLOGGING mode:
• INSERT INTO new_table_nolog_test ...,
• UPDATE new_table_nolog_test SET …,
• DELETE FROM new_table_nolog_test ..
The following will not generate redo (except from dictionary changes and indexes):
• INSERT /*
APPEND/ …
• ALTER TABLE new_table_nolog_test MOVE …
• ALTER TABLE new_table_nolog_test MOVE PARTITION …
To activate the NOLOGGING for one of the ALTER commands above add the NOLOGGING clause after the end of the ALTER command.
For example:
• ALTER TABLE new_table_nolog_test MOVE PARTITION parti_001 TABLESPACE new_ts_001 NOLOGGING;
The same applies for CREATE INDEX but for CREATE TABLE the NOLOGGING should come after the table name.
Example:
• CREATE TABLE new_table_nolog_test NOLOGGING AS SELECT * FROM big_table;
"It is a common mistake to add the NOLOGGING option at the end of the SQL (Because oracle will consider it an alias and the table will generate a lot of logging)."
To user Direct Path Load in SQL*Loader you must run the $ORACLE_HOME/rdbms/admin/catldr.sql script before your first sqlldr is run in direct path mode. To run sqlldr in direct path mode use direct=true.
Note: Even though direct path load reduces the generation of redo, it is not totally eliminated. That's because those inserts still generate undo which in turn generates redo.
If there is an index on the table, and an +APPEND INSERT is made on the table, the indexes will produce redo. This can be circumvented by setting the index to unusable and altering the session's (before 10g you only can set this at session level, after 10g you also can set this parameter at instance level) skip_unusable_indexes to true (This doesn't apply to UNIQUE indexes.).
Note: Please be free to comment or correct it.
Regards,
Francisco Munoz Alvarez
