Although there is no direct link between the number of extents in a rollback
segment and the number of transactions that can make use of that rollback
segment, multiple equally-sized extents will maximize the number and size of
transactions that may make use of that rollback segment. The SYSTEM
rollback
segment and deferred rollback segments are special system resources not
under
the control of the DBA, so they are ignored in the following
discussion.Assume
o Rollback segment s with N bytes total space.
o Transaction t such that between the first statement in t
and the ROLLBACK or COMMIT that terminates t, Nt bytes are
written to s by all transactions using s, including
transaction t. Whether or not t itself writes any bytes
to s is irrelevant; the total number of bytes written
to s during the internal between the start and end of t is
the value of Nt to assume here. Measuring or calculating
Nt for a transaction is not discussed in this bulletin;
Nt is merely used to derive a useful result.
When the database fills up one extent of s with rollback information for one
or
more transactions, it attempts to enter the next extent e of s. When the
database enters extent e after the previous extent has been filled, the
previous contents of extent e will no longer be accessible. Thus, extent e
will never be entered if any active update transaction is making use of e.
This is different than transaction t requesting that the database allocate
it some space: a transaction can be allocated space in an extent that is
also
used by other transactions. The prohibition against entering an extent that
has active update transactions in it applies only when extent boundaries are
crossed. Transactions involving only queries (SET TRANSACTION READ ONLY, or
SELECT statements) are not considered update transactions, while
transactions
having any UPDATE, INSERT, DELETE, CREATE TABLE ... AS SELECT, etc.
statements
are.
If e can not be entered, the database attempts to allocate another extent
for
s. If another extent can not be allocated, errors such as ORA 1555
"snapshot
too old", ORA 1562 "failed to extend rollback segment", or ORA 1547 "failed
to
allocate extent of size num in tablesapace 'name'" may result.Example
Let s have 2 equally sized extents, e1 and e2, and let
Nt = .75 * N (1)
If t begins in the first half of e1, then t will complete before the
database
will need to re-enter e1 (see Figure 1).
e1 e2 e1
entered entered re-entered
| | |
|-|-------|+-------|--------+------||-------+--------->
| |
transaction t t
begins ends
Figure 1
If t begins in the latter half of e1, then the database will need to
re-enter
e1 before t completes (see Figure 2). e1 e2
e1
entered entered re-entered
| | |
|-|--------+|------|--------+-------||------+--------->
| |
transaction t t
begins ends
Figure 2
Although rollback segment s has N bytes available, t might not successfully
run
even though Nt < N.Multiple Equally-Sized Extents
Clearly, if Nt > N, and t is using rollback segment s, t will not complete
successfully unless more extents are added to s as t runs.
In order to discuss cases where Nt < N, the statistic Ne, the effective
number
of bytes in s, is useful.
Ne = N - Nm (2)
where Nm is the number of bytes in the largest extent of s. If
Ne < Nt < N (3)
t may finish without needing another extent to be added to s. If
Nt < Ne (3.1)
t will finish without needing another extent to be added to s unless another
transaction interferes. Assume transaction T such that during the duration
of T, the number of bytes NT written to s is greater than the Nt for any
other
transaction t. Confining the discussion to a single rollback segment s,
clearly then the condition
NT < Ne (4)
is sufficient to ensure that T have enough space available in s. If T has
enough space to run, then any other transaction t also has sufficient space.
Maximizing Ne allows the database to more effectively use the space
allocated
to a rollback segment.
Ne is maximized for a particular E when each extent (including the initial
extent) of s is the same size. In that case,
N = E * Nm (5)and thus
N (E - 1)
Ne = N - Nm = N - --- = N * --------- (6)
E E
and, more simply, the useful relationship is (E - 1)
Ne = N * --------- (7)
E
where E is the number of extents in s. As E grows, Ne will asymptotically
approach N, as illustrated in Figure 3.
E (E - 1)/E Ne
--- ----------- --------
2 0.5 0.5 * N
5 0.8 0.8 * N
10 0.9 0.9 * N
20 0.95 0.95 * N
50 0.98 0.98 * N
Figure 3Interactions Between Rollback
Segments
Inequality (4) was derived by limiting our attention to a single rollback
segment s. When more than one rollback segment is used at once, the
interactions between rollback segments must be considered. When the
database
needs to enter the next extent e in s, in addition to checking the status of
e, the statuses of all other rollback segments are also checked. Extent e
will
not be entered if there is an active update transaction t in another
rollback
segment that began before the oldest (committed) transaction in e. Extent e
will not be entered because entering e will remove information that could
potentially be required for giving t a read-consistent view of data.
Read-only
transactions will never deter the database from entering extent e.
Because transaction t may use any (non-SYSTEM) rollback segment in the
database, for multiple runs of t, the Ne available to t will be more
consistent
if the rollback segments in the database have equivalent Ne. For more
practical information about cross-rollback segment interactions, see the
bulletin "How Many Rollback Segments to Have."The Moral
Create each rollback segment with several extents of equal size.
Increasing the number of extents in a rollback segments beyond 20 will have
very little impact on the size of transactions able to use a rollback
segment.
Page 20-22 of the ORACLE RDBMS Database Administrator's Guide Version 6.0
discusses reasons for allocating to each rollback segment in the database
the
same amount of space. In light of the above discussion, the rollback
segments
of a database should have nearly equivalent Ne. (Many sites benefit from an
exception to this general rule. If desired, a rollback segment that has Ne
several times larger than the Ne of the other rollback segments may be
created.
For special periods of processing where transactions with large Nt must be
run,
the database may be brought up with only the large rollback segment in use.
During normal processing, the other rollback segments are brought online as
well.)Variables
The variables used in this bulletin are listed in alpahbetical order. Note
that two-letter variable names are used.
e The extent in rollback segment s that the database will
attempt to enter when another extent is needed in s.
e1 The first extent of rollback segment s having two extents.
e2 The second extent of rollback segment s having two extents.
E The number of extents in rollback segment s.
N Size in bytes of rollback segment s.
Ne The 'effective number of bytes' in rollback segment s.
Maximizing this statistic for a rollback segment
is the point of this bulletin.
Nm The number of bytes in the largest extent of rollback segment
s.
Nt Number of bytes written to rollback segment s by all
transactions during the running of transaction t.
NT The maximum Nt for rollback segment s. s Rollback segment.
t A Transaction.
T The transaction that requires NT bytes in s to complete.
Len Nowers wrote in message
<01be07ee$c83b6860$85051eac_at_pc-lenn.pepstores.com>...
>I get the following error when I run my Report.
>
>REP-0451: Unhandled exception ORA-01555: snapshot too old: rollback segment
>number 6 with name "R06" too small
>Ora-06512: at "PEPPRD.P_log061", LINE 284
>
>As far as I understand, it occurs because updates are done on the same
>tables on which I do my selects.
>Is it possible to avoid using the rollback segments or are there anyting
>else I can try. We already increased the size of the
>rollback segments 2 times.
>
>Thank you in advance
>
>Len Nowers
begin 666 Yassir Khogaly.vcf
M0D5'24XZ5D-!4D0-"E9%4E-)3TXZ,BXQ#0I..DMH;V=A;'D[66%S<VER#0I&
M3CI987-S:7(@2VAO9V%L>0T*5$E43$4Z4V5N:6]R($]R86-L92!$0D$-"E1%
M3#M(3TU%.U9/24-%.BLT-"@P*3$X,2 T-C T,#0R#0I414P[0T5,3#M63TE#
M13HK-#0T,3$Y,#8W-S8-"D%$4CM(3TU%.CL[.SM+96YT.SM%;F=L86YD#0I,
M04)%3#M(3TU%.T5.0T]$24Y'/5%53U1%1"U04DE.5$%"3$4Z2V5N=#TP1#TP
M045N9VQA;F0-"E523#IH='1P.B\O=W=W+FMH;V=A;'DN9G)E97-E<G9E+F-O
M+G5K#0I54DPZ:'1T<#HO+W=W=RYA965U+F]R9RYU:PT*14U!24P[4%)%1CM)
M3E1%4DY%5#IY87-S:7) :VAO9V%L>2YF<F5E<V5R=F4N8V\N=6L-"E)%5CHQ
<.3DX,3$P-%0Q,C4U,SE:#0I%3D0Z5D-!4D0-"@``
`
end
Received on Wed Nov 04 1998 - 06:55:39 CST
