Control File Updates Can Be Disabled During NOLOGGING OperationsPosted: 30/11/2010
DB_UNRECOVERABLE_SCN_TRACKING = [ TRUE | FALSE ] can be used to turn off control file writes to update fields that track the highest unrecoverable SCN and Time during a
NOLOGGING direct path operation.
Performance of the
NOLOGGING load operation could be limited by the control file write I/O.
When the value is set to
true, updates are made to the controlfile that maintains the
UNRECOVERABLE_TIME columns. When the value is set to
false, updates are not made to the controlfile. Setting this parameter to
false may improve performance of direct-path
DB_UNRECOVERABLE_SCN_TRACKING initialization parameter is available starting with Oracle Database 11g Release 2 (184.108.40.206)
Direct-Path INSERT without Logging
In this mode, Oracle Database inserts data without redo or undo logging. Instead, the database logs a small number of block range invalidation redo records and periodically updates the controlfile with information about the most recent direct write.
Direct-path insert without logging improves performance. However, if you subsequently must perform media recovery, the invalidation redo records mark a range of blocks as logically corrupt, because no redo data was logged for them. Therefore, it is important that you back up the data after such an insert operation.
Beginning with release 220.127.116.11 of Oracle Database, you can significantly improve the performance of unrecoverable direct path inserts by disabling the periodic update of the controlfiles. You do so by setting the initialization parameter
FALSE. However, if you perform an unrecoverable direct path insert with these controlfile updates disabled, you will no longer be able to accurately query the database to determine if any datafiles are currently unrecoverable.