Re: ora-1555 under automatic undo management (resend ?)
> I'm having an ora-1555 under Oracle9 database and not > sure what I can do to get rid of it. I had some > recollecions from Oracle8 days , but the things like > adding a new rollback segment or shrinking the > segments I don't think are applicable under the auto > undo management. Besides separating the long queries > from batch programs, is ther anything that I can do > here? Hi Gene, Increase UNDO_RETENTION (which determines how long Oracle will attempt to keep your undo before being overwritten) and/or increase the size of your undo tablespace (to ensure Oracle will succeed in meeting your undo_retention target). Check out V%UNDOSTAT to see how it's going. Cheers Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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).
ora-1555 under automatic undo management
Hi all: I'm having an ora-1555 under Oracle9 database and not sure what I can do to get rid of it. I had some recollecions from Oracle8 days , but the things like adding a new rollback segment or shrinking the segments I don't think are applicable under the auto undo management. Besides separating the long queries from batch programs, is ther anything that I can do here? thanks Gene __ Do you Yahoo!? Yahoo! Hotjobs: Enter the "Signing Bonus" Sweepstakes http://hotjobs.sweepstakes.yahoo.com/signingbonus -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei 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: Bug with automatic undo management?
they eventually said its data dictionary blocks in the old RBS, that were having delayed block cleanout. they couldnt be cleaned out because the tablespace was offline. its a bug. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, January 06, 2004 10:34 PM > In order to switch between undo management modes, you must shutdown and restart, so all sessions are disconnected. Therefore, session cleanout is not the culprit. > > OTS's answer does not make sense since the error is that the file can't be read, not an ORA-1555 as I would expect if the rbs can't be read. There are some tests I'd like to make as offlining the tablespace/datafile as part of the upgrade does not quite make sense. > > If you could send me more detailed info as to the exact steps (upgrade/shutdown/parameter changes/startup/etc), I'd like to reproduce the scenario to see if I can get the same error. > > Daniel Fink > > "Goulet, Dick" wrote: > > > Ryan, > > > > I agree with OTS. If you had dropped the rollback segment(s) before creating the object then you'd probably never have seen the error. I have found that SCN's do get cached by your session and sometimes don't get updated correctly. Therefore even though you had switched to undo management before creating the object it is possible that the block header on the object has an scn that predated your change. I don't know if it's delayed block cleanout or delayed session cleanout, but one of them is definitely the culprit. > > > > Dick Goulet > > Senior Oracle DBA > > Oracle Certified 8i DBA > > > > -Original Message- > > Sent: Wednesday, December 31, 2003 10:29 AM > > To: Multiple recipients of list ORACLE-L > > > > I have a TAR open on this and Im arguing with the Oracle tech support guy. > > > > Here is what happened. We upgraded an instance to 9i. Switched to automatic undo management. Set our undo parameters to point to a newly created undo tablespace. > > > > 1. took our old rollback tablespace(with rollback segments in it) offline. > > > > 2. I created some new objects. Fine. > > > > 3. Then I started creating indexes and doing selects. I would periodically get the following error: > > > > ORA-00604: error occurred at recursive SQL level 1 > > ORA-00376: file 3 cannot be read at this time > > ORA-01110: data file 3: '/rbs_01.dbf' > > > > 4. This is becaus that is the old rollback tablespace that was taken off line and is NOT indicated in the undo parameter as the undo tablespace. > > > > 5. Oracle support said the following. > > 'Most likely what happened is that when you went to create the index it encountered some information in the table in one of the block headers that needed to be retrieved/verified from the rollback segment due to delayed block cleanout. If we see that the rollback segment still exists we try to access it. (It doesn't matter whether we are using auto ot manual at this point.) If we can't access it then we throw an error. If we see that the rollback segment has been dropped then we know for sure that the information in the block header is old because we never drop rollback segments until all active transactions have completed.' > > > > 6. Not possible in my opinion. Since the object in question was created AFTER this rollback segment was taken offline. > > > > 7. We dropped the old rollback segment and it works fine now. > > > > Is this a bug? > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: <[EMAIL PROTECTED] > > 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: Goulet, Dick > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting services > >
Re: Bug with automatic undo management?
In order to switch between undo management modes, you must shutdown and restart, so all sessions are disconnected. Therefore, session cleanout is not the culprit. OTS's answer does not make sense since the error is that the file can't be read, not an ORA-1555 as I would expect if the rbs can't be read. There are some tests I'd like to make as offlining the tablespace/datafile as part of the upgrade does not quite make sense. If you could send me more detailed info as to the exact steps (upgrade/shutdown/parameter changes/startup/etc), I'd like to reproduce the scenario to see if I can get the same error. Daniel Fink "Goulet, Dick" wrote: > Ryan, > > I agree with OTS. If you had dropped the rollback segment(s) before > creating the object then you'd probably never have seen the error. I have found > that SCN's do get cached by your session and sometimes don't get updated correctly. > Therefore even though you had switched to undo management before creating the object > it is possible that the block header on the object has an scn that predated your > change. I don't know if it's delayed block cleanout or delayed session cleanout, > but one of them is definitely the culprit. > > Dick Goulet > Senior Oracle DBA > Oracle Certified 8i DBA > > -Original Message- > Sent: Wednesday, December 31, 2003 10:29 AM > To: Multiple recipients of list ORACLE-L > > I have a TAR open on this and Im arguing with the Oracle tech support guy. > > Here is what happened. We upgraded an instance to 9i. Switched to automatic undo > management. Set our undo parameters to point to a newly created undo tablespace. > > 1. took our old rollback tablespace(with rollback segments in it) offline. > > 2. I created some new objects. Fine. > > 3. Then I started creating indexes and doing selects. I would periodically get the > following error: > > ORA-00604: error occurred at recursive SQL level 1 > ORA-00376: file 3 cannot be read at this time > ORA-01110: data file 3: '/rbs_01.dbf' > > 4. This is becaus that is the old rollback tablespace that was taken off line and is > NOT indicated in the undo parameter as the undo tablespace. > > 5. Oracle support said the following. > 'Most likely what happened is that when you went to create the index it encountered > some information in the table in one of the block headers that needed to be > retrieved/verified from the rollback segment due to delayed block cleanout. If we > see that the rollback segment still exists we try to access it. (It doesn't matter > whether we are using auto ot manual at this point.) If we can't access it then we > throw an error. If we see that the rollback segment has been dropped then we know > for sure that the information in the block header is old because we never drop > rollback segments until all active transactions have completed.' > > 6. Not possible in my opinion. Since the object in question was created AFTER this > rollback segment was taken offline. > > 7. We dropped the old rollback segment and it works fine now. > > Is this a bug? > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > 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: Goulet, Dick > 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: Daniel W. Fink INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San
RE: Bug with automatic undo management?
Ryan, I agree with OTS. If you had dropped the rollback segment(s) before creating the object then you'd probably never have seen the error. I have found that SCN's do get cached by your session and sometimes don't get updated correctly. Therefore even though you had switched to undo management before creating the object it is possible that the block header on the object has an scn that predated your change. I don't know if it's delayed block cleanout or delayed session cleanout, but one of them is definitely the culprit. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, December 31, 2003 10:29 AM To: Multiple recipients of list ORACLE-L I have a TAR open on this and Im arguing with the Oracle tech support guy. Here is what happened. We upgraded an instance to 9i. Switched to automatic undo management. Set our undo parameters to point to a newly created undo tablespace. 1. took our old rollback tablespace(with rollback segments in it) offline. 2. I created some new objects. Fine. 3. Then I started creating indexes and doing selects. I would periodically get the following error: ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/rbs_01.dbf' 4. This is becaus that is the old rollback tablespace that was taken off line and is NOT indicated in the undo parameter as the undo tablespace. 5. Oracle support said the following. 'Most likely what happened is that when you went to create the index it encountered some information in the table in one of the block headers that needed to be retrieved/verified from the rollback segment due to delayed block cleanout. If we see that the rollback segment still exists we try to access it. (It doesn't matter whether we are using auto ot manual at this point.) If we can't access it then we throw an error. If we see that the rollback segment has been dropped then we know for sure that the information in the block header is old because we never drop rollback segments until all active transactions have completed.' 6. Not possible in my opinion. Since the object in question was created AFTER this rollback segment was taken offline. 7. We dropped the old rollback segment and it works fine now. Is this a bug? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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: Goulet, Dick 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).
Bug with automatic undo management?
I have a TAR open on this and Im arguing with the Oracle tech support guy. Here is what happened. We upgraded an instance to 9i. Switched to automatic undo management. Set our undo parameters to point to a newly created undo tablespace. 1. took our old rollback tablespace(with rollback segments in it) offline. 2. I created some new objects. Fine. 3. Then I started creating indexes and doing selects. I would periodically get the following error: ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 3 cannot be read at this time ORA-01110: data file 3: '/rbs_01.dbf' 4. This is becaus that is the old rollback tablespace that was taken off line and is NOT indicated in the undo parameter as the undo tablespace. 5. Oracle support said the following. 'Most likely what happened is that when you went to create the index it encountered some information in the table in one of the block headers that needed to be retrieved/verified from the rollback segment due to delayed block cleanout. If we see that the rollback segment still exists we try to access it. (It doesn't matter whether we are using auto ot manual at this point.) If we can't access it then we throw an error. If we see that the rollback segment has been dropped then we know for sure that the information in the block header is old because we never drop rollback segments until all active transactions have completed.' 6. Not possible in my opinion. Since the object in question was created AFTER this rollback segment was taken offline. 7. We dropped the old rollback segment and it works fine now. Is this a bug? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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: undo and insert
ITL = Interested Transaction List The entries are used for locking. See the following articles on ITL http://www.jlcomp.demon.co.uk/faq/locked_rows.html http://www.ixora.com.au/q+a/0010/13133621.htm Jared "Akshay Kumar" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 12/26/2003 10:54 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Re: undo and insert What is ITL ? - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, December 24, 2003 4:59 PM > > Just the previous version of the changed columns, > plus an overhead of about 80 bytes which relates > to ITLs, linked lists, operation descriptions etc. > > Bear in mind that undo relating to indexes is not > the same as undo relating to tables, though. An > update to an indexed column results in one index > entry being deleted (so the whole index entry > is coped to the undo) and another index entry > being inserted (which also means the whole (new) > index entry being copied to the undo). > > There is a statistic relating to undo size in v$sysstat/v$sesstat > in the most recent versions of Oracle. > > While a transaction is active, you can track it in v$transaction, > and there are two columns in that view giving you information > about the undo - used_urec (undo records created) and used_ublk > (undo block used). > > 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 24, 2003 8:44 PM > > > > I have a related question : What about update? In rollback segment : > > Will it store the whole row for before image or just the changed column > > and rowid. Is there a way to get the size of the rollback from some > > where in the database. or v$ views. Like we can get an idea about redo > > size from redo log files generated. Thank you > > > > > > -- > 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: Akshay Kumar 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: undo and insert
What is ITL ? - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, December 24, 2003 4:59 PM > > Just the previous version of the changed columns, > plus an overhead of about 80 bytes which relates > to ITLs, linked lists, operation descriptions etc. > > Bear in mind that undo relating to indexes is not > the same as undo relating to tables, though. An > update to an indexed column results in one index > entry being deleted (so the whole index entry > is coped to the undo) and another index entry > being inserted (which also means the whole (new) > index entry being copied to the undo). > > There is a statistic relating to undo size in v$sysstat/v$sesstat > in the most recent versions of Oracle. > > While a transaction is active, you can track it in v$transaction, > and there are two columns in that view giving you information > about the undo - used_urec (undo records created) and used_ublk > (undo block used). > > 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 24, 2003 8:44 PM > > > > I have a related question : What about update? In rollback segment : > > Will it store the whole row for before image or just the changed column > > and rowid. Is there a way to get the size of the rollback from some > > where in the database. or v$ views. Like we can get an idea about redo > > size from redo log files generated. Thank you > > > > > > -- > 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: Akshay Kumar 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: undo and insert
Just the previous version of the changed columns, plus an overhead of about 80 bytes which relates to ITLs, linked lists, operation descriptions etc. Bear in mind that undo relating to indexes is not the same as undo relating to tables, though. An update to an indexed column results in one index entry being deleted (so the whole index entry is coped to the undo) and another index entry being inserted (which also means the whole (new) index entry being copied to the undo). There is a statistic relating to undo size in v$sysstat/v$sesstat in the most recent versions of Oracle. While a transaction is active, you can track it in v$transaction, and there are two columns in that view giving you information about the undo - used_urec (undo records created) and used_ublk (undo block used). 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 24, 2003 8:44 PM > I have a related question : What about update? In rollback segment : > Will it store the whole row for before image or just the changed column > and rowid. Is there a way to get the size of the rollback from some > where in the database. or v$ views. Like we can get an idea about redo > size from redo log files generated. Thank you > > -- 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: undo and insert
And then there's the previous version of whichever ITL entry gets taken by the transaction doing the insert. 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 24, 2003 8:19 PM > For insert, in order to rollback, Oracle will still have to get the rowid of the new inserted rows, so that it can rollback when needed. > So there will still be undo. > > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, December 25, 2003 1:49 AM > > > > An undo segment is used to save the old value of data. > > For insert operation, there is no old data to be saved. > > So, there should be no undo generated. Right? > > > > > > Roger Xu > > Database Administrator > > Dr Pepper Bottling Company of Texas > > (972)721-8337 > > -- 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: undo and insert
I have a related question : What about update? In rollback segment : Will it store the whole row for before image or just the changed column and rowid. Is there a way to get the size of the rollback from some where in the database. or v$ views. Like we can get an idea about redo size from redo log files generated. Thank youzhu chao <[EMAIL PROTECTED]> wrote: For insert, in order to rollback, Oracle will still have to get the rowid of the new inserted rows, so that it can rollback when needed.So there will still be undo. Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
Re: undo and insert
For insert, in order to rollback, Oracle will still have to get the rowid of the new inserted rows, so that it can rollback when needed. So there will still be undo. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, December 25, 2003 1:49 AM > An undo segment is used to save the old value of data. > For insert operation, there is no old data to be saved. > So, there should be no undo generated. Right? > > > Roger Xu > Database Administrator > Dr Pepper Bottling Company of Texas > (972)721-8337 > > > > This email has been scanned for all viruses by the MessageLabs Email > Security System. For more information on a proactive email security > service working around the clock, around the globe, visit > http://www.messagelabs.com > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Roger Xu > 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: zhu chao 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).
undo and insert
An undo segment is used to save the old value of data. For insert operation, there is no old data to be saved. So, there should be no undo generated. Right? Roger Xu Database Administrator Dr Pepper Bottling Company of Texas (972)721-8337 This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Roger Xu 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).
Automatic Undo Management & Memory management in 9i
Fellow Listers, Could you please share your experience with Automatic Undo Management and Automatic Memory Management. Would you recommend it? One of the Sr. DBAs here suggested not to implement automatic memory management in 9.2.0.3 but wants to implement it in 9.2.0.4. His suggestion that things would have been fixed in newer version of oracle does'nt seem right to me. I have RTFM ed and seems simple for AUM ...as with memory management, I am a little hesitant and would like to consider your experiences. Thanks in advance. Murali. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: Murali_Pavuloori/[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: Problem with undo tablespace and snapshot too old
Is your particular problem reproducible? Without any changes to the cache size? Many times simply running the job again works. - Kirti --- John Kanagaraj <[EMAIL PROTECTED]> wrote: > Helmut, > > The SELECT article 'Understanding ORA-0155' by Tim Gorman is a must-read. > Get a subscription to IOUG, or ask Tim nicely and he might give a copy of > the article to you... (Probably better to get an IOUG subs - there is a ton > of excellent articles and tech stuff out there) > > John Kanagaraj > DB Soft Inc > Phone: 408-970-7002 (W) > > Disappointment is inevitable, but Discouragement is optional! > > ** The opinions and facts contained in this message are entirely mine and do > not reflect those of my employer or customers ** > >-Original Message- > >From: Daiminger, Helmut [mailto:[EMAIL PROTECTED] > >Sent: Wednesday, October 15, 2003 9:25 AM > >To: Multiple recipients of list ORACLE-L > >Subject: Problem with undo tablespace and snapshot too old > > > > > >Hi! > > > >We are experiencing a weird problem here... > > > >We have automatic undo management enabled and the undo > >tablespace is 6 GB in > >size. undo_retention is set to 30 minutes. > > > >when a certain transaction runs, it fails with ORA-1555 > >Snapshot too old, > >although the undo tablespace only uses 700 MB (out of 6 GB possible). > > > >That loos weird to me... > > > >Then our other DBA suggested to cut the size of the buffer > >cache in half and > >let the transaction run again. We have done that and it worked > >flawlessly... > >WHY??? > > > >What is the relation between the buffer cache size und > >rollback (i.e. undo > >retention)? > > > >This is 9.2 on HP-UX. > > > >Thanks, > >Helmut > > > >-- > >Please see the official ORACLE-L FAQ: http://www.orafaq.net > __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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: Problem with undo tablespace and snapshot too old
Helmut, The SELECT article 'Understanding ORA-0155' by Tim Gorman is a must-read. Get a subscription to IOUG, or ask Tim nicely and he might give a copy of the article to you... (Probably better to get an IOUG subs - there is a ton of excellent articles and tech stuff out there) John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Disappointment is inevitable, but Discouragement is optional! ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** >-Original Message- >From: Daiminger, Helmut [mailto:[EMAIL PROTECTED] >Sent: Wednesday, October 15, 2003 9:25 AM >To: Multiple recipients of list ORACLE-L >Subject: Problem with undo tablespace and snapshot too old > > >Hi! > >We are experiencing a weird problem here... > >We have automatic undo management enabled and the undo >tablespace is 6 GB in >size. undo_retention is set to 30 minutes. > >when a certain transaction runs, it fails with ORA-1555 >Snapshot too old, >although the undo tablespace only uses 700 MB (out of 6 GB possible). > >That loos weird to me... > >Then our other DBA suggested to cut the size of the buffer >cache in half and >let the transaction run again. We have done that and it worked >flawlessly... >WHY??? > >What is the relation between the buffer cache size und >rollback (i.e. undo >retention)? > >This is 9.2 on HP-UX. > >Thanks, >Helmut > >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: Daiminger, Helmut > 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: John Kanagaraj 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).
Problem with undo tablespace and snapshot too old
Hi! We are experiencing a weird problem here... We have automatic undo management enabled and the undo tablespace is 6 GB in size. undo_retention is set to 30 minutes. when a certain transaction runs, it fails with ORA-1555 Snapshot too old, although the undo tablespace only uses 700 MB (out of 6 GB possible). That loos weird to me... Then our other DBA suggested to cut the size of the buffer cache in half and let the transaction run again. We have done that and it worked flawlessly... WHY??? What is the relation between the buffer cache size und rollback (i.e. undo retention)? This is 9.2 on HP-UX. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daiminger, Helmut 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: Turning off undo for one session
Thanks for the response, unfortunately the application requires the data to be stored in the database, loading the files as BFILE type and then using DBMS_LOB.READ is not possible. Darren -Original Message- Sent: Thursday, October 09, 2003 10:10 AM To: Multiple recipients of list ORACLE-L Undo cannot be "turned off" because without commit/rollback capability, RDBMS can no longer manage resources and cannot observe ACID properties. What you can do is to load files as BFILE type, which, essentially, means that you are copying them to file system and just recording the pathname into the database. If you use DBMS_LOB.READ, then LOB blocks are protected by the transaction mechanism and there is nothing you can do. On Thu, 2003-10-09 at 12:44, Browett, Darren wrote: > We are trying to load 23Gb's of raster images into our 9.2.0.2/Rac > Database, using ESRI's SDE GIS package, > and as usual there is a big rush to load the data. > > If we try to load 14 images at one time, we run out of undo space > (datafile is currently 2 Gb). I could simply add another > datafile and not worry about, but I figure that's the easy way out. > > I have turned logging off for the table and the tablespace, but I can't > figure out how to turn the creation of undo > off for a particular session, or if it is even possible. > > Thanks > > Darren > > > -- > Darren Browett P.Eng This > message was transmitted > Data Administratorusing > 100% recycled electrons > Information and Communication Technology > City of Coquitlam > P:(604)927 - 3614 > E:[EMAIL PROTECTED] > > --- > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Browett, Darren 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: Turning off undo for one session
Undo cannot be "turned off" because without commit/rollback capability, RDBMS can no longer manage resources and cannot observe ACID properties. What you can do is to load files as BFILE type, which, essentially, means that you are copying them to file system and just recording the pathname into the database. If you use DBMS_LOB.READ, then LOB blocks are protected by the transaction mechanism and there is nothing you can do. On Thu, 2003-10-09 at 12:44, Browett, Darren wrote: > We are trying to load 23Gb's of raster images into our 9.2.0.2/Rac > Database, using ESRI's SDE GIS package, > and as usual there is a big rush to load the data. > > If we try to load 14 images at one time, we run out of undo space > (datafile is currently 2 Gb). I could simply add another > datafile and not worry about, but I figure that's the easy way out. > > I have turned logging off for the table and the tablespace, but I can't > figure out how to turn the creation of undo > off for a particular session, or if it is even possible. > > Thanks > > Darren > > > -- > Darren Browett P.Eng This > message was transmitted > Data Administratorusing > 100% recycled electrons > Information and Communication Technology > City of Coquitlam > P:(604)927 - 3614 > E:[EMAIL PROTECTED] > > --- > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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).
Turning off undo for one session
We are trying to load 23Gb's of raster images into our 9.2.0.2/Rac Database, using ESRI's SDE GIS package, and as usual there is a big rush to load the data. If we try to load 14 images at one time, we run out of undo space (datafile is currently 2 Gb). I could simply add another datafile and not worry about, but I figure that's the easy way out. I have turned logging off for the table and the tablespace, but I can't figure out how to turn the creation of undo off for a particular session, or if it is even possible. Thanks Darren -- Darren Browett P.EngThis message was transmitted Data Administrator using 100% recycled electrons Information and Communication Technology City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Browett, Darren 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: Estimating space needed for UNDO tablespaces
Try these queries. /* Rows returned below mean that UNDO_RETENTION needs to be increased */ select * from v$undostat where UNXPSTEALCNT > 0 or SSOLDERRCNT > 0; /* Rows returned below mean that space needs to be added to the undo tablespace. All space in the tablespace was used and no free space was available when requested */ select * from v$undostat where NOSPACEERRCNT > 0; Kirtikumar DeshpandeTo: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by: ml-errors 10/01/2003 10:14 AM Please respond to ORACLE-L Hi Jeff, Stealing extents is normal when there is no free space available to grow the active undo segment. If expired extents are getting stolen, I would not worry too much about adding more space to the undo tablespace, but monitor how much undo space the segment takes up. If unexpired extents are getting stolen, then you may have to consider either adding more space, or reducing undo retention time. If the undo tablespace does not have enough space to accommodate your largest transaction, event after stealing extents, and the data files are not autoexensible, then you will get ORA-1650 (I think, that the error#). And to avoid it, auto undo management still needs to be monitored! HTH, - Kirti --- Thomas Jeff <[EMAIL PROTECTED]> wrote: > Kirti, > > Thanks for this information.I've implemented AUM in a number of our > development > databases.One of the things I have to do is write up a monitoring policy > to hand > to our contracted production DBAs -- guidelines on how to address certain > scenarios > and so forth -- otherwise, they will simply resort to adding 'more' of > whatever they > presume is in short supply in event of a production crisis. > > For example, I'm seeing some steal counts in v$undostat, implying that the > undo > tablespace needs more space. However, from what you are saying, it seems > that if > undo_retention is consistently larger then maxquerylen during the period of > time when > the steal counts occur, that maybe the smarter thing to do is simply reduce > the > undo_retention parameter before considering adding more space? > > > Jeff > > > > -Original Message- > Sent: Friday, September 26, 2003 11:50 PM > To: Multiple recipients of list ORACLE-L > > > You can run following query to get an idea of undo generation rate and max > query length: > > SELECT > to_char(min(begin_time),'MM/DD/ HH24:MI:SS') "Begin Time", > to_char(max(end_time),'MM/DD/ HH24:MI:SS') "End Time", > (max(end_time)-min(begin_time))*24*60*60 "Seconds", > sum(undoblks) "UndoBlks", > sum(undoblks)/((max(end_time)-min(begin_time))*24*60*60) > "UndoBlksPerSec", > max(maxquerylen) "MaxQueryLenSecs" > FROM > v$undostat; > > Unfortunately, the TXNCOUNT column in v$undostat view in Oracle 9.2.0.x is > screwed up. It does not > correctly report the transaction count for the sample interval. Instead it > keeps accumulating. On
RE: Estimating space needed for UNDO tablespaces
Hi Jeff, Stealing extents is normal when there is no free space available to grow the active undo segment. If expired extents are getting stolen, I would not worry too much about adding more space to the undo tablespace, but monitor how much undo space the segment takes up. If unexpired extents are getting stolen, then you may have to consider either adding more space, or reducing undo retention time. If the undo tablespace does not have enough space to accommodate your largest transaction, event after stealing extents, and the data files are not autoexensible, then you will get ORA-1650 (I think, that the error#). And to avoid it, auto undo management still needs to be monitored! HTH, - Kirti --- Thomas Jeff <[EMAIL PROTECTED]> wrote: > Kirti, > > Thanks for this information.I've implemented AUM in a number of our > development > databases.One of the things I have to do is write up a monitoring policy > to hand > to our contracted production DBAs -- guidelines on how to address certain > scenarios > and so forth -- otherwise, they will simply resort to adding 'more' of > whatever they > presume is in short supply in event of a production crisis. > > For example, I'm seeing some steal counts in v$undostat, implying that the > undo > tablespace needs more space. However, from what you are saying, it seems > that if > undo_retention is consistently larger then maxquerylen during the period of > time when > the steal counts occur, that maybe the smarter thing to do is simply reduce > the > undo_retention parameter before considering adding more space? > > > Jeff > > > > -Original Message- > Sent: Friday, September 26, 2003 11:50 PM > To: Multiple recipients of list ORACLE-L > > > You can run following query to get an idea of undo generation rate and max > query length: > > SELECT > to_char(min(begin_time),'MM/DD/ HH24:MI:SS') "Begin Time", > to_char(max(end_time),'MM/DD/ HH24:MI:SS') "End Time", > (max(end_time)-min(begin_time))*24*60*60 "Seconds", > sum(undoblks) "UndoBlks", > sum(undoblks)/((max(end_time)-min(begin_time))*24*60*60) > "UndoBlksPerSec", > max(maxquerylen) "MaxQueryLenSecs" > FROM > v$undostat; > > Unfortunately, the TXNCOUNT column in v$undostat view in Oracle 9.2.0.x is > screwed up. It does not > correctly report the transaction count for the sample interval. Instead it > keeps accumulating. One > needs to do the math to get the correct count for the desired sample > interval. It will show the > time of high transaction activity with related undo generation. > > Oracle recommends setting undo_retention to the max(maxquerylen), but use > your judgement. If data > loads and queries accessing same tables, do not run at the same time (in DW, > for example), setting > undo_retention to a high number (maxquerylen) will simply waste disk space. > > If undo_retention is not set appropriately, you will get ORA-1555, and it > will be reported in > alert.log along with the affected SQL statement. The log entry will also > contain the query time, > in seconds, before it got aborted due to ORA-1555. > > Also, the above query works only when the database is using AUM. V$undostat > does not report > anything in 9.2.0.x when using MUM (manual undo mode). In 9.0.1, it returns > one useless row when > using MUM! > > BTW, you can also use the OEM to see the undo generation rate. It is one of > the few things in OEM > (standalone mode) I use. > > > Hth. > > - Kirti > > > __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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: Estimating space needed for UNDO tablespaces
Kirti, Thanks for this information.I've implemented AUM in a number of our development databases.One of the things I have to do is write up a monitoring policy to hand to our contracted production DBAs -- guidelines on how to address certain scenarios and so forth -- otherwise, they will simply resort to adding 'more' of whatever they presume is in short supply in event of a production crisis. For example, I'm seeing some steal counts in v$undostat, implying that the undo tablespace needs more space. However, from what you are saying, it seems that if undo_retention is consistently larger then maxquerylen during the period of time when the steal counts occur, that maybe the smarter thing to do is simply reduce the undo_retention parameter before considering adding more space? Jeff -Original Message- Sent: Friday, September 26, 2003 11:50 PM To: Multiple recipients of list ORACLE-L You can run following query to get an idea of undo generation rate and max query length: SELECT to_char(min(begin_time),'MM/DD/ HH24:MI:SS') "Begin Time", to_char(max(end_time),'MM/DD/ HH24:MI:SS') "End Time", (max(end_time)-min(begin_time))*24*60*60 "Seconds", sum(undoblks) "UndoBlks", sum(undoblks)/((max(end_time)-min(begin_time))*24*60*60) "UndoBlksPerSec", max(maxquerylen) "MaxQueryLenSecs" FROM v$undostat; Unfortunately, the TXNCOUNT column in v$undostat view in Oracle 9.2.0.x is screwed up. It does not correctly report the transaction count for the sample interval. Instead it keeps accumulating. One needs to do the math to get the correct count for the desired sample interval. It will show the time of high transaction activity with related undo generation. Oracle recommends setting undo_retention to the max(maxquerylen), but use your judgement. If data loads and queries accessing same tables, do not run at the same time (in DW, for example), setting undo_retention to a high number (maxquerylen) will simply waste disk space. If undo_retention is not set appropriately, you will get ORA-1555, and it will be reported in alert.log along with the affected SQL statement. The log entry will also contain the query time, in seconds, before it got aborted due to ORA-1555. Also, the above query works only when the database is using AUM. V$undostat does not report anything in 9.2.0.x when using MUM (manual undo mode). In 9.0.1, it returns one useless row when using MUM! BTW, you can also use the OEM to see the undo generation rate. It is one of the few things in OEM (standalone mode) I use. Hth. - Kirti --- Daniel Fink <[EMAIL PROTECTED]> wrote: > That sounds very reasonable. You can check that number against the values in > v$undostat as it runs. Remember, UNDO_RETENTION is not guaranteed. If the > space is needed by another segment, it may be taken even if the expire time > has not been reached. > > Daniel > > Thomas Jeff wrote: > > > Thanks for the reply Dan. > > > > Would you suggest setting UNDO_RETENTION to roughly the length of time of > > the longest > > running job in the database? For example, in our DW, our BI analysts tell > > me that their > > longest batch run is about 1 hr 45 minutes. My uneducated guess is to > > accordingly > > set the parameter to approx 2 hours. > > > > -Original Message- > > Sent: Friday, September 26, 2003 4:15 PM > > To: Multiple recipients of list ORACLE-L > > > > That is a good place to start. You might consider adding a little if you > > have many concurrent transactions or want to increase the undo_retention > > to a high number. Once you are using AUM, keep a close eye on > > v$undostat, though there are some known issues with it not populating > > properly, keep an eye on the begin_time and end_time. However, for > > estimation purposes it should work. > > > > Daniel Fink > > > > Thomas Jeff wrote: > > > > > I'm beginning the process of converting over to automatic > > > undo management. I'm wondering as to exactly how large to > > > initially build the UNDO tablespace.Make it roughly > > > the same size as the sum of the current rollback > > > tablespaces?Or has your experience been different, > > > i.e., you've found you've generally needed more or less > > > space with respect to the previous allocation for rollback > > > segments (manual undo)? > > > > > > Thanks. > > > > > > > > > Jeffery D Thomas > > > DBA > > > Thomson Infor
Re: Estimating space needed for UNDO tablespaces
You can run following query to get an idea of undo generation rate and max query length: SELECT to_char(min(begin_time),'MM/DD/ HH24:MI:SS') "Begin Time", to_char(max(end_time),'MM/DD/ HH24:MI:SS') "End Time", (max(end_time)-min(begin_time))*24*60*60 "Seconds", sum(undoblks) "UndoBlks", sum(undoblks)/((max(end_time)-min(begin_time))*24*60*60) "UndoBlksPerSec", max(maxquerylen) "MaxQueryLenSecs" FROM v$undostat; Unfortunately, the TXNCOUNT column in v$undostat view in Oracle 9.2.0.x is screwed up. It does not correctly report the transaction count for the sample interval. Instead it keeps accumulating. One needs to do the math to get the correct count for the desired sample interval. It will show the time of high transaction activity with related undo generation. Oracle recommends setting undo_retention to the max(maxquerylen), but use your judgement. If data loads and queries accessing same tables, do not run at the same time (in DW, for example), setting undo_retention to a high number (maxquerylen) will simply waste disk space. If undo_retention is not set appropriately, you will get ORA-1555, and it will be reported in alert.log along with the affected SQL statement. The log entry will also contain the query time, in seconds, before it got aborted due to ORA-1555. Also, the above query works only when the database is using AUM. V$undostat does not report anything in 9.2.0.x when using MUM (manual undo mode). In 9.0.1, it returns one useless row when using MUM! BTW, you can also use the OEM to see the undo generation rate. It is one of the few things in OEM (standalone mode) I use. Hth. - Kirti --- Daniel Fink <[EMAIL PROTECTED]> wrote: > That sounds very reasonable. You can check that number against the values in > v$undostat as it runs. Remember, UNDO_RETENTION is not guaranteed. If the > space is needed by another segment, it may be taken even if the expire time > has not been reached. > > Daniel > > Thomas Jeff wrote: > > > Thanks for the reply Dan. > > > > Would you suggest setting UNDO_RETENTION to roughly the length of time of > > the longest > > running job in the database? For example, in our DW, our BI analysts tell > > me that their > > longest batch run is about 1 hr 45 minutes. My uneducated guess is to > > accordingly > > set the parameter to approx 2 hours. > > > > -Original Message- > > Sent: Friday, September 26, 2003 4:15 PM > > To: Multiple recipients of list ORACLE-L > > > > That is a good place to start. You might consider adding a little if you > > have many concurrent transactions or want to increase the undo_retention > > to a high number. Once you are using AUM, keep a close eye on > > v$undostat, though there are some known issues with it not populating > > properly, keep an eye on the begin_time and end_time. However, for > > estimation purposes it should work. > > > > Daniel Fink > > > > Thomas Jeff wrote: > > > > > I'm beginning the process of converting over to automatic > > > undo management. I'm wondering as to exactly how large to > > > initially build the UNDO tablespace.Make it roughly > > > the same size as the sum of the current rollback > > > tablespaces?Or has your experience been different, > > > i.e., you've found you've generally needed more or less > > > space with respect to the previous allocation for rollback > > > segments (manual undo)? > > > > > > Thanks. > > > > > > > > > Jeffery D Thomas > > > DBA > > > Thomson Information Services > > > Thomson, Inc. > > > > __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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: Estimating space needed for UNDO tablespaces
That sounds very reasonable. You can check that number against the values in v$undostat as it runs. Remember, UNDO_RETENTION is not guaranteed. If the space is needed by another segment, it may be taken even if the expire time has not been reached. Daniel Thomas Jeff wrote: > Thanks for the reply Dan. > > Would you suggest setting UNDO_RETENTION to roughly the length of time of > the longest > running job in the database? For example, in our DW, our BI analysts tell > me that their > longest batch run is about 1 hr 45 minutes. My uneducated guess is to > accordingly > set the parameter to approx 2 hours. > > -Original Message- > Sent: Friday, September 26, 2003 4:15 PM > To: Multiple recipients of list ORACLE-L > > That is a good place to start. You might consider adding a little if you > have many concurrent transactions or want to increase the undo_retention > to a high number. Once you are using AUM, keep a close eye on > v$undostat, though there are some known issues with it not populating > properly, keep an eye on the begin_time and end_time. However, for > estimation purposes it should work. > > Daniel Fink > > Thomas Jeff wrote: > > > I'm beginning the process of converting over to automatic > > undo management. I'm wondering as to exactly how large to > > initially build the UNDO tablespace.Make it roughly > > the same size as the sum of the current rollback > > tablespaces?Or has your experience been different, > > i.e., you've found you've generally needed more or less > > space with respect to the previous allocation for rollback > > segments (manual undo)? > > > > Thanks. > > > > > > Jeffery D Thomas > > DBA > > Thomson Information Services > > Thomson, Inc. > > > > Email: [EMAIL PROTECTED] > > > > Indy DBA Master Documentation available at: > > http://gkmqp.tce.com/tis_dba > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Thomas Jeff > > 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: Thomas Jeff > 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). begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
RE: Estimating space needed for UNDO tablespaces
Thanks for the reply Dan. Would you suggest setting UNDO_RETENTION to roughly the length of time of the longest running job in the database? For example, in our DW, our BI analysts tell me that their longest batch run is about 1 hr 45 minutes. My uneducated guess is to accordingly set the parameter to approx 2 hours. -Original Message- Sent: Friday, September 26, 2003 4:15 PM To: Multiple recipients of list ORACLE-L That is a good place to start. You might consider adding a little if you have many concurrent transactions or want to increase the undo_retention to a high number. Once you are using AUM, keep a close eye on v$undostat, though there are some known issues with it not populating properly, keep an eye on the begin_time and end_time. However, for estimation purposes it should work. Daniel Fink Thomas Jeff wrote: > I'm beginning the process of converting over to automatic > undo management. I'm wondering as to exactly how large to > initially build the UNDO tablespace.Make it roughly > the same size as the sum of the current rollback > tablespaces?Or has your experience been different, > i.e., you've found you've generally needed more or less > space with respect to the previous allocation for rollback > segments (manual undo)? > > Thanks. > > > Jeffery D Thomas > DBA > Thomson Information Services > Thomson, Inc. > > Email: [EMAIL PROTECTED] > > Indy DBA Master Documentation available at: > http://gkmqp.tce.com/tis_dba > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Thomas Jeff > 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: Thomas Jeff 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: Estimating space needed for UNDO tablespaces
That is a good place to start. You might consider adding a little if you have many concurrent transactions or want to increase the undo_retention to a high number. Once you are using AUM, keep a close eye on v$undostat, though there are some known issues with it not populating properly, keep an eye on the begin_time and end_time. However, for estimation purposes it should work. Daniel Fink Thomas Jeff wrote: > I'm beginning the process of converting over to automatic > undo management. I'm wondering as to exactly how large to > initially build the UNDO tablespace.Make it roughly > the same size as the sum of the current rollback > tablespaces?Or has your experience been different, > i.e., you've found you've generally needed more or less > space with respect to the previous allocation for rollback > segments (manual undo)? > > Thanks. > > > Jeffery D Thomas > DBA > Thomson Information Services > Thomson, Inc. > > Email: [EMAIL PROTECTED] > > Indy DBA Master Documentation available at: > http://gkmqp.tce.com/tis_dba > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Thomas Jeff > 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). begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
Estimating space needed for UNDO tablespaces
I'm beginning the process of converting over to automatic undo management. I'm wondering as to exactly how large to initially build the UNDO tablespace.Make it roughly the same size as the sum of the current rollback tablespaces?Or has your experience been different, i.e., you've found you've generally needed more or less space with respect to the previous allocation for rollback segments (manual undo)? Thanks. Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Jeff 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: # of Undo segments and db crashes
Both ORA-600 and ORA-7445 are so generic that quoting them without the first parameter is not meaningful. In square [brackets], the first parameter defines the kernel code/position/function which raises this error, subsequent parameters (if not blank) define what it is not happy with. Even if the parameters are not identical, there is usually a pattern - the real cause, ie the initial ones, and a bunch of subsequent red-herrings, eventually leading to an instance crash. You have associated AUM with these errors, of which you may be right.But this is something that needs to be taken up wth Oracle Support. The approach I would take is search Metalink for the first parameter and then log a TAR With older releases, when you quote an ORA-600/7445 and the symptoms, chances are thats probably it when someone replies. With 9.2, it gets speculative. Of course, it always possible that some will have encountered what you have just described and provide a most definitive answer..., I would not bet my production databases on it. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, September 09, 2003 3:49 AM > Has anyone experienced the following scenario in 9.2 dbs running > Automatic Undo? > > 1) A large (500+) number of undo segments have been created. > 2) SMON is offlining a significant portion (100+) of them. > 3) The database begins recording ORA-00600 and ORA-07445 errors > (additional fields not identical) > 4) Instance crashes (may not record termination in alert log). > > I am wondering if there is an issue with AUM and a large number > of segments. Any thoughts, experiences, etc. are greatly > appreciated. > > Daniel Fink > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Binley Lim 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).
# of Undo segments and db crashes
Has anyone experienced the following scenario in 9.2 dbs running Automatic Undo? 1) A large (500+) number of undo segments have been created. 2) SMON is offlining a significant portion (100+) of them. 3) The database begins recording ORA-00600 and ORA-07445 errors (additional fields not identical) 4) Instance crashes (may not record termination in alert log). I am wondering if there is an issue with AUM and a large number of segments. Any thoughts, experiences, etc. are greatly appreciated. Daniel Fink begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
Re: Snapshot too old in undo tablespace in 9i?
Daniel, that make sense , thanks. Stephen Hodgkinson Oracle DBA Total Gas & Power Ltd 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 15/08/03 15:26 Please respond to ORACLE-L Stephen, Tim's statement is correct, but can be construed incorrectly if you read it and think of TEMP segments. AUM still uses undo segments (same basic structure as rollback segments). However, one of the space management steps is to allow an undo segment to 'steal' extents from another undo segment. This means that all extents (other than those currently in use or extent 0 (and perhaps 1)) are available to any other segment should it require them. A single transaction cannot start in undo segment #1, allocate space in it and then move to undo segment #2. However, the transaction can cause undo segment #1 to allocate space currently allocated to #2. I hope this clears up the disparity between the statements. Daniel [EMAIL PROTECTED] wrote: > > 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] ** This insert confirms that this email message and all associated attachments have been swept by TotalFinaElf us
Re: Snapshot too old in undo tablespace in 9i?
Stephen, Tim's statement is correct, but can be construed incorrectly if you read it and think of TEMP segments. AUM still uses undo segments (same basic structure as rollback segments). However, one of the space management steps is to allow an undo segment to 'steal' extents from another undo segment. This means that all extents (other than those currently in use or extent 0 (and perhaps 1)) are available to any other segment should it require them. A single transaction cannot start in undo segment #1, allocate space in it and then move to undo segment #2. However, the transaction can cause undo segment #1 to allocate space currently allocated to #2. I hope this clears up the disparity between the statements. Daniel [EMAIL PROTECTED] wrote: > > 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]begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
Re: Snapshot too old in undo tablespace in 9i?
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.c
Re: UNDO Tablespace
Hi! What exact error message you get? Maybe it's ORA-1555, not running out of free space in undo seg? If you got 1555's, then increase your undo retention to the lenght of longest query. Or redesign. The DBA is wrong, no physical copies of any tables are made in undo (well, unless you update all columns of all rows in a table), only changes to table are recorded to undo. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, August 12, 2003 4:39 PM > I have a 2gb UNDO tablespace. A third-party application continually runs > out of UNDO when it joins two tables to produce a result table. Our > retention time is set to 15 minutes, the NoSpaceErrCnt in V$UNDOSTAT is > always zero. The answer for this from our vendor is to increase the size > of the UNDO based on their DBAs statement that UNDO is consumed rapidly > because every query makes a physical copy of all tables and holds on to > them for the retention period. I can find nothing that discusses exactly > how UNDO physically works, and am not sure that this can be true. That > would mean that every user querying our database would have copies of the > tables in the UNDO, and I'd need about a gazillion gb to handle that. Does > anyone have any insights on how the UNDO physically works? > > > > --- > Sherrie Kubis > Southwest Florida Water Management District > 2379 Broad Street > Brooksville FL 34604-6899 > > Phone: (352) 796-7211, Ext. 4033 > Fax: (352) 754-6776 > Email: Mailto:[EMAIL PROTECTED] > http://WaterMatters.org > > > > > -- > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: UNDO Tablespace
Sherrie, Also check out Tim Gorman's paper/presentation "Cat's, Dog's and ORA-1555s" on his website www.evdbt.com. It is a great place to start, then move on to my papers which are more geeky in nature (reading block dumps, following the undo chain, etc.). Daniel Fink (Gotta run, just checking the email before the Hotsos Clinic. YEAH!) Rachel Carmichael wrote: > > Sherrie, > > First, the Oracle documentation does a pretty good job of explaining > how undo works. check out the concepts manual. > > Second, you might want to read Dan Fink's papers on Undo Internals, you > can find them on his website at > > http://www.optimaldba.com/library.html > > He does a very good job of explaining how undo works. > > Lastly, I would verify that the vendor's DBA has actually worked with > Oracle before. Oracle DOES NOT make a copy of the entire physical table > for every table involved in every query, nor does it hold onto those > copies for the length of the retention period. > > Retention period is misleading, as Oracle WILL overwrite information in > an undo segment, even if the retention time has not been reached, if > space is needed. Dan does an excellent job of explaining that. > > He's off at the Hotsos Clinic now (lucky man!) or he'd be answering > this himself > > --- [EMAIL PROTECTED] wrote: > > I have a 2gb UNDO tablespace. A third-party application continually > > runs > > out of UNDO when it joins two tables to produce a result table. Our > > retention time is set to 15 minutes, the NoSpaceErrCnt in V$UNDOSTAT > > is > > always zero. The answer for this from our vendor is to increase the > > size > > of the UNDO based on their DBAs statement that UNDO is consumed > > rapidly > > because every query makes a physical copy of all tables and holds on > > to > > them for the retention period. I can find nothing that discusses > > exactly > > how UNDO physically works, and am not sure that this can be true. > > That > > would mean that every user querying our database would have copies of > > the > > tables in the UNDO, and I'd need about a gazillion gb to handle that. > > Does > > anyone have any insights on how the UNDO physically works? > > > > > > > > --- > > Sherrie Kubis > > Southwest Florida Water Management District > > 2379 Broad Street > > Brooksville FL 34604-6899 > > > > Phone: (352) 796-7211, Ext. 4033 > > Fax: (352) 754-6776 > > Email: Mailto:[EMAIL PROTECTED] > > http://WaterMatters.org > > > > > > > > > > -- > > 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). > > __ > Do you Yahoo!? > Yahoo! SiteBuilder - Free, easy-to-use web site design software > http://sitebuilder.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Rachel Carmichael > 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).begin:vcard n:Fink;Daniel x-mozilla-html:FALSE org:Sun Microsystems, Inc. adr:;; version:2.1 title:Lead, Database Services x-mozilla-cpt:;9168 fn:Daniel W. Fink end:vcard
Re: UNDO Tablespace
Sherrie, First, the Oracle documentation does a pretty good job of explaining how undo works. check out the concepts manual. Second, you might want to read Dan Fink's papers on Undo Internals, you can find them on his website at http://www.optimaldba.com/library.html He does a very good job of explaining how undo works. Lastly, I would verify that the vendor's DBA has actually worked with Oracle before. Oracle DOES NOT make a copy of the entire physical table for every table involved in every query, nor does it hold onto those copies for the length of the retention period. Retention period is misleading, as Oracle WILL overwrite information in an undo segment, even if the retention time has not been reached, if space is needed. Dan does an excellent job of explaining that. He's off at the Hotsos Clinic now (lucky man!) or he'd be answering this himself --- [EMAIL PROTECTED] wrote: > I have a 2gb UNDO tablespace. A third-party application continually > runs > out of UNDO when it joins two tables to produce a result table. Our > retention time is set to 15 minutes, the NoSpaceErrCnt in V$UNDOSTAT > is > always zero. The answer for this from our vendor is to increase the > size > of the UNDO based on their DBAs statement that UNDO is consumed > rapidly > because every query makes a physical copy of all tables and holds on > to > them for the retention period. I can find nothing that discusses > exactly > how UNDO physically works, and am not sure that this can be true. > That > would mean that every user querying our database would have copies of > the > tables in the UNDO, and I'd need about a gazillion gb to handle that. > Does > anyone have any insights on how the UNDO physically works? > > > > --- > Sherrie Kubis > Southwest Florida Water Management District > 2379 Broad Street > Brooksville FL 34604-6899 > > Phone: (352) 796-7211, Ext. 4033 > Fax: (352) 754-6776 > Email: Mailto:[EMAIL PROTECTED] > http://WaterMatters.org > > > > > -- > 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: UNDO Tablespace
otn.oracle.com click documentation, books. answer is in the concepts document. The DBA is correct, but the answer could be simplified. Undo creates before images of transactions. It only captures the before images of 'rows' involved in insert,update, and delete statements. Not all the rows in every table. This is so that if you choose or if there is a failure you can 'rollback' to the state the database was in before your transaction started. Its also so that you can get a 'consistent' view of the database. Lets say User A is running an update on Table A that takes 20 minutes. User B comes in and does a select that involves a SUM. User A may get to the first block which has already been updated, then go to a later block that has not been updated, so you get a view of the table that doesnt really exist. Its in transition. The Select statement will go to the rollback to see what the update looks like BEFORE the transaction takes place. This way you either have a select from data before an update takes place or after a commit, after the update, Not half and half. you probably need to increase the size of your undo tablespace. Just get a hard disk and stick it on there. You can get 37 GB SCSI drives cheap. > > From: [EMAIL PROTECTED] > Date: 2003/08/12 Tue AM 09:39:29 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: UNDO Tablespace > > I have a 2gb UNDO tablespace. A third-party application continually runs > out of UNDO when it joins two tables to produce a result table. Our > retention time is set to 15 minutes, the NoSpaceErrCnt in V$UNDOSTAT is > always zero. The answer for this from our vendor is to increase the size > of the UNDO based on their DBAs statement that UNDO is consumed rapidly > because every query makes a physical copy of all tables and holds on to > them for the retention period. I can find nothing that discusses exactly > how UNDO physically works, and am not sure that this can be true. That > would mean that every user querying our database would have copies of the > tables in the UNDO, and I'd need about a gazillion gb to handle that. Does > anyone have any insights on how the UNDO physically works? > > > > --- > Sherrie Kubis > Southwest Florida Water Management District > 2379 Broad Street > Brooksville FL 34604-6899 > > Phone: (352) 796-7211, Ext. 4033 > Fax: (352) 754-6776 > Email: Mailto:[EMAIL PROTECTED] > http://WaterMatters.org > > > > > -- > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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: UNDO Tablespace
Given the answer you got, you can bet that their application must be fun to look at ... Undo space is basically born-again rollback segments; look for Dan Fink's papers on the topic, you'll probably find all the answers you need there. I presume that you must be getting something like ORA-1555. If this is right, basically, your join of death runs while updates are concurrently taking place (and committed). As a result, when your select needs the data as it was when it started, it has to look for them in the undo tablespace. After 15mn it's no longer here. First thing to do would probably be to make that query runtime fall below 15mn. If rewriting it is out of question, try outlines. >- --- Original Message --- - >From: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Tue, 12 Aug 2003 05:39:29 > >I have a 2gb UNDO tablespace. A third-party >application continually runs >out of UNDO when it joins two tables to produce a >result table. Our >retention time is set to 15 minutes, the >NoSpaceErrCnt in V$UNDOSTAT is >always zero. The answer for this from our vendor >is to increase the size >of the UNDO based on their DBAs statement that UNDO >is consumed rapidly >because every query makes a physical copy of all >tables and holds on to >them for the retention period. I can find nothing >that discusses exactly >how UNDO physically works, and am not sure that >this can be true. That >would mean that every user querying our database >would have copies of the >tables in the UNDO, and I'd need about a gazillion >gb to handle that. Does >anyone have any insights on how the UNDO physically >works? > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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).
UNDO Tablespace
Thanks guys. I have some reading to do. I DID look at the concepts manual, and was probably concentrating on the UNDO section where it talks about calculating your size and whatnot, so I'm missing something. Here is the error message: ORA-30036 Unable to extend segment by 1024 in undo tablespace 'UNDOTBSP' - 30036 Cause: the specified undo tablespace has no more space available. Action: Add more space to the undo tablespace before retrying the operation. An alternative is to wait until active transactions to commit What I'm trying to get these guys to do is to apply incremental commits in their code rather than extending the size of the UNDO tablespace. What extension to the datafile works today, may not work tomorrow because of a larger number of changes. --- Sherrie Kubis Southwest Florida Water Management District 2379 Broad Street Brooksville FL 34604-6899 Phone: (352) 796-7211, Ext. 4033 Fax: (352) 754-6776 Email: Mailto:[EMAIL PROTECTED] http://WaterMatters.org -- 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).
Re: Snapshot too old in undo tablespace in 9i?
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" 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).begin:vcard n:Fink;Daniel tel;cell:303.808.3282 tel;work:303.272.3225 x-mozilla-html:TRUE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:DB Services Lead x-mozilla-cpt:;-4832 fn:Daniel Fink end:vcard
UNDO Tablespace
I have a 2gb UNDO tablespace. A third-party application continually runs out of UNDO when it joins two tables to produce a result table. Our retention time is set to 15 minutes, the NoSpaceErrCnt in V$UNDOSTAT is always zero. The answer for this from our vendor is to increase the size of the UNDO based on their DBAs statement that UNDO is consumed rapidly because every query makes a physical copy of all tables and holds on to them for the retention period. I can find nothing that discusses exactly how UNDO physically works, and am not sure that this can be true. That would mean that every user querying our database would have copies of the tables in the UNDO, and I'd need about a gazillion gb to handle that. Does anyone have any insights on how the UNDO physically works? --- Sherrie Kubis Southwest Florida Water Management District 2379 Broad Street Brooksville FL 34604-6899 Phone: (352) 796-7211, Ext. 4033 Fax: (352) 754-6776 Email: Mailto:[EMAIL PROTECTED] http://WaterMatters.org -- 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).
RE: UNDO Tablespace
>"The answer for this from our vendor is to increase the size > of the UNDO based on their DBAs statement that UNDO is consumed rapidly > because every query makes a physical copy of all tables and holds on to > them for the retention period. Sherrie, In my considered opinion: Woohoohoohoo. That's a ridiculous thing for a DBA to say. I hope he was misinterpreted somewhere down the line because otherwise he's got a very flimsy grasp of the read-consistency model. In very simple terms Undo is used to hold a copy of any changed data (not the whole table!) which has not been committed and flushed to the datafiles. While it's there the data for an update transaction is available a) to the 'owning' transaction in case it has to ROLLBACK the updates it's made. b) to any other transactions which need to reconstruct the data as it was before the update began. On a more positive note I agree that we need the text of the error message in order to give some help. Cheers, Mike - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, August 12, 2003 4:39 PM > I have a 2gb UNDO tablespace. A third-party application continually runs > out of UNDO when it joins two tables to produce a result table. Our > retention time is set to 15 minutes, the NoSpaceErrCnt in V$UNDOSTAT is > always zero. The answer for this from our vendor is to increase the size > of the UNDO based on their DBAs statement that UNDO is consumed rapidly > because every query makes a physical copy of all tables and holds on to > them for the retention period. I can find nothing that discusses exactly > how UNDO physically works, and am not sure that this can be true. That > would mean that every user querying our database would have copies of the > tables in the UNDO, and I'd need about a gazillion gb to handle that. Does > anyone have any insights on how the UNDO physically works? > > > > --- > Sherrie Kubis > Southwest Florida Water Management District > 2379 Broad Street > Brooksville FL 34604-6899 E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator. This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. CE Electric UK Funding Company Lloyds Court 78 Grey Street Newcastle upon Tyne NE1 6AF Registered in England and Wales: Number 3476201 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (LogicaCMG) 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).
Snapshot too old in undo tablespace in 9i?
Title: Message 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 <>
RE: Snapshot too old in undo tablespace in 9i?
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" @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).
Re: Snapshot too old in undo tablespace in 9i?
retention time --- set it for a couple of hours longer than you think the export will take. "Guerra, Abraham J" @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).
RE: undo tablespace and rollback segments in oracle9i
Your attachment was removed by the list server. However, your direct e-mail to me had the following: SQL> show parameter undo ; NAME TYPEVALUE --- -- undo_management string MANUAL undo_retention integer 900 undo_suppress_errors boolean FALSE undo_tablespace string rbs SQL> SQL> select segment_name, tablespace_name from dba_rollback_segs; SEGMENT_NAME TABLESPACE_NAME -- -- SYSTEM SYSTEM _SYSSMU1$ RBS _SYSSMU2$ RBS _SYSSMU3$ RBS _SYSSMU4$ RBS _SYSSMU5$ RBS _SYSSMU6$ RBS _SYSSMU7$ RBS _SYSSMU8$ RBS _SYSSMU9$ RBS _SYSSMU10$ RBS SEGMENT_NAME TABLESPACE_NAME -- -- RBS01 RBS RBS02 RBS RBS03 RBS RBS04 RBS RBS05 RBS RBS06 RBS 17 rows selected. SQL> select a.usn, a.name, b.status 2 from v$rollname a, 3 v$rollstat b 4 where a.usn = b.usn; USN NAME STATUS -- -- --- 0 SYSTEM ONLINE 11 RBS01 ONLINE 12 RBS02 ONLINE 13 RBS03 ONLINE 14 RBS04 ONLINE 15 RBS05 ONLINE 16 RBS06 ONLINE 7 rows selected. --- So, it is clear that RBSnn were created in an undo tablespace titled RBS. To remove system managed undo segments (_SYSSMUn$) one must drop the undo tablespace itself. But, in your case, you will first have to: 1. Create a new, normal tablespace, say, rollback_ts, to hold new rollback segments (do not create it using "create undo tablespace" ) 2. Create required rollback segments (rbs07, rbs08 etc) in rollback_ts. 3. Bring them all online. 4. Offline RBS01 to RBS06 and drop them. 5. Drop tablespace RBS. Modify init.ora parameter as required. Hope this helps. - Kirti --- [EMAIL PROTECTED] wrote: > hi , > please look attached file ; > > > > -Original Message- > Sent: Wednesday, July 30, 2003 3:52 PM > To: [EMAIL PROTECTED] > Cc: Bahar, Arslan > > > Your question is not very clear. Sorry. > > Can you please post results of following commands? > > Thanks. > > - Kirti > > SQL> show parameter undo > > SQL> select segment_name, tablespace_name from dba_rollback_segs; > > SQL> select a.usn, a.name, b.status > 2 from v$rollname a, > 3 v$rollstat b > 4 where a.usn = b.usn; > > > > --- [EMAIL PROTECTED] wrote: > > __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services -
Re: undo tablespace and rollback segments in oracle9i
If you are now running in manual undo mode and have created rollback segments, you can remove the undo segments. Verify that the segments are offline and you are not using them. Then drop the undo tablespace. This is the only way to get rid of them. [EMAIL PROTECTED] wrote: > > yes . What should i do > > -Original Message- > Sent: Wednesday, July 30, 2003 5:30 PM > To: Multiple recipients of list ORACLE-L > > The scenario you describe is not consistent with manual undo. Was the database > created/started with automatic undo and you have switched to manual? > > [EMAIL PROTECTED] wrote: > > > > I use manual managed undo tablespace but there are some system named rollback > > segments on that > > tablespace ( I did not created them ) and they can not be dropped. > > What are they , how can I drop them. > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: <[EMAIL PROTECTED] > > 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: <[EMAIL PROTECTED] > 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).begin:vcard n:Fink;Daniel tel;cell:303.808.3282 tel;work:303.272.3225 x-mozilla-html:TRUE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:DB Services Lead x-mozilla-cpt:;-4832 fn:Daniel Fink end:vcard
RE: undo tablespace and rollback segments in oracle9i
yes . What should i do -Original Message- Sent: Wednesday, July 30, 2003 5:30 PM To: Multiple recipients of list ORACLE-L The scenario you describe is not consistent with manual undo. Was the database created/started with automatic undo and you have switched to manual? [EMAIL PROTECTED] wrote: > > I use manual managed undo tablespace but there are some system named rollback > segments on that > tablespace ( I did not created them ) and they can not be dropped. > What are they , how can I drop them. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > 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: <[EMAIL PROTECTED] 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: undo tablespace and rollback segments in oracle9i
hi , please look attached file ; -Original Message- Sent: Wednesday, July 30, 2003 3:52 PM To: [EMAIL PROTECTED] Cc: Bahar, Arslan Your question is not very clear. Sorry. Can you please post results of following commands? Thanks. - Kirti SQL> show parameter undo SQL> select segment_name, tablespace_name from dba_rollback_segs; SQL> select a.usn, a.name, b.status 2 from v$rollname a, 3 v$rollstat b 4 where a.usn = b.usn; --- [EMAIL PROTECTED] wrote: > I use manual managed undo tablespace but there are some system named rollback > segments on that > tablespace ( I did not created them ) and they can not be dropped. > What are they , how can I drop them. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > INET: [EMAIL PROTECTED] > > __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com
Re: undo tablespace and rollback segments in oracle9i
The scenario you describe is not consistent with manual undo. Was the database created/started with automatic undo and you have switched to manual? [EMAIL PROTECTED] wrote: > > I use manual managed undo tablespace but there are some system named rollback > segments on that > tablespace ( I did not created them ) and they can not be dropped. > What are they , how can I drop them. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > 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).begin:vcard n:Fink;Daniel tel;cell:303.808.3282 tel;work:303.272.3225 x-mozilla-html:TRUE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:DB Services Lead x-mozilla-cpt:;-4832 fn:Daniel Fink end:vcard
Re: undo tablespace and rollback segments in oracle9i
Your question is not very clear. Sorry. Can you please post results of following commands? Thanks. - Kirti SQL> show parameter undo SQL> select segment_name, tablespace_name from dba_rollback_segs; SQL> select a.usn, a.name, b.status 2 from v$rollname a, 3 v$rollstat b 4 where a.usn = b.usn; --- [EMAIL PROTECTED] wrote: > I use manual managed undo tablespace but there are some system named rollback > segments on that > tablespace ( I did not created them ) and they can not be dropped. > What are they , how can I drop them. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > INET: [EMAIL PROTECTED] > > __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Kirtikumar Deshpande 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).
undo tablespace and rollback segments in oracle9i
I use manual managed undo tablespace but there are some system named rollback segments on that tablespace ( I did not created them ) and they can not be dropped. What are they , how can I drop them. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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: undo tablespace and set transaction use rollback segment
Hi! You can't anymore. Either: 1) remove set transaction commands from your scripts 2) if can't remove 'em, set undo_suppress_errors=true in spfile/init.ora that your scripts wont error out 3) use old fashioned rollback segments - set undo_management=manual in parameter file. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, July 29, 2003 1:04 PM > >when we were using oracle8i , whe had some process which use "set transaction use rollback segment". > and now we use undo tablespace and we have still same porocesses . > how can set a rollbback segment for process with undo tablepsace > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > 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: Tanel Poder 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).
undo tablespace and set transaction use rollback segment
when we were using oracle8i , whe had some process which use "set transaction use rollback segment". and now we use undo tablespace and we have still same porocesses . how can set a rollbback segment for process with undo tablepsace -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] 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).
Undo Tablespace vs Rollback segments
Can anyone point me to an article or white paper that goes into detail on this. When I RTFM, it just says use Undo TBS, don't worrry about it and all your problems will be solved. We are upgrading from Oracle8 to 9i and currently have Rollback segments in 4 tablespaces spread over 4 disks. It looks like you can specify multiple data files, but I can't find anything on how they would be used. I know that striping would spread the load, but that is not an option. Thanks, Keith Moore Oracle Certified Professional 972-431-5126 [EMAIL PROTECTED] The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer.
Re: ORA-01555 with Automatic Undo Management mode
I looked through metalink (doc 40689.1) and I don't see how the delayed
block cleanout scenario could happen (in my case) given that there was only
one transaction running.
I'm still stuck with that last 4g block that was allocated with only 3G of
free space remaining. Also, that filespace was not autoextensible.
SQL> select * from dba_data_files where tablespace_name = 'UNDOTBS1';
FILE_NAME
FILE_ID TABLESPACE_NAME BYTES BLOCKS
STATUSRELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY
-- -- --
- --- -- --
USER_BYTES USER_BLOCKS
-- ---
D:\ORACLE92\ORADATA\EID9SNP\UNDOTBS01.DBF
2 UNDOTBS1209,715,200 25600
AVAILABLE2 YES 3.4360E+104194302 640
209649664 25592
D:\ORACLE92\ORADATA\EID9SNP\UNDOTBS02.DBF
25 UNDOTBS1 7,340,032,000 896000
AVAILABLE 25 NO 0 00
7339966464 895992
SQL> select tablespace_name, initial_extent, next_extent, status,
extent_management, allocation_type, segment_space_management
2 from dba_tablespaces where tablespace_name = 'UNDOTBS1';
TABLESPACE_NAMEINITIAL_EXTENT NEXT_EXTENT STATUS
EXTENT_MAN ALLOCATIO SEGMEN
-- -- --- -
-- - --
UNDOTBS165536 ONLINELOCAL
SYSTEMMANUAL
The snapshot built just fine (7G in 9.5 hrs) using rollback segments -
RBS_LARGE below.
Map of Tablespace rbs2
File
OWNER OBJECT Id BLOCK_ID BLOCKS
BYTES
-- --- -- --
SYSRBS_LARGE 249 25600
209,715,200
SYSRBS_LARGE 2425609 25600
209,715,200
SYSRBS_LARGE 2451209 25600
209,715,200
SYSRBS_LARGE 2476809 25600
209,715,200
SYSRBS_LARGE 24 102409 25600
209,715,200
SYSRBS_LARGE 24 128009 25600
209,715,200
SYSRB3 24 153609 25600
209,715,200
SYSRB3 24 179209 25600
209,715,200
You can't argue with success and we're out of time so automatic undo bites
(bytes) the dust on this database. I haven't given up on it, I just don't
have enough time to make it work here.
"Darrell
Landrum" To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
Subject: Re: ORA-01555 with Automatic
Undo Management mode
Sent by:
ml-errors
06/19/2003 08:44
AM
Please respond
to ORACLE-L
>>Are you certain that the 1555 was caused by extent stealing?
Nope, not certain at all.
>>> [EMAIL PROTECTED] 06/18/03 09:54PM >>>
Darrell,
Th
Re: ORA-01555 with Automatic Undo Management mode
>>Are you certain that the 1555 was caused by extent stealing? Nope, not certain at all. >>> [EMAIL PROTECTED] 06/18/03 09:54PM >>> Darrell, The space management algorithm is such that free extent acquisition is the second method for undo allocation. The first is claiming expired extents from the current segment. With a low retention setting, this is entirely possible. Are you certain that the 1555 was caused by extent stealing? What I was trying to say is that a segment will extend by grabbing free (unallocated) extents before it allocates extents from other segments (stealing). If the mview creation is a single transaction, it is likely that the extents in the large undo segment are all still active and cannot be reused, no matter what (active undo is NEVER overwritten). -- Daniel W. Fink http://www.optimaldba.com Darrell Landrum wrote: >>>That all being said, the fact that there is free space in the >>> >>> >tablespace implies that the 1555 is not due to an extent being >overwritten. Oracle should grab free space before it grabs other >extents, even expired ones. << > >Daniel, >Are you saying this correctly? The reason I ask, is I've seen a 1555 >error in a system with one job running, only 480 MBs of undo space used, >10 minute retention setting, and an undo tablespace of 12 GB. It >doesn't seem that Oracle grabbed an extent from free space before using >an expired one. (Retention setting is now much, much higher. Got to >watch those hours to seconds conversions.) > > > > > > > >>>>[EMAIL PROTECTED] 06/18/03 03:19PM >>> >>>> >>>> >Thomas, > How long ago was the data for the mview loaded? It is possible >that you are running into the scenario where delayed block cleanout is >causing the ORA-1555. Is the name of the undo segment in the error the >same as the large undo extent? I would suspect that they are different. > > I have not tested mviews as transactions, but I presume oracle >considers it as one large transaction. This would prevent it from >acquiring new undo segments, which makes sense as there is one segment >that is very large in respect to all the others. There are only 88 >segments in the tablespace, both offline and online, not 88 online? >Assuming the 88 segments have been shrunk to minimum (128k), they will >consume only 10meg. With the numbers you quote, this sounds like the >case. > > SMON awakes periodically to offline undo segments and it may >have offlined a segment that was needed by the transaction (it just did >not know that at the time). SMON should not offline a segment if the >retention time has not been met, but I don't know this for a fact. There >is a thought in the deep dark recesses of my mind that the situation may >be the result of the expire time algorithm that Oracle uses. (The sound >you have just heard is Kirti fleeing from the list before we go down >that path >again!) > > That all being said, the fact that there is free space in the >tablespace implies that the 1555 is not due to an extent being >overwritten. Oracle should grab free space before it grabs other >extents, even expired ones. If the process was attempting to acquire an >extent and could not find sufficient free space, you would receive the >'Unable to extend segment' error and not the ORA-1555. > > Since you are the only process running, I think delayed block >cleanout (a result of the data loading process and nothing to do with >your mview) or segment offlining are your likely culprits. I can't >recommend this will work, but give it a shot. Alter the tablespace >containing the source table into read only mode, then run the mview >statement. The fact that the ts is read-only guarantees that all data >contained within has been committed. This solution was suggested to me >by a very wise member of >the list, so it is worth a try. > >Daniel Fink > >BTW, AUM is not a requirement for Flashback Query. However, it is the >only configuration Oracle will support for FBQ and it is more likely to >succeed with AUM. > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum 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: ORA-01555 with Automatic Undo Management mode
Darrell, The space management algorithm is such that free extent acquisition is the second method for undo allocation. The first is claiming expired extents from the current segment. With a low retention setting, this is entirely possible. Are you certain that the 1555 was caused by extent stealing? What I was trying to say is that a segment will extend by grabbing free (unallocated) extents before it allocates extents from other segments (stealing). If the mview creation is a single transaction, it is likely that the extents in the large undo segment are all still active and cannot be reused, no matter what (active undo is NEVER overwritten). -- Daniel W. Fink http://www.optimaldba.com Darrell Landrum wrote: That all being said, the fact that there is free space in the tablespace implies that the 1555 is not due to an extent being overwritten. Oracle should grab free space before it grabs other extents, even expired ones. << Daniel, Are you saying this correctly? The reason I ask, is I've seen a 1555 error in a system with one job running, only 480 MBs of undo space used, 10 minute retention setting, and an undo tablespace of 12 GB. It doesn't seem that Oracle grabbed an extent from free space before using an expired one. (Retention setting is now much, much higher. Got to watch those hours to seconds conversions.) [EMAIL PROTECTED] 06/18/03 03:19PM >>> Thomas, How long ago was the data for the mview loaded? It is possible that you are running into the scenario where delayed block cleanout is causing the ORA-1555. Is the name of the undo segment in the error the same as the large undo extent? I would suspect that they are different. I have not tested mviews as transactions, but I presume oracle considers it as one large transaction. This would prevent it from acquiring new undo segments, which makes sense as there is one segment that is very large in respect to all the others. There are only 88 segments in the tablespace, both offline and online, not 88 online? Assuming the 88 segments have been shrunk to minimum (128k), they will consume only 10meg. With the numbers you quote, this sounds like the case. SMON awakes periodically to offline undo segments and it may have offlined a segment that was needed by the transaction (it just did not know that at the time). SMON should not offline a segment if the retention time has not been met, but I don't know this for a fact. There is a thought in the deep dark recesses of my mind that the situation may be the result of the expire time algorithm that Oracle uses. (The sound you have just heard is Kirti fleeing from the list before we go down that path again!) That all being said, the fact that there is free space in the tablespace implies that the 1555 is not due to an extent being overwritten. Oracle should grab free space before it grabs other extents, even expired ones. If the process was attempting to acquire an extent and could not find sufficient free space, you would receive the 'Unable to extend segment' error and not the ORA-1555. Since you are the only process running, I think delayed block cleanout (a result of the data loading process and nothing to do with your mview) or segment offlining are your likely culprits. I can't recommend this will work, but give it a shot. Alter the tablespace containing the source table into read only mode, then run the mview statement. The fact that the ts is read-only guarantees that all data contained within has been committed. This solution was suggested to me by a very wise member of the list, so it is worth a try. Daniel Fink BTW, AUM is not a requirement for Flashback Query. However, it is the only configuration Oracle will support for FBQ and it is more likely to succeed with AUM.
Re: ORA-01555 with Automatic Undo Management mode
The more I thought about this (after exchanging a few direct emails with Thomas), I am leaning towards the delayed block cleanout possibility. There was nothing in the alert log that indicated SMON off-lining any undo segments (there were only 10 SMUs to deal with). It is, however, possible that SMON may have stolen extents from other segments for long running jobs (if needed) and eventually caused ORA-1555. But Thomas did not find any relevant rows in V$UNDOSTAT, corresponding to the timeframe of the 1555 error. That would have confirmed if there was any stealing and mugging going on.. So, we are really back to just brain storming and intelligent guesswork, unfortunately :( - Kirti --- Daniel Fink <[EMAIL PROTECTED]> wrote: > Thomas, > How long ago was the data for the mview loaded? It is possible that you are > running into the > scenario where delayed block cleanout is causing the ORA-1555. Is the name of the > undo segment > in the error the same as the large undo extent? I would suspect that they are > different. > > I have not tested mviews as transactions, but I presume oracle considers it as > one large > transaction. This would prevent it from acquiring new undo segments, which makes > sense as there > is one segment that is very large in respect to all the others. There are only 88 > segments in > the tablespace, both offline and online, not 88 online? Assuming the 88 segments > have been > shrunk to minimum (128k), they will consume only 10meg. With the numbers you quote, > this sounds > like the case. > > SMON awakes periodically to offline undo segments and it may have offlined a > segment that was > needed by the transaction (it just did not know that at the time). SMON should not > offline a > segment if the retention time has not been met, but I don't know this for a fact. > There is a > thought in the deep dark recesses of my mind that the situation may be the result of > the expire > time algorithm that Oracle uses. (The sound you have just heard is Kirti fleeing > from the list > before we go down that path > again!) > > That all being said, the fact that there is free space in the tablespace > implies that the 1555 > is not due to an extent being overwritten. Oracle should grab free space before it > grabs other > extents, even expired ones. If the process was attempting to acquire an extent and > could not > find sufficient free space, you would receive the 'Unable to extend segment' error > and not the > ORA-1555. > > Since you are the only process running, I think delayed block cleanout (a > result of the data > loading process and nothing to do with your mview) or segment offlining are your > likely > culprits. I can't recommend this will work, but give it a shot. Alter the tablespace > containing > the source table into read only mode, then run the mview statement. The fact that > the ts is > read-only guarantees that all data contained within has been committed. This > solution was > suggested to me by a very wise member of > the list, so it is worth a try. > > Daniel Fink > > BTW, AUM is not a requirement for Flashback Query. However, it is the only > configuration Oracle > will support for FBQ and it is more likely to succeed with AUM. > > > Thomas Day wrote: > > > > I'm trying to create a materialized view on a 7G table. I'm using UNDO and > > undo_retention=108000, which if I understand correctly is 30 hours. I have > > 7G in UNDOTBS1 --- I shouldn't need that much. After 7 hrs I get > > ORA-01555: snapshot too old: rollback segment number with name "" too > > small > > > > The FM says: > > > > ORA-01555 snapshot too old: rollback segment number string with name " > > string" too small > > Cause: Rollback records needed by a reader for consistent read are > > overwritten by other writers. > > Action: If in Automatic Undo Management mode, increase the setting of > > UNDO_RETENTION. Otherwise, use larger rollback segments. > > > > UNDOTBS1 still had 3G of space free with about 88 active undo extents, the > > largest was 4,154,458,112 bytes. Was it trying to create another 4G > > extent? Is there something I am missing? This Automatic stuff doesn't > > seem to be so automatic. I can create this using rollback segments but I > > wanted to use UNDO because it allows past point in time queries. > > > > Oracle 9.2.0.1.0 > > > > Win2K. > > > > Any thoughts (besides get a real operating system and use rollback > > segments) would be greatly appreciat
Re: ORA-01555 with Automatic Undo Management mode
>>That all being said, the fact that there is free space in the tablespace implies that the 1555 is not due to an extent being overwritten. Oracle should grab free space before it grabs other extents, even expired ones. << Daniel, Are you saying this correctly? The reason I ask, is I've seen a 1555 error in a system with one job running, only 480 MBs of undo space used, 10 minute retention setting, and an undo tablespace of 12 GB. It doesn't seem that Oracle grabbed an extent from free space before using an expired one. (Retention setting is now much, much higher. Got to watch those hours to seconds conversions.) >>> [EMAIL PROTECTED] 06/18/03 03:19PM >>> Thomas, How long ago was the data for the mview loaded? It is possible that you are running into the scenario where delayed block cleanout is causing the ORA-1555. Is the name of the undo segment in the error the same as the large undo extent? I would suspect that they are different. I have not tested mviews as transactions, but I presume oracle considers it as one large transaction. This would prevent it from acquiring new undo segments, which makes sense as there is one segment that is very large in respect to all the others. There are only 88 segments in the tablespace, both offline and online, not 88 online? Assuming the 88 segments have been shrunk to minimum (128k), they will consume only 10meg. With the numbers you quote, this sounds like the case. SMON awakes periodically to offline undo segments and it may have offlined a segment that was needed by the transaction (it just did not know that at the time). SMON should not offline a segment if the retention time has not been met, but I don't know this for a fact. There is a thought in the deep dark recesses of my mind that the situation may be the result of the expire time algorithm that Oracle uses. (The sound you have just heard is Kirti fleeing from the list before we go down that path again!) That all being said, the fact that there is free space in the tablespace implies that the 1555 is not due to an extent being overwritten. Oracle should grab free space before it grabs other extents, even expired ones. If the process was attempting to acquire an extent and could not find sufficient free space, you would receive the 'Unable to extend segment' error and not the ORA-1555. Since you are the only process running, I think delayed block cleanout (a result of the data loading process and nothing to do with your mview) or segment offlining are your likely culprits. I can't recommend this will work, but give it a shot. Alter the tablespace containing the source table into read only mode, then run the mview statement. The fact that the ts is read-only guarantees that all data contained within has been committed. This solution was suggested to me by a very wise member of the list, so it is worth a try. Daniel Fink BTW, AUM is not a requirement for Flashback Query. However, it is the only configuration Oracle will support for FBQ and it is more likely to succeed with AUM. Thomas Day wrote: > > I'm trying to create a materialized view on a 7G table. I'm using UNDO and > undo_retention=108000, which if I understand correctly is 30 hours. I have > 7G in UNDOTBS1 --- I shouldn't need that much. After 7 hrs I get > ORA-01555: snapshot too old: rollback segment number with name "" too > small > > The FM says: > > ORA-01555 snapshot too old: rollback segment number string with name " > string" too small > Cause: Rollback records needed by a reader for consistent read are > overwritten by other writers. > Action: If in Automatic Undo Management mode, increase the setting of > UNDO_RETENTION. Otherwise, use larger rollback segments. > > UNDOTBS1 still had 3G of space free with about 88 active undo extents, the > largest was 4,154,458,112 bytes. Was it trying to create another 4G > extent? Is there something I am missing? This Automatic stuff doesn't > seem to be so automatic. I can create this using rollback segments but I > wanted to use UNDO because it allows past point in time queries. > > Oracle 9.2.0.1.0 > > Win2K. > > Any thoughts (besides get a real operating system and use rollback > segments) would be greatly appreciated. > > -- > 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 'List
Re: ORA-01555 with Automatic Undo Management mode
Thomas, How long ago was the data for the mview loaded? It is possible that you are running into the scenario where delayed block cleanout is causing the ORA-1555. Is the name of the undo segment in the error the same as the large undo extent? I would suspect that they are different. I have not tested mviews as transactions, but I presume oracle considers it as one large transaction. This would prevent it from acquiring new undo segments, which makes sense as there is one segment that is very large in respect to all the others. There are only 88 segments in the tablespace, both offline and online, not 88 online? Assuming the 88 segments have been shrunk to minimum (128k), they will consume only 10meg. With the numbers you quote, this sounds like the case. SMON awakes periodically to offline undo segments and it may have offlined a segment that was needed by the transaction (it just did not know that at the time). SMON should not offline a segment if the retention time has not been met, but I don't know this for a fact. There is a thought in the deep dark recesses of my mind that the situation may be the result of the expire time algorithm that Oracle uses. (The sound you have just heard is Kirti fleeing from the list before we go down that path again!) That all being said, the fact that there is free space in the tablespace implies that the 1555 is not due to an extent being overwritten. Oracle should grab free space before it grabs other extents, even expired ones. If the process was attempting to acquire an extent and could not find sufficient free space, you would receive the 'Unable to extend segment' error and not the ORA-1555. Since you are the only process running, I think delayed block cleanout (a result of the data loading process and nothing to do with your mview) or segment offlining are your likely culprits. I can't recommend this will work, but give it a shot. Alter the tablespace containing the source table into read only mode, then run the mview statement. The fact that the ts is read-only guarantees that all data contained within has been committed. This solution was suggested to me by a very wise member of the list, so it is worth a try. Daniel Fink BTW, AUM is not a requirement for Flashback Query. However, it is the only configuration Oracle will support for FBQ and it is more likely to succeed with AUM. Thomas Day wrote: > > I'm trying to create a materialized view on a 7G table. I'm using UNDO and > undo_retention=108000, which if I understand correctly is 30 hours. I have > 7G in UNDOTBS1 --- I shouldn't need that much. After 7 hrs I get > ORA-01555: snapshot too old: rollback segment number with name "" too > small > > The FM says: > > ORA-01555 snapshot too old: rollback segment number string with name " > string" too small > Cause: Rollback records needed by a reader for consistent read are > overwritten by other writers. > Action: If in Automatic Undo Management mode, increase the setting of > UNDO_RETENTION. Otherwise, use larger rollback segments. > > UNDOTBS1 still had 3G of space free with about 88 active undo extents, the > largest was 4,154,458,112 bytes. Was it trying to create another 4G > extent? Is there something I am missing? This Automatic stuff doesn't > seem to be so automatic. I can create this using rollback segments but I > wanted to use UNDO because it allows past point in time queries. > > Oracle 9.2.0.1.0 > > Win2K. > > Any thoughts (besides get a real operating system and use rollback > segments) would be greatly appreciated. > > -- > 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).begin:vcard n:Fink;Daniel tel;cell:303.808.3282 tel;work:303.272.3225 x-mozilla-html:TRUE adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:DB Services Lead x-mozilla-cpt:;-4832 fn:Daniel Fink end:vcard
RE: ORA-01555 with Automatic Undo Management mode
My guess is that this has to do with being able to get a read consistent image of the underlying tables of the mview. can you provide us with: 1. The version of Oracle 2. The statement being used to create the mview 3. Is anyone doing anything to the underlying tables while you are creating the mview? 4. Does this table have any LOB columns in it? -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/18/2003 9:59 AM I'm trying to create a materialized view on a 7G table. I'm using UNDO and undo_retention=108000, which if I understand correctly is 30 hours. I have 7G in UNDOTBS1 --- I shouldn't need that much. After 7 hrs I get ORA-01555: snapshot too old: rollback segment number with name "" too small The FM says: ORA-01555 snapshot too old: rollback segment number string with name " string" too small Cause: Rollback records needed by a reader for consistent read are overwritten by other writers. Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments. UNDOTBS1 still had 3G of space free with about 88 active undo extents, the largest was 4,154,458,112 bytes. Was it trying to create another 4G extent? Is there something I am missing? This Automatic stuff doesn't seem to be so automatic. I can create this using rollback segments but I wanted to use UNDO because it allows past point in time queries. Oracle 9.2.0.1.0 Win2K. Any thoughts (besides get a real operating system and use rollback segments) would be greatly appreciated. -- 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: Freeman Robert - IL 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: ORA-01555 with Automatic Undo Management mode
What's in the alert log file for this error? Does it report any undo segments getting off-lined? - Kirti --- Thomas Day <[EMAIL PROTECTED]> wrote: > > Sorry, I'm the only user with access to this database and this was the only > job running. > > Very frustrating two days of reading manuals and metalink has not improved > my "warm and fuzzy" about using UNDO. > > > > > > > Rachel > > > Carmichael To: Multiple recipients of list > ORACLE-L > <[EMAIL PROTECTED]> > > > @yahoo.com> Subject: Re: ORA-01555 with > Automatic Undo > Management mode > Sent by: > > > ml-errors > > > > > > > > > 06/18/2003 12:14 > > > PM > > > Please respond > > > to ORACLE-L > > > > > > > > > > > > > I'm sure Dan Fink will have more (and better) information on this, as > he did a presentation on AUM at IOUG. But we did do a demonstration of > how you can get an ORA-1555 even with AUM. It has nothing to do with > trying to create another extent or how much room is left in the > tablespace but has much to do with the fact that other transactions are > running in the database at the same time. > > Undo_retention (if I remember Dan's presentation correctly) is a "wish" > -- Oracle can and will overwrite the undo segment extent even if the > retention time is not past if space is needed. > > Dan -- did I get it right? > > Rachel > > > --- Thomas Day <[EMAIL PROTECTED]> wrote: > > > > I'm trying to create a materialized view on a 7G table. I'm using > > UNDO and > > undo_retention=108000, which if I understand correctly is 30 hours. > > I have > > 7G in UNDOTBS1 --- I shouldn't need that much. After 7 hrs I get > > ORA-01555: snapshot too old: rollback segment number with name "" > > too > > small > > > > The FM says: > > > > ORA-01555 snapshot too old: rollback segment number string with name > > " > > string" too small > > Cause: Rollback records needed by a reader for consistent read > > are > > overwritten by other writers. > > Action: If in Automatic Undo Management mode, increase the > > setting of > > UNDO_RETENTION. Otherwise, use larger rollback segments. > > > > > > > > UNDOTBS1 still had 3G of space free with about 88 active undo > > extents, the > > largest was 4,154,458,112 bytes. Was it trying to create another 4G > > extent? Is there something I am missing? This Automatic stuff > > doesn't > > seem to be so automatic. I can create this using rollback segments > > but I > > wanted to use UNDO because it allows past point in time queries. > > > > > > Oracle 9.2.0.1.0 > > > > > > Win2K. > > > > > > > > > > > > Any thoughts (besides get a real operating syste
Re: ORA-01555 with Automatic Undo Management mode
Sorry, I'm the only user with access to this database and this was the only job running. Very frustrating two days of reading manuals and metalink has not improved my "warm and fuzzy" about using UNDO. Rachel Carmichael To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: ORA-01555 with Automatic Undo Management mode Sent by: ml-errors 06/18/2003 12:14 PM Please respond to ORACLE-L I'm sure Dan Fink will have more (and better) information on this, as he did a presentation on AUM at IOUG. But we did do a demonstration of how you can get an ORA-1555 even with AUM. It has nothing to do with trying to create another extent or how much room is left in the tablespace but has much to do with the fact that other transactions are running in the database at the same time. Undo_retention (if I remember Dan's presentation correctly) is a "wish" -- Oracle can and will overwrite the undo segment extent even if the retention time is not past if space is needed. Dan -- did I get it right? Rachel --- Thomas Day <[EMAIL PROTECTED]> wrote: > > I'm trying to create a materialized view on a 7G table. I'm using > UNDO and > undo_retention=108000, which if I understand correctly is 30 hours. > I have > 7G in UNDOTBS1 --- I shouldn't need that much. After 7 hrs I get > ORA-01555: snapshot too old: rollback segment number with name "" > too > small > > The FM says: > > ORA-01555 snapshot too old: rollback segment number string with name > " > string" too small > Cause: Rollback records needed by a reader for consistent read > are > overwritten by other writers. > Action: If in Automatic Undo Management mode, increase the > setting of > UNDO_RETENTION. Otherwise, use larger rollback segments. > > > > UNDOTBS1 still had 3G of space free with about 88 active undo > extents, the > largest was 4,154,458,112 bytes. Was it trying to create another 4G > extent? Is there something I am missing? This Automatic stuff > doesn't > seem to be so automatic. I can create this using rollback segments > but I > wanted to use UNDO because it allows past point in time queries. > > > Oracle 9.2.0.1.0 > > > Win2K. > > > > > > Any thoughts (besides get a real operating system and use rollback > segments) would be greatly appreciated. > > > -- > 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). __ Do you Y
Re: ORA-01555 with Automatic Undo Management mode
I'm sure Dan Fink will have more (and better) information on this, as he did a presentation on AUM at IOUG. But we did do a demonstration of how you can get an ORA-1555 even with AUM. It has nothing to do with trying to create another extent or how much room is left in the tablespace but has much to do with the fact that other transactions are running in the database at the same time. Undo_retention (if I remember Dan's presentation correctly) is a "wish" -- Oracle can and will overwrite the undo segment extent even if the retention time is not past if space is needed. Dan -- did I get it right? Rachel --- Thomas Day <[EMAIL PROTECTED]> wrote: > > I'm trying to create a materialized view on a 7G table. I'm using > UNDO and > undo_retention=108000, which if I understand correctly is 30 hours. > I have > 7G in UNDOTBS1 --- I shouldn't need that much. After 7 hrs I get > ORA-01555: snapshot too old: rollback segment number with name "" > too > small > > The FM says: > > ORA-01555 snapshot too old: rollback segment number string with name > " > string" too small > Cause: Rollback records needed by a reader for consistent read > are > overwritten by other writers. > Action: If in Automatic Undo Management mode, increase the > setting of > UNDO_RETENTION. Otherwise, use larger rollback segments. > > > > UNDOTBS1 still had 3G of space free with about 88 active undo > extents, the > largest was 4,154,458,112 bytes. Was it trying to create another 4G > extent? Is there something I am missing? This Automatic stuff > doesn't > seem to be so automatic. I can create this using rollback segments > but I > wanted to use UNDO because it allows past point in time queries. > > > Oracle 9.2.0.1.0 > > > Win2K. > > > > > > Any thoughts (besides get a real operating system and use rollback > segments) would be greatly appreciated. > > > -- > 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). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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).
ORA-01555 with Automatic Undo Management mode
I'm trying to create a materialized view on a 7G table. I'm using UNDO and undo_retention=108000, which if I understand correctly is 30 hours. I have 7G in UNDOTBS1 --- I shouldn't need that much. After 7 hrs I get ORA-01555: snapshot too old: rollback segment number with name "" too small The FM says: ORA-01555 snapshot too old: rollback segment number string with name " string" too small Cause: Rollback records needed by a reader for consistent read are overwritten by other writers. Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments. UNDOTBS1 still had 3G of space free with about 88 active undo extents, the largest was 4,154,458,112 bytes. Was it trying to create another 4G extent? Is there something I am missing? This Automatic stuff doesn't seem to be so automatic. I can create this using rollback segments but I wanted to use UNDO because it allows past point in time queries. Oracle 9.2.0.1.0 Win2K. Any thoughts (besides get a real operating system and use rollback segments) would be greatly appreciated. -- 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).
Re: Re: Undo Analyze Table
There's a good document about this two interesting parameters: http://www.evdbt.com/SearchIntelligenceCBO.doc and also see "asktom" http://asktom.oracle.com/pls/ask/f?p=4950:8:308736813964790489::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6601251003901, === At 2003-06-16, 04:37:00 you wrote: === >The answer is found in the Jonathan Lewis book "Building Efficient Databases". >You should set OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING. >Explanation: >It is realistic to expect a part of an index to be cached and it is also >realistic to expect the index access to be somewhat cheaper then the table >access. These two parameters give the same slant toward the index access path >and nested loops path as RBO. For the rest, buy Jonathan's book. > >On 2003.06.16 02:54, Hussain Ahmed Qadri wrote: >> Hi, >> We have two DBs, a production and a development, identical query was running >> very quickly on the Development and very slowly on the Production. Both have >> the similar structures, same number of indexes and everything. When I >> checked the explain plan, I found out that on the Production DB, it was >> doing a FTS on a couple of tables and was doing an Index scan on the >> Development server. The only difference was that the tables on the >> Production were ANALYZED. >> To confirm my theory, I analyzed the tables on Development and it started >> doing a FTS there as well hence slowing the query down. I know the >> optimizer, after analyzing, would have chosen the better path in its own >> sense but its not producing the desired result and it is taking ages now. >> is there any way to undo that? >> >> Regards, >> >> Hussain Ahmed Qadri >> DBA >> SKMCH&RC >> > >-- >Mladen Gogala >Oracle DBA >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: Mladen Gogala > 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). > >. = = = = = = = = = = = = = = = = = = = = Best regards. Zhai Jingmin [EMAIL PROTECTED] 2003-06-16 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Zhai Jingmin 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: Undo Analyze Table
Title: RE: Undo Analyze Table Thank you all, I'll test and see what helps, Regards, Hussain -Original Message- From: Mladen Gogala [mailto:[EMAIL PROTECTED]] Sent: Monday, June 16, 2003 5:37 PM To: Multiple recipients of list ORACLE-L Subject: Re: Undo Analyze Table The answer is found in the Jonathan Lewis book "Building Efficient Databases". You should set OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING. Explanation: It is realistic to expect a part of an index to be cached and it is also realistic to expect the index access to be somewhat cheaper then the table access. These two parameters give the same slant toward the index access path and nested loops path as RBO. For the rest, buy Jonathan's book. On 2003.06.16 02:54, Hussain Ahmed Qadri wrote: > Hi, > We have two DBs, a production and a development, identical query was running > very quickly on the Development and very slowly on the Production. Both have > the similar structures, same number of indexes and everything. When I > checked the explain plan, I found out that on the Production DB, it was > doing a FTS on a couple of tables and was doing an Index scan on the > Development server. The only difference was that the tables on the > Production were ANALYZED. > To confirm my theory, I analyzed the tables on Development and it started > doing a FTS there as well hence slowing the query down. I know the > optimizer, after analyzing, would have chosen the better path in its own > sense but its not producing the desired result and it is taking ages now. > is there any way to undo that? > > Regards, > > Hussain Ahmed Qadri > DBA > SKMCH&RC > -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Undo Analyze Table
The answer is found in the Jonathan Lewis book "Building Efficient Databases". You should set OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING. Explanation: It is realistic to expect a part of an index to be cached and it is also realistic to expect the index access to be somewhat cheaper then the table access. These two parameters give the same slant toward the index access path and nested loops path as RBO. For the rest, buy Jonathan's book. On 2003.06.16 02:54, Hussain Ahmed Qadri wrote: Hi, We have two DBs, a production and a development, identical query was running very quickly on the Development and very slowly on the Production. Both have the similar structures, same number of indexes and everything. When I checked the explain plan, I found out that on the Production DB, it was doing a FTS on a couple of tables and was doing an Index scan on the Development server. The only difference was that the tables on the Production were ANALYZED. To confirm my theory, I analyzed the tables on Development and it started doing a FTS there as well hence slowing the query down. I know the optimizer, after analyzing, would have chosen the better path in its own sense but its not producing the desired result and it is taking ages now. is there any way to undo that? Regards, Hussain Ahmed Qadri DBA SKMCH&RC -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: Undo Analyze Table
You might try playing with different values for optimizer_index_cost_adj. If this is still at the default of 100, it may be allowing the optimizer to consider the index more expensive than it should. You can change this parameter at the session level, but may consider testing to lower it system wide. >>> [EMAIL PROTECTED] 06/16/03 01:54AM >>> Hi, We have two DBs, a production and a development, identical query was running very quickly on the Development and very slowly on the Production. Both have the similar structures, same number of indexes and everything. When I checked the explain plan, I found out that on the Production DB, it was doing a FTS on a couple of tables and was doing an Index scan on the Development server. The only difference was that the tables on the Production were ANALYZED. To confirm my theory, I analyzed the tables on Development and it started doing a FTS there as well hence slowing the query down. I know the optimizer, after analyzing, would have chosen the better path in its own sense but its not producing the desired result and it is taking ages now. is there any way to undo that? Regards, Hussain Ahmed Qadri DBA SKMCH&RC -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum 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: Undo Analyze Table
Analyze table ... delete statistics; regards Jo Hussain Ahmed Qadri <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 06/16/2003 08:54 Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:Undo Analyze Table Hi, We have two DBs, a production and a development, identical query was running very quickly on the Development and very slowly on the Production. Both have the similar structures, same number of indexes and everything. When I checked the explain plan, I found out that on the Production DB, it was doing a FTS on a couple of tables and was doing an Index scan on the Development server. The only difference was that the tables on the Production were ANALYZED. To confirm my theory, I analyzed the tables on Development and it started doing a FTS there as well hence slowing the query down. I know the optimizer, after analyzing, would have chosen the better path in its own sense but its not producing the desired result and it is taking ages now. is there any way to undo that? Regards, Hussain Ahmed Qadri DBA SKMCH&RC -- 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).
Undo Analyze Table
Title: Undo Analyze Table Hi, We have two DBs, a production and a development, identical query was running very quickly on the Development and very slowly on the Production. Both have the similar structures, same number of indexes and everything. When I checked the explain plan, I found out that on the Production DB, it was doing a FTS on a couple of tables and was doing an Index scan on the Development server. The only difference was that the tables on the Production were ANALYZED. To confirm my theory, I analyzed the tables on Development and it started doing a FTS there as well hence slowing the query down. I know the optimizer, after analyzing, would have chosen the better path in its own sense but its not producing the desired result and it is taking ages now. is there any way to undo that? Regards, Hussain Ahmed Qadri DBA SKMCH&RC
RE: Re[4]: undo tablespace
Title: RE: Re[4]: undo tablespace I think it might be a typo, or I've not had enuf coffee yet today, but the nologging on b.table2 wouldn't impact the redo logging; nologging on a.table1 would, tho... > -Original Message- > From: Breno A. K. Magnago [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, January 29, 2003 10:10 AM > To: Multiple recipients of list ORACLE-L > Subject: Re[4]: undo tablespace > > > Mike, > > What type of operation are you performing on your table? > > INSERT INTO A.TABLE1(COL1,COL2) > SELECT COL1,COL2 > FROM B.TABLE2@DB_LINK; > > I think that command support the NOLOGGING.Is is correct ? > The table B.TABLE2 and all indexes are in NOLOGGING Option. > > -- > Breno A. K. Magnago > mailto:[EMAIL PROTECTED] > > Wednesday, January 29, 2003, 2:04:39 PM, you wrote: > > HMNI> You're correct in saying that your undo blocks are protected by > HMNI> your redo files. What type of operation are you > performing on your > HMNI> table? I ask because only a small subset of commands > support the > HMNI> NOLOGGING feature; the remainder will generate redo as usual. > > HMNI> If you're not using a syntax that supports NOLOGGING maybe you > HMNI> could adapt your job to adopt one. Alternatively you > may find that > HMNI> you just need to optimise your redo log placement in order to > HMNI> handle the load. > > HMNI> Regards, > HMNI> Mike Hately > > > HMNI> -Original Message- > HMNI> Sent: 29 January 2003 14:10 > HMNI> To: Multiple recipients of list ORACLE-L > > > HMNI> Mike, > > HMNI> I asked it because I have a problem. > HMNI> Any insert data in UNDO tablespace generate insert in > REDO Files. > HMNI> Is is correct ? > > HMNI> When I execute a high procedure, many inserts in UNDO > tablespace > HMNI> ocurres, so many inserts in REDO´s are genereate. I > want to avoid > HMNI> this REDO´s generation. My tables and indexes are in NOLOGGING, > HMNI> but I high value of REDO are generate (100 MB each 20 > minutes). It > HMNI> is desnecessary. > > HMNI> Oracle 9i / NT > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Breno A. K. Magnago > 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[4]: undo tablespace
Mike, What type of operation are you performing on your table? INSERT INTO A.TABLE1(COL1,COL2) SELECT COL1,COL2 FROM B.TABLE2@DB_LINK; I think that command support the NOLOGGING.Is is correct ? The table B.TABLE2 and all indexes are in NOLOGGING Option. -- Breno A. K. Magnagomailto:[EMAIL PROTECTED] Wednesday, January 29, 2003, 2:04:39 PM, you wrote: HMNI> You're correct in saying that your undo blocks are protected by your redo HMNI> files. HMNI> What type of operation are you performing on your table? I ask because only HMNI> a small subset of commands support the NOLOGGING feature; the remainder will HMNI> generate redo as usual. HMNI> If you're not using a syntax that supports NOLOGGING maybe you could adapt HMNI> your job to adopt one. HMNI> Alternatively you may find that you just need to optimise your redo log HMNI> placement in order to handle the load. HMNI> Regards, HMNI> Mike Hately HMNI> -Original Message- HMNI> Sent: 29 January 2003 14:10 HMNI> To: Multiple recipients of list ORACLE-L HMNI> Mike, HMNI> I asked it because I have a problem. HMNI> Any insert data in UNDO tablespace generate insert in REDO Files. Is HMNI> is correct ? HMNI> When I execute a high procedure, many inserts in UNDO tablespace HMNI> ocurres, so many inserts in REDO´s are genereate. HMNI> I want to avoid this REDO´s generation. HMNI> My tables and indexes are in NOLOGGING, but I high value of HMNI> REDO are generate (100 MB each 20 minutes). It is desnecessary. HMNI> Oracle 9i / NT -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Breno A. K. Magnago 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: Re[4]: undo tablespace
Breno, There is your problem. Don't try to solve it by trying to eliminate undo. I have run into that situation several times. The key is to set up the database management processes to handle the situation. Things to think about: 1) Increase the size of the device where archive_dump_dest resides 2) NEVER, NEVER, NEVER delete archived redo logs until they are on several different backup tapes. You lose 1 of this beauties and your recovery is halted. 3) Put together a script/process to move the logs from one device to another, then purge them after several days. Dan Fink -Original Message- Sent: Wednesday, January 29, 2003 9:55 AM To: Multiple recipients of list ORACLE-L Dan, Is the generation of redo (and archived logs) causing the system to halt due to the archive_dump_dest filling up? YES I'll work to minimize this situation. Thanks. -- Breno A. K. Magnago mailto:[EMAIL PROTECTED] Wednesday, January 29, 2003, 1:19:50 PM, you wrote: FD> Breno, FD> 100mb of redo in 20 minutes is not all that high. I have seen far FD> worse (as I am sure most on the list have as well). Why do you perceive this FD> as a problem? Are you seeing poor performance or waits? FD> Is the generation of redo (and archived logs) causing the system to FD> halt due to the archive_dump_dest filling up? If so, the problem is not the FD> procedure, but rather the lack of proper process to manage your archive FD> logs. FD> Even with NOLOGGING, UNDO must be generated for read consistency, FD> rollback and recovery. You are not inserting data into the undo tablespace. FD> The undo entries are generated for each operation (insert/update/delete). FD> However, the undo entry for an insert is very small and thus will consume FD> very little undo space and redo. FD> Don't concern yourself with trying to stop the generation of UNDO. FD> You will end up causing yourself more problems that you will ever try to FD> solve. It is part of the Oracle kernel and not modifiable (at least in this FD> release). Focus on performance and decide if 100mb in 20 minutes is really a FD> problem. FD> Dan Fink FD> -Original Message- FD> Sent: Wednesday, January 29, 2003 7:10 AM FD> To: Multiple recipients of list ORACLE-L FD> Mike, FD> I asked it because I have a problem. FD> Any insert data in UNDO tablespace generate insert in REDO Files. Is FD> is correct ? FD> When I execute a high procedure, many inserts in UNDO tablespace FD> ocurres, so many inserts in REDO´s are genereate. FD> I want to avoid this REDO´s generation. FD> My tables and indexes are in NOLOGGING, but I high value of FD> REDO are generate (100 MB each 20 minutes). It is desnecessary. FD> Oracle 9i / NT -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Breno A. K. Magnago 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: Fink, Dan 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: Re[2]: undo tablespace
AFAIK, in 8i+ tablespaces can be created with the"NOLOGGING" clause
LOGGING | NOLOGGING
Specify the default logging attributes of all tables, indexes, and
partitions within the tablespace. LOGGING is the default.
The tablespace-level logging attribute can be overridden by logging
specifications at the table, index, and partition levels.
Only the following operations support the NOLOGGING mode:
DML: direct-load INSERT (serial or parallel), Direct Loader
(SQL*Loader)
DDL: CREATE TABLE ... AS SELECT, CREATE INDEX, ALTER INDEX ...
REBUILD, ALTER INDEX ... REBUILD PARTITION, ALTER INDEX ... SPLIT
PARTITION, ALTER TABLE ... SPLIT PARTITION, and ALTER TABLE ... MOVE
PARTITION
In NOLOGGING mode, data is modified with minimal logging (to mark new
extents INVALID and to record dictionary changes). When applied during
media recovery, the extent invalidation records mark a range of blocks as
logically corrupt, because the redo data is not logged. Therefore, if you
cannot afford to lose the object, you should take a backup after the
NOLOGGING operation.
"Hately, Mike
(NESL-IT)" To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
Subject: RE: Re[2]: undo
tablespace
Sent by:
[EMAIL PROTECTED]
01/29/2003 08:04 AM
Please respond to
ORACLE-L
You're correct in saying that your undo blocks are protected by your redo
files.
What type of operation are you performing on your table? I ask because only
a small subset of commands support the NOLOGGING feature; the remainder
will
generate redo as usual.
If you're not using a syntax that supports NOLOGGING maybe you could adapt
your job to adopt one.
Alternatively you may find that you just need to optimise your redo log
placement in order to handle the load.
Regards,
Mike Hately
-Original Message-
Sent: 29 January 2003 14:10
To: Multiple recipients of list ORACLE-L
Mike,
I asked it because I have a problem.
Any insert data in UNDO tablespace generate insert in REDO Files. Is
is correct ?
When I execute a high procedure, many inserts in UNDO tablespace
ocurres, so many inserts in REDO´s are genereate.
I want to avoid this REDO´s generation.
My tables and indexes are in NOLOGGING, but I high value of
REDO are generate (100 MB each 20 minutes). It is desnecessary.
Oracle 9i / NT
--
Breno A. K. Magnago
mailto:[EMAIL PROTECTED]
Mercantil de Alimentos Soares
Wednesday, January 29, 2003, 10:29:15 AM, you wrote:
HMNI> Breno,
HMNI> There's no way to do this because it's the central pillar of Oracle's
read
HMNI> consistency mechanism.
HMNI> It's possible to minimise or suppress redo but undo is out of your
control.
HMNI> regards,
HMNI> Mike Hately
HMNI> -Original Message-
HMNI> Sent: 29 January 2003 11:39
HMNI> To: Multiple recipients of list ORACLE-L
HMNI> I have a high procedure (many INSERT's and UPDATE´s).
HMNI> This procedure generate insert's in UNDO TableSpace for rollback.
HMNI> I want to know if exists any way for don´t generate insert´s in UNDO
HMNI> Tablespace.
HMNI> Oracle 9i / NT
HMNI> Thanks.
**
The information contained in this e-mail is confidential and
intended only for the use of the addressee. If the reader of
this message is not the addressee, you are hereby notified
that you
Re[4]: undo tablespace
Dan, Is the generation of redo (and archived logs) causing the system to halt due to the archive_dump_dest filling up? YES I'll work to minimize this situation. Thanks. -- Breno A. K. Magnagomailto:[EMAIL PROTECTED] Wednesday, January 29, 2003, 1:19:50 PM, you wrote: FD> Breno, FD> 100mb of redo in 20 minutes is not all that high. I have seen far FD> worse (as I am sure most on the list have as well). Why do you perceive this FD> as a problem? Are you seeing poor performance or waits? FD> Is the generation of redo (and archived logs) causing the system to FD> halt due to the archive_dump_dest filling up? If so, the problem is not the FD> procedure, but rather the lack of proper process to manage your archive FD> logs. FD> Even with NOLOGGING, UNDO must be generated for read consistency, FD> rollback and recovery. You are not inserting data into the undo tablespace. FD> The undo entries are generated for each operation (insert/update/delete). FD> However, the undo entry for an insert is very small and thus will consume FD> very little undo space and redo. FD> Don't concern yourself with trying to stop the generation of UNDO. FD> You will end up causing yourself more problems that you will ever try to FD> solve. It is part of the Oracle kernel and not modifiable (at least in this FD> release). Focus on performance and decide if 100mb in 20 minutes is really a FD> problem. FD> Dan Fink FD> -Original Message- FD> Sent: Wednesday, January 29, 2003 7:10 AM FD> To: Multiple recipients of list ORACLE-L FD> Mike, FD> I asked it because I have a problem. FD> Any insert data in UNDO tablespace generate insert in REDO Files. Is FD> is correct ? FD> When I execute a high procedure, many inserts in UNDO tablespace FD> ocurres, so many inserts in REDO´s are genereate. FD> I want to avoid this REDO´s generation. FD> My tables and indexes are in NOLOGGING, but I high value of FD> REDO are generate (100 MB each 20 minutes). It is desnecessary. FD> Oracle 9i / NT -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Breno A. K. Magnago 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: Re[2]: undo tablespace
I agree in principal with Dan's thesis, but I'll add something. Don't do things that generate unnecessary undo and redo, either. One common example is SQL that updates a column to a value that it already contains. For example, we see things like setting a flag='y' without checking that the flag doesn't already have a 'y' value. Things like this provide zero business value, but they degrade operational performance and recovery operations, and they consume unnecessary space in your undo and redo. There are probably a million others... Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - 2003 Hotsos Symposium, Feb 9-12 Dallas - RMOUG Training Days 2003, Mar 5-6 Denver - Hotsos Clinic 101, Mar 26-28 London -Original Message- Sent: Wednesday, January 29, 2003 9:20 AM To: Multiple recipients of list ORACLE-L Breno, 100mb of redo in 20 minutes is not all that high. I have seen far worse (as I am sure most on the list have as well). Why do you perceive this as a problem? Are you seeing poor performance or waits? Is the generation of redo (and archived logs) causing the system to halt due to the archive_dump_dest filling up? If so, the problem is not the procedure, but rather the lack of proper process to manage your archive logs. Even with NOLOGGING, UNDO must be generated for read consistency, rollback and recovery. You are not inserting data into the undo tablespace. The undo entries are generated for each operation (insert/update/delete). However, the undo entry for an insert is very small and thus will consume very little undo space and redo. Don't concern yourself with trying to stop the generation of UNDO. You will end up causing yourself more problems that you will ever try to solve. It is part of the Oracle kernel and not modifiable (at least in this release). Focus on performance and decide if 100mb in 20 minutes is really a problem. Dan Fink -Original Message- Sent: Wednesday, January 29, 2003 7:10 AM To: Multiple recipients of list ORACLE-L Mike, I asked it because I have a problem. Any insert data in UNDO tablespace generate insert in REDO Files. Is is correct ? When I execute a high procedure, many inserts in UNDO tablespace ocurres, so many inserts in REDO´s are genereate. I want to avoid this REDO´s generation. My tables and indexes are in NOLOGGING, but I high value of REDO are generate (100 MB each 20 minutes). It is desnecessary. Oracle 9i / NT -- Breno A. K. Magnago mailto:[EMAIL PROTECTED] Mercantil de Alimentos Soares Wednesday, January 29, 2003, 10:29:15 AM, you wrote: HMNI> Breno, HMNI> There's no way to do this because it's the central pillar of Oracle's read HMNI> consistency mechanism. HMNI> It's possible to minimise or suppress redo but undo is out of your control. HMNI> regards, HMNI> Mike Hately HMNI> -Original Message- HMNI> Sent: 29 January 2003 11:39 HMNI> To: Multiple recipients of list ORACLE-L HMNI> I have a high procedure (many INSERT's and UPDATE´s). HMNI> This procedure generate insert's in UNDO TableSpace for rollback. HMNI> I want to know if exists any way for don´t generate insert´s in UNDO HMNI> Tablespace. HMNI> Oracle 9i / NT HMNI> Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Breno A. K. Magnago 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: Fink, Dan 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: Cary Millsap INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services ---
Re: Re[2]: undo tablespace
breno, i believe the 'no logging' option only applies to data that is direct loaded into your tables therefore, you may be able to bypass logging if you used sqlldr or 'create table as select' good luck, steve "Breno A. K. Magnago" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 01/29/2003 09:09 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject: Re[2]: undo tablespace Mike, I asked it because I have a problem. Any insert data in UNDO tablespace generate insert in REDO Files. Is is correct ? When I execute a high procedure, many inserts in UNDO tablespace ocurres, so many inserts in REDO´s are genereate. I want to avoid this REDO´s generation. My tables and indexes are in NOLOGGING, but I high value of REDO are generate (100 MB each 20 minutes). It is desnecessary. Oracle 9i / NT -- Breno A. K. Magnago mailto:[EMAIL PROTECTED] Mercantil de Alimentos Soares Wednesday, January 29, 2003, 10:29:15 AM, you wrote: HMNI> Breno, HMNI> There's no way to do this because it's the central pillar of Oracle's read HMNI> consistency mechanism. HMNI> It's possible to minimise or suppress redo but undo is out of your control. HMNI> regards, HMNI> Mike Hately HMNI> -Original Message- HMNI> Sent: 29 January 2003 11:39 HMNI> To: Multiple recipients of list ORACLE-L HMNI> I have a high procedure (many INSERT's and UPDATE´s). HMNI> This procedure generate insert's in UNDO TableSpace for rollback. HMNI> I want to know if exists any way for don´t generate insert´s in UNDO HMNI> Tablespace. HMNI> Oracle 9i / NT HMNI> Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Breno A. K. Magnago 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: Re[2]: undo tablespace
You're correct in saying that your undo blocks are protected by your redo files. What type of operation are you performing on your table? I ask because only a small subset of commands support the NOLOGGING feature; the remainder will generate redo as usual. If you're not using a syntax that supports NOLOGGING maybe you could adapt your job to adopt one. Alternatively you may find that you just need to optimise your redo log placement in order to handle the load. Regards, Mike Hately -Original Message- Sent: 29 January 2003 14:10 To: Multiple recipients of list ORACLE-L Mike, I asked it because I have a problem. Any insert data in UNDO tablespace generate insert in REDO Files. Is is correct ? When I execute a high procedure, many inserts in UNDO tablespace ocurres, so many inserts in REDO´s are genereate. I want to avoid this REDO´s generation. My tables and indexes are in NOLOGGING, but I high value of REDO are generate (100 MB each 20 minutes). It is desnecessary. Oracle 9i / NT -- Breno A. K. Magnago mailto:[EMAIL PROTECTED] Mercantil de Alimentos Soares Wednesday, January 29, 2003, 10:29:15 AM, you wrote: HMNI> Breno, HMNI> There's no way to do this because it's the central pillar of Oracle's read HMNI> consistency mechanism. HMNI> It's possible to minimise or suppress redo but undo is out of your control. HMNI> regards, HMNI> Mike Hately HMNI> -Original Message- HMNI> Sent: 29 January 2003 11:39 HMNI> To: Multiple recipients of list ORACLE-L HMNI> I have a high procedure (many INSERT's and UPDATE´s). HMNI> This procedure generate insert's in UNDO TableSpace for rollback. HMNI> I want to know if exists any way for don´t generate insert´s in UNDO HMNI> Tablespace. HMNI> Oracle 9i / NT HMNI> Thanks. ** The information contained in this e-mail is confidential and intended only for the use of the addressee. If the reader of this message is not the addressee, you are hereby notified that you have received this e-mail in error and you must not copy, disseminate, distribute, use or take any action as a result of the information contained in it. If you have received this e-mail in error, please notify [EMAIL PROTECTED] (UK 01384 275454) and delete it immediately from your system. Neither Npower nor any of the other companies in the Innogy group from whom this e-mail originates accept any responsibility for losses or damage as a result of any viruses and it is your responsibility to check attachments (if any) for viruses. Npower Limited Registered office: Windmill Hill Business Park, Whitehill Way, Swindon SN5 6PB. Registered in England and Wales: number 3653277 This e-mail may be sent on behalf of a member of the Innogy group of companies. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (NESL-IT) 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: Re[2]: undo tablespace
Breno, 100mb of redo in 20 minutes is not all that high. I have seen far worse (as I am sure most on the list have as well). Why do you perceive this as a problem? Are you seeing poor performance or waits? Is the generation of redo (and archived logs) causing the system to halt due to the archive_dump_dest filling up? If so, the problem is not the procedure, but rather the lack of proper process to manage your archive logs. Even with NOLOGGING, UNDO must be generated for read consistency, rollback and recovery. You are not inserting data into the undo tablespace. The undo entries are generated for each operation (insert/update/delete). However, the undo entry for an insert is very small and thus will consume very little undo space and redo. Don't concern yourself with trying to stop the generation of UNDO. You will end up causing yourself more problems that you will ever try to solve. It is part of the Oracle kernel and not modifiable (at least in this release). Focus on performance and decide if 100mb in 20 minutes is really a problem. Dan Fink -Original Message- Sent: Wednesday, January 29, 2003 7:10 AM To: Multiple recipients of list ORACLE-L Mike, I asked it because I have a problem. Any insert data in UNDO tablespace generate insert in REDO Files. Is is correct ? When I execute a high procedure, many inserts in UNDO tablespace ocurres, so many inserts in REDO´s are genereate. I want to avoid this REDO´s generation. My tables and indexes are in NOLOGGING, but I high value of REDO are generate (100 MB each 20 minutes). It is desnecessary. Oracle 9i / NT -- Breno A. K. Magnago mailto:[EMAIL PROTECTED] Mercantil de Alimentos Soares Wednesday, January 29, 2003, 10:29:15 AM, you wrote: HMNI> Breno, HMNI> There's no way to do this because it's the central pillar of Oracle's read HMNI> consistency mechanism. HMNI> It's possible to minimise or suppress redo but undo is out of your control. HMNI> regards, HMNI> Mike Hately HMNI> -Original Message- HMNI> Sent: 29 January 2003 11:39 HMNI> To: Multiple recipients of list ORACLE-L HMNI> I have a high procedure (many INSERT's and UPDATE´s). HMNI> This procedure generate insert's in UNDO TableSpace for rollback. HMNI> I want to know if exists any way for don´t generate insert´s in UNDO HMNI> Tablespace. HMNI> Oracle 9i / NT HMNI> Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Breno A. K. Magnago 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: Fink, Dan 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: Re[2]: undo tablespace
think CAREFULLY about this as it invalidates your recovery procedures. Nologging means there is nothing in the redo logs to be applied. and your backup is invalid. Other than that, I suggest you do some research in the Oracle docs. A VERY brief check (search on nologging) brought me this information: Direct-path INSERT is subject to a number of restrictions. If any of these restrictions is violated, then Oracle executes conventional INSERT serially without returning any message (unless otherwise noted): * You can have multiple direct-path INSERT statements in a single transaction, with or without other DML statements. However, after one DML statement alters a particular table, partition, or index, no other DML statement in the transaction can access that table, partition, or index. * Queries that access the same table, partition, or index are allowed before the direct-path INSERT statement, but not after it. * If any serial or parallel statement attempts to access a table that has already been modified by a direct-path INSERT in the same transaction, then Oracle returns an error and rejects the statement. * The ROW_LOCKING initialization parameter cannot be set to INTENT. * The target table cannot be index organized or clustered. * The target table cannot contain object type or LOB columns. * The target table cannot have any triggers or referential integrity constraints defined on it. * The target table cannot be replicated. * A transaction containing a direct-path INSERT statement cannot be or become distributed. --- "Breno A. K. Magnago" <[EMAIL PROTECTED]> wrote: > Mike, > > I asked it because I have a problem. > Any insert data in UNDO tablespace generate insert in REDO Files. Is > is correct ? > > When I execute a high procedure, many inserts in UNDO tablespace > ocurres, so many inserts in REDO´s are genereate. > I want to avoid this REDO´s generation. > My tables and indexes are in NOLOGGING, but I high value of > REDO are generate (100 MB each 20 minutes). It is desnecessary. > > Oracle 9i / NT > > -- > Breno A. K. Magnago > mailto:[EMAIL PROTECTED] > Mercantil de Alimentos Soares > > Wednesday, January 29, 2003, 10:29:15 AM, you wrote: > > HMNI> Breno, > HMNI> There's no way to do this because it's the central pillar of > Oracle's read > HMNI> consistency mechanism. > HMNI> It's possible to minimise or suppress redo but undo is out of > your control. > > HMNI> regards, > HMNI> Mike Hately > > HMNI> -Original Message- > HMNI> Sent: 29 January 2003 11:39 > HMNI> To: Multiple recipients of list ORACLE-L > > > HMNI> I have a high procedure (many INSERT's and UPDATE´s). > HMNI> This procedure generate insert's in UNDO TableSpace for > rollback. > HMNI> I want to know if exists any way for don´t generate insert´s in > UNDO > HMNI> Tablespace. > > HMNI> Oracle 9i / NT > > HMNI> Thanks. > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Breno A. K. Magnago > 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). > __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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[2]: undo tablespace
Mike, I asked it because I have a problem. Any insert data in UNDO tablespace generate insert in REDO Files. Is is correct ? When I execute a high procedure, many inserts in UNDO tablespace ocurres, so many inserts in REDO´s are genereate. I want to avoid this REDO´s generation. My tables and indexes are in NOLOGGING, but I high value of REDO are generate (100 MB each 20 minutes). It is desnecessary. Oracle 9i / NT -- Breno A. K. Magnagomailto:[EMAIL PROTECTED] Mercantil de Alimentos Soares Wednesday, January 29, 2003, 10:29:15 AM, you wrote: HMNI> Breno, HMNI> There's no way to do this because it's the central pillar of Oracle's read HMNI> consistency mechanism. HMNI> It's possible to minimise or suppress redo but undo is out of your control. HMNI> regards, HMNI> Mike Hately HMNI> -Original Message- HMNI> Sent: 29 January 2003 11:39 HMNI> To: Multiple recipients of list ORACLE-L HMNI> I have a high procedure (many INSERT's and UPDATE´s). HMNI> This procedure generate insert's in UNDO TableSpace for rollback. HMNI> I want to know if exists any way for don´t generate insert´s in UNDO HMNI> Tablespace. HMNI> Oracle 9i / NT HMNI> Thanks. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Breno A. K. Magnago 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: undo tablespace
Breno, There's no way to do this because it's the central pillar of Oracle's read consistency mechanism. It's possible to minimise or suppress redo but undo is out of your control. regards, Mike Hately -Original Message- Sent: 29 January 2003 11:39 To: Multiple recipients of list ORACLE-L I have a high procedure (many INSERT's and UPDATE´s). This procedure generate insert's in UNDO TableSpace for rollback. I want to know if exists any way for don´t generate insert´s in UNDO Tablespace. Oracle 9i / NT Thanks. -- Breno A. K. Magnago mailto:[EMAIL PROTECTED] Mercantil de Alimentos Soares -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Breno A. K. Magnago 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). ** The information contained in this e-mail is confidential and intended only for the use of the addressee. If the reader of this message is not the addressee, you are hereby notified that you have received this e-mail in error and you must not copy, disseminate, distribute, use or take any action as a result of the information contained in it. If you have received this e-mail in error, please notify [EMAIL PROTECTED] (UK 01384 275454) and delete it immediately from your system. ** ** The information contained in this e-mail is confidential and intended only for the use of the addressee. If the reader of this message is not the addressee, you are hereby notified that you have received this e-mail in error and you must not copy, disseminate, distribute, use or take any action as a result of the information contained in it. If you have received this e-mail in error, please notify [EMAIL PROTECTED] (UK 01384 275454) and delete it immediately from your system. Neither Npower nor any of the other companies in the Innogy group from whom this e-mail originates accept any responsibility for losses or damage as a result of any viruses and it is your responsibility to check attachments (if any) for viruses. Npower Limited Registered office: Windmill Hill Business Park, Whitehill Way, Swindon SN5 6PB. Registered in England and Wales: number 3653277 This e-mail may be sent on behalf of a member of the Innogy group of companies. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (NESL-IT) 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).
undo tablespace
I have a high procedure (many INSERT's and UPDATE´s). This procedure generate insert's in UNDO TableSpace for rollback. I want to know if exists any way for don´t generate insert´s in UNDO Tablespace. Oracle 9i / NT Thanks. -- Breno A. K. Magnago mailto:[EMAIL PROTECTED] Mercantil de Alimentos Soares -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Breno A. K. Magnago 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: Re: Experiences with Automatic Undo Management
the only time i've had the need for a regular shutdown was when there was that lovely old solaris problem where an os clock wrapped and put junk into the controlfile (and thus made a little bit of a mess of the database) so a db had to be bounced at least once every (I think) 248 days, but other than that, i view shutting the database as wasting money... I say money because your license fee's or part thereof goes into building trendy little things like buffer cache management, library cache management etc etc...when you shutdown, its like throwing away some of your license dollars there of course are some (i would contend rare) occasions when a regular shutdown policy can be justified (eg memory leak accumulation etc), but i try to avoid it at all costs if I can cheers connor --- chao_ping <[EMAIL PROTECTED]> wrote: > Connor McDonald, > Hi, for IO balance , I think in most case, we put > datafiles on raid so it is not a problem? Or you can > use multiple datafiles, which will give you solution > when no raid or multiple raid is used and you want > to balance io? > I used AUM too in my rac system, but my system is > most read only so did not see anything different , > performance is pretty good. > By the way, do friends in this list also suggest > never shutdown database? I prefer to shutdown > db/unix every several monthes, to give out a stable > os enviroment. > > > > > > Regards > zhu chao > msn:[EMAIL PROTECTED] > www.happyit.net > www.cnoug.org(China Oracle User Group) > > === 2003-01-27 15:33:00 ,you wrote£º=== > > >Maybe I'm just a cynic but I view AUM as being akin > >the SAME disk layout policy. That is, (and this is > >subjective figures) it gives 90 optimal performance > >in about 90 of databases out there. > > > >Since going to aum on a "significant" (read: > >reasonable number of users and workload) database I > >used to manage in the UK, I found aum gave ever so > >slightly increased undo figures in v$waitstat, but > not > >really enough to discount it. Similarly, file IO > was > >slight increased (which I have put down to its > fairly > >aggressive nature of enforcing retention time). > > > >One thing that is good about aum, is that it > >encourages people to never shut their databases (a > >policy I advocate strongly). > > > >I would like to be able to have multiple undo > tspaces > >simply as an aid to IO balancing > > > >Cheers > >Connor > > > > --- "Fink, Dan" <[EMAIL PROTECTED]> wrote: > This > is > >more of a survey than a question about > >> problems. > >> > >> For those of you using automatic undo management, > >> what have been your > >> experiences, both good and bad. Are you also > using > >> flashback query and what > >> are your experiences? > >> > >> Thank you for all the responses, > >> > >> Dan Fink > >> > >> > > > >= > >Connor McDonald > >web: http://www.oracledba.co.uk > >web: http://www.oaktable.net > >email: [EMAIL PROTECTED] > > > >"GIVE a man a fish and he will eat for a day. But > TEACH him how to fish, and...he will sit in a boat > and drink beer all day" > > > >__ > >Do You Yahoo!? > >Everything you'll ever need on one web page > >from News and Sport to Email and Music Charts > >http://uk.my.yahoo.com > >-- > >Please see the official ORACLE-L FAQ: > http://www.orafaq.net > >-- > >Author: =?iso-8859-1?q?Connor=20McDonald?= > > 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: chao_ping > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.co
RE: Experiences with Automatic Undo Management
>> I would like to be able to have multiple undo tspaces simply as an aid to IO balancing... Connor, you can... Oooo... You meant you want them active at the same TIME :-) They are saving that as a "new" feature for some later release I'm sure. It's probably on some marketing guys white board right now... Cheers! Robert -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Robert Freeman 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: Re: Experiences with Automatic Undo Management
Connor McDonald, Hi, for IO balance , I think in most case, we put datafiles on raid so it is not a problem? Or you can use multiple datafiles, which will give you solution when no raid or multiple raid is used and you want to balance io? I used AUM too in my rac system, but my system is most read only so did not see anything different , performance is pretty good. By the way, do friends in this list also suggest never shutdown database? I prefer to shutdown db/unix every several monthes, to give out a stable os enviroment. Regards zhu chao msn:[EMAIL PROTECTED] www.happyit.net www.cnoug.org(China Oracle User Group) === 2003-01-27 15:33:00 ,you wrote£º=== >Maybe I'm just a cynic but I view AUM as being akin >the SAME disk layout policy. That is, (and this is >subjective figures) it gives 90 optimal performance >in about 90 of databases out there. > >Since going to aum on a "significant" (read: >reasonable number of users and workload) database I >used to manage in the UK, I found aum gave ever so >slightly increased undo figures in v$waitstat, but not >really enough to discount it. Similarly, file IO was >slight increased (which I have put down to its fairly >aggressive nature of enforcing retention time). > >One thing that is good about aum, is that it >encourages people to never shut their databases (a >policy I advocate strongly). > >I would like to be able to have multiple undo tspaces >simply as an aid to IO balancing > >Cheers >Connor > > --- "Fink, Dan" <[EMAIL PROTECTED]> wrote: > This is >more of a survey than a question about >> problems. >> >> For those of you using automatic undo management, >> what have been your >> experiences, both good and bad. Are you also using >> flashback query and what >> are your experiences? >> >> Thank you for all the responses, >> >> Dan Fink >> >> > >= >Connor McDonald >web: http://www.oracledba.co.uk >web: http://www.oaktable.net >email: [EMAIL PROTECTED] > >"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he >will sit in a boat and drink beer all day" > >__ >Do You Yahoo!? >Everything you'll ever need on one web page >from News and Sport to Email and Music Charts >http://uk.my.yahoo.com >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.net >-- >Author: =?iso-8859-1?q?Connor=20McDonald?= > 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: chao_ping 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: Experiences with Automatic Undo Management
Maybe I'm just a cynic but I view AUM as being akin the SAME disk layout policy. That is, (and this is subjective figures) it gives 90% optimal performance in about 90% of databases out there. Since going to aum on a "significant" (read: reasonable number of users and workload) database I used to manage in the UK, I found aum gave ever so slightly increased undo figures in v$waitstat, but not really enough to discount it. Similarly, file IO was slight increased (which I have put down to its fairly aggressive nature of enforcing retention time). One thing that is good about aum, is that it encourages people to never shut their databases (a policy I advocate strongly). I would like to be able to have multiple undo tspaces simply as an aid to IO balancing Cheers Connor --- "Fink, Dan" <[EMAIL PROTECTED]> wrote: > This is more of a survey than a question about > problems. > > For those of you using automatic undo management, > what have been your > experiences, both good and bad. Are you also using > flashback query and what > are your experiences? > > Thank you for all the responses, > > Dan Fink > > = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day" __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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: Experiences with Automatic Undo Management
We are using AUM on all databases(9014). Works great and we manipulate retention time as needed. Had one encounter with flashback query when got a call in lunch hour that someone updated wrong data in production. Flashback worked like a charm and data was restored within 15 minutes. -Shaleen - Original Message - From: Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L Sent: Monday, January 27, 2003 8:49 AM Subject: RE: Experiences with Automatic Undo Management Dan, We are using AUM on ALL our PROD/DEVL/TEST environments, no problems so far. Haven't used much of flashback though ... Are you looking for anything specific? The DB versions are 9012 and 9202 ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]]Sent: Monday, January 27, 2003 10:09 AMTo: Multiple recipients of list ORACLE-LSubject: Experiences with Automatic Undo Management This is more of a survey than a question about problems. For those of you using automatic undo management, what have been your experiences, both good and bad. Are you also using flashback query and what are your experiences? Thank you for all the responses, Dan Fink
RE: Experiences with Automatic Undo Management
Dan, We are using AUM on ALL our PROD/DEVL/TEST environments, no problems so far. Haven't used much of flashback though ... Are you looking for anything specific? The DB versions are 9012 and 9202 ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message-From: Fink, Dan [mailto:[EMAIL PROTECTED]]Sent: Monday, January 27, 2003 10:09 AMTo: Multiple recipients of list ORACLE-LSubject: Experiences with Automatic Undo Management This is more of a survey than a question about problems. For those of you using automatic undo management, what have been your experiences, both good and bad. Are you also using flashback query and what are your experiences? Thank you for all the responses, Dan Fink This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
Re: Experiences with Automatic Undo Management
We are using automatic undo management (version 9.2) - no problems so far. Not using flashback query - so, no opinion on that. Igor Neyman, OCP DBA[EMAIL PROTECTED] - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Monday, January 27, 2003 10:09 AM Subject: Experiences with Automatic Undo Management This is more of a survey than a question about problems. For those of you using automatic undo management, what have been your experiences, both good and bad. Are you also using flashback query and what are your experiences? Thank you for all the responses, Dan Fink
Experiences with Automatic Undo Management
This is more of a survey than a question about problems. For those of you using automatic undo management, what have been your experiences, both good and bad. Are you also using flashback query and what are your experiences? Thank you for all the responses, Dan Fink
RE: Undo Segment of 4GB for 1mn 4col update ?
At the next set of runs, I did increment the counter variable.
However as it is in a cursor and loop, the loop should still
loop through all the records in the table and then error out
with sql%notfound after the last record ?
Anyway, see my next mail on the timings when I ran it
thrice with 100,000 row updates.
Hemant
At 07:33 PM 20-01-03 -0800, you wrote:
It seems like you are in an infinte loop. Your counter cntr
never gets incremented.
-Original Message-
Sent: Monday, January 20, 2003 8:19 PM
To: Multiple recipients of list ORACLE-L
I have been trying to run a "benchmark" of a server
[9iRel2 on HPUX]
The database is 9.2.0.2 with Extent Management Local and
an Undo Tablespace.
This is my table :
create table txn_table
(setrangenumber(2) not null,
col1 varchar2(6),
col2 varchar2(255),
col3 number,
col4 varchar2(45),
update_date date
);
create index txn_table_setnumber_n1
on txn_table(setrange);
create index txn_table_update_dt_n1
on txn_table(update_date);
{SETRANGE will have values 0 to 5 and I am deliberately indexing this column
to see if the database uses the index or does a FTS)
The INSERT of 1 million records took 02:21.86 [2min] :
DECLARE
BEGIN
FOR i IN 1..10 LOOP
IF MOD(i,5) = 0 THEN -- multiple of 5
INSERT INTO txn_table VALUES (mod(i,5), to_char(i), 'the quick brown
fox jumps over the lazy dog',i*2.4,'multiple of 5 ',sysdate);
ELSE
INSERT INTO txn_table VALUES (mod(i,5),to_char(i)||'
','zxcv.,mnbasdfgf;lkjhjqwertpoiuyu',i*3-4,'not a multiple',sysdate);
END IF;
END LOOP;
COMMIT;
END;
/
However, my Update initially ran out of Undo Tablespace which had grown to
2GB. I found that UNDO_RETENTION was 10800, reduced it to 30 and even
bounced the Instance before re-running the Update. [I had also added 2 more
files to the Undo Tablespace].
During the first round of testing, another user was testing a WebMethods
application. However, during the second round I was supposed to be the only
person on the instance [with OEM connecting as DBSNMP, other than my SQLPlus
session]. Yet, the update failed and all three Undo Tablespace files had
grown to 2GB each.
Why should the update take 5hours ? Why should it take some much undo ? Is
the logic of the update plsql block wrong ? [I haven't put a c1%notfound ;
I am using rowid
from the fetch to go back to the table and update it]
17:13:00 SQL> set serveroutput on size 5;
17:13:00 SQL>
17:13:00 SQL> DECLARE
17:13:00 2
17:13:00 3 CURSOR C1 is
17:13:00 4 SELECT SETRANGE,COL1,COL2, rowid
17:13:00 5 from TXN_TABLE;
17:13:00 6
17:13:00 7
17:13:00 8 P_SN number;
17:13:00 9 P_C1 varchar2(6);
17:13:00 10 P_C2 varchar2(255);
17:13:00 11 P_Dvarchar2(15);
17:13:00 12 P_Row rowid;
17:13:00 13
17:13:00 14 cntr number;
17:13:00 15
17:13:00 16 BEGIN
17:13:00 17 cntr := 0;
17:13:00 18 OPEN C1;
17:13:00 19loop
17:13:00 20FETCH C1 into P_SN, P_C1, P_C2, P_Row ;
17:13:00 21 update txn_table set col4 =
'updated'||to_char(mod(p_sn,5)),
17:13:00 22 update_date = sysdate
17:13:00 23 where rowid = P_Row ;
17:13:00 24
17:13:00 25if cntr = 100 then
17:13:00 26 dbms_output.put_line('exiting ...');
17:13:00 27 exit;
17:13:00 28end if;
17:13:00 29end loop;
17:13:00 30
17:13:00 31 COMMIT;
17:13:00 32 END;
17:13:00 33 /
DECLARE
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8192 in undo tablespace 'UNDOTBS1'
ORA-06512: at line 21
Elapsed: 05:31:09.32
22:44:09 SQL>
22:44:09 SQL> spool off
1* select usn, extents, rssize, xacts, writes, gets, optsize, hwmsize
SQL> /
USNEXTENTS RSSIZE XACTS WRITES GETSOPTSIZE
-- -- -- -- -- -- --
HWMSIZE
--
0 7 450560 0 84602052885
450560
1 2 122880 0 2308802056248
7462912
2 2 122880 0 18442538642815995
4234141696
3 38511488 0 2925022061328
8511488
4 31171456 0 2703522057293
1171456
5 31171456 0 2358682056307
2220032
6 31171456 0 2392342056328
2220032
USNEXTENTS RSSIZE XACTS WRITES GETSOPTSIZE
-- -- -- -- -- -- --
HWMSIZE
--
7 31171456 0 3506062058513
2220032
8 31171456 0 2851342056422
1171456
9 2 122880 0 237370 14800561
2416041984
10 2
Fwd: Fwd: Undo Segment of 4GB for 1mn 4col update ?
Hmm. Running the update with 100,000 records in the table : Now I get different run-times : 1. 01hr:16min:41.55sec, 386 log-switches [10MB redologs] 2. 00hr:03min:39.76sec, 33 log-switches 3. 00h4:03min:37.56sec, 32 log-switches What I can see is that the SMON was still busy when I started the first run after a SHUTDOWN ABORT and STARTUP [and also in the 5-hour failed attempt]. There doesn't seem to be any Row-Migration : SQL> analyze table txn_user.txn_table compute statistics; Table analyzed. SQL> select * from dba_tables where table_name = 'TXN_TABLE'; OWNER TABLE_NAME -- -- TABLESPACE_NAMECLUSTER_NAME -- -- IOT_NAME PCT_FREE PCT_USED INI_TRANS MAX_TRANS -- -- -- -- -- INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE FREELISTS -- --- --- --- -- FREELIST_GROUPS LOG B NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT --- --- - -- -- -- -- AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS DEGREE INSTANCES --- - --- -- -- CACHE TABLE_LO SAMPLE_SIZE LAST_ANAL PAR IOT_TYPE T S NES BUFFER_ ROW_MOVE - --- - --- - - --- --- GLO USE DURATIONSKIP_COR MON CLUSTER_OWNER DEPENDEN --- --- --- --- -- TXN_USER TXN_TABLE SYSTEM 10 40 1255 65536 1 2147483645 1 1 YES N 10 1078 73 1433 0 70 7420 5 1 1 N ENABLED 10 21-JAN-03 NO N N NO DEFAULT DISABLED NO NO DISABLED NO DISABLED SQL> exit What AM I missing ? Some, silly error .. something I am overlooking ... Oviously, there was some other activity going on.. SMON had been cleaning up the earlier, failed, update. Was it also deleting freed extents in the Undo Tablespace ? [Locally-Managed, SYSTEM Allocation, AUTOMATIC SegmentSpaceManagement] I am going to end up with egg on my face. Hemant - Forwarded message from Hemant K Chitale <[EMAIL PROTECTED]> - Date: Mon, 20 Jan 2003 17:43:44 -0800 Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> oops. The "cntr := cntr+1" is missing from the update. The previous update round didn't have a counter, though. I am re-running the update now. Hemant Hemant K Chitale http://hkchital.tripod.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hemant K Chitale 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: Undo Segment of 4GB for 1mn 4col update ?
It seems like you are in an infinte loop. Your counter cntr
never gets incremented.
-Original Message-
Sent: Monday, January 20, 2003 8:19 PM
To: Multiple recipients of list ORACLE-L
I have been trying to run a "benchmark" of a server
[9iRel2 on HPUX]
The database is 9.2.0.2 with Extent Management Local and
an Undo Tablespace.
This is my table :
create table txn_table
(setrangenumber(2) not null,
col1 varchar2(6),
col2 varchar2(255),
col3 number,
col4 varchar2(45),
update_date date
);
create index txn_table_setnumber_n1
on txn_table(setrange);
create index txn_table_update_dt_n1
on txn_table(update_date);
{SETRANGE will have values 0 to 5 and I am deliberately indexing this column
to see if the database uses the index or does a FTS)
The INSERT of 1 million records took 02:21.86 [2min] :
DECLARE
BEGIN
FOR i IN 1..10 LOOP
IF MOD(i,5) = 0 THEN -- multiple of 5
INSERT INTO txn_table VALUES (mod(i,5), to_char(i), 'the quick brown
fox jumps over the lazy dog',i*2.4,'multiple of 5 ',sysdate);
ELSE
INSERT INTO txn_table VALUES (mod(i,5),to_char(i)||'
','zxcv.,mnbasdfgf;lkjhjqwertpoiuyu',i*3-4,'not a multiple',sysdate);
END IF;
END LOOP;
COMMIT;
END;
/
However, my Update initially ran out of Undo Tablespace which had grown to
2GB. I found that UNDO_RETENTION was 10800, reduced it to 30 and even
bounced the Instance before re-running the Update. [I had also added 2 more
files to the Undo Tablespace].
During the first round of testing, another user was testing a WebMethods
application. However, during the second round I was supposed to be the only
person on the instance [with OEM connecting as DBSNMP, other than my SQLPlus
session]. Yet, the update failed and all three Undo Tablespace files had
grown to 2GB each.
Why should the update take 5hours ? Why should it take some much undo ? Is
the logic of the update plsql block wrong ? [I haven't put a c1%notfound ;
I am using rowid
from the fetch to go back to the table and update it]
17:13:00 SQL> set serveroutput on size 5;
17:13:00 SQL>
17:13:00 SQL> DECLARE
17:13:00 2
17:13:00 3 CURSOR C1 is
17:13:00 4 SELECT SETRANGE,COL1,COL2, rowid
17:13:00 5 from TXN_TABLE;
17:13:00 6
17:13:00 7
17:13:00 8 P_SN number;
17:13:00 9 P_C1 varchar2(6);
17:13:00 10 P_C2 varchar2(255);
17:13:00 11 P_Dvarchar2(15);
17:13:00 12 P_Row rowid;
17:13:00 13
17:13:00 14 cntr number;
17:13:00 15
17:13:00 16 BEGIN
17:13:00 17 cntr := 0;
17:13:00 18 OPEN C1;
17:13:00 19loop
17:13:00 20FETCH C1 into P_SN, P_C1, P_C2, P_Row ;
17:13:00 21 update txn_table set col4 =
'updated'||to_char(mod(p_sn,5)),
17:13:00 22 update_date = sysdate
17:13:00 23 where rowid = P_Row ;
17:13:00 24
17:13:00 25if cntr = 100 then
17:13:00 26 dbms_output.put_line('exiting ...');
17:13:00 27 exit;
17:13:00 28end if;
17:13:00 29end loop;
17:13:00 30
17:13:00 31 COMMIT;
17:13:00 32 END;
17:13:00 33 /
DECLARE
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8192 in undo tablespace 'UNDOTBS1'
ORA-06512: at line 21
Elapsed: 05:31:09.32
22:44:09 SQL>
22:44:09 SQL> spool off
1* select usn, extents, rssize, xacts, writes, gets, optsize, hwmsize
SQL> /
USNEXTENTS RSSIZE XACTS WRITES GETSOPTSIZE
-- -- -- -- -- -- --
HWMSIZE
--
0 7 450560 0 84602052885
450560
1 2 122880 0 2308802056248
7462912
2 2 122880 0 18442538642815995
4234141696
3 38511488 0 2925022061328
8511488
4 31171456 0 2703522057293
1171456
5 31171456 0 2358682056307
2220032
6 31171456 0 2392342056328
2220032
USNEXTENTS RSSIZE XACTS WRITES GETSOPTSIZE
-- -- -- -- -- -- --
HWMSIZE
--
7 31171456 0 3506062058513
2220032
8 31171456 0 2851342056422
1171456
9 2 122880 0 237370 14800561
2416041984
10 2 122880 0 2378262056313
67231744
11 rows selected.
SQL> exit
SQL> show parameter undo
NAME TYPEVALUE
---
--
undo_management string AUTO
undo_retention
Fwd: Undo Segment of 4GB for 1mn 4col update ?
oops. The "cntr := cntr+1" is missing from the update.
The previous update round didn't have a counter, though.
I am re-running the update now.
Hemant
- Forwarded message from Hemant K Chitale <[EMAIL PROTECTED]> -
Date: Tue, 21 Jan 2003 09:16:28 +0800 (SGT)
Reply-To: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
I have been trying to run a "benchmark" of a server
[9iRel2 on HPUX]
The database is 9.2.0.2 with Extent Management Local and
an Undo Tablespace.
This is my table :
create table txn_table
(setrangenumber(2) not null,
col1 varchar2(6),
col2 varchar2(255),
col3 number,
col4 varchar2(45),
update_date date
);
create index txn_table_setnumber_n1
on txn_table(setrange);
create index txn_table_update_dt_n1
on txn_table(update_date);
{SETRANGE will have values 0 to 5 and I am deliberately indexing this column to
see if the database uses the index or does a FTS)
The INSERT of 1 million records took 02:21.86 [2min] :
DECLARE
BEGIN
FOR i IN 1..10 LOOP
IF MOD(i,5) = 0 THEN -- multiple of 5
INSERT INTO txn_table VALUES (mod(i,5), to_char(i), 'the quick brown fox
jumps over the lazy dog',i*2.4,'multiple of 5 ',sysdate);
ELSE
INSERT INTO txn_table VALUES (mod(i,5),to_char(i)||'
','zxcv.,mnbasdfgf;lkjhjqwertpoiuyu',i*3-4,'not a multiple',sysdate);
END IF;
END LOOP;
COMMIT;
END;
/
However, my Update initially ran out of Undo Tablespace which had grown to 2GB.
I found that UNDO_RETENTION was 10800, reduced it to 30 and even bounced the
Instance before re-running the Update. [I had also added 2 more files to the
Undo Tablespace].
During the first round of testing, another user was testing a WebMethods
application. However, during the second round I was supposed to be the only
person on the instance [with OEM connecting as DBSNMP, other than my SQLPlus
session]. Yet, the update failed and all three Undo Tablespace files had grown
to 2GB each.
Why should the update take 5hours ? Why should it take some much undo ? Is the
logic of the update plsql block wrong ? [I haven't put a c1%notfound ; I am
using rowid
from the fetch to go back to the table and update it]
17:13:00 SQL> set serveroutput on size 5;
17:13:00 SQL>
17:13:00 SQL> DECLARE
17:13:00 2
17:13:00 3 CURSOR C1 is
17:13:00 4 SELECT SETRANGE,COL1,COL2, rowid
17:13:00 5 from TXN_TABLE;
17:13:00 6
17:13:00 7
17:13:00 8 P_SN number;
17:13:00 9 P_C1 varchar2(6);
17:13:00 10 P_C2 varchar2(255);
17:13:00 11 P_Dvarchar2(15);
17:13:00 12 P_Row rowid;
17:13:00 13
17:13:00 14 cntr number;
17:13:00 15
17:13:00 16 BEGIN
17:13:00 17 cntr := 0;
17:13:00 18 OPEN C1;
17:13:00 19loop
17:13:00 20FETCH C1 into P_SN, P_C1, P_C2, P_Row ;
17:13:00 21 update txn_table set col4 = 'updated'||to_char(mod(p_sn,5)),
17:13:00 22 update_date = sysdate
17:13:00 23 where rowid = P_Row ;
17:13:00 24
17:13:00 25if cntr = 100 then
17:13:00 26 dbms_output.put_line('exiting ...');
17:13:00 27 exit;
17:13:00 28end if;
17:13:00 29end loop;
17:13:00 30
17:13:00 31 COMMIT;
17:13:00 32 END;
17:13:00 33 /
DECLARE
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8192 in undo tablespace 'UNDOTBS1'
ORA-06512: at line 21
Elapsed: 05:31:09.32
22:44:09 SQL>
22:44:09 SQL> spool off
1* select usn, extents, rssize, xacts, writes, gets, optsize, hwmsize
SQL> /
USNEXTENTS RSSIZE XACTS WRITES GETSOPTSIZE
-- -- -- -- -- -- --
HWMSIZE
--
0 7 450560 0 84602052885
450560
1 2 122880 0 2308802056248
7462912
2 2 122880 0 18442538642815995
4234141696
3 38511488 0 2925022061328
8511488
4 31171456 0 2703522057293
1171456
5 31171456 0 2358682056307
2220032
6 31171456 0 2392342056328
2220032
USNEXTENTS RSSIZE XACTS WRITES GETSOPTSIZE
-- -- -- -- -- -- --
HWMSIZE
--
7 31171456 0 3506062058513
2220032
8 31171456 0 2851342056422
1171456
9 2 122880 0 237370 14800561
2416041984
10 2 122880 0 2378262056313
67231744
11 rows selected.
SQL> exit
SQL> show parameter undo
NAME TYPEVALUE
Undo Segment of 4GB for 1mn 4col update ?
I have been trying to run a "benchmark" of a server
[9iRel2 on HPUX]
The database is 9.2.0.2 with Extent Management Local and
an Undo Tablespace.
This is my table :
create table txn_table
(setrangenumber(2) not null,
col1 varchar2(6),
col2 varchar2(255),
col3 number,
col4 varchar2(45),
update_date date
);
create index txn_table_setnumber_n1
on txn_table(setrange);
create index txn_table_update_dt_n1
on txn_table(update_date);
{SETRANGE will have values 0 to 5 and I am deliberately indexing this column to see if
the database uses the index or does a FTS)
The INSERT of 1 million records took 02:21.86 [2min] :
DECLARE
BEGIN
FOR i IN 1..10 LOOP
IF MOD(i,5) = 0 THEN -- multiple of 5
INSERT INTO txn_table VALUES (mod(i,5), to_char(i), 'the quick brown fox jumps
over the lazy dog',i*2.4,'multiple of 5 ',sysdate);
ELSE
INSERT INTO txn_table VALUES (mod(i,5),to_char(i)||'
','zxcv.,mnbasdfgf;lkjhjqwertpoiuyu',i*3-4,'not a multiple',sysdate);
END IF;
END LOOP;
COMMIT;
END;
/
However, my Update initially ran out of Undo Tablespace which had grown to 2GB. I
found that UNDO_RETENTION was 10800, reduced it to 30 and even bounced the Instance
before re-running the Update. [I had also added 2 more files to the Undo Tablespace].
During the first round of testing, another user was testing a WebMethods application.
However, during the second round I was supposed to be the only person on the instance
[with OEM connecting as DBSNMP, other than my SQLPlus session]. Yet, the update
failed and all three Undo Tablespace files had grown to 2GB each.
Why should the update take 5hours ? Why should it take some much undo ? Is the logic
of the update plsql block wrong ? [I haven't put a c1%notfound ; I am using rowid
from the fetch to go back to the table and update it]
17:13:00 SQL> set serveroutput on size 5;
17:13:00 SQL>
17:13:00 SQL> DECLARE
17:13:00 2
17:13:00 3 CURSOR C1 is
17:13:00 4 SELECT SETRANGE,COL1,COL2, rowid
17:13:00 5 from TXN_TABLE;
17:13:00 6
17:13:00 7
17:13:00 8 P_SN number;
17:13:00 9 P_C1 varchar2(6);
17:13:00 10 P_C2 varchar2(255);
17:13:00 11 P_Dvarchar2(15);
17:13:00 12 P_Row rowid;
17:13:00 13
17:13:00 14 cntr number;
17:13:00 15
17:13:00 16 BEGIN
17:13:00 17 cntr := 0;
17:13:00 18 OPEN C1;
17:13:00 19loop
17:13:00 20FETCH C1 into P_SN, P_C1, P_C2, P_Row ;
17:13:00 21 update txn_table set col4 = 'updated'||to_char(mod(p_sn,5)),
17:13:00 22 update_date = sysdate
17:13:00 23 where rowid = P_Row ;
17:13:00 24
17:13:00 25if cntr = 100 then
17:13:00 26 dbms_output.put_line('exiting ...');
17:13:00 27 exit;
17:13:00 28end if;
17:13:00 29end loop;
17:13:00 30
17:13:00 31 COMMIT;
17:13:00 32 END;
17:13:00 33 /
DECLARE
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8192 in undo tablespace 'UNDOTBS1'
ORA-06512: at line 21
Elapsed: 05:31:09.32
22:44:09 SQL>
22:44:09 SQL> spool off
1* select usn, extents, rssize, xacts, writes, gets, optsize, hwmsize
SQL> /
USNEXTENTS RSSIZE XACTS WRITES GETSOPTSIZE
-- -- -- -- -- -- --
HWMSIZE
--
0 7 450560 0 84602052885
450560
1 2 122880 0 2308802056248
7462912
2 2 122880 0 18442538642815995
4234141696
3 38511488 0 2925022061328
8511488
4 31171456 0 2703522057293
1171456
5 31171456 0 2358682056307
2220032
6 31171456 0 2392342056328
2220032
USNEXTENTS RSSIZE XACTS WRITES GETSOPTSIZE
-- -- -- -- -- -- --
HWMSIZE
--
7 31171456 0 3506062058513
2220032
8 31171456 0 2851342056422
1171456
9 2 122880 0 237370 14800561
2416041984
10 2 122880 0 2378262056313
67231744
11 rows selected.
SQL> exit
SQL> show parameter undo
NAME TYPEVALUE
--- --
undo_management string AUTO
undo_retention integer 30
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
Hemant K Chitale
http://hkchital.tripod.com
--
Please see th
Re: UNDO segments in 9.2
Is the table a pre-existing empty table, or is it being created on the fly by the import ? In the latter case, does your table have a primary key, or other index - as this could explain why you are seeing two large rollback segments. (Although 200M of table plus an index shouldn't take anything like a gigabyte of undo). Which error message are you getting ? Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Coming soon a new one-day tutorial: Cost Based Optimisation (see http://www.jlcomp.demon.co.uk/tutorial.html ) Next Seminar dates: (see http://www.jlcomp.demon.co.uk/seminar.html ) England__January 21/23 USA_(CA, TX)_August 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]> Date: 08 January 2003 00:09 >Hi all: > >I'm seeing something odd with the undo segments in >Oracle 9.2. I have a 1G undo tablespace and am >trying to import a 200M table. This is the only >transaction going on at the time and it is failing >because it is running out of space. When I check the >undo segments I see TWO undo segments of a significant >size. One that is used by my import and one more, >which eats up about a half of the tablespace. I >thought that Oracle would use up the space taken by >this "other" segment, but it doesn't and my import >fails. What am I not knowing about the undo segments >and oracle9.2? > >thanks for any help > >Gene > -- 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: UNDO segments in 9.2
Gene, Are you using Automatic (System Managed) UNDO? or the traditional Rollback segment method? If you are using Automatic UNDO, you should have more than 2 segments in the UNDO tablespace. The number of segments initially created is the function of processes and the minimum I have been able to create is 4 undo segments. The problem you describe sounds more like a Rollback Segment problem. Unless there is a transaction in the 'other' rollback segment, it will not be shrunk. Check out www.optimaldba.com/library.html and www.evdbt.com/papers.htm for papers on Undo Internals and ORA-01555. They should explain your situation. Dan Fink -Original Message- Sent: Tuesday, January 07, 2003 4:49 PM To: Multiple recipients of list ORACLE-L Hi all: I'm seeing something odd with the undo segments in Oracle 9.2. I have a 1G undo tablespace and am trying to import a 200M table. This is the only transaction going on at the time and it is failing because it is running out of space. When I check the undo segments I see TWO undo segments of a significant size. One that is used by my import and one more, which eats up about a half of the tablespace. I thought that Oracle would use up the space taken by this "other" segment, but it doesn't and my import fails. What am I not knowing about the undo segments and oracle9.2? thanks for any help Gene __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gurelei 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: Fink, Dan 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).
