Comments below
HJR
--
--------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources: http://www.geocities.com/howardjr2000
--------------------------------------------------------------------------
"Mark D Powell" <markp7832_at_my-deja.com> wrote in message
news:8shq0p$mvq$1_at_nnrp1.deja.com...
> In article <971618594.24915.0.nnrp-08.9e984b29_at_news.demon.co.uk>,
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
[snip]
> >
> Rather that attach a post lower on the thread where the topic now has
> to do rbs IO I am going to attach here.
>
> 1) I belive you should allocate 10 - 20 extents because A- this reduces
> the allocation of rbs extents during normal processing which is a
> relatively expensive and slow operation and B- this increases the
> amount of time that change data is available to help reduce snapshot
> too old errors for longer running transactions. I believe that this is
> Oracle current thinking on rbs segments and not based on the Oracle 6
> behavior of transactions not sharing rbs segment blocks/extents.
>
First off, it is what the current training documentation recommends, for
sure. However, as anyone familiar with the latest 8i training documentation
can attest, that training documentation is utter crap in many respects. And
the 20 extents nonsense is one of them.
The recomendation for 20 extents *is* based on Oracle 6 non-shareable
extents data, and as such it is out-of-date, and an example of Oracle's
fallability. And that's just a demonstrable fact.
As for your point A "this reduces the allocation of extents" -if your 6
extents are properly sized, there will be no need for the allocation of
extra extents. Conversely, if your 400-extent rollback segment has 400
extents of 2K each, I suspect you may yet end up having to acquire a 401st
extent! The need to allocate extra extents is a consequence of badly
*sized* extents, not of the *number* of extents you happen to have.
As for your point B, again: snapshot too old errors are caused because the
rollback segment is too small, not because the extent numbers are too few.
6 extents of 100K will do just as much to avoid a 1555 error as 20 extents
of 30K.
> 2) Rebuilding the rbs segments may not solve the snapshot too old
> errors being seen by the original poster. No description was given for
> the type of processing being done by the job that received the error
> and whether it is the same jobs that are receiving the error most of
> the time or purely random.
>
It is possible that there is some horrible coding going on that guarantees
snapshot too old errors, but I can't think of any sequence of events where a
suitably huge rollback segment would not prevent the problem. Snapshot too
old is caused by a block required for read consistency being over-written
with new rollback data because the earlier transaction that had acquired it
has now completed. Since rollbacks only ever move in a forward direction,
the fact of finishing the first transaction only poses a risk to its
read-consistent contents if the rollback segment is of such a size that we
are in danger of wrapping back on top of that block.
If the segment is suitably large, that will not be a problem.
Practically, of course, it is possible that his transaction is of such a
size that nothing short of a 50Tb Rollback segment would prevent the
problem -and of course, at that point, re-visiting the code might be a more
reasonable first step. But the general case remains true: bigger segments
equals fewer 1555s.
Regards
HJR
> If the errors are purely random then extending the minextents is
> probably going to help, but if the errors are being received by the
> same jobs then some processing changes may be in order.
>
> As most of the readers and posters to this thread know it is possible
> for a job that reads and updates the same table commiting as it runs to
> create its own snapshot too old error. In this case it may be
> necessary to modify the application to use a driving table so that the
> locating of the target rows is separated from the DML operation.
>
> Sometimes this problem can be traced to two jobs that both make heavy
> updates to the same table that run at the same time. Some of the time
> job A take the error and some of the time job B, but the jobs will only
> receive the error when both are running at the same time. A minor
> scheduling change or the use of a user lock to single thread the jobs
> can eliminate the problem in this case.
>
> Anyway these are a couple of ideas if extending the segments does not
> solve the entire problem.
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Oct 18 2000 - 04:47:58 CDT
