RE: ** can two processes use the same rollback extent
1)rollback segment will not shrink. Correct, assuming you do not shrink it manually. 2)It will keep growing till tablespace fills up. Correct assuming that you have a SQL statement that causes the rollback segment to grow in such a way and that you have no constraints on the rollback segments to prevent unconstrained growth (e.g. maxextents). Note that this may not be a good thing... if you have one transaction that causes one rbs to grow really large, that may prevent growth of other rbs's. Unless you are manually assigning rollback segments to every transaction, that could have negative consequences. It is, IMHO, best to have use automated undo, if possible, then you don't need to worry about these things. If you can't use automated undo, then create a nice big tablespace, but also use optimal, setting it to a reasonable size. 3)And there will be no 'snapshot too old' errors? Wrong. Even with large rollback segments, 1555's are possible depending on a number of things. Robert G. Freeman Technical Management Consultant TUSC - The Oracle Experts www.tusc.com 630.819.9077 Cell (It's everywhere that I am!) Author of several books you can find on Amazon.com! -Original Message-From: A Joshi [mailto:[EMAIL PROTECTED]Sent: Sunday, December 28, 2003 12:39 AMTo: Multiple recipients of list ORACLE-LSubject: Re: ** can two processes use the same rollback extent Hi, A simple rollback segment question. If I do not set a optimal thencan I assume following 1)rollback segment will not shrink. 2)It will keep growing till tablespace fills up. 3)And there will be no 'snapshot too old' errors? ThanksA Joshi [EMAIL PROTECTED] wrote: Hi, One rollback segment can be used by multiple transactions/processes but what about the extents? Is it safe to assume that only one transaction/process can use one extent? What is a good way to size the extent for warehousing tasks? Or guess the rollback generated and size needed. Thank You Do you Yahoo!?Protect your identity with Yahoo! Mail AddressGuard Do you Yahoo!?Protect your identity with Yahoo! Mail AddressGuard
RE: ** can two processes use the same rollback extent
1.) Correct, except that the DBA can always shrink it manually. 2.) Well, that's true, but a rollback segment w/ optimal set could conceivably grow to fill the tablespace as well. It depends on the size of the rollback tablespace and the size(s) of your transaction(s). The reason that it's perceived that not having optimal set exacerbates it is probably due to the fact that without optimal, the rollback segments never shrink. So, one occurrance of a very large or runaway transaction can bloat a particular rollback segment. That permanently decreases the free space in the tablespace. Over time, multiple rollback segments could become bloated, and eventually, you run out of space. In the case where optimal is set, this is less likely to happen, because the rollback segments will shrink, and the cumulative effect never occurs. 3.) Not at all. ORA-1555s can occur whenever the size or number of rollback segments is inadequate. Systems can be constructed where ORA-1555 is inevitable. (For example processes doing lots of DML concurrent with processes that execute long running queries.) There's been a *LOT* written on ORA-1555 over the years. If you have specific problems or questions, start w/ MetaLink if you have access. If not, check out these URLs: http://www.jlcomp.demon.co.uk/faq/snapshot.html http://www.jlcomp.demon.co.uk/faq/settrans.html Hope that helps, -Mark -Original Message- From: A Joshi [mailto:[EMAIL PROTECTED] Sent: Sun 12/28/2003 1:39 AM To: Multiple recipients of list ORACLE-L Cc: Subject:Re: ** can two processes use the same rollback extent Hi, A simple rollback segment question. If I do not set a optimal then can I assume following 1) rollback segment will not shrink. 2) It will keep growing till tablespace fills up. 3) And there will be no 'snapshot too old' errors? Thanks A Joshi [EMAIL PROTECTED] wrote: Hi, One rollback segment can be used by multiple transactions/processes but what about the extents? Is it safe to assume that only one transaction/process can use one extent? What is a good way to size the extent for warehousing tasks? Or guess the rollback generated and size needed. Thank You - Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard - Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard winmail.dat
Re: ** can two processes use the same rollback extent
Hi, A simple rollback segment question. If I do not set a optimal thencan I assume following 1)rollback segment will not shrink. 2)It will keep growing till tablespace fills up. 3)And there will be no 'snapshot too old' errors? ThanksA Joshi [EMAIL PROTECTED] wrote: Hi, One rollback segment can be used by multiple transactions/processes but what about the extents? Is it safe to assume that only one transaction/process can use one extent? What is a good way to size the extent for warehousing tasks? Or guess the rollback generated and size needed. Thank You Do you Yahoo!?Protect your identity with Yahoo! Mail AddressGuard Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
Re: ** can two processes use the same rollback extent
A single block can only be 'owned' by one transaction at a time, but when a transaction commits, it may put its last undo block into that segment's 'free pool' list if there is a lot of free space left in the block so that it can be made available for new transactions . (Gaja Vaidyanatha quoted 400 bytes as the limit). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 3:59 AM i can answer the first part of your question.. YES, two transactions can write on the same extent of an RBS, i think the restriction is at the block level, this was true until 8i, might have changed in 9 + -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
Re: ** can two processes use the same rollback extent
Yes Jonathan: I think that 400 byte concept came from one of the TPC benchmarks during Oracle 7 (or ORacle 6) days where the average undo size for a transcation was around 380+ bytes. So they had rounded that to 400 bytes and a maximum of 5 blocks will be linked to the freelist in the undo segment header. THis is called 'free extent pool' and the structure and the blocks are clearly visible in the undo header dumps. Regards, K Gopalakrishnan Bangalore, INDIA - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 1:59 PM A single block can only be 'owned' by one transaction at a time, but when a transaction commits, it may put its last undo block into that segment's 'free pool' list if there is a lot of free space left in the block so that it can be made available for new transactions . (Gaja Vaidyanatha quoted 400 bytes as the limit). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 03, 2003 3:59 AM i can answer the first part of your question.. YES, two transactions can write on the same extent of an RBS, i think the restriction is at the block level, this was true until 8i, might have changed in 9 + -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: K Gopalakrishnan 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).
Re: ** can two processes use the same rollback extent
IIRC, a segment will not wrap into an extent that has an active transaction in it, but rather allocate a new extent. So the answer is...it depends. Daniel Fink A Joshi wrote: Hi, One rollback segment can be used by multiple transactions/processes but what about the extents? Is it safe to assume that only one transaction/process can use one extent? What is a good way to size the extent for warehousing tasks? Or guess the rollback generated and size needed. Thank You Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
Re: ** can two processes use the same rollback extent
And to further refine that statement and IIRC, a transaction will not use an extent E, that already has an active transaction in it, when the transaction has filled up the previous extent S, and is now looking to wrap into a new extent E. However, a fresh transaction can always be allocated space in an extent that already has an active transaction in it. Regards Raj Daniel Fink [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] un.COM cc: Sent by: Subject: Re: ** can two processes use the same rollback extent [EMAIL PROTECTED] ity.com 12/03/2003 12:04 PM Please respond to ORACLE-L IIRC, a segment will not wrap into an extent that has an active transaction in it, but rather allocate a new extent. So the answer is...it depends. Daniel Fink A Joshi wrote: Hi, One rollback segment can be used by multiple transactions/processes but what about the extents? Is it safe to assume that only one transaction/process can use one extent? What is a good way to size the extent for warehousing tasks? Or guess the rollback generated and size needed. Thank You -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: 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).
** can two processes use the same rollback extent
Hi, One rollback segment can be used by multiple transactions/processes but what about the extents? Is it safe to assume that only one transaction/process can use one extent? What is a good way to size the extent for warehousing tasks? Or guess the rollback generated and size needed. Thank You Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
Re: ** can two processes use the same rollback extent
i can answer the first part of your question.. YES, two transactions can write on the same extent of an RBS, i think the restriction is at the block level, this was true until 8i, might have changed in 9 + - Original Message - From: A Joshi To: Multiple recipients of list ORACLE-L Sent: Wednesday, December 03, 2003 9:09 AM Subject: ** can two processes use the same rollback extent Hi, One rollback segment can be used by multiple transactions/processes but what about the extents? Is it safe to assume that only one transaction/process can use one extent? What is a good way to size the extent for warehousing tasks? Or guess the rollback generated and size needed. Thank You Do you Yahoo!?Protect your identity with Yahoo! Mail AddressGuard
