Re: Can sombody list disadvantages of using Optimal setting for Rollback segs.

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Sun, 19 Oct 2003 08:49:23 +1000
Message-ID: <3f91c427$0$21652$afc38c87@news.optusnet.com.au>

Jonathan Lewis wrote:

>
> Howard,
>
> The whole rollback area is one where I will
> have to keep on disagreeing with you.

That's OK ;-)

>
> Notes in-line.
>
>
> Which process do you think handles the re-sizing, and
> do you think it is done asynchronously, or in real time
> whilst your transaction waits ?

Well, it's SMON of course (unless you're stuck using Oracle 7, but that's another story). And it's done asynchronously. And small transactions won't therefore experience waits. But a transaction that wants to place some more undo in the optimally-shrunken rollback segment will have to wait for the shrink to happen, and I was rather under the impression that the wait event 'undo segment extension' records precisely that (as well as the waits you experience when a shrunken segment needs to grow again).

> One unpleasant detail with shrinks is that Oracle (dbwr)
> writes any dirty blocks in the discarded extent before
> freeing it -- but I beleve there are a couple of buffer queues
> to optimise dirty block writes when the dirty blocks are
> being written for reasons of extent dumping -- and the
> writes have to take place at some time anyway unless
> your rollback segments are small enough for re-use
> before they are written, which is a good reason for trying
> to minimise the size of rollback segments.
>

>>
>>     A bit of disk space cannot conceivably be worth the hassles of
>> having optimal kick in every wrap (ie, every extent-to-extent

> transition).

>>

>
> If optimal is set correctly, and the extent size is sensible, then
> shrinks happen rarely, and the overhead is unlikely to be a
> relevant issue.

I worry that it depends on rather a lot of 'ifs' there. It is true that at a wrap your server process will post SMON to do the shrinking work, rather than you having to do it yourself. But SMON still has to check what optimal is set to, even if no shrink is due. And if you want to place another bit of rollback, you will have to wait for that check to complete.

> Disk Space is cheap - using disks is not.
> By all means, use excessive amounts of disc space for
> adsministrative reasons, so long as it doesn't turn into actively
> used disk space. (e.g. mark indexes unusable, for a batch, then
> rebuild (at twice the disc space) rather then dropping and
> rebuilding).
>
> And what about backups - disk space is cheap, but there is an
> overhead to backing it up and recovery that is potentially more
> painful. The nature of rollback space means an incremental backup
> doesn't help - the whole tablespace is likely to be backed up.

I never said there weren't costs associated with anything! As ever, it's strike the right balance between performance/hassle or performance/administration.  

>>
>> Listen: in 9i, you won't have the option. Well, OK... you can opt to

> do this

>> botch job in 9i exactly as you have in 8i. But what about 10G, or

> 11Y???
>
> In my beta release, I've got Oracle adding a couple of undo segments
> every few minutes when I'm the only person using the database.

You have a beta release of 11Y???? ;-)

> Just because the code does it, it isn't automatically a good idea.

No, I don't think I said it was necessarily a good idea either. Just that you'll have no choice (one day). I feel the same about ASSM, incidentally.

> The general principals of automatic undo may be reasonable, but
> that doesn't make them perfect, or even a very good idea, for every
> system - and it certainly doesn't mean that the Oracle 9
> implementation
> is correct.

Never suggested otherwise (and for a first release feature to be 'correct' would be unusual at the best of times). All I was saying was, since that's how it's done in 9i, good or bad, one might as well get used to the idea.

>

>>
>> you think rollback segments will still be around then? Nah.. it will

> be all

>> automatic. And at that point, what will Oracle tend to do? It will

> tend to

>> need 35-45Gig of undo tablespace. Add in undo_retention to avoid

> your

>> 1555s, and you might be looking at 200Gig. But who cares??
>>

>
> I do - because (a) you may have doubled your required I/O throughput
> for a pointless reason, and (b) you have to backup and recover the
> whole 200GB -- and if that's 8 times the size of my database I'm
> going to be a little peeved.

(a) I can't do much about. (b) is do-able.

But fair comments both... so the issue is where you strike the balance.

Regards
HJR

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------

Received on Sat Oct 18 2003 - 17:49:23 CDT