NOLOGGING What it is (and what it is not)


redo

Whenever a insert or bulk load operations are performed on a database, also redo and undo blocks are created. Through the NOLOGGING option less redo blocks are written by the corresponding operation.

When are NOLOGGING operations useful:

  • To improve the performance of large insert operations
  • To improve the build times of large indexes
  • To improve the build times of large tables (CTAS)
  • The amount of redo IO and result in further reducing the number of archive log files

What operations can use NOLOGGING?

  • Create Table As Select (CTAS)
  • ALTER TABLE operationen
  • ALTER TABLE statements (add/move/merge/split partitions)
  • INSERT /*+APPEND*/
  • CREATE INDEX
  • ALTER INDEX statements (add/move/merge/split partitions)

Examples of NOLOGGING operations:

NOLOGGING is either an object parameter to the partition => Tables => LOB Storage => Index or at the creation by ALTER or CREATE commands. The NOLOGGING option issued by ALTER TABLESPACE command determines the default value of the objects that are created in this tablespace.

SQL> create table big_emp nologging as select * from emp;
SQL> insert /*+ APPEND */ into emp select * from big_emp;
SQL> create index emp_no  on emp(ename) nologging;
UNRECOVERABLE Option im SQL Loader Controlfile

Note: Update operations can not be done with NOLOGGING

Why REDO is still generated?

It is a common assumption that NOLOGGING operations generate no redo stream, but that’s incorrect. Oracle generated anyway for all data dictionary changes REDO blocks to protect this for the Media Recovery. Furthermore, you should not forget the NOLOGGING operation is related to the table, but not for indexes that have been created on the segment. Also, meta information (Commit Records, etc) will be written in the REDO stream.

REDO or at least UNDO?

One further confusion that NOLOGGING also reduce UNDO blocks generated. Again, this is not right. NOLOGGING belongs to REDO. Properly that is done by Direct Path operations, the data is written directly to the data files and thus the UNDO is bypassed.

Disadvantages of NOLOGGING operations:

Developers use this feature often to improve the performance of their application, however they do that without the complete impact of a database. This means that all processes of a database on which the redo and archive log files access are concerned. As an example, all the objects affected only limited or no restorable from a backup and usually this falls if a DBA trys to restore a Backup. Also standby databases or Oracle Streams can not restore or replicate these objects. For this reason, database administrators should keep a eye on NOLOGGING operations. Data Files which NOLOGGING should be backed up include objects and which are not logically recoverable after the NOLOGGING operation as a media recovery is limited.

Preventing NOLOGGING operations:

Database level:

At a database level, the FORCE LOGGING option can be used.

SQL> ALTER DATABASE {NO} FORCE LOGGING;
SQL> select FORCE_LOGGING from v$database;

FORCE_LOGGING
---------------------------------------
NO

Tablespace level:

On tablespace level the same option as on database level can be used.

SQL> ALTER TABLESPACE users {NO} FORCE LOGGING;
SQL> SELECT tablespace_name, force_logging FROM dba_tablespaces;

TABLESPACE_NAME                FOR
------------------------------ ---
SYSTEM                         YES
...

Object level:

At the object level, the LOGGING option is used.

SQL> ALTER TABLE emp {NO}LOGGING;
A FORCE LOGGING option to a higher level always overrides the NOLOGGING option on a lower level. Thus, objects can not be loaded with NOLOGGING in a tablespace has the FORCE LOGGING.

NOLOGGING operations monitoring

A simple test to check whether a database is concerned, the v$datafile can be selected. If the database parameter DB_UNRECOVERABLE_SCN_TRACKING is set to FALSE the Unrecoverable information is no longer written to the controlfile but on the other side you have a higher performance. If this value is set to FALSE the view v$datafile is also affected.

SELECT NAME, UNRECOVERABLE_CHANGE#,                
TO_CHAR (UNRECOVERABLE_TIME,'DD.MON.YYYY HH:MI:SS') 
FROM V$DATAFILE;
Advertisements

Ein Gedanke zu “NOLOGGING What it is (and what it is not)

  1. Pingback: NOLOGGING Was es ist (und was nicht ist) - www.dbconcepts.at

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s