what's the internal transaction table for....
in [Oracle]
|
From: Mark D Powell on 9 Feb 2010 09:17 On Feb 8, 10:29 pm, Ken Quirici <kquir...(a)yahoo.com> wrote: > On Feb 8, 4:03 pm, Mark D Powell <Mark.Powe...(a)hp.com> wrote: > > > > > > > On Feb 8, 2:53 pm, Ken Quirici <kquir...(a)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 --
From: Ken Quirici on 9 Feb 2010 10:44 On Feb 9, 9:17 am, Mark D Powell <Mark.Powe...(a)hp.com> wrote: > On Feb 8, 10:29 pm, Ken Quirici <kquir...(a)yahoo.com> wrote: > > > > > > > On Feb 8, 4:03 pm, Mark D Powell <Mark.Powe...(a)hp.com> wrote: > > > > On Feb 8, 2:53 pm, Ken Quirici <kquir...(a)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 Excellent ref - should keep me busy for awhile - thanks! but if you have questions on the manual material you should put > those to rest before seeking it out. > > HTH -- Mark D Powell --
From: Ken Quirici on 9 Feb 2010 10:44 On Feb 9, 9:17 am, Mark D Powell <Mark.Powe...(a)hp.com> wrote: > On Feb 8, 10:29 pm, Ken Quirici <kquir...(a)yahoo.com> wrote: > > > > > > > On Feb 8, 4:03 pm, Mark D Powell <Mark.Powe...(a)hp.com> wrote: > > > > On Feb 8, 2:53 pm, Ken Quirici <kquir...(a)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
First
|
Prev
|
Pages: 1 2 Prev: Best way to duplicate 11g table and range partition Next: Unable to disable tracing - XE |