Re: what's the internal transaction table for....
From: Ken Quirici <kquirici_at_yahoo.com>
Date: Tue, 9 Feb 2010 06:00:49 -0800 (PST)
Message-ID: <97075b47-0339-4449-a013-a9206b3e81d4_at_o28g2000yqh.googlegroups.com>
On Feb 8, 10:29 pm, Ken Quirici <kquir..._at_yahoo.com> wrote:
> On Feb 8, 4:03 pm, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
>
>
>
>
>
> > On Feb 8, 2:53 pm, Ken Quirici <kquir..._at_yahoo.com> wrote:
>
> > > The 10g discussion of transaction commitment has this:
>
> > > The internal transaction table for the associated undo tablespace that
> > > the transaction has committed, and the corresponding unique system
> > > change number (SCN) of the transaction is assigned and recorded in the
> > > table.
>
> > > which the 11g manual corrects to:
>
> > > The internal transaction table for the associated undo tablespace
> > > RECORDS [my uppercase] that the transaction has committed, etc.
>
> > > What is this internal transaction table? I can't find reference to it
> > > anywhere else. It seems as though it
> > > would be important in deciding how long to keep rollback segments
> > > around, but I could be wrong there,
> > > since they seem to be kept around forever, or until they get
> > > overwritten, whichever comes first.
>
> > > Thanks for any illumination!
>
> > I believe that the material is referring to the Interested Transaction
> > List, ITL, stored in each table block to keep track of changes by
> > pointing to the undo segment that contains the undo for the
> > transaction. The table parameter initrans controls how many of these
> > areas are pre-allocated to the blocks. Each ITL is 23 bytes in length
> > plus I believe the ITL is preceeded by a length or usage byte so the
> > cost is 24 bytes each space wise.
>
> > Jonathan Lewis has written in detail on Oracle usage of the ITL. You
> > can find his web site via a search.
> > You can see the ITL in a block dump.
>
> > HTH -- Mark D Powell --
>
> I think what the documentation I was quoting was referring to was
> something
> stored in the rollback segments. There was an illustration which
> showed
> Oracle ensuring query data consistency by getting blocks from rollback
> segments with SCN's less than (or equal to except it only illustrated
> SCN's less than) what was the current SCN when the query started. It
> can only be these internal transaction tables, it seems, that have
> this
> SCN *in* the rollback segments.
>
> And I also think Oracle 'blindly' overwrites rollback segments when it
> gets to the end of allocating new rollback segments up to the amount
> specified in the init parameters as max. There appears to be no
> intelligence involved in the overwrite or not-overwrite process
> except,
> if you run out of space, start from the beginning again.
>
> This is much less sophisticated than I had thought it might be. ON the
> other hand, it works nearly all the time!
Date: Tue, 9 Feb 2010 06:00:49 -0800 (PST)
Message-ID: <97075b47-0339-4449-a013-a9206b3e81d4_at_o28g2000yqh.googlegroups.com>
On Feb 8, 10:29 pm, Ken Quirici <kquir..._at_yahoo.com> wrote:
> On Feb 8, 4:03 pm, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
>
>
>
>
>
> > On Feb 8, 2:53 pm, Ken Quirici <kquir..._at_yahoo.com> wrote:
>
> > > The 10g discussion of transaction commitment has this:
>
> > > The internal transaction table for the associated undo tablespace that
> > > the transaction has committed, and the corresponding unique system
> > > change number (SCN) of the transaction is assigned and recorded in the
> > > table.
>
> > > which the 11g manual corrects to:
>
> > > The internal transaction table for the associated undo tablespace
> > > RECORDS [my uppercase] that the transaction has committed, etc.
>
> > > What is this internal transaction table? I can't find reference to it
> > > anywhere else. It seems as though it
> > > would be important in deciding how long to keep rollback segments
> > > around, but I could be wrong there,
> > > since they seem to be kept around forever, or until they get
> > > overwritten, whichever comes first.
>
> > > Thanks for any illumination!
>
> > I believe that the material is referring to the Interested Transaction
> > List, ITL, stored in each table block to keep track of changes by
> > pointing to the undo segment that contains the undo for the
> > transaction. The table parameter initrans controls how many of these
> > areas are pre-allocated to the blocks. Each ITL is 23 bytes in length
> > plus I believe the ITL is preceeded by a length or usage byte so the
> > cost is 24 bytes each space wise.
>
> > Jonathan Lewis has written in detail on Oracle usage of the ITL. You
> > can find his web site via a search.
> > You can see the ITL in a block dump.
>
> > HTH -- Mark D Powell --
>
> I think what the documentation I was quoting was referring to was
> something
> stored in the rollback segments. There was an illustration which
> showed
> Oracle ensuring query data consistency by getting blocks from rollback
> segments with SCN's less than (or equal to except it only illustrated
> SCN's less than) what was the current SCN when the query started. It
> can only be these internal transaction tables, it seems, that have
> this
> SCN *in* the rollback segments.
>
> And I also think Oracle 'blindly' overwrites rollback segments when it
> gets to the end of allocating new rollback segments up to the amount
> specified in the init parameters as max. There appears to be no
> intelligence involved in the overwrite or not-overwrite process
> except,
> if you run out of space, start from the beginning again.
>
> This is much less sophisticated than I had thought it might be. ON the
> other hand, it works nearly all the time!
One possible place for intelligence in the rollback-segment allocation/
usage
process is the allocation of rollback-segments. How does Oracle know
when
to allocate more rollback-segments, or does it blindly answer all
requests
for a rollback space (i.e. all attempts to write to rollback space)
with a new
segment until it runs out of space, and which point it starts
overwriting?
Received on Tue Feb 09 2010 - 08:00:49 CST