Daniel, I have just finished reading your document on UNdo Internals and Tims "Cats, Dogs and ORA-1555s".
Thanks for the documents they were both great.
There is something I don't understand and I am not sure about it.
You have said below:
"When a transaction is bound to an undo segment, it allocates a slot in the
tx table."
I thought that transactions were no longer bound to UNDO segments and
this was one of the improvements in 9i.
I have pasted an extract from Tims document:
Into the future: Oracle9i UNDO tablespaces.
As you may have observed, one of the reasons space management for rollback
segments is so difficult is due
to the fact that a transaction is assigned irrevocably to a single rollback
segment.
Each rollback segment can only handle a finite number of transactions
(due to block-level contention for the transaction table in the header
block),
so there must be multiple rollback segments to handle potentially large
numbers of transactions.
UNDO tablespaces in Oracle9i allow an entire tablespace to become a single,
large pool of undo blocks for use by any and all transactions.
Instead of having available space carved up into many smaller rollback
segments,
a single transaction can utilize all of the space in the UNDO tablespace,
if necessary. Many, many transactions can share that space also,
because the controlling transaction table is no longer contained in a
single database block,
avoiding contention for this important resource.
I guess I am jumping to the wrong assumption in Tims extract - can you
clarify it for me.
thanks, stephen
Phone: 01737 27 5564
[EMAIL PROTECTED]
Daniel Fink
<[EMAIL PROTECTED] To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
un.com> cc:
Sent by: Subject: Re: Snapshot too old in undo
tablespace in 9i?
[EMAIL PROTECTED]
ity.com
05/08/03 22:29
Please respond
to ORACLE-L
Abraham,
Setting the retention time may not solve the problem. One of the ways that
an ORA-1555 can be triggered is when the transaction table slot is
overwritten. This is caused by having many small, serial transactions in
the database while the export is running.
In each undo segment (or rollback segment), there is a structure called the
transaction(tx) table. This contains transaction - undo segment
binding/status information. The number of slots is block-size dependent. I
don't recall the exact numbers. When a transaction is bound to an undo
segment, it allocates a slot in the tx table. This provides the links
between the data/index block and the undo entries. If the data block points
to a slot that has been reused, there is not a way to reconstruct
the data, so it throws a 1555. The undo information may be preserved in the
segments, but the link necessary has been lost.
For illustration purposes, let's say you have 10 undo segments and each of
them has a transaction table containing 40 slots. You have 400 slots
available. If you have 100 transactions per minute (no more than 10
concurrently (so as to prevent new undo segments being created)), a slot
will be reused every 4 minutes.
If I have not sufficiently bored you, more detail can be found at
www.optimaldba.com/library.html. Look for the documents on Undo Internals
and Automatic Undo Internals. Tim Gorman also has a great paper called
"Cats, Dogs and ORA-1555s" on his site (www.evdbt.com).
Daniel
"Guerra, Abraham J" wrote:
>
> Thanks.
>
> Abraham
>
> -----Original Message-----
> Sent: Tuesday, August 05, 2003 12:35 PM
> To: Multiple recipients of list ORACLE-L
>
> retention time --- set it for a couple of hours longer than you think the
> export will take.
>
>
> "Guerra, Abraham
> J" <AGUERRA To: Multiple
recipients of list ORACLE-L <[EMAIL PROTECTED]>
> @amfam.com> cc:
> Sent by: Subject: Snapshot too old
in undo tablespace in 9i?
> ml-errors
>
>
> 08/05/2003 01:24
> PM
> Please respond
> to ORACLE-L
>
>
>
> Hello Group,
>
> I just upgraded a database to Oracle 9.2.0.3 from 8.1.7. I created an
> undo tablespace with 10 (default) undo segments... however, during an
> export I got the following message:
>
> ORA-01555: snapshot too old: rollback segment number 15 with name "
> _SYSSMU15$" too small
>
> I thought this was a thing of the past... According to the
documentation,
> if an undo segment gets full, it starts using idle ones... also, the
undo
> tablespace still had a lot of room to grow...
>
> Any insights will be welcome.
>
> Thanks
>
> Abraham Guerra
> Oracle DBA
> American Family Insurance
>
> << Attachment Removed : Notebook.jpg >>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Thomas Day
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Guerra, Abraham J
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
******************************************************
This insert confirms that this email message
and all associated attachments have been
swept by TotalFinaElf using Network Associates
VirusScan for the presence of computer
viruses during the receipt of this message.
******************************************************
(See attached file: daniel.fink.vcf)
**********************************************************************
This email contains information which is confidential. It is for the
exclusive use of the addressee. If you are not an addressee please
contact us immediately on 01737 275500. Please note that any
distribution, copying or use of this communication or its contents is
prohibited.
This footnote also confirms that this email message and all associated
attachments have been swept by Network Associates VirusScan for the
presence of computer viruses.
**********************************************************************
daniel.fink.vcf
Description: Binary data
