Oracle Server - Question on REDO & SCN

Author

Question on REDO & SCN

Pradeep

2005-11-24, 9:23 am

I am presently reading Expert one-on-one oracle, but have a few
questions:

1. After a DML statement all the required redo is generated. But if i
commit there is a small amount of redo generated. What is this redo ?
(i think that this redo is is generated after the undo is updated with
the SCN on COMMIT)

2. delay block cleanout happens by getting the status of the
transaction from the UNDO. In case the undo is overwritten, then it
gives an error. My questions is why does it not take the SCN for that
transaction from the redo (if not avaible in undo) and do block
cleanout?

Thanks

Pradeep

Jonathan Lewis

2005-11-24, 9:23 am

"Pradeep" <agarwalp@eeism.com> wrote in message
news:1132843965.534875.93730@g47g2000cwa.googlegroups.com...
>I am presently reading Expert one-on-one oracle, but have a few
> questions:
>
> 1. After a DML statement all the required redo is generated. But if i
> commit there is a small amount of redo generated. What is this redo ?
> (i think that this redo is is generated after the undo is updated with
> the SCN on COMMIT)
>
The redo is the bit that describes the change
to the undo segment header block that marks
the transaction as complete (and possibly puts
the current undo block into the free block pool)
When a normal data block is touched on commit,
(and in that case it isn't a proper cleanout) the redo
for that is not generated. Proper cleanout is generated
by the next transaction that changes the block - or by
a delayed block cleanout.

> 2. delay block cleanout happens by getting the status of the
> transaction from the UNDO. In case the undo is overwritten, then it
> gives an error. My questions is why does it not take the SCN for that
> transaction from the redo (if not avaible in undo) and do block
> cleanout?
>

Because NOTHING reads the online redo log
under normal circumstances. It is important that
the redo log be written as rapidly as possible, so
it is effectively a 'write only' stream.

--
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005

Pradeep

2005-11-24, 9:23 am

Thanks for the answer Jonathan
[color=darkred]

Isn't this the SCN?

Jonathan Lewis

2005-11-24, 11:23 am

"Pradeep" <agarwalp@eeism.com> wrote in message
news:1132845637.997279.213440@o13g2000cwo.googlegroups.com...
> Thanks for the answer Jonathan
>
>
> Isn't this the SCN?
>

No, the SCN is just a number being counted
up somewhere in memory.

To start a transaction you acquire a row (aka slot)
in the transaction table that exists in each undo segment
header block. This is the thing that your TX lock is
locking. You update this row to show that your
transaction is running, and to show the start SCN
and a couple of other bits of information.

To end a transaction, you update the row with
the commit SCN, and flag the row to show that
it is free. It is this change that is 'just another block
change' and therefore described by a bit of redo,
which the manuals (incorrectly) call the 'commit record'.

--
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005

Pradeep

2005-11-25, 3:23 am

Thanks a lot Jonathan

-Pradeep

Jonathan Lewis wrote:
> "Pradeep" <agarwalp@eeism.com> wrote in message
> news:1132845637.997279.213440@o13g2000cwo.googlegroups.com...
>
>
> No, the SCN is just a number being counted
> up somewhere in memory.
>
> To start a transaction you acquire a row (aka slot)
> in the transaction table that exists in each undo segment
> header block. This is the thing that your TX lock is
> locking. You update this row to show that your
> transaction is running, and to show the start SCN
> and a couple of other bits of information.
>
> To end a transaction, you update the row with
> the commit SCN, and flag the row to show that
> it is free. It is this change that is 'just another block
> change' and therefore described by a bit of redo,
> which the manuals (incorrectly) call the 'commit record'.
>
>
> --
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
> Cost Based Oracle: Fundamentals
>
> http://www.jlcomp.demon.co.uk/appearances.html
> Public Appearances - schedule updated 4th Nov 2005

Pradeep

2005-11-25, 3:23 am

Is it because of this redo generated after each commit that we should
not commit in a loop, rather commit after the transaction

Jonathan Lewis

2005-11-25, 7:23 am

"Pradeep" <agarwalp@eeism.com> wrote in message
news:1132907250.204580.84100@o13g2000cwo.googlegroups.com...
> Is it because of this redo generated after each commit that we should
> not commit in a loop, rather commit after the transaction
>

The first reason for committing after
the transaction is data correctness.

The second reason for committing
after the transaction is for restartability.

The third reason committing after the
transaction - and especially for not
committing inside the loop - is that
the various overheads from a large
number of very small transactions
(loop commits being the prime example)
can be dramatic.

--
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005

Mladen Gogala

2005-11-27, 3:23 am

On Thu, 24 Nov 2005 16:07:43 +0000, Jonathan Lewis wrote:

> It is this change that is 'just another block
> change' and therefore described by a bit of redo,
> which the manuals (incorrectly) call the 'commit record'.

But there must be something to distinguish this record from the "normal"
change records. How would recovery process know which transaction has
committed? When applying redo ("roll forward") finishes, uncommitted
changes are rolled back. Without a clear delimiter, it is a bit unclear
how can recovery know which ones should be rolled back? I believe that
there must be some kind of flag saying "I am a commit record for
transaction id=...".

--
http://www.mgogala.com

Jonathan Lewis

2005-11-27, 3:23 am

"Mladen Gogala" <gogala@sbcglobal.net> wrote in message
news:pan.2005.11.27.06.32.46.429428@sbcglobal.net...
> On Thu, 24 Nov 2005 16:07:43 +0000, Jonathan Lewis wrote:
>
>
> But there must be something to distinguish this record from the "normal"
> change records. How would recovery process know which transaction has
> committed? When applying redo ("roll forward") finishes, uncommitted
> changes are rolled back. Without a clear delimiter, it is a bit unclear
> how can recovery know which ones should be rolled back? I believe that
> there must be some kind of flag saying "I am a commit record for
> transaction id=...".
>
> --
> http://www.mgogala.com
>

You didn't quote the sentence before - which explains
that your update IS the 'commit record'

> To end a transaction, you update the row (in
the transaction table in the undo segment) with
> the commit SCN, and flag the row to show that
> it is free.

The following mess is from that table, 1st row:
index state cflags wrap# uel scn dba parent-xid nub stmt_num

------------------------------------------------------------------------------------------------

0x00 9 0x00 0x16af6 0x002a 0x0000.06c04359 0x00000000 0x0000.000.00000000
0x00000000 0x00000000

Note the state is 9 - when I start a transaction, I grab
one of these and change the state to 10. When I commit,
I change the status back to 9. (I do make several other
changes but I'm ignoring them).

The change vector for the last change IS the "commit record".
When rollforward completes, smon checks the state flags on
all transaction table slots looking for 10's and rolls those back.

--
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 22nd Nov 2005

Sponsored Links