Control file writes when updating???

I had never seen this before, but while testing a migration strategy for a large table with XMLTYPE’s, almost half our wait time was spent on control file sequential read and control file parallel write waits. Each of these occurred immediately after a “direct path write” wait.

When I dug a little further, I found that we had the table set to NOLOGGING. We had not set the LOBS to NOCACHE or NOLOGGING. However, evidently that was trumped, and the unrecoverable changes caused each update to write the corresponding SCN to the control file.

We set an event (10359) that eliminated control file writes for our session, and improved our performance by almost 50% (27 minutes for our test case down to 16)!!!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.