Re: what's the internal transaction table for....
From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Mon, 8 Feb 2010 13:03:57 -0800 (PST)
Message-ID: <dfd3ac8f-ea1d-4773-8c8a-12b744b59b3e_at_f8g2000yqn.googlegroups.com>
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!
Date: Mon, 8 Feb 2010 13:03:57 -0800 (PST)
Message-ID: <dfd3ac8f-ea1d-4773-8c8a-12b744b59b3e_at_f8g2000yqn.googlegroups.com>
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 -- Received on Mon Feb 08 2010 - 15:03:57 CST