Re: Rollbacks - ORA-1555
Christine, Howdy neighbor. I've almost eliminated 1555's. It took some trial and error, but what we ended up with in one of our bigger db's is: 30 rollback segments OPTIMAL (NULL) minextents 20 initial = next = 4MB 6GB total space We have between 400 and 500 interactive users, several high volume batch jobs and some DSS jobs. By getting rid of OPTIMAL, you avoid one of the more common causes of the 1555 where a rollback segment is shrunk while the data block is being used. We schedule a shrink script to run and manually shrink all the rollback segments weekly. At times some tasks can eat up more rbs, but since they are dba activities, we just shrink a single rollback segment at a time if it gets too big. HTH Stephen Andert [EMAIL PROTECTED] 12/04/01 03:50PM Greetings All I am some what new to the list, so forgive me if I don't have the proper etiquette in addressing my issue. I have a database, 8.1.6, running on Windows NT, that currently has 5 rollback segments. The specs are as follows for each segment: OPTIMAL 350M minextents 7 maxextents unlimited initial 50M next 50M These segments are currently in one tablespace, for rollbacks only, which is sized at 2.5 gig, and currently the segments are taking 1.7 gig, obviously aprox 750 meg free. I have an application,written by our developers here, which is doing a functionality called "pricing". Within this process is alot of DML (updates and deletes) with some DDL inter-mixed. There is an auto-commit feature, which is currently commiting every 1000 records. There is also a locking feature, before the actual "fetches" the application is performing for it's cursors, and the developers are currently using "select * from table for update nowait" to lock the whole table for this process. The locking is in place because this particular process can use up to 5 different sessions. Currently the stats of the rollbacks look like this: data requests- 3817488 CLASS COUNT-- --system undo header 0system undo block 0undo header 3undo block 1 USN NAME AVEACTIVE OPTSIZE WAITS WRAPS EXTENDS SHRINKS AVESHRINK -- -- -- - - --- -- -- 0 SYSTEM 0 0 0 0 0 0 2 SV_ROLL0 0 367001600 2 0 0 0 0 3 SV_ROLL1 0 367001600 0 0 0 0 0 4 SV_ROLL2 0 367001600 1 0 0 0 0 5 SV_ROLL3 0 367001600 0 0 0 0 0 6 SV_ROLL4 0 367001600 0 0 0 0 0 6 rows selected. TSPACE TOTAL USED FREE--- -- -- --SV_ROLL_TSP 2500 1751 750 At times I have seen the "aveactive" column have some numeric value in it, but when the database and services are shutdown and brought back up, this number clears out. My question is this: how much larger are these rollbacks supposed to be before I can eliminate the waits and wraps? More importantly, eliminate the undo headers and block. I have done alot of testing, with different sizing, and I feel like I'm chasing my tail. This is a major feature of our software, so it's not like it can be "ran at night" to differ to a timing issue. I have also noticed, that PMON doesn't really "shrink" appropriately, not back to a state like they are when they are first created. At this point, I guess I'm looking for some insight, advice as to what to specifically do to tune these segments a little more. Thanks So Much, in advance Christine
RE: Rollbacks - ORA-1555
Hello All, I changed the rollback segments, and here are the results. I'm now at 20 segments, 1 meg each, minextents 20, optimal 20. Here are the results after running the process within the application with auto-commits turned off... data requests - 2969079 CLASS COUNT -- - system undo header 0 system undo block 0 undo header1 undo block 0 USN NAME AVEACTIVE OPTSIZE WAITS WRAPS EXTENDS SHRINKS AVESHRINK -- - - - - --- - - 0 SYSTEM 0 0 0 0 0 0 2 SV_ROLL0 106086 20971520 0 1 0 0 0 3 SV_ROLL1 106086 20971520 0 1 0 0 0 4 SV_ROLL2 106086 20971520 0 1 0 0 0 5 SV_ROLL3 106086 20971520 0 1 0 0 0 6 SV_ROLL4 0 20971520 0 0 0 0 0 7 SV_ROLL5 106086 20971520 0 1 0 0 0 8 SV_ROLL6 106086 20971520 0 1 0 0 0 9 SV_ROLL7 106086 20971520 0 1 0 0 0 10 SV_ROLL8 106086 20971520 0 1 0 0 0 11 SV_ROLL9 106086 20971520 0 1 0 0 0 12 SV_ROLL10 201973 20971520 1 2 0 0 0 13 SV_ROLL11 0 20971520 0 0 0 0 0 14 SV_ROLL12 106086 20971520 0 1 0 0 0 15 SV_ROLL13 106086 20971520 0 1 0 0 0 16 SV_ROLL14 106086 20971520 0 1 0 0 0 17 SV_ROLL15 106086 20971520 0 1 0 0 0 18 SV_ROLL16 201973 20971520 0 2 0 0 0 19 SV_ROLL17 106086 20971520 0 1 0 0 0 20 SV_ROLL18 106086 20971520 0 1 0 0 0 21 SV_ROLL19 106086 20971520 0 1 0 0 0 21 rows selected. TSPACE TOTAL USED FREE --- - - - SV_ROLL_TSP 800 407 394 This doesn't look real good to meam I correct??? I will try processing without the optimal being set to see what happens, while I await other response. thanks! Christine -Original Message- Thapliyal Sent: Tuesday, December 04, 2001 5:12 PM To: Multiple recipients of list ORACLE-L Hi Christine, Your rollback segments look large to me based on the description you have given for your application. One way of eliminating header waits is to increase the number of rbs .. try this and post if this helps your stats.. 1. Create total of 20 rollback segments 2. Specification for each rbs is : initial 1M next 1M minextents 20 maxextents unlimited optimal 20M meaning that each rbs will have 20 extents initially and size of each rbs will be 20M initially. since you have 20 such rbs, total rbs used is 20 * 20M = 400M if you observe, the above structure uses smaller sized large number of rollback segments as from your description below, it looks like you have a oltp system with large number of transactions tell me if this helps .. Deepak --- Christine Turner [EMAIL PROTECTED] wrote: RE: BMC Patrol DBXray / CA UnicenterGreetings All I am some what new to the list, so forgive me if I don't have the proper etiquette in addressing my issue. I have a database, 8.1.6, running on Windows NT, that currently has 5 rollback segments. The specs are as follows for each segment: OPTIMAL 350M minextents 7 maxextents unlimited initial 50M next 50M These segments are currently in one tablespace, for rollbacks only, which is sized at 2.5 gig, and currently the segments are taking 1.7 gig, obviously aprox 750 meg free. I have an application, written by our developers here, which is doing a functionality called pricing. Within this process is alot of DML (updates and deletes) with some DDL inter-mixed. There is an auto-commit feature, which is currently commiting every 1000 records. There is also a locking feature, before the actual fetches the application is performing for it's cursors, and the developers are currently using select * from table for update nowait to lock the whole table for this process. The locking is in place because this particular process can use up to 5 different sessions. Currently the stats of the rollbacks look like this: data requests - 3817488 CLASS COUNT -- -- system undo header 0 system undo block 0 undo header 3 undo block 1 USN NAMEAVEACTIVEOPTSIZE WAITS
RE: Rollbacks - ORA-1555
Title: RE: BMC Patrol DBXray / CA Unicenter Christine, Here is a white paper I recently found while chasing down another link on this site. I wish I had read something like this 4 years ago, when I was a developer first trying to figure out what to do about snapshot too old problems. I too once tried to tame the tiger with ever increasingly large rollback segments. It doesnt really touch on how to specifically prevent the error, but it certainly explains the problem much better than I can. After reading Stephens response, I think I need to brush up on rollback management, because I always just tell the developers to figure out a way to accomplish their task, and avoid the error on their own. Oh the link is Cats Dogs and ORA-01555 Steve McClure -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Christine Turner Sent: Tuesday, December 04, 2001 2:50 PM To: Multiple recipients of list ORACLE-L Subject: Rollbacks - ORA-1555 Greetings All I am some what new to the list, so forgive me if I don't have the proper etiquette in addressing my issue. I have a database, 8.1.6, running on Windows NT, that currently has 5 rollback segments. The specs are as follows for each segment: OPTIMAL 350M minextents 7 maxextents unlimited initial 50M next 50M These segments are currently in one tablespace, for rollbacks only, which is sized at 2.5 gig, and currently the segments are taking 1.7 gig, obviously aprox 750 meg free. I have an application,written by our developers here, which is doing a functionality called pricing. Within this process is alot of DML (updates and deletes) with some DDL inter-mixed. There is an auto-commit feature, which is currently commiting every 1000 records. There is also a locking feature, before the actual fetches the application is performing for it's cursors, and the developers are currently using select * from table for update nowait to lock the whole table for this process. The locking is in place because this particular process can use up to 5 different sessions. Currently the stats of the rollbacks look like this: data requests - 3817488 CLASS COUNT -- -- system undo header 0 system undo block 0 undo header 3 undo block 1 USN NAME AVEACTIVE OPTSIZE WAITS WRAPS EXTENDS SHRINKS AVESHRINK -- -- -- - - --- -- -- 0 SYSTEM 0 0 0 0 0 0 2 SV_ROLL0 0 367001600 2 0 0 0 0 3 SV_ROLL1 0 367001600 0 0 0 0 0 4 SV_ROLL2 0 367001600 1 0 0 0 0 5 SV_ROLL3 0 367001600 0 0 0 0 0 6 SV_ROLL4 0 367001600 0 0 0 0 0 6 rows selected. TSPACE TOTAL USED FREE --- -- -- -- SV_ROLL_TSP 2500 1751 750 At times I have seen the aveactive column have some numeric value in it, but when the database and services are shutdown and brought back up, this number clears out. My question is this: how much larger are these rollbacks supposed to be before I can eliminate the waits and wraps? More importantly, eliminate the undo headers and block. I have done alot of testing, with different sizing, and I feel like I'm chasing my tail. This is a major feature of our software, so it's not like it can be ran at night to differ to a timing issue. I have also noticed, that PMON doesn't really shrink appropriately, not back to a state like they are when they are first created. At this point, I guess I'm looking for some insight, advice as to what to specifically do to tune these segments a little more. Thanks So Much, in advance Christine
RE: Rollbacks - ORA-1555
Hi Christine, these results are slightly differnt from the earlier ones you .. as the latest ones had 8,48409 fewer data requests. I believe you are worried about the wraps.. if yes .. dont be , cuz at some point or the other the rbs will wrap. the definition of wrapping is controversial and lots of confusion still exists. That said, wraps are probably not the best metrics to help you identify rbs issues.. here are the metrics i use to for identifying if there are rbs issues from v$rollstat.. (excluding v$waitstat stats as you already seem to have figured that one it seems) 1. waits (ideally you want this to be 0) 2. shrink (if high means that there is dynamic allocation and reallocation.. tune optimal.. ideally you want your rbs to always stay at its optimal size with 0 shrinks..) 3. AVEACTIVE: you reached a maximum of about 200K. so maybe you should try changing initial and next to 250K with other parameters same and check if the numbers are looking better finally the reason i replied to the whole list and not only to you is that i am hoping someone can come forward and say that .. dude!! you have it figgured all wrong!! that way at least i get to learn somin new for sure... ;) Thx Deepak PS: might also help to check v$session_wait for buffer waits to see what type of blocks are being contended for.. --- Christine Turner [EMAIL PROTECTED] wrote: Hello All, I changed the rollback segments, and here are the results. I'm now at 20 segments, 1 meg each, minextents 20, optimal 20. Here are the results after running the process within the application with auto-commits turned off... data requests - 2969079 CLASS COUNT -- - system undo header 0 system undo block 0 undo header1 undo block 0 USN NAME AVEACTIVE OPTSIZE WAITS WRAPS EXTENDS SHRINKS AVESHRINK -- - - - - --- - - 0 SYSTEM 0 0 0 0 0 0 2 SV_ROLL0 106086 20971520 0 1 0 0 0 3 SV_ROLL1 106086 20971520 0 1 0 0 0 4 SV_ROLL2 106086 20971520 0 1 0 0 0 5 SV_ROLL3 106086 20971520 0 1 0 0 0 6 SV_ROLL4 0 20971520 0 0 0 0 0 7 SV_ROLL5 106086 20971520 0 1 0 0 0 8 SV_ROLL6 106086 20971520 0 1 0 0 0 9 SV_ROLL7 106086 20971520 0 1 0 0 0 10 SV_ROLL8 106086 20971520 0 1 0 0 0 11 SV_ROLL9 106086 20971520 0 1 0 0 0 12 SV_ROLL10 201973 20971520 1 2 0 0 0 13 SV_ROLL11 0 20971520 0 0 0 0 0 14 SV_ROLL12 106086 20971520 0 1 0 0 0 15 SV_ROLL13 106086 20971520 0 1 0 0 0 16 SV_ROLL14 106086 20971520 0 1 0 0 0 17 SV_ROLL15 106086 20971520 0 1 0 0 0 18 SV_ROLL16 201973 20971520 0 2 0 0 0 19 SV_ROLL17 106086 20971520 0 1 0 0 0 20 SV_ROLL18 106086 20971520 0 1 0 0 0 21 SV_ROLL19 106086 20971520 0 1 0 0 0 21 rows selected. TSPACE TOTAL USED FREE --- - - - SV_ROLL_TSP 800 407 394 This doesn't look real good to meam I correct??? I will try processing without the optimal being set to see what happens, while I await other response. thanks! Christine -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Deepak Thapliyal Sent: Tuesday, December 04, 2001 5:12 PM To: Multiple recipients of list ORACLE-L Subject: Re: Rollbacks - ORA-1555 Hi Christine, Your rollback segments look large to me based on the description you have given for your application. One way of eliminating header waits is to increase the number of rbs .. try this and post if this helps your stats.. 1. Create total of 20 rollback segments 2. Specification for each rbs is : initial 1M next 1M minextents 20 maxextents unlimited optimal 20M meaning that each rbs will have 20 extents initially and size of each rbs will be 20M initially. since you have 20 such rbs, total rbs used is 20 * 20M = 400M if you observe, the above structure uses smaller sized large number of rollback segments as from your description below, it looks like you have a oltp
Re: Rollbacks - ORA-1555
Hi Christine, Your rollback segments look large to me based on the description you have given for your application. One way of eliminating header waits is to increase the number of rbs .. try this and post if this helps your stats.. 1. Create total of 20 rollback segments 2. Specification for each rbs is : initial 1M next 1M minextents 20 maxextents unlimited optimal 20M meaning that each rbs will have 20 extents initially and size of each rbs will be 20M initially. since you have 20 such rbs, total rbs used is 20 * 20M = 400M if you observe, the above structure uses smaller sized large number of rollback segments as from your description below, it looks like you have a oltp system with large number of transactions tell me if this helps .. Deepak --- Christine Turner [EMAIL PROTECTED] wrote: RE: BMC Patrol DBXray / CA UnicenterGreetings All I am some what new to the list, so forgive me if I don't have the proper etiquette in addressing my issue. I have a database, 8.1.6, running on Windows NT, that currently has 5 rollback segments. The specs are as follows for each segment: OPTIMAL 350M minextents 7 maxextents unlimited initial 50M next 50M These segments are currently in one tablespace, for rollbacks only, which is sized at 2.5 gig, and currently the segments are taking 1.7 gig, obviously aprox 750 meg free. I have an application, written by our developers here, which is doing a functionality called pricing. Within this process is alot of DML (updates and deletes) with some DDL inter-mixed. There is an auto-commit feature, which is currently commiting every 1000 records. There is also a locking feature, before the actual fetches the application is performing for it's cursors, and the developers are currently using select * from table for update nowait to lock the whole table for this process. The locking is in place because this particular process can use up to 5 different sessions. Currently the stats of the rollbacks look like this: data requests - 3817488 CLASS COUNT -- -- system undo header 0 system undo block 0 undo header 3 undo block 1 USN NAMEAVEACTIVEOPTSIZE WAITS WRAPS EXTENDSSHRINKS AVESHRINK -- -- -- - - --- -- --- --- 0 SYSTEM 00 0 0 0 0 2 SV_ROLL00 367001600 2 0 0 0 0 3 SV_ROLL10 367001600 0 0 0 0 0 4 SV_ROLL20 367001600 1 0 0 0 0 5 SV_ROLL30 367001600 0 0 0 0 0 6 SV_ROLL40 367001600 0 0 0 0 0 6 rows selected. TSPACE TOTAL USED FREE --- -- -- -- SV_ROLL_TSP 2500 1751750 At times I have seen the aveactive column have some numeric value in it, but when the database and services are shutdown and brought back up, this number clears out. My question is this: how much larger are these rollbacks supposed to be before I can eliminate the waits and wraps? More importantly, eliminate the undo headers and block. I have done alot of testing, with different sizing, and I feel like I'm chasing my tail. This is a major feature of our software, so it's not like it can be ran at night to differ to a timing issue. I have also noticed, that PMON doesn't really shrink appropriately, not back to a state like they are when they are first created. At this point, I guess I'm looking for some insight, advice as to what to specifically do to tune these segments a little more. Thanks So Much, in advance Christine __ Do You Yahoo!? Buy the perfect holiday gifts at Yahoo! Shopping. http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
