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

Howard,

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

Notes in-line.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
____Iceland__November (tbc)
____Belgium__November (EOUG event)
____UK_______December (UKOUG conference)


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____USA__October
____UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
news:3f90f8cf$0$24515$afc38c87_at_news.optusnet.com.au...


>

> Why set optimal? You're going to size your rollback segments for the

regular


> small transactions, and then let the big transaction blow one of

them out.


> Then to cover your tracks, you'll use optimal to shrink the

blown-out


> segment back down to "normal". I wouldn't mind that approach if

optimal


> didn't have side effects. But the side effects are potentially huge:

every


> rollback segment checking its size every move from one extent to

another.


> Everyone suffers, not just your big transaction.

>


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 ?

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.  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.




>

> 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.
Just because the code does it, it isn't automatically a good idea.
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.



>

> 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.