Why does Oracle do so much I/O to the controlfile? It is because whenever a datafile is changed by a NOLOGGING operation, the unrecoverable SCN for that datafile, which is stored in the controlfile, needs to be updated. These updates must occur in controlfile transactions to permit recoverability from an instance crash while the controlfile is being changed.
Unfortunately, controlfile transactions are very I/O intensive. Each controlfile transaction requires at least two reads and two data synchronous writes per controlfile copy. If the controlfiles are buffered by the operating system's file system buffer cache, then the reads may be relatively cheap. Even so, in a typical environment with two or three controlfile copies, a controlfile transaction must nevertheless wait for 4 or 6 random physical writes to the controlfiles.
If your application makes frequent small changes to NOLOGGING LOBs, then it may well be that the controlfile transactions required to update the unrecoverable SCN are actually taking a lot longer than it would take to log the redo for the LOB changes if the LOBs were changed to LOGGING. However, there is a better alternative -- namely, setting event 10359. Most numeric events are undocumented, and not normally supported. However, this particular event is sanctioned with a reference in the Oracle9i Application Developer' Guide - Large Objects (LOBs).
Event 10359 disables all updates of unrecoverable SCNs. By setting this event you can retain the performance benefit of not logging LOB changes without sustaining the performance penalty of repeated foreground controlfile transactions. The only disadvantage is that RMAN will no longer be able to report which datafiles have recently been affected by NOLOGGING operations, and so you will have to adopt a backup strategy that compensates for that.
© Ixora Pty Ltd. All rights reserved.