Private Redo

Following a question on the Oracle Core Addenda pages, here’s a little script to tell you about the sizes of the public and private redo threads currently active in the instance. It’s a minor variation of a script I published in Appendix D (Dumping and Debugging), page 237 to show the addresses of current activity in the various log buffers:

Redo

In the comments to a recent blog posting about log file syncs, Tony Hasler has produced a stunning observation about Oracle and ACID, in particular the D (durability) bit of transactions.

The observation can best be described with a demonstration (which I have run on versions from 8.1 to 11.2) involving three sessions, one of which has to be connected with sysdba privileges.

Audit Ouch!

A few days ago I was rehearsing a presentation about how to investigate how Oracle works, and came across something surprising. Here’s a simple bit of code:

Redo

A couple of days ago I published a link to some comments I had made on OTN about differences in redo generation between 10g and earlier versions of Oracle. This raised a few questions that suggested a follow-up (or perhaps “prequel”) note might be a little helpful. So I’ve created a simple SQL script to help demonstrate the differences and show how some of the pieces hang together.

Private Redo

About this time last year I wrote a short answer on OTN about Private Redo Threads and In-Memory Undo. Thanks to a follow-up question a year later I’ve been prompted to supply a link to the thread because my original answer was actually pretty good. (And the answer to the follow-up is also worth reading).

Update: If you’re looking at this blogo note and haven’t expanded the view to see the comments from readers, make sure that you do look at them since they include a couple of technical details I described in response to further follow-up questions on the OTN thread.

Unrecoverable

A recent question on the OTN database forum asked: “What’s the difference between index rebuild unrecoverable and nologging?”

The most important difference, of course, is that unrecoverable is a deprecated option so you shouldn’t be using it even though it still works.

Coalesce

The following question came up in an email conversation a little while ago:

Are you aware of any problems a large oltp site might have with running index coalesce during production hours, as opposed to doing index rebuilds in a maintenance window?

Why Undo ?

A recent thread on the OTN database forum asks the question:

“… since redo also has past and current information why can’t redo logs be used to retrieve that information? Why is undo required when redo already has all that information.”

The thread has generated some interesting replies – but to a large extent they describe how the undo and redo work rather than explaining why the designers at Oracle Corp. chose to implement undo and redo the way they did. Since I’m sitting in an airport (Zurich – where the coffee bar in the business lounge has a very large bowl of Lindt chocolate squares available) waiting for a plane I thought I’d use the time to share my thoughts on the why.

logging

I’ve just jotted down a few notes about “log file sync” waits, “log file parallel write” waits, and the nologging option in response to a question on OTN about redo activity when creating a large index. The ensuing conversation also picks up various topics relating also to backup, recovry and dataguard.

Bitmap Updates

It is fairly well-known that bitmap indexes are very dense structures that can behave badly if their underlying tables are subject to even fairly low levels of insert, update or delete activity. Problems include contention, space management and performance, and these problens have spawned a couple of well-known guidelines relating to bitmap indexes:

• Avoid concurrent modification of data by multiple processes – otherwise you can end up with processes dead-locking
• Drop/disable bitmap indexes before data loads and rebuild them afterwards.

Of course, with a little care and experimentation, you may find that you don’t need to apply the second guideline in all cases – especially for bulk inserts.

Nutshell – 1

Undo, redo, and recovery in a nutshell. (The “1” in the title may turn out to be optimistic, I offer no guarantee about further nutshells.)

In the most general case of updating a block – when you change one row in an table, say, or mark an index entry as deleted – Oracle does the following:

Log file write

A few years ago, I wrote a note about a bug in 9i that made it difficult to decide whether or not you had a problem with the log writer (lgwr). The problem was that the time spent in wait event “log file parallel write” was not captured properly – so you were reduced to guessing whether the time spent in “log file sync” might actually be an indication of a log file write problem.

Time changes everything – including the available tools and levels of skill of the people doing the trouble – and I see that Riyaj Shamsudeen recently published a note about the same problem.

Riyaj approaches the problem from a different direction, using exactly the right tool for the task, to demonstrate the error directly (rather than relying on modelling, inference and a follow-up check with Metalink as I did in 2005).

[Update 30th Nov 2008:] Here’s another interesting article on a closely related topic from Christian Bilien – in this case from a 10g system.

Log File Switch

A few days ago, I published a simple query you could run against v$log_history to show the time between log file switches.  So if you use this script and decide that your log file switches are happening at inappropriate intervals, what can you do ?

Log Switching

Analytic function have all sorts of uses, and the lag() and lead() functions are among my favourites. I always like to remind people of the sorting overheads involved with analytic functions – but if you are prepared to accept the overhead the simplicity of comparing ‘current’ rows with ‘earlier’ or ‘later’ rows using lag() or lead() is a tremendous coding benefit.

Tuning Updates

Consider the following update statement:

rem
rem     Script:         index_update_01.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Oct 2006
rem

update
        /*+ index(t1 t1_n1) */
        t1
set
        n1 = 0,
        n2 = n2 + 1,
        status = 'X'
where
        n1 > 100
and     status = 'A'
;