It could be that your own process is causing the error by committing between DML operations. Take a look at metalink note 40689.1. My hazy understanding of which is as follows: -
When you change a block in the buffer cache, a pointer to the rollback entry is placed in the block header. When the transaction is committed, the rollback entry is freed, but the buffer is not updated to reflect this. When the block is revisited, the process must find the rollback entry to determine whether the transaction has been committed or not. If the entry has been overwritten, then you get snapshot too old. The answer is not to commit between the insert select, update and delete, or alternatively, to force the block cleanout by doing a full scan of the table (for instance by doing a select count(*) from my_table) after each commit. Regards David Lord > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: 05 June 2003 18:40 > To: Multiple recipients of list ORACLE-L > Subject: Re: Re: Snapshot too old during stress test... how to avoid > > > nope. My process is rather simple. > > A flag comes in... either we do a create table as, drop the > old table, and create the indexes based on the data in the > stage tablespace > > or, we do > insert select > Commit; > update > commit; > delete > commit; > > The snapshot too old is occuring during the process of create > table as, all the data is getting created in the new table, > then Im getting a snapshot too old. I didnt have error > handling there to know EXACTLY when its happening, but right > after I do the create table as, I have a query of the data > dictionary, then I drop the old table. The old table is not > being dropped and the new table is being created. > > Im at a loss. > > > > From: "Daniel W. Fink" <[EMAIL PROTECTED]> > > Date: 2003/06/05 Thu AM 10:55:05 EDT > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > Subject: Re: Snapshot too old during stress test... how to avoid > > > > Are you hitting ORA-1555 because of a fetch across commit? > > > > [EMAIL PROTECTED] wrote: > > > > >why would removing optimal setting help with snapshot too old? > > > > > >what I dont understand is that each of my DML transactions > are independent of each other. They query and perform DML on > different tables. None of them overlap. The only time they > overlap is when they hit the data dictionary for some brief queries. > > > > > > > > >>From: Tim Gorman <[EMAIL PROTECTED]> > > >>Date: 2003/06/05 Thu AM 03:25:36 EDT > > >>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > > >>Subject: Re: Snapshot too old during stress test... how to avoid > > >> > > >>Speaking of this trick with a txn in each RBS, I've got a > shell script on my > > >>website (http://www.evdbt.com/tools.htm) that does just > that. It is named > > >>"prevent1555.sh" which uses a stored procedure created by > a SQL script named > > >>"prevent1555_ddl.sql"... > > >> > > >>As Jared mentioned, it is kind of a last resort, but it works... > > >> > > >> > > >> > > >> > > >>on 6/4/03 4:05 PM, [EMAIL PROTECTED] at > [EMAIL PROTECTED] wrote: > > >> > > >> > > >> > > >>>.. and if it still doesn't work, use the trick of > putting a transaction > > >>>in > > >>>each of the rollback segments while the system is > otherwise quiesced, > > >>>and *do not* commit or rollback the transactions. > > >>> > > >>>This forces the rollbacks to extend if necessary, they > will never wrap > > >>>back to the first extent ( actually the second) as long as those > > >>>transactions > > >>>are not committed. > > >>> > > >>>It just uses a lot of disk space. Disk is cheap, right? :) > > >>> > > >>>Consider offlining all your production RBS and creating > temporary ones > > >>>that you can easily drop later. > > >>> > > >>>I've used it with SAP client copies, which will not run > to completion > > >>>without > > >>>doing this, at least on our system. > > >>> > > >>>Jared > > >>> > > >>> > > >>> > > >>> > > >>> > > >>>Kirtikumar Deshpande <[EMAIL PROTECTED]> > > >>>Sent by: [EMAIL PROTECTED] > > >>>06/04/2003 09:45 AM > > >>>Please respond to ORACLE-L > > >>> > > >>> > > >>> To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > > >>> cc: > > >>> Subject: Re: Snapshot too old during stress > test... how to avoid > > >>> > > >>> > > >>>Try removing optimal setting, and shrinking RBS to the > min extents (or > > >>>even below) before running > > >>>your tests. > > >>> > > >>>- Kirti > > >>> > > >>> > > >>>--- Garry Gillies <[EMAIL PROTECTED]> wrote: > > >>> > > >>> > > >>>>From memory (of a course attended looong ago), > > >>>>Oracle recommends one rollback segment for every > > >>>>three to four users. > > >>>>Four rollback segments between thirty six processes > > >>>>does seem a little mean. > > >>>> > > >>>>Garry > > >>>> > > >>>> > > >>>> > > >>>> > > >>>> > > >>>><[EMAIL PROTECTED]> > > >>>>Sent by: [EMAIL PROTECTED] > > >>>>04/06/03 13:59 > > >>>>Please respond to ORACLE-L > > >>>> > > >>>> > > >>>> To: Multiple recipients of list ORACLE-L > > >>>> > > >>>> > > >>><[EMAIL PROTECTED]> > > >>> > > >>> > > >>>> cc: > > >>>> Subject: Snapshot too old during stress > test... how to > > >>>> > > >>>> > > >>>avoid > > >>> > > >>> > > >>>>Im testing worst case scenarios right now. So Im doing batch > > >>>>updates,inserts,deletes and 'create table as' from a > staging tablespace > > >>>> > > >>>> > > >>>of > > >>> > > >>> > > >>>>approximately 5GBs to a master tablespace of > approximately 11GBs. > > >>>> > > >>>>Ive got my job queue processes set to 36 and Im running > 36 at a time in > > >>>>the background in order to gather statistics and timing > under worst case > > >>>> > > >>>> > > >>>>scenarios. > > >>>> > > >>>>Im in 8.1.7.3 and I have increased the size of my RBS > tablespace to 11GB > > >>>> > > >>>> > > >>>>for this test. I have 4 standard RBS with optimal size > set to 1GB. Why > > >>>>would I get a snapshot too old? I would think that > 11GBs of rollback > > >>>> > > >>>> > > >>>would > > >>> > > >>> > > >>>>be big enough. Would increasing the number of Rollback > segments avoid > > >>>> > > >>>> > > >>>this > > >>> > > >>> > > >>>>even though I have the same amount of space in the tablespace? > > >>>> > > >>>>In reality Im going to seriallize the process to avoid > this and to > > >>>> > > >>>> > > >>>improve > > >>> > > >>> > > >>>>performance, however, I want to stress the system. > > >>>> > > >>>>any advice? > > >>>> > > >>>>-- > > >>>>Please see the official ORACLE-L FAQ: http://www.orafaq.net > > >>>>-- > > >>>>Author: <[EMAIL PROTECTED] > > >>>> INET: [EMAIL PROTECTED] > > >>>> > > >>>> > > >>>> > > >>> > > >>>__________________________________ > > >>>Do you Yahoo!? > > >>>Yahoo! Calendar - Free online calendar with sync to Outlook(TM). > > >>>http://calendar.yahoo.com > > >>> > > >>> > > >>-- > > >>Please see the official ORACLE-L FAQ: http://www.orafaq.net > > >>-- > > >>Author: Tim Gorman > > >> 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). > >> > >> > >> > >> > > > > > > > > -- > Daniel W. Fink > http://www.optimaldba.com > > > > ********************************************************************** This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at [EMAIL PROTECTED] Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays. A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ. ********************************************************************** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Lord, David - CSG 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).
