Re: Re: Snapshot too old during stress test... how to avoid
With optimal, the rollback segment shrinks back to its optimal size as soon as the update, insert, or delete transaction is committed. However, there may be a long running query that is using that rollback segment for a consistent view of the database. If the rollback segment shrinks, the query cannot find the data that it needs. This results in a snapshot too old error. rgaffuri @cox.netTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: rootcc: Subject: Re: Re: Snapshot too old during stress test... how to avoid 06/05/2003 08:31 AM Please respond to 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 [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
RE: Re: Snapshot too old during stress test... how to avoid
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 [EMAIL PROTECTED] Date: 2003/06/05 Thu AM 10:09:52 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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- [EMAIL PROTECTED] 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 [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
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- [EMAIL PROTECTED] 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 [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
RE: Re: Snapshot too old during stress test... how to avoid
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- [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Date: 2003/06/05 Thu AM 10:09:52 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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- [EMAIL PROTECTED] 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 [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
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]
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
Re: Snapshot too old during stress test... how to avoid
The rbs does not shrink to optimal when the transaction commits. The 2nd transaction to 'find' that the rbs has extended beyond optimal will cause the rbs to shrink. -- Daniel W. Fink http://www.optimaldba.com Thomas Day wrote: With optimal, the rollback segment shrinks back to its optimal size as soon as the update, insert, or delete transaction is committed. However, there may be a long running query that is using that rollback segment for a consistent view of the database. If the rollback segment shrinks, the query cannot find the data that it needs. This results in a snapshot too old error. -- 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 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: Snapshot too old during stress test... how to avoid
Not as soon as the Update is Over.. Only after a Certain period of Time. This operation is done By SMON. HTH Best Regards, Ganesh R DID : +65-6215-8413 HP : +65-9067-8474 === Live to learn... forget... and learn again. === -Original Message- Sent: Thursday, June 05, 2003 10:05 PM To: Multiple recipients of list ORACLE-L With optimal, the rollback segment shrinks back to its optimal size as soon as the update, insert, or delete transaction is committed. However, there may be a long running query that is using that rollback segment for a consistent view of the database. If the rollback segment shrinks, the query cannot find the data that it needs. This results in a snapshot too old error. rgaffuri @cox.netTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: rootcc: Subject: Re: Re: Snapshot too old during stress test... how to avoid 06/05/2003 08:31 AM Please respond to 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 [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
Re: Snapshot too old during stress test... how to avoid
Is the error only occurring when you do the create table as select? Are any other processes modifying data in the source table? What is the db_block_size? [EMAIL PROTECTED] wrote: 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 occurring 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. -- 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 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: Snapshot too old during stress test... how to avoid
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
Re: Re: Snapshot too old during stress test... how to avoid
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). -- 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
Re: Snapshot too old during stress test... how to avoid
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] 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: Garry Gillies 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 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: 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: Snapshot too old during stress test... how to avoid
... 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: 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). -- 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 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).
