Re: what's the internal transaction table for....
From: Ken Quirici <kquirici_at_yahoo.com>
Date: Tue, 9 Feb 2010 07:44:54 -0800 (PST)
Message-ID: <6107c7aa-4570-4cab-b5eb-6d6abd484e63_at_d34g2000vbl.googlegroups.com>
On Feb 9, 9:17 am, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> 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!- Hide quoted text -
>
> > - Show quoted text -
>
> Oracle does not overwrite the end of the rbs (now undo) segment if the
> data is still in use. When the data is in use it extends the
> segment. The manual explains this fairly well.
>
> Reference the manual name, version, chapter, and topic if using a hard
> or local copy of the manual so I or someone else can find the section
> you are asking about. If using one of the online versions please post
> the link along with enough information to allow finding the exact
> section in question.
>
> Oracle keeps origional copies of data in undo. Oracle keeps track of
> the undo via the undo segment header blocks and through the ITL enties
> in the changed table blocks. Oracle marks changed table blocks with
> the SCN of the change so that when the block is accessed Oracle can
> determine if it needs to create a read consistent view of the block.
>
> The basic concept is not difficutl but the details get a little
> involved. As I posted Jonathan Lewis has discussed undo processing in
> detail but if you have questions on the manual material you should put
> those to rest before seeking it out.
>
> HTH -- Mark D Powell --
Date: Tue, 9 Feb 2010 07:44:54 -0800 (PST)
Message-ID: <6107c7aa-4570-4cab-b5eb-6d6abd484e63_at_d34g2000vbl.googlegroups.com>
On Feb 9, 9:17 am, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> 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!- Hide quoted text -
>
> > - Show quoted text -
>
> Oracle does not overwrite the end of the rbs (now undo) segment if the
> data is still in use. When the data is in use it extends the
> segment. The manual explains this fairly well.
>
> Reference the manual name, version, chapter, and topic if using a hard
> or local copy of the manual so I or someone else can find the section
> you are asking about. If using one of the online versions please post
> the link along with enough information to allow finding the exact
> section in question.
>
> Oracle keeps origional copies of data in undo. Oracle keeps track of
> the undo via the undo segment header blocks and through the ITL enties
> in the changed table blocks. Oracle marks changed table blocks with
> the SCN of the change so that when the block is accessed Oracle can
> determine if it needs to create a read consistent view of the block.
>
> The basic concept is not difficutl but the details get a little
> involved. As I posted Jonathan Lewis has discussed undo processing in
> detail but if you have questions on the manual material you should put
> those to rest before seeking it out.
>
> HTH -- Mark D Powell --
also apologies for asking before reading; seems to be a bug I caught somewhere, possibly at birth Received on Tue Feb 09 2010 - 09:44:54 CST