Creating, Optimizing, and Understanding Rollback Segments
from ML notes:
62005.1 - 10579.1 - 69464.1
Introduction
A rollback segment consists of contiguous multi-block pieces called extents.
The segment uses these extents in an ordered circular fashion, moving from
one to the next after the current extent is full. A transaction writes
a record to the current location in the rollback segment and advances the
current pointer by the size of the record. Is better to have smaller RBS
in size with more extents.
For big transactions you might want big RBS with few extents, but for standard
OLTP would be the opposite. Remember that rollback segments can't be reused
as long as there is at least 1 active transaction still using that extent.
So, if you only have 5 extents, conceivably you could have only have 5 transactions
tying up your whole rollback segment and Oracle is forced to extend if it
needs to use it again. Worse is 1 transaction still using say 8k in extent#1
and extents 2,3,4,5 are empty. Oracle can't skip extents so it's forced to
extend to #6 and so on. (called a blocking session).
Size Considerations
The size needed for a rollback segment depends directly on the transaction
activity of the database. The primary concern should be the activity
during normal processing of the database, not rare or semi-frequent large
transactions. These special cases should be dealt with separately.
In determining the number of rollback segments, the goal is to avoid contention
among the rollback segments. Every transaction is stored in a transaction
table in the header of the RBS. Because every transaction has to update
this table, contention is a possibility. A rule of thumb would be not
to have more than one user using a rollback segment at any given time.
The number of rollback segments needed to prevent contention between processes
can be determined with the use of the v$waitstat table. Waits are a
definite indication of contention. The following v$waitstat query will
display number of waits since instance startup. A non-zero value for the
'WAITS' column indicates a rollback segment contention, that means that you
need to add more rollback segments.
Select
SUBSTR(a. name,1,8) NAME, b.extents, b.rssize, b.xacts, b.waits, b. gets,
optsize, substr(status,1,4) status
From v$rollname a, v$rollstat b
Where a.usn = b.usn;
You should see results similar to the following:
NAME
EXTENTS RSSIZE XACTS WAITS GETS OPTSIZE STATUS
SYSTEM
4 540672 1
0 51
ONLINE
RB1
2 10240000 0 0
427 10240000 ONLINE
RB2
2 10240000 1 0
425 10240000 ONLINE
RB3
2 10240000 1 0
422 10240000 ONLINE
RB4
2 10240000 0 0
421 10240000 ONLINE
If you run the above query, and the column "xacts" (which are active transactions) are continually over 1 for the RBS's, you will probably need to increase the number of rollback segments to avoid the possibility of contention. If the waits are greater than zero, and this is normal processing, there may also be a need to increase the number of rollback segments.
To calculate the number of rollback segments, you need to know how many
transactions are likely to be active at any given time. This depends on what
users are doing. Note that queries do not need transaction table access,
so not all active users will have active transactions (OLTP applications
tend to have many short transactions).
General recommendation for how many rollback segments:
For OLTP : One rollback segment for every
four (better) to ten users.
For BATCH jobs : One rollback segment for
each concurrent job.
Too small or not enough rollback segments can have serious impact on the behavior of your database. Therefore several issues must be taken into account. The following query will show you if there are not enough rollback segments online or if the rollback segments are too small.
select SUBSTR(d.segment_name,1,10)
NAME,
substr(d.tablespace_name,1,15)
Tablespace_name, s.waits, s.shrinks,
s.wraps, substr(s.status,1,6) status
from v$rollstat s, dba_rollback_segs
d
where s.usn = d.segment_id
order by 1;
SEGMENT_NAME
TABLESPACE_NAME
WAITS SHRINKS WRAPS STATUS
----------------
----------------------- --------- --------- --------- --------
RB1
ROLLBACK_DATA
1 0
160 ONLINE
RB2
ROLLBACK_DATA
31 1
149 ONLINE
SYSTEM
SYSTEM
0 0
0 ONLINE
The WAITS indicates which rollback segment headers had waits for them. Typically you would want to reduce such contention by adding rollback segments.
If SHRINKS is non zero then the OPTIMAL parameter is set for that particularly rollback segment, or a DBA explicitly issued a shrink on the rollback segment. The number of shrinks indicates the number of times a rollback segment shrinked because a transaction has extended it beyond the OPTIMAL size. If this value is too high then the value of the OPTIMAL size should be increased as well as the overall size of the rollback segment (the value of minextents can be increased or the extend size itself, this depends mostly on the indications of the WRAPS column).
The WRAPS column indicate the number of times the rollback segment wrapped
to another extent to serve the transaction. If this number is significant
then you need to increase the extent size of the rollback segment.
Incidentally, the above report also shows a column called 'WRAPS'. The myth
is that this indicates when a rollback segment has been filled completely,
and has 'wrapped around' back into the first extent. It doesn't. It's actually
a count of the number of times the rollback moves from any extent to another.
(As proof, if you take the total amount of rollback written -the WRITES column,
and divide by the number of WRAPS, you should come out to something very
close to the extent size. From the above report, for segment number 6, the
value of WRITES/WRAPS is 429,612 ÷ 7 = 61373ish (ie, around 64K).
For the same segment, the actual size (RSSIZE) divided by the number of extents
is 520,192 ÷ 8 = 65024 bytes (ie, around 64K)).
The OPTIMAL parameter, which lets you set to size to which a rollback segment
shrinks, can be set for each rollback segment - rollback segments cannot
inherit this value from their tablespace's default setting. You can use the
OPTIMAL parameter to specify the size to which a rollback segment should shrink
if it extends past a certain size. If your rollback segments are frequently
extending past OPTIMAL, then your database is doing extra work, and OPTIMAL
is probably set too low. Check the AveShrink and Extends columns of V$ROLLSTAT
to see if a rollback segment is frequently extending.
This is one of those "features" designed to come to the rescue of DBAs who
can't be bothered to size their rollback segments properly to begin with.
As a transaction wraps into the next extent, the rollback segment can determine
whether it has grown bigger than its "optimal" size, and automatically shrink
to something closer to that optimal setting if it has. It's a disastrous
idea for performance, since shrinks at the best of times are relatively expensive
things - and doing it whilst a transaction is trying to write its rollback
is just about the worst time to pick!
Ideally:
1. Each transaction will fit in one extent of a rollback segment. All non-system
rollback segments will be located outside of the SYSTEM tablespace all rollback
segments will be of equal size.
NOTE: if #1 is not true, then you will see a non-zero value for the Wraps
column inV$ROLLSTAT. If wraps are occurring, then individual rollback segment
entries are spanningextents within the rollback segments - in which case
the extents may be too small. Toincrease the size of the extents, you'll
need to drop and recreate the rollback segments.
NOTE: #2 may seem hard to figure out at first. After all, you have to have
a second rollbacksegment in SYSTEM before you can write data to non-SYSTEM
rollback segments. However, onceyou have done that, you can create a tablespace
called ROLLBACKS (eg) and create rollbacksegments there. Once you have done
this, you no longer need the non-SYSTEM rollback segmentin the SYSTEM tablespace.
No other objects should be created in the ROLLBACKS tablespace.
NOTE: #3 is important because it means that any given transaction, when running,
will have agood chance of running without dynamically extending the rollback
segment it resides in (canyou say "recursive calls"?), improving its performance
and its chance of succeeding.
Force a Rollback Segment
To force the use of a specific rollback segment in SQL do the following:
set transaction use rollback
segment name;
On PL/SQL :
execute dbms_transaction.use_rollback_segment('name');
How to determine which user is using what rollback segment?
SET TERMOUT OFF
SET HEADING ON
SET PAGESIZE 70
SET LINESIZE 80
SET NEWPAGE 0
SET VERIFY OFF
SET ECHO OFF
SET UNDERLINE =
SET FEEDBACK Off
SET LONG 1000
SET EMBED ON
COLUMN sys_id NOPRINT
new_value system_id
COLUMN sys_date NOPRINT new_value
system_date
COLUMN sys_user NOPRINT new_value
system_user
COLUMN rbs
FORMAT a6
HEADING ' RBS'
COLUMN oracle_user FORMAT a12
HEADING 'Oracle|Username'
COLUMN sid
FORMAT 99999 HEADING
' SID'
COLUMN serial# FORMAT
99999 HEADING 'Serial#'
COLUMN unix_pid FORMAT a6 HEADING
'O/S|PID'
COLUMN Client_User FORMAT a12
HEADING 'Client|Username'
COLUMN Unix_user FORMAT a12 HEADING 'O/S|Username'
SELECT r.name
rbs,
nvl(s.username,
'None') oracle_user,
s.osuser
client_user,
p.username
unix_user,
s.sid
sid,
s.serial#
serial#,
p.spid
unix_pid
FROM v$process
p,
v$rollname r,
v$session s,
v$transaction
t
WHERE s.taddr = t.addr
AND s.paddr = p.addr(+)
AND r.usn
= t.xidusn(+)
ORDER BY r.name;
Determining the proper rollback segment size:
To find out the size and number of rollback segments needed to handle normal
processing on the database you need to do some testing. A good test
is to start with small rollback segments and allow your application to force
them toextend.
Here are the steps to run such a test:
1. Create a rollback segment tablespace.
2. Select a number of rollback segments to test and create them in the tablespace.
3. Create the rollback segments so that all extents are the same size. Choose
an extent size that you suspect will need between 10 to 30 extents when the
segments grow to full size.
4. Each rollback segments should start with two extents before the test
is run. This is the minimum number of extents any rollback segment
can have.
5. Only the rollback segments that you are testing and the SYSTEM rollback
segment should be online. Run transactions and load typical of the application.
6. Watch for rollback segment contention.
7. Watch for the maximum size a rollback extends to.
The maximum size any one of the rollback segments reaches during the test is the size you want to use when configuring. This size we will call the "minimum coverage size". If you see contention, adjust the number of segments and rerun the test. Also, if the largest size requires fewer than 10 extents, or more than 30, it is a good idea to lower or raise the extent size, respectively, and rerun the test. Otherwise, space may be getting wasted during the test and this would throw the number off.
For sizing rollback segment extents, it is strongly recommended that each
extent be of the same size. In fact, for all strategies listed below
it is assumed that all rollback segments have extents of the same size and
that the size of the rollback tablespace is some multiple of the common extent
size. The minimum number of extents for an individual segment should be around
twenty forbest performance.
The size needed for a rollback segment depends directly on the transaction
activity of your database. You need to be concerned about the activity during
normal processing of the database, not with rare or semi-frequent large transactions.
We will deal with these special cases separately.
As a general rule of thumb, in an OLTP environment, Oracle suggests 1 rollback
segment per 4 concurrent transactions (perhaps degrading that ratio gradually
down to 1 per 10). Each segment would be created at a size that can comfortably
fit those 4 concurrent transactions.
But how do you determine the number of concurrent transactions at any one time? The simplest approach is to query V$TRANSACTION:
SELECT COUNT(*), SUM(USED_UBLK) FROM V$TRANSACTION;
COUNT(*)
SUM(USED_UBLK)
----------
--------------
30 1016
This shows that thirty transactions are currently pending, and in total,
they are using 1016 blocks from the existing rollback segments (that's 8.3Mb
of rolback). Using the "1 per 4" rule, we ought to create at least 8 rollback
segments to accomodate these thirty transactions -and given that they generate
8Mb of rollback in total, each one should be around 1Mb in size.
Of course, this sort of thing only works if you query V$TRANSACTION during
peak periods, and when the figures you obtain are likely to be the maximum
possible values. That means you need to keep re-testing throughout the day,
and pick the worst-case scenario as your sizing guide.
Same size extents:
For sizing rollback segments extents, Oracle strongly recommend that each
extent be of the same size.
INITIAL extent size:
Choose the INITIAL storage parameter from the list 2KB, 4KB, 8KB, 16KB,
32KB ...etc. This will insure that when you drop the extent you can reuse
all the
freed space without waste.
NEXT extent size:
Use the same value for NEXT as INITIAL.
MINEXTENTS:
Set MINEXTENTS to 20, this will make it unlikely that the rollback segment
needs to grab another extent because the extent that should move into is
still being used by an active transaction.
Why size a rollback segment with a 'minimum' of twenty extents?
Rollback segments dynamically allocate space when required and deallocate
space when no longer needed (if the OPTIMAL parameter is used). The
fewer extents that a rollback segment consists of, the larger the less granular
these space allocations and deallocations are. For example, consider
a 200 megabyte rollback segment which consists of only two 100-megabyte extents.
If this segment were to require additional space, it would allocate another
100M extent. This immediately increases the size of the rollback segment
by 50% and potentially acquires more space than is really needed. By
contrast, if the rollback segment consisted of twenty 10-megabyte extents,
any additional space required would be allocated in 10-megabyte pieces.
When a rollback segment consists of twenty or more extents, any single change
in the number of extents will not move the total size of the rollback segment
by more than 5%, resulting in a much smoother allocation and deallocation
of space.
Sizing rollback segments for STEADY AVERAGE transaction rate:
For databases where the transaction rate base has NO fluctuation, there
is a straightforward way to configure the tablespace:
Create a tablespace that will fit your calculated number of rollback
segments with the "minimum coverage size" you have determined. Follow the
guidelines above for INITIAL and NEXT extents.
As a safety net, allocate some additional space in the tablespace to allow
segments to grow if necessary. If you select to do this, use the OPTIMAL feature
to force the rollback segments to free up any additional space they allocate
beyond their determined size requirement.
Sizing rollback segments for FREQUENT LARGE transaction rate:
A large transaction is one in which there is not enough space to create
all rollback segments of the size necessary to handle its rollback information.
Since we can't depend on the segment shrinking in time to allow repeated
large transactions, OPTIMAL is not really an option for this environment.
There are basically two options that you can choose from for your rollback
segment tablespace.
A) Reduce the number of segments so that all are large enough to hold the largest transactions. This option will introduce contention and will cause some degradation in performance. It is a reasonable choice if performance is not extremely critical.
B) Build one or more large rollback segments and make sure that large transactions use these segments. The SET TRANSACTION USE ROLLBACK SEGMENT command is necessary to control the placement of these large transactions. This option is difficult to implement if large transactions are being run with adhoc queries and there is no systematic control of large transactions. This option is recommended in an environment where the large transactions are issued from a controlled environment. In other words, an application which will set the transaction to the appropriate rollback segment.
Sizing rollback segments for INFREQUENT LARGE transaction rate:
Use the OPTIMAL feature to set up a flexible rollback segment scheme, one
in which you are not concerned about which rollback segment the large transaction
falls upon. The key is to leave enough free space in the rollback tablespace
that the largest transaction's rollback information can fit entirely into
it. To do this, create the rollback tablespace with the space needed for
your calculated number of segments and their "minimum coverage size" plus
this additional space. Set the OPTIMAL for each segment equal to the minimum
coverage size.
What you will see is that the large transaction will randomly make one of
the segments grow and consume the free space, but the segment will release
the space before the next large transaction comes along. Note that you are
sacrificing some performance for this flexibility.
When is the SYSTEM rollback segment used?
When a database is first created using the CREATE DATABASE command, only
a single rollback segment is created. This is the system rollback segment
and it is created in the system tablespace. The system rollback segment
has one basic difference from any other rollback segment, including any other
rollback segments that are created in the system tablespace. This difference
is that the system rollback segment can only be used for transactions that
occur on objects inside the system tablespace. This is done because
the main purpose of the system rollback segment is to handle rollback for
DDL transactions - that is transactions against the data dictionary tables
themselves. Making the system rollback usable only for the system tablespace
was simply an easy way to enforce that. It is possible for the system
rollback segment to be used for non-data dictionary tables, but only if those
tables are created inside the system tablespace (which is very bad development
practice).
Read Consistency and ORA-1555 errors
A common error to receive when issuing long-running transactions is `ORA-1555,
`Snapshot too old'. In the Oracle Server messages manual for this error,
the cause given is `Rollback Segment too small.' That is misleading,
as it is possible to get this error message with any size rollback segment
and increasing the size of rollback segments may not necessarily help.
When a transaction is started, Oracle keeps track of the time (actually the SCN) that it was first issued. While gathering row information to fulfill the statement, Oracle checks each row to make sure that none of the rows was modified after the begin date of the current transaction. If a row is located which was modified, Oracle goes out to the rollback segment for the value of that row which existed when the current transaction started. For uncommitted changes, the information will always exist in the rollback segment, and there are no snapshot issues. However, if there is a change that was committed after the current transaction started, then the rollback space where that transaction information is stored may get overwritten by subsequent transactions (or eliminated entirely by an OPTIMAL shrink). If Oracle tries to get information for that row and the rollback transaction no longer exists, a read-consistent result set cannot be returned and an ORA-1555 error is generated.
No matter what size rollback segment(s) exists on the database, it is possible for committed transactions to be overwritten. The larger (and more) rollback segments that exist in the system, the less often transactions will be overwritten. This is the basis for the Server Messages error explanation of `rollback segment too small'.
The best way to handle ORA-1555 errors is simply to start the long-running transaction when there are few (or no) other transactions running against the database. So long as there are updates occurring to the table(s) being accessed, snapshot errors are possible. If possible, it also helps to split the transaction into smaller pieces that take less time to run. However, if neither of these is possible, there are a couple of items to keep in mind when trying to resolve ORA-1555 errors by modifying rollback configurations:
Make sure all rollback segments are online. The more segments are online, the more transactions are spread out and the less often any individual transaction will be overwritten. Exceptions to this include cases where there is a massive rollback segment that is reserved for other uses and tiny rollback segments that `wrap' head to tail often. Having such tiny segments online can actually make a 1555 worse.
Make all rollback segments that are online (except SYSTEM) approximately
the same size. Transactions are assigned rollback segments in a round-robin
fashion (not exactly, but close enough). Since a transaction which
can cause an ORA-1555 can appear in any segment (other than SYSTEM), the
likelihood of receiving and ORA-1555 will almost always be dictated by how
fast the smallest rollback segment wraps (and rewrites old transactions)
Setting OPTIMAL on Rollback Segments
In the rollback segment storage clause, there is a parameter called OPTIMAL.
This specifies the `optimal' size of a rollback segment in bytes. When
set, Oracle will try to keep the segment at the specified size, rounded up
to the extent boundary. The RDBMS tries to have the fewest number of
extents such that the total size is greater than or equal to the size specified
as OPTIMAL. If additional space is needed beyond the optimal size, the rollback
segment will expand beyond optimal to accommodate the current transaction(s),
but will eventually deallocate extents to shrink back to this size.
The OPTIMAL clause is a very handy tool, however, be aware of a couple of points when using it.
Point one is that extent allocation and deallocation is expensive in regards to performance. This means that an OPTIMAL setting may decrease performance if it is too low. (Actually, it will always decrease performance. It may noticeably decrease performance if set too low.)
Point two is that you are never guaranteed when a rollback segment will
shrink down to its optimal size. The rollback segment only shrinks
when a transaction
attempts to move into another extent and sees that the extent meets the requirements
for deallocation. If a rollback is a candidate for shrinks, but no
transactions are allocated to it, it will not shrink. The obvious corollary
is that a segment must be online to shrink.
Bringing Rollback Segments Online and Offline
By default whenever a rollback segment is created it is offline and must
be acquired by the instance or brought online. The SQL command ALTER
ROLLBACK SEGMENT can be used to bring a rollback segment online or offline
while the instance is running. If a segment is taken offline and the
specified rollback segment does not have any active transactions, it is immediately
taken offline. But if the segment contains active transactions then it is
taken offline only after all the active transaction are either committed or
rolled back. No new transactions will be written to a rollback segment
that is waiting for other transactions to complete so that it can be brought
offline. To become available again without shutting down the instance,
a rollback segment that has been taken offline must be explicitly brought
back online.
Rollback segments are also brought online during instance startup. There are two basic ways that rollback segments can be brought online. The init.ora file can contain a parameter called rollback_segments. You can add this line to the init.ora and specify all rollback segments that are to be explicitly brought online during instance startup. Note that if any of these rollback segments do not exist, an ORA-1534 error is returned and the instance does not complete startup. The segments must already exist before you can add them to the list to be brought online.
Public vs. Private Rollback Segments
A common misconception about `Private' rollback segments is that they are
segments reserved for a particular use or a particular transaction.
The only difference between Public and Private rollback segments is in relation
to the Parallel Server Option. A public rollback segment can be acquired
implicitly by any instance in a parallel server environment. A private
rollback segment must be explicitly acquired by a particular instance using
the rollback_segments parameter. If not using OPS, the difference between
the two is insignificant.
Configuring Rollback Segments
After calculating the size and the number of rollback segments required,
you should plan the configuration of the rollback tablespace. This section
discusses three different transaction environments:
1. a steady average transaction rate
2. frequent large transactions
3. infrequent large transactions
The recommended configurations for the three different types of scenarios
follow.
Steady Average Transaction Rate
For databases where the transaction rate has no fluctuation, there is a straightforward
way to configure the tablespace. Create a tablespace that will fit your calculated
number of rollback segments with the minimum coverage size you have determined.
Make all extents the same size. For a safety net, you may allocate
some additional space in the tablespace to allow segments to grow if they
need to. If you elect to do this, use the OPTIMAL feature to force
all rollback segments to free up any additional space they allocate beyond
their determined size requirement. Do not make OPTIMAL smaller than
the minimum coverage size. If this is done, performance will suffer
due to excessive segment resizing.
Frequent Large Transactions
Databases with frequent large transactions are the hardest case to deal
with. We will define frequent as the time between large transactions being
less than the time needed to allow all rollback segments to shrink back to
optimal size. A large transaction is one in which we don't have enough space
to create all rollback segments of the size necessary to handle its rollback
information. Since we can't depend on the segment shrinking in time to allow
repeated large transactions, OPTIMAL is not really an option for this environment.
There are basically two options that you can choose from for your rollback
segment tablespace. One is to reduce the number of segments so that
all are large enough to hold the largest transactions. This option
may well introduce contention and cause some degradation in performance.
It is a reasonable choice if performance is not extremely critical.
The second option is to build one or more large rollback segments and make
sure that large transactions use these segments. The SET TRANSACTION
USE ROLLBACK SEGMENT command is necessary to control the placement of these
large transactions. This option is difficult to implement if large
transactions are being run with adhoc queries and there is no systematic
control of large transactions. It is recommended in an environment
where the large transactions are issued from a controlled environment (i.e.
an application) that can set the transaction to the
appropriate rollback segment.
Infrequent Large Transactions
For cases where large transactions are rare, you can use OPTIMAL feature
to set up a flexible rollback segment scheme, one in which you are not concerned
about which rollback segment the large transaction falls upon. The
key is to leave enough free space in the rollback tablespace that the largest
transaction's rollback nformation can fit entirely into it. To do this,
create the rollback tablespace with the space needed for your calculated
number of segments and their minimum coverage size plus this additional space.
Then set the OPTIMAL for each segment equal to the minimum coverage size.
What you will see is that the large transactions will randomly make one of
the segments grow and eat up the free space, but the segment will release
the space before the next large transaction comes along. Note that
you are sacrificing some performance for this flexibility.