Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re: Snapshot too old during stress test... how to avoid

RE: Re: Snapshot too old during stress test... how to avoid

From: Ganesh Raja <gans_r_at_rediffmail.com>
Date: Thu, 05 Jun 2003 07:40:02 -0800
Message-ID: <F001.005AB01E.20030605074002@fatcity.com> 





Actually when are u getting this error.. 



There are a Lot Of reasons for ORA-01555 and primary one is DB Block
Cleanout or Long Running quries... 





Best Regards,


Ganesh R


DID : +65-6215-8413



HP  : +65-9067-8474




Live to learn... forget... and learn again.






-----Original Message-----


rgaffuri_at_cox.net


Sent: Thursday, June 05, 2003 11:10 PM


To: Multiple recipients of list ORACLE-L




the thing is Im not doing any of that. 



Here is waht Im doing.



update,inserts and deletes on tables


select on data dictionary.


Im also doing 'create table as'



Transactions do NOT overlap. for example update,insert,and delete will
not use the same tables in different transactions. 



and Im not updating the data dictionary. 


> 

> From: "Ganesh Raja" <gans_r_at_rediffmail.com>

> Date: 2003/06/05 Thu AM 10:09:52 EDT

> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

> Subject: RE: Re: Snapshot too old during stress test... how to avoid

> 

> Snapshot Too Old Maybe caused bcos of Delayed Block Cleanout Problems.




> Now this Bock was updated an Hour ago and u are now selecting it.. It 

> finds that there are ITL Entries in there and oracle wants to see if 

> the Transaction has been commited.

> 

> He Goes back to the Transaction Table of the RBS Pointed by this ITL 

> and he finds that there is No Rollback entry available.. Why is this 

> ??? The Optimal Parameter Shrunk the RBS Down and with it all the Undo




> Information needed to do a Block Clean out.

> 

> HTH


> 

> Best Regards,

> Ganesh R

> DID : +65-6215-8413


> HP  : +65-9067-8474 ===========================================

> Live to learn... forget... and learn again.

> ===========================================

> 

> 

> -----Original Message-----

> rgaffuri_at_cox.net

> Sent: Thursday, June 05, 2003 8:32 PM

> To: Multiple recipients of list ORACLE-L

> 

> 

> 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 <tim_at_sagelogix.com>

> > Date: 2003/06/05 Thu AM 03:25:36 EDT

> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

> > 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, Jared.Still_at_radisys.com at 

> > Jared.Still_at_radisys.com

> > 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 <kirtikumar_deshpande_at_yahoo.com> Sent by: 

> > > root_at_fatcity.com 06/04/2003 09:45 AM

> > > Please respond to ORACLE-L

> > > 

> > > 

> > >       To:     Multiple recipients of list ORACLE-L

> <ORACLE-L_at_fatcity.com>

> > >       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 <g.gillies_at_weir.co.uk> 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

> > >> 

> > >> 

> > >> 

> > >> 

> > >> 

> > >> <rgaffuri_at_cox.net>

> > >> Sent by: root_at_fatcity.com

> > >> 04/06/03 13:59

> > >> Please respond to ORACLE-L

> > >> 

> > >> 

> > >>         To:     Multiple recipients of list ORACLE-L

> > > <ORACLE-L_at_fatcity.com>

> > >>         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: <rgaffuri_at_cox.net

> > >>   INET: rgaffuri_at_cox.net

> > >> 

> > > 

> > > 

> > > 

> > > __________________________________

> > > 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: tim_at_sagelogix.com

> > 

> > 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: ListGuru_at_fatcity.com (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: <rgaffuri_at_cox.net

>   INET: rgaffuri_at_cox.net

> 

> 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: ListGuru_at_fatcity.com (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: Ganesh Raja

>   INET: gans_r_at_rediffmail.com

> 

> 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: ListGuru_at_fatcity.com (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: <rgaffuri_at_cox.net
  INET: rgaffuri_at_cox.net

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: ListGuru_at_fatcity.com (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: Ganesh Raja
  INET: gans_r_at_rediffmail.com

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: ListGuru_at_fatcity.com (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).


Received on Thu Jun 05 2003 - 10:40:02 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US