Oracle Scratchpad

October 14, 2009

Nutshell – 1

Filed under: Infrastructure,redo,undo — Jonathan Lewis @ 8:22 pm BST Oct 14,2009

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:

November 28, 2008

Log file write

Filed under: Infrastructure,Performance,redo,Troubleshooting — Jonathan Lewis @ 9:04 am BST Nov 28,2008

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.

April 12, 2007

Log File Switch

Filed under: Infrastructure,redo,Tuning — Jonathan Lewis @ 9:55 pm BST Apr 12,2007

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 ?


April 9, 2007

Log Switching

Filed under: Performance,redo,Tuning — Jonathan Lewis @ 7:04 pm BST Apr 9,2007

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.


November 22, 2006

Tuning Updates

Filed under: Indexing,Infrastructure,Oracle,redo,Tuning,undo — Jonathan Lewis @ 8:45 pm BST Nov 22,2006

Consider the following update statement:

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


« Previous Page

Powered by