LOGGING or NOLOGGING, that is the question – Part VI

 By Francisco Munoz Alvarez Oracle ACE

TIPS USING NOLOGGING MODE 

DIRECT PATH INSERT 

To use Direct Path Insert use the /*+ APPEND */ hint as follow:

  • INSERT /*+ APPEND */ into … SELECT …

When direct path insert is used oracle does the following:

  • Format the data to be inserted as oracle blocks.
  • Insert the blocks above the High Water Mark (HWM)

  • When commit takes place the HWM is moved to the new place (The process is done bypassing the buffer cache).

It is clear that direct load is useful for bulk inserts. Using it to insert few hundred records at a time can have bad effect on space and performance.

It is very important to understand how Direct Path Inserts affects redo generation. As mentioned above it does not affect indexes but it is affected by the following factors:

  • The database Archivelog mode.
  • Using the /*+ APPEND */ hint.
  • The LOGGING mode of the table.
  • The FORCE LOGGING mode of the database (from 9i R2).

If the database is in FORCE LOGGING mode then Oracle will treat the table as if it was in LOGGING mode regardless of its mode. To find out if the database is in FORCED LOGGING or not run:

  • select FORCE_LOGGING from v$database ;

If the /*+ APPEND */ Hint is not used then the insertion will generate the normal amount of redo regardless of the other factors.

This table will show the relation between ARCHIVELOG mode and having the table in LOGGING mode when the /*+ APPEND */ hint is used. This does not include index and  data dictionary changes redo generation.

LOGGING MODE

ARCHIVELOG

NOARCHIVELOG

LOGGING

Redo

No Redo

NOLOGGING

No Redo

No Redo

For Bulk DML  

Bulk Inserts 

To load bulk data using Direct Path.

  • set table in nologging mode. Alter table table_name nologging;
  • alter index index_name unusable ;
  • alter session set skip_unusable_indexes=true ;(*)
  •  Insert /*+ APPEND */ into table_name select …
  •  Alter index index_name rebuild nologging;
  • Alter table table_name logging ;
  • Alter index index_name logging ;
  • Backup the data.

(*)skip_unusable_indexes is an instance initialization parameter in 10g and defaulted to true. Before 10g, skip_unusable_indexes needs to be set in a session or the user will get an error. It is a good practice to set it in a session, regardless of the database version, when the above is done.

There is no direct way (at the time of writing this document) of reducing redo generation for bulk update and delete. The user needs to reduce the workload on the database.

Bulk Delete 

  1. Create a new_table with no logging, CREATE TABLE table_name NOLOGGING (….); The NOLOGGING comes after the TABLE_NAME not at the end of the statement.
  2. Insert /*+ Append */ into new_table select the records you want to keep from current_table.
  3. Create the indexes on the new table with NOLOGGING (*)
  4. Create constraints, grants etc.
  5. Drop current_table.
  6. Rename new_table to current.
  7. Alter new_table and indexes logging.
  8. Backup the data.

(*) If the data left is so small or there are a lot of dependencies on the table (views, procedures, functions) the following steps can be used instead of 3-6 above:

  1. Disable constrains on current_table;
  2. Truncate current_table;
  3. make indexes unusable;
  4. alter current table NOLOGGING ;
  5. Insert /*+ APPEND */ into current_table select * from new_table ;
  6. commit;
  7. rebuild indexes with NOLOGGING;
  8. enable constraints
  9. Put current table and indexes in LOGGING mode
  10. backup the data
  11. drop table new_table;

Bulk Update 

Follow the steps for bulk Delete but integrate the update within the select statement. Lets say that you want to update the value column in the goods table by increasing it by 10% the statement will be like:

  1. Create a new_table with no logging, CREATE TABLE table_name NOLOGGING (….); (The nologging comes after the table_name, not at the end of the statement.).
  2.  Insert /*+ Append */ into new_table select (update statement eg: col1, col2* 1.1,…)
  3. Create the indexes on the new table with NOLOGGING (*)
  4. Create constraints, grants etc.
  5. Drop current_table.
  6. Rename new_table to current.
  7. Alter new_table and indexes logging.
  8. Backup the data.

Backup and Nologging 

If required, it is possible that the data loaded using NOLOGGING can be loaded again. If the database crashed before backing up the new data then this data can not be recovered.

Here are the two scenarios of backing up:

Export (exp or expdp) 

This method will allow you to recover the loaded data up to the point the export was taken but not later.

For customers using 10g Oracle Streams, there is also the option of using Data Pump Export and Import Direct Path API. For more details please refer to the Utilities Guide

Hot Backup 

In order to recover any additional data or modification to the table you bulk inserted into using NOLOGGING the least you need to do is a hot backup of that tablespace. Remember you still generate redo for DML on the table when it is in NOLOGGING mode but you are strongly advised to put it in LOGGING mode in case you run one of the operations mentioned in the Disabling Logging section.

Wait for next part, I will talk about Redo Log I/O related wait events..  

Advertisements

Oracle ACE Director and President of LAOUC, NZOUG and CLOUG. Organizer of LA and APAC OTN Tours,

Posted in Redo Logs, Tutorials, White Papers
2 comments on “LOGGING or NOLOGGING, that is the question – Part VI
  1. […] LOGGING or NOLOGGING, that is the question – Part VI […]

  2. […]  LOGGING or NOLOGGING, that is the question – Part VI […]

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

%d bloggers like this: