Oracle Performance Tuning Tips

Consider event 10359
for NOLOGGING LOBs

If your application performs frequent NOLOGGING operations, particularly frequent small changes to NOLOGGING LOBs, then you may find that it also spends a lot of time waiting for control file sequential read and control file parallel write waits. In some real world applications, these waits have been seen to account for as much as 20% of application response time.

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.
12-Oct-2007 22:22
Search   Questions   Feedback   Up   Home