“The [NO]LOGGING clause applies to tables, partitions, tablespaces, and indexes. Virtually no log is generated for certain operations (such as direct-path INSERT) if the NOLOGGING clause is used. The NOLOGGING attribute is not specified at the INSERT statement level but is instead specified when using the ALTER or CREATE statement for a table, partition, index, or tablespace.

When a table or index has NOLOGGING set, neither parallel nor serial direct-path INSERT operations generate redo logs. Processes running with the NOLOGGING option set run faster because no redo is generated. However, after a NOLOGGING operation against a table, partition, or index, if a media failure occurs before a backup is performed, then all tables, partitions, and indexes that have been modified might be corrupted.

Direct-path INSERT operations (except for dictionary updates) never generate redo logs if the NOLOGGING clause is used. The NOLOGGING attribute does not affect undo, only redo. To be precise, NOLOGGING allows the direct-path INSERT operation to generate a negligible amount of redo (range-invalidation redo, as opposed to full image redo).”

I vaguely remember that a nologging operation would generate something called “invalidation redo” in a way that a direct load wouldn’t, but that may be a myth or an obsolete state of affairs. Do you know something about that?

]]>By: Fladohttps://hoopercharles.wordpress.com/2011/06/08/nologging-option-and-log-file-sync-waits-what-is-wrong-with-this-quote/#comment-3451
Fri, 10 Jun 2011 15:21:59 +0000http://hoopercharles.wordpress.com/?p=4979#comment-3451Yes, I think you need /*+ append */ if you want the absolute minimum of redo generated (that for moving the HWM and associated undo, plus eventual delayed block cleanouts on the dictionary tables). Conventional insert with nologging still generates undo which needs redo.
Or I may have overlooked yet another detail and you did generate just a tiny bit of redo but had the bad luck of filling up the last buffer before the switch

Anyway, if you repeat the test (just Session 1) with autotrace on, it should indeed be easy to see.

“One common optimization leveraged by data warehouses is to execute bulk-data operations using the NOLOGGING mode. The database operations that support NOLOGGING modes are direct-path loads and inserts, index creation, and table creation. When an operation runs in NOLOGGING mode, data is not written to the redo log (or more precisely, only a small set of metadata is written to the redo log). This mode is widely used within data warehouses and can improve the performance of bulk data operations by up to 50 percent.

However, the trade-off is that a NOLOGGING operation cannot be recovered using conventional recovery mechanisms, because the necessary data to support the recovery was never written to the log file. Moreover, subsequent operations to the data upon which a NOLOGGING operation has occurred also cannot be recovered even if those operations were not using NOLOGGING mode. Because of the performance gains provided by NOLOGGING operations, it is generally recommended that data warehouses utilize NOLOGGING mode in their ETL process.”

So, a NOLOGGING operation should generate minimal redo, and that is why I was surprised to see “log buffer space” and “log file switch completion” waits. Did I by chance incorrectly specify NOLOGGING, this is the SQL statement (did I need an APPEND hint to make the NOLOGGING specication have an effect?):

The AskTom article linked at the start of this blog article includes the following quote:

“Setting the nologging option in this case (noarchivelog) is not necessary they are correct. The
insert /*+ append */ won’t generate log for the table modifications. In archivelog mode, the table
would (should) be in nologging mode.

So, in a noarchive log mode database, you don’t need to set nologging on the table to bypass redo
generation with insert /*+ append */. This is easy to see and demonstrate”

As I stated, there is always the chance that I am misunderstanding something (or overlooking an important detail).

]]>By: Fladohttps://hoopercharles.wordpress.com/2011/06/08/nologging-option-and-log-file-sync-waits-what-is-wrong-with-this-quote/#comment-3449
Fri, 10 Jun 2011 14:25:03 +0000http://hoopercharles.wordpress.com/?p=4979#comment-3449Why would it surprise you that a NOARCHIVELOG database can experience these two waits? Log buffer operations have nothing to do with log file archiving. “log buffer space” only means that there is currently no space in the buffer, so the session has to wait for LGWR to flush it. “log file switch completion” is a variant of “log buffer space” specifying the reason why there’s no space in the buffer (because LGWR is busy switching the log file). In turn, there are two variants of “log file switch completion”, detailing just why it takes so long to switch the file – “log file switch completion (checkpoint incomplete)” and “log file switch completion (archiving needed)”. Of all these, it’s only the last one I would be surprised to see in a NOARCHIVELOG database. In your test, session 1 was generating redo at an alarming rate, so I would consider it normal for it to hit those two events from time to time.

This is the best explanation of redo internals I have ever seen. Ten years ago (don’t believe the timestamp on that page – The Wayback Machine has crawled it as early as Jan 2002) these notes made me realize that there is no mystery in how Oracle works and it is just a program built using the same building blocks as the programs I was writing at the time. I’d buy Steve Adams a beer were I to ever meet him 🙂

Why can’t I use that NOLOGGING keyword when other users are adding or updating data in the same database – a database typically has more than one table, right? Finally, is it the database that processes the user requests, or is a database a set of files on disk?

]]>By: Fladohttps://hoopercharles.wordpress.com/2011/06/08/nologging-option-and-log-file-sync-waits-what-is-wrong-with-this-quote/#comment-3442
Fri, 10 Jun 2011 10:03:53 +0000http://hoopercharles.wordpress.com/?p=4979#comment-3442Using just remnants of what I once (2001) learned from ixora.co.au:

> As the log file sync wait event is caused by contention caused by the LGWR process,
Wrong. This event is caused by either a session issuing a commit, or DBWR wanting to checkpoint a block. LGWR just does the work.

> see if you can use the NOLOGGING option to get rid of these waits.
Wrong. See above – the rate of redo generation has nothing to do with ‘log file sync’ waits. Rate of commits does. Place open your database in read-only mode, and you will get rid of these (and many other) events.
Unclear: what does “use the NOLOGGING option” mean? Is this explained in the surrounding text/chapters?

>Of course, in a production system, you can’t use the NOLOGGING option when the database is processing user requests,
Unclear: why not?

>so this option is of limited use in most cases
That might be true, depending on the value of “most” and the definition of “use”. If “use” is “get rid of log file sync waits”, then “most” should be 0 for this to be correct.

And does it say why one might want to get rid of ‘log file sync’ waits? These are pretty harmless waits in most (>>0) cases, I think.

I think that there is still more to say about the second half, and the first half for that matter. Thanks for setting the foundation upon which other people will likely add.

I think that I have about half of a page of typed review notes for recipe 5-7 (this is NOT a good sign) – I hope that this section of the book is still a work in process. Regarding the “redo block” size, yes, although I thought that I heard that the “redo block” size was set to change soon to 4096 bytes. Reference to the book “Oracle Wait Interface: a Practical Guide to Performance Diagnostics & Tuning” regarding this parameter:http://books.google.com/books?id=14OmJzfCfXMC&pg=PA137#v=onepage&q&f=false

True, the book (in this case) did not tell the reader to modify a hidden initialization parameter without warning – it described a non-existent parameter, and then demonstrated why people should not just modify those parameters on a whim. 🙂