RE: Rollback Segment tuning
Hi all,=0D =0D How to find out the smallest (minimal) transaction size from Dictionary vie= w or base tables =0D I try to set my OPTIMAL rollback segment base on smallest transaction size = to prevent ora-1555. since shrinking rollback segments may cause ora-1555.= =0D =0D =0D Thanks =0D =0D Sinardy=0D --=0D Smallest ? Are you sure that you do not mean greatest ? Opinions diverge about OPTIMAL. As far as I am concerned I like it on databases where you may have occasionally very big changes. Look at V$ROLLSTAT. You have there the average amount of rollback segment used, as well as the high-water mark. You can use those values to derive something looking common-sensical. Alternatively, I think that there is in the 'X-rated' section of the Oriole site (http://www.oriole.com) something to set OPTIMAL automatically (don't worry, even with this name it contains nothing illegal in Singapore :-)). Even if you do not run the script as is, looking at what it does - and possibly questioning it - may give you the answer you are looking for. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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 Segment tuning
Thanks man, I mean the minimum optimal size base on the maximum transaction size, So the HWM is the answer ? Sinardy -Original Message- Sent: 03 June 2002 16:53 To: Multiple recipients of list ORACLE-L Hi all,=0D =0D How to find out the smallest (minimal) transaction size from Dictionary vie= w or base tables =0D I try to set my OPTIMAL rollback segment base on smallest transaction size = to prevent ora-1555. since shrinking rollback segments may cause ora-1555.= =0D =0D =0D Thanks =0D =0D Sinardy=0D --=0D Smallest ? Are you sure that you do not mean greatest ? Opinions diverge about OPTIMAL. As far as I am concerned I like it on databases where you may have occasionally very big changes. Look at V$ROLLSTAT. You have there the average amount of rollback segment used, as well as the high-water mark. You can use those values to derive something looking common-sensical. Alternatively, I think that there is in the 'X-rated' section of the Oriole site (http://www.oriole.com) something to set OPTIMAL automatically (don't worry, even with this name it contains nothing illegal in Singapore :-)). Even if you do not run the script as is, looking at what it does - and possibly questioning it - may give you the answer you are looking for. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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: Sinardy Xing 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 Segment tuning
Hi Stephane, I run the script and I have OPTIMAL = 5 * ceil(max(v$rollstat.aveactive) / 4 / sys.ts$.blocksize)* sys.ts$.blocksize / 1024 the result is 0 K for my optimal. : ) regards, Sinardy -Original Message- Sent: 03 June 2002 16:53 To: Multiple recipients of list ORACLE-L Hi all,=0D =0D How to find out the smallest (minimal) transaction size from Dictionary vie= w or base tables =0D I try to set my OPTIMAL rollback segment base on smallest transaction size = to prevent ora-1555. since shrinking rollback segments may cause ora-1555.= =0D =0D =0D Thanks =0D =0D Sinardy=0D --=0D Smallest ? Are you sure that you do not mean greatest ? Opinions diverge about OPTIMAL. As far as I am concerned I like it on databases where you may have occasionally very big changes. Look at V$ROLLSTAT. You have there the average amount of rollback segment used, as well as the high-water mark. You can use those values to derive something looking common-sensical. Alternatively, I think that there is in the 'X-rated' section of the Oriole site (http://www.oriole.com) something to set OPTIMAL automatically (don't worry, even with this name it contains nothing illegal in Singapore :-)). Even if you do not run the script as is, looking at what it does - and possibly questioning it - may give you the answer you are looking for. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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: Sinardy Xing 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: RE: Rollback Segment tuning
Thanks man,=0D =0D I mean the minimum optimal size base on the maximum transaction size, So th= e HWM is the answer ?=0D =0D =0D Sinardy=0D =0D Depends. If activity was 'normal', yes (if HWM is about the same for all RS, you can be fairly confident with the value). If somebody has run a big imp without 'COMMIT=Y', no. And beware that these values refer to the latest startup, so do not check it first thing in the morning if you shut your databases down every night. Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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: RE: Rollback Segment tuning
Hi Stephane,=0D =0D I run the script and I have =0D =0D OPTIMAL =3D 5 * ceil(max(v$rollstat.aveactive) / 4 / sys.ts$.blocksize)* sy= s.ts$.blocksize / 1024 =0D =0D the result is 0 K for my optimal. : )=0D =0D regards,=0D Sinardy=0D Probably not enough significant activity. It tries to set OPTIMAL to the 'average active' plus a security margin of 20% (to avoid too many shrinks). If you have a very small transaction from time to time, it can be a very low value indeed. In that case, you probably do not have to worry anyway ... Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul 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 Segment tuning
I find that not setting OPTIMAL seems to be optimal. Jared Sinardy Xing [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/02/2002 09:53 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Rollback Segment tuning Hi all, How to find out the smallest (minimal) transaction size from Dictionary view or base tables I try to set my OPTIMAL rollback segment base on smallest transaction size to prevent ora-1555. since shrinking rollback segments may cause ora-1555. Thanks Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinardy Xing 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: 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).
