Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle nologging tips

Oracle Database Tips by Donald BurlesonApril 28,  2015

The nologging option is a great way to speed-up inserts and index creation.  It bypasses the writing of the redo log, significantly improving performance.   However, this approach is quite dangerous if you need to roll-forward through this time period during a database recovery. In nologging mode you are running without a safety net when you run nologging operations and you must:

  • Backup before and after - You must take a backup, both before and after all nologging operations

  • Only nologging operations during the nologging window -  Between the backups (the nologging processing window), ONLY nologging operations should be run in the middle of this "backup sandwich".

The nologging clause IS NOT A SQL HINT, and the NOLOGGING clause is quite convoluted and dependent on several factors.

  • Database noarchivelog mode - If your database is in "noarchivelog" mode and you are no using the APPEND hint for inserts, you WILL STILL generate redo logs!

  • Database archivelog mode - If you are in archivelog mode, the table must be altered to nologging mode AND the SQL must be using the APPEND hint.  Else, redo WILL be generated.

You can use nologging for batch  inserts into tables and for creating indexes:

  • You can insert into tables with nologging - If you use the APPEND hint and place the table in nologging mode, redo will be bypassed. 

alter table customer nologging;

insert /*+ append */ into customer values ('hello',';there');

  • You can create indexes with nologging - The only danger with using nologging is that you must re-run the create index syntax if you perform a roll-forward database recovery.  Using nologging with create index can speed index creation by up to 30%.

create index newidx . . . nologging;

  • Other nologging options - Only the following operations can make use of the NOLOGGING option:
     
        alter table...move partition
        alter table...split partition
        alter index...split partition
        alter index...rebuild
        alter index...rebuild partition
        create table...as select
        create index
        direct load with SQL*Loader
        direct load INSERT (using APPEND)

For more information on using nologging for optimal performance, see my book Oracle Tuning: The Definitive Reference.

NOLOGGING clause warning!

Be very careful using UNRECOVERABLE clause and the NOLOGGING clause when performing CREATE INDEX or CREATE TABLE AS SELECT (CTAS) commands.

The CTAS with NOLOGGING or UNRECOVERABLE will send the actual create statement to the redo logs (this information is needed in the data dictionary), but all rows loaded into the table during the operation are NOT sent to the redo logs.

With NOLOGGING , although you can set the NOLOGGING attribute
for a table, partition, index, or tablespace, NOLOGGING mode does not apply to every operation performed on the schema object for which you set the NOLOGGING attribute.

It is not possible to roll forward through a point in time when an NOLOGGING operation has taken place. This can be a CREATE INDEX NOLOGGING, CREATE TABLE AS SELECT NOLOGGING, or an NOLOGGING table load.

The NOLOGGING clause is a wonderful tool since it often halves run times, but you need to remember the danger. For example, a common practice is to reorganize very large tables is to use CTAS:

Create table
   new_customer
tablespace
   new_ts
NOLOGGING
as
   select * from customer;

Drop table customer;
Rename new_customer to customer;

However, you must be aware that a roll-forward through this operation is not possible, since there are no images in the archived redo logs for this operation. Hence, you MUST take a full backup after performing any NOLOGGING operation.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.