Oracle Scratchpad

April 20, 2015

Manuals

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 4:24 pm GMT Apr 20,2015

From time to time I read a question (or, worse, an answer) on OTN and wonder how someone could have managed to misunderstand some fundamental feature of Oracle – and then, as I keep telling people everyone should do – I re-read the manuals and realise that that sometimes the manuals make it really easy to come to the wrong conclusion.

Having nothing exciting to do on the plane to Bucharest today, I decided it was time to read the Concepts manual again – 12c version – to remind myself of how much I’ve forgotten. Since I was reading the mobi version on an iPad mini I can’t quote page numbers, but at “location 9913 of 16157” I found the following text in a sidebar:

“LGWR can write redo log entries to disk before a transaction commits. The redo entries become permanent only if the transaction later commits.”

Now I know what that’s trying to say because I already know how Oracle works – but it explains the various questions that I’ve seen on OTN (and elsewhere) struggling with the idea of how Oracle manages to “not have” redo for transactions that didn’t commit.

The redo entries become permanent the moment they are written to disc – nothing makes any of the content of the redo log files disappear 1, nothing goes back and flags some bits of the redo log as “not really there”. It’s the changes to the data blocks that have been described by the redo that become permanent only if the transaction later commits. If the transaction rolls back2 the session doesn’t “seek and destroy” the previous redo, it generates MORE redo (based on the descriptions that it originally put into the undo segment) and applies the changes described by that redo to reverse out the effects of the previous changes.

So next time you see a really bizarre question about how Oracle works remember that it could have arisen from someone reading the manual carefully; because sometimes the manual writers know exactly what they mean to say but don’t actually say it clearly and unambiguously.

1 I am aware that strange and rare events such disc crashes could make all sorts of things disappear, but I think it’s reasonable to assume here that we’re talking about standard processing mechanisms.

2 I am also aware that there are variations dependent on events like sessions being killed, or instance failure that could need some further explanation, but there’s a time, place, and pace, for everything.

16 Comments »

  1. I remember early in my Oracle career running around in small panicked circles because I believed I couldn’t grant privileges on a sequence. A manual had an oddly worded sentence that made it seem true. Twenty years later, it seems humorous that I let my fate hang on one sentence from one source. And I’ve filed a few Oracle documentation bugs since then.

    Comment by Mary Elizabeth McNeely — April 20, 2015 @ 4:43 pm GMT Apr 20,2015 | Reply

  2. thanks Jonathan – I’ll see about getting that clarified…

    Comment by Thomas Kyte — April 20, 2015 @ 5:46 pm GMT Apr 20,2015 | Reply

    • Tom,
      Thanks for that. One benefit (from my perspective) of reading the docs in mobi on my iPad rather than in HTML on my laptop is that I don’t have an option for filling in an email feedback form.

      Comment by Jonathan Lewis — April 22, 2015 @ 9:42 pm GMT Apr 22,2015 | Reply

  3. Oracle doc. sometimes can be incomprehensible, but still it is one of the best written documentations out there.

    Regards,
    Ivica Arsov

    Comment by Ivica Arsov — April 20, 2015 @ 7:28 pm GMT Apr 20,2015 | Reply

  4. some bizarre questions on OTN may arise from a careful reading of the documentation. But I think there is at least one more frequent reason for bizarre questions on OTN…

    Comment by Martin Preiss — April 20, 2015 @ 8:15 pm GMT Apr 20,2015 | Reply

    • Paraphrasing Oscar Wilde: “There is only one thing in the job worse than reading the manuals, and that is not reading the manuals”.

      Comment by Jonathan Lewis — April 22, 2015 @ 9:36 pm GMT Apr 22,2015 | Reply

  5. Thanks Jonathan for pointing out how “fuzzy formulations” in the documentation can lead to severe misunderstandings. Dependent on the case, it may be difficult to decide whether a sentence is a deliberate simplification, awkwardly phrased or just plain wrong … (of course this applies to every documentation, not just Oracle).
    Speaking of the Concepts Guide, there is something I’ve been wondering for quite some time – it’s about the foreign key locking issue, where it says (below http://docs.oracle.com/database/121/CNCPT/consist.htm#CNCPT1340 )

    Locks and Unindexed Foreign Keys
    When both of the following conditions are true, the database acquires a full table lock on the child table:
    No index exists on the foreign key column of the child table.
    A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table.

    There is no distinction between the on delete cascade vs normal constraint case… But the described locking behavior, AFAIK, only applies to on delete cascade constraints… Or am I overlooking something here?
    Thanks!
    Sigrid

    Comment by recurrentnull — April 21, 2015 @ 10:17 am GMT Apr 21,2015 | Reply

    • it applies to any of the documented operations – the docs say “update of primary key”, “delete of parent record”, “merge into parent”. The on delete cascade would happen to the child table and the doc talks only about things you do to the parent table…

      (and in current releases, merge does not always take the lock anymore, the merge would have to include an update to the primary key or a delete component…)

      Comment by Thomas Kyte — April 21, 2015 @ 2:42 pm GMT Apr 21,2015 | Reply

  6. Tom, thanks for answering, I’m afraid I guess I was not very clear, sorry… I was not talking about delete cascade, but about locking behavior on the child table being different whether the foreign key constraint was defined “on delete cascade” or not.

    At least that’s what I thought I had found in https://recurrentnull.wordpress.com/2013/12/17/unindexed-foreign-keys-considered-harmful-but-are-they/
    Now that I look at it again (it was quite some time ago) I think that perhaps it doesn’t make sense to state things like that, as of course there can be no locking behavior if, in the case of a “normal constraint”, an operation is not allowed anyway … so some cases of locking can exist with on delete cascade constraints only, per definitionem…

    Also perhaps, that article was too “academic” anyway, as in reality, for the application, there really WAS an issue of foreign key locking: https://recurrentnull.wordpress.com/2014/05/20/unindexed-foreign-keys-again-and-yes-it-does-matter/

    (Sorry for the long writeup, sometimes things look different when you look at them later ;-))

    Comment by recurrentnull — April 21, 2015 @ 3:17 pm GMT Apr 21,2015 | Reply

    • “Sometimes things look different when you look at them later”.

      I think everyone finds that to be true – including people who write documentation and manuals ;)

      Comment by Jonathan Lewis — April 22, 2015 @ 9:40 pm GMT Apr 22,2015 | Reply

  7. “and applies the changes described by that redo to reverse out the effects of the previous changes.”

    should it be like below? or I miss something?

    and applies the changes described by that undo to reverse out the effects of the previous changes.

    Comment by Henish — April 22, 2015 @ 6:09 pm GMT Apr 22,2015 | Reply

    • I wrote exactly what I intended.

      Compacting the sentence: “Oracle uses the undo to generate redo then uses the resulting redo to change the data blocks.”

      It would, however, be a reasonable approximation to say that Oracle applies the undo to the data blocks, but technically it’s redo that has to be applied to make changes, hence undo -> redo -> data block changes.

      Comment by Jonathan Lewis — April 22, 2015 @ 9:33 pm GMT Apr 22,2015 | Reply

  8. “Oracle applies the redo vector present in the undo” would be better, just to avoid the creation of confusion ????….. or am I the one confused?

    Comment by Peter — April 23, 2015 @ 5:25 pm GMT Apr 23,2015 | Reply

    • Peter,

      It always takes a long time to polish the words to avoid ambiguity and confusion. Personally I think the image of “redo vector present in the undo” might cause bafflement and dropped jaws, but “redo vector defined by the undo” could go down very well.

      Comment by Jonathan Lewis — April 25, 2015 @ 10:57 am GMT Apr 25,2015 | Reply

  9. Thanks Jonathan! its time to read your Oracle Core :)

    Comment by Henish — April 23, 2015 @ 6:23 pm GMT Apr 23,2015 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.