Re: rollback and redo questions

Thomas Kyte wrote:

> A copy of this was sent to Doug Cowles <dcowles_at_bigfoot.com>
> (if that email address didn't require changing)
> On Tue, 03 Nov 1998 23:29:20 -0500, you wrote:
>
> >I jus ran a canned script on my rollback segments and got the following
> >info
> >at the bottom of the report:
> >
> >"Rollback contention for system undo header = 0% (Total requests = 36)
> >
> >Rollback contention for system undo block = 0% (Total requests = 36)
> >Rollback contention for undo header = 100% (Total requests =
> >36)
> >Rollback contention for undo block = 0% (Total requests = 36)
> >If percentage is more than 1%, create more rollback segments "
> >
> >Question: What's with the undo header, and what is it? Can anyone
> >explain
> >why it's contention is sitting at 100%, whereas the others are at 0?
> >
>
> its rollback (undo) headers. In theory, the above output indicates rollback
> segment contention however, without seeing the canned script we cannot really
> tell. For all we know, the canned script is:
>
> select 'rollback contention for undo header = 100%' from dual;
>
> we need to see the question that is being asked of the database -- not just the
> answer...
>

The actual line of code is " select 3 so, 'Rollback conention for undo header

        (round(max(decode(class, 'undo header', count, 0 )) /
           sum(count)+0.000000000001,4))*100||'%'||
           '     (Total requests = '||sum(count)||')'
            from v$waitstat

The acutal output from v$waitstat is
CLASS                   COUNT       TIME
------------------ ---------- ----------
data block                  0          0
sort block                  0          0
save undo block             0          0
segment header              0          0
save undo header            0          0
free list                   0          0
system undo header          0          0
system undo block           0          0
undo header                36          0
undo block                  0          0

So it's really taking 36 and dividing by 36 and giving me 100%. This can't be right.
Any thoughts? It look like it supposed to take the max undo entry header count, and divide it my the sum of the counts to come up with a conention percentage. Is this right? Will there be more that one entry for undo header in certain circumstances?

> >Also, supplementary question - as a transaction begins, it grabs a
> >rollback
> >segment, right?
>
> It doesn't 'grab' as 'grab' implies "it owns it". It is assigned, along with
> other transactions, to a single rollback segment that it will use during the
> course of the transaction.
>
> It will place UNDO information here.
>
> >At the same time, it places this uncommitted
> >information in
> >whatever is the current redo log file, right?
>
> Into the current online redo log file it will place REDO information, after
> images of information. It may place this data into many of the online redo log
> files over time.
>
> > Now, if you should lose
> >the database,
> >it will make use of any redo information in the redo log file, rollback
> >any uncommitted
> >transactions, and roll forward any committed transactions, right?
>
> It'll actually roll forward from redo first ( recovering the rollback segements
> as well -- they are part of the redo information, they are logged) and then
> rollback any uncommitted transactions from the rollback segements (which just
> got recovered themselves).
>

What's involved in recovering a rollback segment with UNDO information in it?If it isn't committed, isn't it safe to ignore? Or is it a matter of taking the REDO information and comparing it against the UNDO information.

> >So,
> >if the redo
> >log is smaller than the rollback segment which I'll assume is being
> >maxed out,
> >can't you potentially lose information IF, you have written a committed
> >transaction
> >to a redo log, there is a log switch for the write of the uncommitted
> >transaction (using the rollback segment), and the checkpoint is not
> >complete, and the database goes down, right?
> >
>
> If the checkpoint is not complete, we will *not* reuse a log file that contains
> log entries for uncheckpointed data.
>
> Everytime a log switch occurrs -- a checkpoint is fired (among other times --
> other events may fire a checkpoint but a log switch ALWAYS does). If the data
> this checkpoint was flushing to disk *does not complete* by the time we want to
> reuse the log file that fired that checkpoint, you will get the infamous
> "checkpoint not complete -- cannot allocate new log" message in your alert file
> and the system will suspend temporarily -- waiting for that checkpoint to
> finish. The scenario you describe above is one the system will not allow itself
> to get into.

What if the system goes down while it's suspended?

>
>
> >I know that sounds somewhat convaluted, but hopefully someone will know
> >what
> >I mean. Just to confirm my understanding, when a transaction commits,
> >it will sit
> >in the redo logs until a checkpoint occurs, at which point it is made
> >permanent, right?
> >
>
> When you commit, its permanent. It might not be on disk in the DATAFILES but
> its on disk (in the redo perhaps). It is however, permanent -- its committed,
> its as good as done.
>
> >So, I guess what I'm asking here is , under what circumstance of the
> >above events
> >could you actually lose something, if you get my intention.
> >
> >- Dc.
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Government
> Herndon VA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
> Anti-Anti Spam Msg: if you want an answer emailed to you,
> you have to make it easy to get email to you. Any bounced
> email will be treated the same way i treat SPAM-- I delete it.
Received on Wed Nov 04 1998 - 10:20:39 CST