Re: Shrinking Rollback Segemnts in a Proc

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Sat, 04 Oct 2003 19:24:20 +1000
Message-Id: <3f7e927f$0$30274$afc38c87@news.optusnet.com.au>

Kenneth Koenraadt wrote:

> Comment below.
> 
> On Sat, 04 Oct 2003 08:42:54 +1000, "Howard J. Rogers"
> <howardjr2000_at_yahoo.com.au> wrote:
> 

>>Kenneth Koenraadt wrote:
>>
>>>
>>> Hi Howard,
>>>
>>> I am certainly not in favor of "optimal", it's like sand in the
>>> shoes.....
>>>
>>> But it should not be necessary to shrink them (manually or
>>> automatically) on a regular basis. The ideal is to let them grow as
>>> necessary and leave them alone, I think.
>>>
>>> And maybe, twice a year, you could shrink them manually, when an
>>> application goes crazy, generating 16 Gb of undo space.....shrinking
>>> them on a regular basis (e.g every night) would hide such symptoms of
>>> crazy applications. That was what I meant.
>>>
>>> - Kenneth Koenraadt
>>>
>>
>>Can't agree, Kenneth. It takes but one careless user to fail to commit
>>something, and rollback segments will start to grow. Yes, you should
>>monitor for blocking transactions on a continuous basis and take action
>>that way; but that's precisely why a procedure to monitor and shrink if
>>needed is such a good idea.

> 
> Absolutely. You should *monitor* RBS and *then* possibly shrink (or do
> whatever else necessary) if something is evidently wrong, but you
> should not just shrink without first examining if anything was wrong,
> which I understood the OP wanted to do. That way user craziness may
> never be detected.

I don't want particularly to drag this one out, but that statement's not right either. From v$rollstat, you can find out the current rollback segment size (rssize) and also the segments' high water marks (ie, its historically biggest size). Where there is a wide disparity, it is evident that the segment has been subject to massive growth in the past. You don't need, particularly, to check that before doing the (manual) shrink.

Moreover, there's nothing to stop our original poster's procedure from writing the size of the segment it's about to shrink into some sort of logging (text) file. So every time the script runs, you can see what it's done, and thus detect continuing problems that way.

My strong recommendation is to size your rollback segments sufficient to house all normal transactional activity (and I think we agree on that). And then to have a script which blindly shrinks the segments back to that size at midnight, 2am or whatever time seems appropriate. And to have that script execute nightly. And to do post-mortem daily review of v$rollstat or the logfile output from your shrinking procedure to see whether there is a continuing problem. If there is, you can get involved manually to try and track it down.

Point is, the OP was on the right path, as far as I"m concerned.

Regards
HJR Received on Sat Oct 04 2003 - 04:24:20 CDT