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

"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message news:<3f91c427$0$21652$afc38c87_at_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
im having trouble sizing my optimal setting for some staging instances. Right now they are in default which is 1m. We do about 40-50 loads a night, many over lap each other. Cant give you a precise rate because they run when the data files come in which varies.

Some loads are very small and run in seconds some loads deal with 10+ GBs of data and have very large rollback segments. We cant really size to specific rollback segment because the size of the individual loads varies(we dont know which file will be large on a particular night).

We have tons of disk space and its just a staging database so we dont backup rollback. We do our backup off of the transportable tablespaces we use to publish data. So if we lose a rollback we just drop it and drop the datafiles, then import the tablespaces.

what rules of thumb can i use to size these? im concerned about wait issues with growing the large segments. Im thinking of sizing them to 50-100m for optimal since maybe 4-5 loads a night grow larger than that and on many nights none of the rollback segments get that large. Its just the 'occasional' case.

Not a particularly easy thing to set up on the fly. Sorry for the vagueness of my response. Im on 8.1.7.4 in Sun Solaris. Received on Mon Oct 20 2003 - 13:58:40 CDT