Re: Rollback OPTIMAL setting
rule 4 ALWAYS applies.. no matter what version of the database :) --- Don Granaman <[EMAIL PROTECTED]> wrote: > As far as I know, the jury is still out - I haven't had a chance yet > to see > if (a) the problems Dan found in 9.0.1.1 are fixed and (2) I like the > way it > works. Also, it does require 9i and I'm not yet ready to go there > with > everything. Even with 9i and system managed undo, rule #4 will still > be in > effect! > > Don Granaman > [certifiable OraSaurus] > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Saturday, June 29, 2002 9:38 PM > > > Don, > > 9i has system managed undo -- you don't think it works yet? > > Rachel > > --- Don Granaman <[EMAIL PROTECTED]> wrote: > > I prefer to have OPTIMAL set and deal with "long running large > > transactions" > > another way - setting "OPTIMAL" on the developers! Granted, this > > doesn't > > work for 3rd party products, but I usually deal with in-house > > applications. > > Getting this to work requires: (1) having enough rollback segments, > > (2) > > large enough rollback segments that extends almost never occur, and > > (3) NOT > > having the "one giant rollback segment for large jobs", and, most > > importantly, (4) all "batch-lookin' thingies" have to be reviewed > by > > the DBA > > and optimized, by either the DBA or the developer - or both, until > > they pass > > muster. Using this, I rarely seen an ORA-01555 or a rollback > shrink > > (over > > six months on the systems I'm working with now) - and when I do, I > > know that > > it (usually) means that somebody broke rule #(4). When that > happens, > > I > > track it down and work with the developer to fix it. My experience > > is that > > the vast majority of developers are receptive - they actually like > to > > have > > their code run without problems. > > > > Of course, this assumes that the "wasted" disk space for (1) and > (2) > > is not > > a significant issue. It rarely is, but I work almost exclusively > > with > > (essentially) OLTP systems where space is typically less critical > > than I/O > > throughput capability and reliability. Usually, we have to add > disks > > for > > performance rather than space. The database datafile drives are > > typically > > 36 GB (less often 18 GB) and are rarely "full", so a GB or two more > > for > > rollback tablespace is OK. If I had to operate under the space > > constraints > > that many seem to have, I might not set OPTIMAL either. > > > > I am looking forward to the day that this "no OPTIMAL" suggestion > > fades into > > the "myths and folklore" category. Either because of basic policy > > changes > > (e.g. the extents myth), a better Oracle algorithm (e.g. 10i system > > managed > > undo???), more intelligently designed batch processes in > > applications, or > > the trend towards ever-increasing drive size. > > > > Note: All this doesn't mean that I don't understand why so many use > > the > > "manual shrink" method. My philosophy differs in that not setting > > OPTIMAL > > should be a last resort, not a blanket policy. > > > > Don Granaman > > [OraSaurus] > > > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Thursday, June 27, 2002 10:13 AM > > > > > > One of the constant comments regarding rollback segments is not to > > set > > optimal. I am wondering why this setting is often discouraged. I > have > > my own > > ideas, but I want to gather more opinions and experiences. > > > > Daniel W. Fink > > Sr. Oracle DBA > > MICROMEDEX > > 303.486.6456 > > > > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Don Granaman > > 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). > > > __ > Do You Yahoo!? > Yahoo! - Official partner of 2002 FIFA World Cup > http://fifaworldcup.yahoo.com > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Rachel Carmichael > 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 messag
Re: Rollback OPTIMAL setting
As far as I know, the jury is still out - I haven't had a chance yet to see if (a) the problems Dan found in 9.0.1.1 are fixed and (2) I like the way it works. Also, it does require 9i and I'm not yet ready to go there with everything. Even with 9i and system managed undo, rule #4 will still be in effect! Don Granaman [certifiable OraSaurus] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Saturday, June 29, 2002 9:38 PM Don, 9i has system managed undo -- you don't think it works yet? Rachel --- Don Granaman <[EMAIL PROTECTED]> wrote: > I prefer to have OPTIMAL set and deal with "long running large > transactions" > another way - setting "OPTIMAL" on the developers! Granted, this > doesn't > work for 3rd party products, but I usually deal with in-house > applications. > Getting this to work requires: (1) having enough rollback segments, > (2) > large enough rollback segments that extends almost never occur, and > (3) NOT > having the "one giant rollback segment for large jobs", and, most > importantly, (4) all "batch-lookin' thingies" have to be reviewed by > the DBA > and optimized, by either the DBA or the developer - or both, until > they pass > muster. Using this, I rarely seen an ORA-01555 or a rollback shrink > (over > six months on the systems I'm working with now) - and when I do, I > know that > it (usually) means that somebody broke rule #(4). When that happens, > I > track it down and work with the developer to fix it. My experience > is that > the vast majority of developers are receptive - they actually like to > have > their code run without problems. > > Of course, this assumes that the "wasted" disk space for (1) and (2) > is not > a significant issue. It rarely is, but I work almost exclusively > with > (essentially) OLTP systems where space is typically less critical > than I/O > throughput capability and reliability. Usually, we have to add disks > for > performance rather than space. The database datafile drives are > typically > 36 GB (less often 18 GB) and are rarely "full", so a GB or two more > for > rollback tablespace is OK. If I had to operate under the space > constraints > that many seem to have, I might not set OPTIMAL either. > > I am looking forward to the day that this "no OPTIMAL" suggestion > fades into > the "myths and folklore" category. Either because of basic policy > changes > (e.g. the extents myth), a better Oracle algorithm (e.g. 10i system > managed > undo???), more intelligently designed batch processes in > applications, or > the trend towards ever-increasing drive size. > > Note: All this doesn't mean that I don't understand why so many use > the > "manual shrink" method. My philosophy differs in that not setting > OPTIMAL > should be a last resort, not a blanket policy. > > Don Granaman > [OraSaurus] > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, June 27, 2002 10:13 AM > > > One of the constant comments regarding rollback segments is not to > set > optimal. I am wondering why this setting is often discouraged. I have > my own > ideas, but I want to gather more opinions and experiences. > > Daniel W. Fink > Sr. Oracle DBA > MICROMEDEX > 303.486.6456 > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Don Granaman > 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). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Publ
Re: Rollback OPTIMAL setting
Don, 9i has system managed undo -- you don't think it works yet? Rachel --- Don Granaman <[EMAIL PROTECTED]> wrote: > I prefer to have OPTIMAL set and deal with "long running large > transactions" > another way - setting "OPTIMAL" on the developers! Granted, this > doesn't > work for 3rd party products, but I usually deal with in-house > applications. > Getting this to work requires: (1) having enough rollback segments, > (2) > large enough rollback segments that extends almost never occur, and > (3) NOT > having the "one giant rollback segment for large jobs", and, most > importantly, (4) all "batch-lookin' thingies" have to be reviewed by > the DBA > and optimized, by either the DBA or the developer - or both, until > they pass > muster. Using this, I rarely seen an ORA-01555 or a rollback shrink > (over > six months on the systems I'm working with now) - and when I do, I > know that > it (usually) means that somebody broke rule #(4). When that happens, > I > track it down and work with the developer to fix it. My experience > is that > the vast majority of developers are receptive - they actually like to > have > their code run without problems. > > Of course, this assumes that the "wasted" disk space for (1) and (2) > is not > a significant issue. It rarely is, but I work almost exclusively > with > (essentially) OLTP systems where space is typically less critical > than I/O > throughput capability and reliability. Usually, we have to add disks > for > performance rather than space. The database datafile drives are > typically > 36 GB (less often 18 GB) and are rarely "full", so a GB or two more > for > rollback tablespace is OK. If I had to operate under the space > constraints > that many seem to have, I might not set OPTIMAL either. > > I am looking forward to the day that this "no OPTIMAL" suggestion > fades into > the "myths and folklore" category. Either because of basic policy > changes > (e.g. the extents myth), a better Oracle algorithm (e.g. 10i system > managed > undo???), more intelligently designed batch processes in > applications, or > the trend towards ever-increasing drive size. > > Note: All this doesn't mean that I don't understand why so many use > the > "manual shrink" method. My philosophy differs in that not setting > OPTIMAL > should be a last resort, not a blanket policy. > > Don Granaman > [OraSaurus] > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Thursday, June 27, 2002 10:13 AM > > > One of the constant comments regarding rollback segments is not to > set > optimal. I am wondering why this setting is often discouraged. I have > my own > ideas, but I want to gather more opinions and experiences. > > Daniel W. Fink > Sr. Oracle DBA > MICROMEDEX > 303.486.6456 > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Don Granaman > 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). __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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).
Re: Rollback OPTIMAL setting
I prefer to have OPTIMAL set and deal with "long running large transactions" another way - setting "OPTIMAL" on the developers! Granted, this doesn't work for 3rd party products, but I usually deal with in-house applications. Getting this to work requires: (1) having enough rollback segments, (2) large enough rollback segments that extends almost never occur, and (3) NOT having the "one giant rollback segment for large jobs", and, most importantly, (4) all "batch-lookin' thingies" have to be reviewed by the DBA and optimized, by either the DBA or the developer - or both, until they pass muster. Using this, I rarely seen an ORA-01555 or a rollback shrink (over six months on the systems I'm working with now) - and when I do, I know that it (usually) means that somebody broke rule #(4). When that happens, I track it down and work with the developer to fix it. My experience is that the vast majority of developers are receptive - they actually like to have their code run without problems. Of course, this assumes that the "wasted" disk space for (1) and (2) is not a significant issue. It rarely is, but I work almost exclusively with (essentially) OLTP systems where space is typically less critical than I/O throughput capability and reliability. Usually, we have to add disks for performance rather than space. The database datafile drives are typically 36 GB (less often 18 GB) and are rarely "full", so a GB or two more for rollback tablespace is OK. If I had to operate under the space constraints that many seem to have, I might not set OPTIMAL either. I am looking forward to the day that this "no OPTIMAL" suggestion fades into the "myths and folklore" category. Either because of basic policy changes (e.g. the extents myth), a better Oracle algorithm (e.g. 10i system managed undo???), more intelligently designed batch processes in applications, or the trend towards ever-increasing drive size. Note: All this doesn't mean that I don't understand why so many use the "manual shrink" method. My philosophy differs in that not setting OPTIMAL should be a last resort, not a blanket policy. Don Granaman [OraSaurus] - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, June 27, 2002 10:13 AM One of the constant comments regarding rollback segments is not to set optimal. I am wondering why this setting is often discouraged. I have my own ideas, but I want to gather more opinions and experiences. Daniel W. Fink Sr. Oracle DBA MICROMEDEX 303.486.6456 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Don Granaman 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).
Re: Rollback OPTIMAL setting
cool! thanks! - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, June 27, 2002 10:28 PM > I do the same thing. No optimal setting, manual (automated job) shrinking of > rollback segments when database has no DML activity (mostly just before > midnight). No ORA-1555 in several years now. > About the space for rollback, we are very generous. Tom Kyte explains this > very well in his wonderful book. > I am working on fixing a rollback problem in one other (7.3.4) database I > acquired. RBS monitoring (v$rollstat, v$transaction etc) script were > installed just last night. Within a week (to cover low/high/eom processing) > we will know the sizing and HWM etc. And then we plan to zap 'Optimal' > setting. With current optimal settings ORA-1555 gets reported at least twice > a month, but I know it will be history after redoing the RBS in a few days. > > - Kirti > > -Original Message- > Sent: Thursday, June 27, 2002 4:16 PM > To: Multiple recipients of list ORACLE-L > > > Tim, > > I rebuilt the rollback segments without OPTIMAL in one case because the > shrinks were causing ORA-1555's at bad times. I have a procedure that > is scheduled via DBMS_JOBS to manually shrink the rollback segments to a > preset size. This runs at what we have found is a relatively quiet time > and has not yet caused an ORA-1555. > > As far as the space wastage goes, this is kept somewhat in control by > the weekly shrink mentioned above. Also, we simply agreed to have a > bigger RBS tablespace that may be strictly needed and when we showed the > business that the frequency of these errors dropped off the chart, they > agreed and the cost of a couple of extra GB was not an issue. > > While this is not a formula that will be needed (or even work) for > everyone, it worked well for this case. There is a mix of OLTP users, > batch jobs and reporting/DSS. Some tools that are is use (SAS) will not > allow SET ROLLBACK SEGMENT, so the recommendation to have 1 big rollback > segment that you bring online for specific users didn't work. > > I like the ability to control when a rollback segment gets shrunk and > at the same time I don't need to do it. To me this is a workable > solution. > > Stephen > > >>> [EMAIL PROTECTED] 06/27/02 11:03AM >>> > I personally prefer to have it set, but I think the reasoning against > setting it has to do with allowing rollback segments to "find > themselves"... :-) > > In other words, it is based on the idea that space allocation (and > deallocation) for rollback segment extents is unnecessary and harmful. > By not setting OPTIMAL, each rollback segment will grow to a "high-water > mark" (HWM) value and never shrink. Therefore, no more "overhead > processing" from extent allocation/deallocation... > > My argument against this involves space "wastage" and sharply varying > workloads. If a set of extremely large transactions (i.e. application > conversion process) runs, they will cause the RBs to grow large, and > stay large, even though the transaction mix may never approach the HWM > boundary. This is a waste of space and a potential failure condition, > as a lack of space may prevent another RBS from growing as it needs in > future. Without OPTIMAL, they will not shrink automatically -- a DBA > would have to manually intervene and shrink them back. That may be > acceptable for some folks, but if I can get the database to do something > automatically instead of me monitoring it, I'd prefer automation. > > If you prefer to minimize RBS extent allocation/deallocation, set > OPTIMAL quite high -- perhaps quite close to the HWMSIZE readings in > V$ROLLSTAT if you prefer. If you are constrained for space in the > tablespace containing rollback segments, then it is quite important to > set OPTIMAL to a lower value and simply "suffer" through the extent > allocations/deallocations necessary to conserve space. If you want to > take the responsibility for monitoring and shrinking RBSs in this > situation manually, well, then to each their own... > > Now, we get to the discussion of just how "onerous" extent > allocation/deallocations are. First of all, such a determination should > be made empirically by monitoring wait-events, V$ROLLSTAT, and > V$WAITSTAT. If nothing there indicates a problem with the > allocation/deallocation of extents, then why would we worry? Obviously, > there are conditions when using DICTIONARY-managed tablespaces where it > can be a concern, but even they are easy to fix, once and for all. If > you put your rollback segments in a UNIFORM
RE: Rollback OPTIMAL setting
I do the same thing. No optimal setting, manual (automated job) shrinking of rollback segments when database has no DML activity (mostly just before midnight). No ORA-1555 in several years now. About the space for rollback, we are very generous. Tom Kyte explains this very well in his wonderful book. I am working on fixing a rollback problem in one other (7.3.4) database I acquired. RBS monitoring (v$rollstat, v$transaction etc) script were installed just last night. Within a week (to cover low/high/eom processing) we will know the sizing and HWM etc. And then we plan to zap 'Optimal' setting. With current optimal settings ORA-1555 gets reported at least twice a month, but I know it will be history after redoing the RBS in a few days. - Kirti -Original Message- Sent: Thursday, June 27, 2002 4:16 PM To: Multiple recipients of list ORACLE-L Tim, I rebuilt the rollback segments without OPTIMAL in one case because the shrinks were causing ORA-1555's at bad times. I have a procedure that is scheduled via DBMS_JOBS to manually shrink the rollback segments to a preset size. This runs at what we have found is a relatively quiet time and has not yet caused an ORA-1555. As far as the space wastage goes, this is kept somewhat in control by the weekly shrink mentioned above. Also, we simply agreed to have a bigger RBS tablespace that may be strictly needed and when we showed the business that the frequency of these errors dropped off the chart, they agreed and the cost of a couple of extra GB was not an issue. While this is not a formula that will be needed (or even work) for everyone, it worked well for this case. There is a mix of OLTP users, batch jobs and reporting/DSS. Some tools that are is use (SAS) will not allow SET ROLLBACK SEGMENT, so the recommendation to have 1 big rollback segment that you bring online for specific users didn't work. I like the ability to control when a rollback segment gets shrunk and at the same time I don't need to do it. To me this is a workable solution. Stephen >>> [EMAIL PROTECTED] 06/27/02 11:03AM >>> I personally prefer to have it set, but I think the reasoning against setting it has to do with allowing rollback segments to "find themselves"... :-) In other words, it is based on the idea that space allocation (and deallocation) for rollback segment extents is unnecessary and harmful. By not setting OPTIMAL, each rollback segment will grow to a "high-water mark" (HWM) value and never shrink. Therefore, no more "overhead processing" from extent allocation/deallocation... My argument against this involves space "wastage" and sharply varying workloads. If a set of extremely large transactions (i.e. application conversion process) runs, they will cause the RBs to grow large, and stay large, even though the transaction mix may never approach the HWM boundary. This is a waste of space and a potential failure condition, as a lack of space may prevent another RBS from growing as it needs in future. Without OPTIMAL, they will not shrink automatically -- a DBA would have to manually intervene and shrink them back. That may be acceptable for some folks, but if I can get the database to do something automatically instead of me monitoring it, I'd prefer automation. If you prefer to minimize RBS extent allocation/deallocation, set OPTIMAL quite high -- perhaps quite close to the HWMSIZE readings in V$ROLLSTAT if you prefer. If you are constrained for space in the tablespace containing rollback segments, then it is quite important to set OPTIMAL to a lower value and simply "suffer" through the extent allocations/deallocations necessary to conserve space. If you want to take the responsibility for monitoring and shrinking RBSs in this situation manually, well, then to each their own... Now, we get to the discussion of just how "onerous" extent allocation/deallocations are. First of all, such a determination should be made empirically by monitoring wait-events, V$ROLLSTAT, and V$WAITSTAT. If nothing there indicates a problem with the allocation/deallocation of extents, then why would we worry? Obviously, there are conditions when using DICTIONARY-managed tablespaces where it can be a concern, but even they are easy to fix, once and for all. If you put your rollback segments in a UNIFORM-type LOCAL-managed tablespace, then any concerns about "overhead" from extent allocations/deallocations are surely unfounded. - Original Message ----- From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Thursday, June 27, 2002 9:13 AM Subject: Rollback OPTIMAL setting One of the constant comments regarding rollback segments is not to set optimal. I am wondering why this setting is often discouraged. I have my own ideas, but I want to gather more opinions and experiences. Daniel W. Fink Sr. Oracle DBA MIC
Re: Rollback OPTIMAL setting
Tim, I rebuilt the rollback segments without OPTIMAL in one case because the shrinks were causing ORA-1555's at bad times. I have a procedure that is scheduled via DBMS_JOBS to manually shrink the rollback segments to a preset size. This runs at what we have found is a relatively quiet time and has not yet caused an ORA-1555. As far as the space wastage goes, this is kept somewhat in control by the weekly shrink mentioned above. Also, we simply agreed to have a bigger RBS tablespace that may be strictly needed and when we showed the business that the frequency of these errors dropped off the chart, they agreed and the cost of a couple of extra GB was not an issue. While this is not a formula that will be needed (or even work) for everyone, it worked well for this case. There is a mix of OLTP users, batch jobs and reporting/DSS. Some tools that are is use (SAS) will not allow SET ROLLBACK SEGMENT, so the recommendation to have 1 big rollback segment that you bring online for specific users didn't work. I like the ability to control when a rollback segment gets shrunk and at the same time I don't need to do it. To me this is a workable solution. Stephen >>> [EMAIL PROTECTED] 06/27/02 11:03AM >>> I personally prefer to have it set, but I think the reasoning against setting it has to do with allowing rollback segments to "find themselves"... :-) In other words, it is based on the idea that space allocation (and deallocation) for rollback segment extents is unnecessary and harmful. By not setting OPTIMAL, each rollback segment will grow to a "high-water mark" (HWM) value and never shrink. Therefore, no more "overhead processing" from extent allocation/deallocation... My argument against this involves space "wastage" and sharply varying workloads. If a set of extremely large transactions (i.e. application conversion process) runs, they will cause the RBs to grow large, and stay large, even though the transaction mix may never approach the HWM boundary. This is a waste of space and a potential failure condition, as a lack of space may prevent another RBS from growing as it needs in future. Without OPTIMAL, they will not shrink automatically -- a DBA would have to manually intervene and shrink them back. That may be acceptable for some folks, but if I can get the database to do something automatically instead of me monitoring it, I'd prefer automation. If you prefer to minimize RBS extent allocation/deallocation, set OPTIMAL quite high -- perhaps quite close to the HWMSIZE readings in V$ROLLSTAT if you prefer. If you are constrained for space in the tablespace containing rollback segments, then it is quite important to set OPTIMAL to a lower value and simply "suffer" through the extent allocations/deallocations necessary to conserve space. If you want to take the responsibility for monitoring and shrinking RBSs in this situation manually, well, then to each their own... Now, we get to the discussion of just how "onerous" extent allocation/deallocations are. First of all, such a determination should be made empirically by monitoring wait-events, V$ROLLSTAT, and V$WAITSTAT. If nothing there indicates a problem with the allocation/deallocation of extents, then why would we worry? Obviously, there are conditions when using DICTIONARY-managed tablespaces where it can be a concern, but even they are easy to fix, once and for all. If you put your rollback segments in a UNIFORM-type LOCAL-managed tablespace, then any concerns about "overhead" from extent allocations/deallocations are surely unfounded. - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Thursday, June 27, 2002 9:13 AM Subject: Rollback OPTIMAL setting One of the constant comments regarding rollback segments is not to set optimal. I am wondering why this setting is often discouraged. I have my own ideas, but I want to gather more opinions and experiences. Daniel W. Fink Sr. Oracle DBA MICROMEDEX 303.486.6456 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Andert 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).
Re: Rollback OPTIMAL setting
I personally prefer to have it set, but I think the reasoning against setting it has to do with allowing rollback segments to "find themselves"... :-) In other words, it is based on the idea that space allocation (and deallocation) for rollback segment extents is unnecessary and harmful. By not setting OPTIMAL, each rollback segment will grow to a "high-water mark" (HWM) value and never shrink. Therefore, no more "overhead processing" from extent allocation/deallocation... My argument against this involves space "wastage" and sharply varying workloads. If a set of extremely large transactions (i.e. application conversion process) runs, they will cause the RBs to grow large, and stay large, even though the transaction mix may never approach the HWM boundary. This is a waste of space and a potential failure condition, as a lack of space may prevent another RBS from growing as it needs in future. Without OPTIMAL, they will not shrink automatically -- a DBA would have to manually intervene and shrink them back. That may be acceptable for some folks, but if I can get the database to do something automatically instead of me monitoring it, I'd prefer automation. If you prefer to minimize RBS extent allocation/deallocation, set OPTIMAL quite high -- perhaps quite close to the HWMSIZE readings in V$ROLLSTAT if you prefer. If you are constrained for space in the tablespace containing rollback segments, then it is quite important to set OPTIMAL to a lower value and simply "suffer" through the extent allocations/deallocations necessary to conserve space. If you want to take the responsibility for monitoring and shrinking RBSs in this situation manually, well, then to each their own... Now, we get to the discussion of just how "onerous" extent allocation/deallocations are. First of all, such a determination should be made empirically by monitoring wait-events, V$ROLLSTAT, and V$WAITSTAT. If nothing there indicates a problem with the allocation/deallocation of extents, then why would we worry? Obviously, there are conditions when using DICTIONARY-managed tablespaces where it can be a concern, but even they are easy to fix, once and for all. If you put your rollback segments in a UNIFORM-type LOCAL-managed tablespace, then any concerns about "overhead" from extent allocations/deallocations are surely unfounded. - Original Message - From: Fink, Dan To: Multiple recipients of list ORACLE-L Sent: Thursday, June 27, 2002 9:13 AM Subject: Rollback OPTIMAL setting One of the constant comments regarding rollback segments is not to set optimal. I am wondering why this setting is often discouraged. I have my own ideas, but I want to gather more opinions and experiences. Daniel W. Fink Sr. Oracle DBA MICROMEDEX 303.486.6456
Rollback OPTIMAL setting
One of the constant comments regarding rollback segments is not to set optimal. I am wondering why this setting is often discouraged. I have my own ideas, but I want to gather more opinions and experiences. Daniel W. Fink Sr. Oracle DBA MICROMEDEX 303.486.6456
