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: <rgaffuri_at_cox.net>
Date: Thu, 05 Jun 2003 09:39:48 -0800
Message-ID: <F001.005AB1F3.20030605093948@fatcity.com> 





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" <optimaldba_at_yahoo.com>

> Date: 2003/06/05 Thu AM 10:55:05 EDT

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

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

> 

> Are you hitting ORA-1555 because of a fetch across commit?

> 

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

> >>

> >>

> >>    

> >>

> >

> >  

> >

> 

> -- 

> Daniel W. Fink

> http://www.optimaldba.com

> 

> 

> 

> 







  
  


Are you hitting ORA-1555 because of a fetch across commit?



rgaffuri@cox.net 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 <tim@sagelogix.com>
Date: 2003/06/05 Thu AM 03:25:36 EDT
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>

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@radisys.com at Jared.Still@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@yahoo.com>
Sent by: root@fatcity.com
06/04/2003 09:45 AM
Please respond to ORACLE-L


      To:     Multiple recipients of list ORACLE-L <ORACLE-L@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@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@cox.net>
Sent by: root@fatcity.com
04/06/03 13:59
Please respond to ORACLE-L


        To:     Multiple recipients of list ORACLE-L
        

      

      
<ORACLE-L@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@cox.net
  INET: rgaffuri@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@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@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).


    

  

  

  






-- 
Daniel W. Fink
http://www.optimaldba.com









-- 
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).


Received on Thu Jun 05 2003 - 12:39:48 CDT












Original text of this message













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