Purpose
This paper is a discussion on configuring a rollback segment tablespace
that is designed to meet the needs of your database applications.
***Checked for relevance on 25-Jul-2010***
Scope & Application
For DBA's requiring information about configuring a rollback segment
tablespace. This note is applicable to older versions of the Oracle
database.
If using Oracle9i/Oracle 10g review
Note:135217.1 Which Operations are Allowed or Prohibited on RBS with or without AUM?:
Note:135053.1 How to Create a Database with Automatic Undo Management:
Note:268870.1 How to Shrink the datafile of Undo Tablespace:
Creating, Optimizing, and Understanding Rollback Segments
=========================================================
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. (The current writing location for records is the
"head" of the rollback segment). The term "tail" is used to refer to the
location on the rollback segment that is the beginning of the oldest active
transaction record.
To determine a general rollback segment configuration, balance the number of
segments against the size of each segment, such that the total size is small
enough to fit into the available disk space. There should be enough segments
to avoid contention as processes access them. It's also important to make sure
that individual rollback segments are large enough to handle their transaction
load. Oracle allocates rollback segments in a round-robin fashion among all
online rollback segments (with the exception of SYSTEM) to try to spread
transactions evenly. The next section discusses balancing requirements of size
and concurrency.
Size and Number of Rollback Segments
------------------------------------
1. A transaction can only use one rollback segment to store all of its
rollback (undo) records.
2. Multiple transactions can write to the same extent.
3. The head of the rollback segment never moves into a rollback extent
currently occupied by the tail.
4. Extents in the ring are never skipped over and used out of order as the
head tries to advance.
5. If the head can't use the next extent, it allocates another extent and
inserts it into the ring.
From these principles it is evident that transaction time as well as
transaction size is important. For instance, a transaction that only modifies
one byte but waits a long period of time before ending could cause a rollback
segment to extend if the extent it occupied is needed again.
Size Considerations
-------------------
There are two issues that need to be considered when deciding if your segment
is large enough. First, you want to make sure that transactions will not cause
the head to wrap around too fast and catch the tail. This causes the segment
to extend in size, as discussed above. Second, if you have long-running
queries that access data that changes frequently, you want to make sure that
the rollback segment doesn't wrap around and prevent the construction of a
read-consistent view. Read-consistency is discussed in more detail later in
the section on avoiding `ORA-1555 - snapshot too old' errors.
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.
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:
SELECT CLASS, COUNT FROM V$WAITSTAT WHERE CLASS = '%undo%';
Any non-zero value in the count column indicates rollback segment header
contention.
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 to
extend.
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.
6. Only the rollback segments that you are testing and the SYSTEM
rollback segment should be online. Run transactions and load typical
of the application.
7. Watch for rollback segment contention.
8. 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.
At the end of this test, you will have some good base estimates for the size
and number of rollback segments needed for normal processing. However,
handling large transactions has not been discussed yet. Calculating the
segment size needed for these types of transactions as well as how to meet the
requests for the calculated space will be addressed.
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 for
best performance.
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.
Given this, increasing the number of extents beyond the suggested twenty will
make space allocation and deallocation even *more* smooth. However, in-house
testing has showed rapidly diminishing returns when increasing the number of
extents past twenty. In addition, allocating and deallocating extents is not a
cost-free operation. The database will have a performance degradation when
performing extent operations. The cost for individual extents is minor, but a
rollback segment which is constantly allocating and deallocating tiny extents
can cause even a minor cost to add up.
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).
Any other rollback segments inside the system tablespace do not have this
restriction. Prior to Oracle 7.1.5, during database creation, at least one
rollback segment must be created in system. If this is not done, additional
tablespaces can never be created (because the CREATE TABLESPACE transaction by
definition affects objects outside the system tablespace.) Attempting to
perform. such an operation will generate an `ORA-1552, cannot use the system
rollback segment for non-system tablespace x' ( Bug:232566 ). You will get this
error any time rollback is generated for a tablespace outside of system and the
only rollback segment online is system. If any other rollback segment is
online, this error will not occur. Normally one of the first operations of
creating a new database is to create a rollback tablespace and place all
non-system rollback segments there. The additional system rollback segment is
either off-lined or dropped. Leaving it online can lead to fragmentation in
the system tablespace.
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'.
Note: A common (and incorrect) assumption is that the ORA-1555 message
indicates that the rollback segment being used by the current transaction is
too small. Most commonly it is SELECT statements which generate ORA-1555
errors. SELECT statements do not generate rollback information. Rollback
information is generated for a `CREATE TABLE AS SELECT' statement, but it is
the CREATE, rather than the SELECT which does so.
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 process of shrinking back to OPTIMAL is not instantaneous. When a
transaction that has expanded the segment beyond the set value has ended, the
rollback segment does not `snap' back to the optimal size. This could have
severe performance implications and cause considerable problems with
read-consistency. The process of deallocating extents is performed during
transactions after the one which caused the segment to extend.When the head
moves from one extent to the next during a transaction, the segment size is
checked. If the segment size is currently above the optimal, the RDBMS
determines if an extent should be deallocated. An extent will only be
deallocated if there are no active transactions in the next two extents (Why
the next two extents? If only the next extent is checked for availability,
Oracle might deallocate it then try to continue with the current transaction
only to find that the following extent is used. At that point, Oracle must
reallocate an extent.). If necessary, the RDBMS will deallocate multiple
extents in a single transaction (one each time the head crosses into a new
extent) until the segment has shrunk back to its optimal size. Since extents
are deallocated by the current transaction(s), the circular nature of rollback
segments guarantees that these are the oldest inactive extents (and the least
likely to be used for read consistency).
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.) The best solution for performance is to
set all of your rollback segments to a size where every single transaction will
always fit. In practical terms, this may well be impossible - if your largest
transaction is 2 gigabytes and you require ten rollback segments for
concurrency. The segments should have an optimal size large enough that 90% or
better of transactions will fit without having to extend the segment. In
addition, the rollback tablespace should be large enough that when all rollback
segments are at the optimal value, there is plenty of space for them to extend
when it becomes necessary. For example, if your segments are set with an
optimal value of 500 megs and you know that there is a particular transaction
that runs infrequently, but requires one gigabyte when it does run, your
rollback tablespace must have 500 megs free (absolute minimum) when all
segments in that tablespace are at optimal. Since you cannot normally count on
all segments being at optimal or on that big transaction being the only one
using space in the rollback segment, prudence suggests having a bit more space
available than the absolute minimum.
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.
Even if rollback segments are not explicitly brought online by being added to
the "rollback_segments" parameter, they may be brought online during startup.
Oracle will do this based on the values of two other parameters: transactions
and "transactions_per_rollback_segment". On startup, Oracle will divide
transactions by "transactions_per_rollback_segment" and round up. If the
resulting number is less than the number of rollback segments brought online by
the "rollback_segments" parameter, nothing is done. However, if the resulting
number is greater and more rollback segments exist which are not currently
online, the segments will be brought online until the number is reached or
until there are no other offline segments. If neither parameter is set in the
init.ora, they will use default values.
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 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. 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.
NOTE:
====
DDL's will not be affected by SET TRANSACTION USE ROLLBACK SEGMENT.
Example:
-------
create table as select, alter table..
Create table as select is a data definition (DDL) statement, and as such it
does an implicit commit. A transaction ends once a commit is processed, so
any statements following a DDL command will not be part of the previous
transaction, and therefore commands following the DDL statement may use a
different rollback segment.
This paper is a discussion on configuring a rollback segment tablespace
that is designed to meet the needs of your database applications.
***Checked for relevance on 25-Jul-2010***
Scope & Application
For DBA's requiring information about configuring a rollback segment
tablespace. This note is applicable to older versions of the Oracle
database.
If using Oracle9i/Oracle 10g review
Note:135217.1 Which Operations are Allowed or Prohibited on RBS with or without AUM?:
Note:135053.1 How to Create a Database with Automatic Undo Management:
Note:268870.1 How to Shrink the datafile of Undo Tablespace:
Creating, Optimizing, and Understanding Rollback Segments
=========================================================
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. (The current writing location for records is the
"head" of the rollback segment). The term "tail" is used to refer to the
location on the rollback segment that is the beginning of the oldest active
transaction record.
To determine a general rollback segment configuration, balance the number of
segments against the size of each segment, such that the total size is small
enough to fit into the available disk space. There should be enough segments
to avoid contention as processes access them. It's also important to make sure
that individual rollback segments are large enough to handle their transaction
load. Oracle allocates rollback segments in a round-robin fashion among all
online rollback segments (with the exception of SYSTEM) to try to spread
transactions evenly. The next section discusses balancing requirements of size
and concurrency.
Size and Number of Rollback Segments
------------------------------------
1. A transaction can only use one rollback segment to store all of its
rollback (undo) records.
2. Multiple transactions can write to the same extent.
3. The head of the rollback segment never moves into a rollback extent
currently occupied by the tail.
4. Extents in the ring are never skipped over and used out of order as the
head tries to advance.
5. If the head can't use the next extent, it allocates another extent and
inserts it into the ring.
From these principles it is evident that transaction time as well as
transaction size is important. For instance, a transaction that only modifies
one byte but waits a long period of time before ending could cause a rollback
segment to extend if the extent it occupied is needed again.
Size Considerations
-------------------
There are two issues that need to be considered when deciding if your segment
is large enough. First, you want to make sure that transactions will not cause
the head to wrap around too fast and catch the tail. This causes the segment
to extend in size, as discussed above. Second, if you have long-running
queries that access data that changes frequently, you want to make sure that
the rollback segment doesn't wrap around and prevent the construction of a
read-consistent view. Read-consistency is discussed in more detail later in
the section on avoiding `ORA-1555 - snapshot too old' errors.
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.
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:
SELECT CLASS, COUNT FROM V$WAITSTAT WHERE CLASS = '%undo%';
Any non-zero value in the count column indicates rollback segment header
contention.
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 to
extend.
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.
6. Only the rollback segments that you are testing and the SYSTEM
rollback segment should be online. Run transactions and load typical
of the application.
7. Watch for rollback segment contention.
8. 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.
At the end of this test, you will have some good base estimates for the size
and number of rollback segments needed for normal processing. However,
handling large transactions has not been discussed yet. Calculating the
segment size needed for these types of transactions as well as how to meet the
requests for the calculated space will be addressed.
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 for
best performance.
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.
Given this, increasing the number of extents beyond the suggested twenty will
make space allocation and deallocation even *more* smooth. However, in-house
testing has showed rapidly diminishing returns when increasing the number of
extents past twenty. In addition, allocating and deallocating extents is not a
cost-free operation. The database will have a performance degradation when
performing extent operations. The cost for individual extents is minor, but a
rollback segment which is constantly allocating and deallocating tiny extents
can cause even a minor cost to add up.
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).
Any other rollback segments inside the system tablespace do not have this
restriction. Prior to Oracle 7.1.5, during database creation, at least one
rollback segment must be created in system. If this is not done, additional
tablespaces can never be created (because the CREATE TABLESPACE transaction by
definition affects objects outside the system tablespace.) Attempting to
perform. such an operation will generate an `ORA-1552, cannot use the system
rollback segment for non-system tablespace x' ( Bug:232566 ). You will get this
error any time rollback is generated for a tablespace outside of system and the
only rollback segment online is system. If any other rollback segment is
online, this error will not occur. Normally one of the first operations of
creating a new database is to create a rollback tablespace and place all
non-system rollback segments there. The additional system rollback segment is
either off-lined or dropped. Leaving it online can lead to fragmentation in
the system tablespace.
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'.
Note: A common (and incorrect) assumption is that the ORA-1555 message
indicates that the rollback segment being used by the current transaction is
too small. Most commonly it is SELECT statements which generate ORA-1555
errors. SELECT statements do not generate rollback information. Rollback
information is generated for a `CREATE TABLE AS SELECT' statement, but it is
the CREATE, rather than the SELECT which does so.
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 process of shrinking back to OPTIMAL is not instantaneous. When a
transaction that has expanded the segment beyond the set value has ended, the
rollback segment does not `snap' back to the optimal size. This could have
severe performance implications and cause considerable problems with
read-consistency. The process of deallocating extents is performed during
transactions after the one which caused the segment to extend.When the head
moves from one extent to the next during a transaction, the segment size is
checked. If the segment size is currently above the optimal, the RDBMS
determines if an extent should be deallocated. An extent will only be
deallocated if there are no active transactions in the next two extents (Why
the next two extents? If only the next extent is checked for availability,
Oracle might deallocate it then try to continue with the current transaction
only to find that the following extent is used. At that point, Oracle must
reallocate an extent.). If necessary, the RDBMS will deallocate multiple
extents in a single transaction (one each time the head crosses into a new
extent) until the segment has shrunk back to its optimal size. Since extents
are deallocated by the current transaction(s), the circular nature of rollback
segments guarantees that these are the oldest inactive extents (and the least
likely to be used for read consistency).
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.) The best solution for performance is to
set all of your rollback segments to a size where every single transaction will
always fit. In practical terms, this may well be impossible - if your largest
transaction is 2 gigabytes and you require ten rollback segments for
concurrency. The segments should have an optimal size large enough that 90% or
better of transactions will fit without having to extend the segment. In
addition, the rollback tablespace should be large enough that when all rollback
segments are at the optimal value, there is plenty of space for them to extend
when it becomes necessary. For example, if your segments are set with an
optimal value of 500 megs and you know that there is a particular transaction
that runs infrequently, but requires one gigabyte when it does run, your
rollback tablespace must have 500 megs free (absolute minimum) when all
segments in that tablespace are at optimal. Since you cannot normally count on
all segments being at optimal or on that big transaction being the only one
using space in the rollback segment, prudence suggests having a bit more space
available than the absolute minimum.
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.
Even if rollback segments are not explicitly brought online by being added to
the "rollback_segments" parameter, they may be brought online during startup.
Oracle will do this based on the values of two other parameters: transactions
and "transactions_per_rollback_segment". On startup, Oracle will divide
transactions by "transactions_per_rollback_segment" and round up. If the
resulting number is less than the number of rollback segments brought online by
the "rollback_segments" parameter, nothing is done. However, if the resulting
number is greater and more rollback segments exist which are not currently
online, the segments will be brought online until the number is reached or
until there are no other offline segments. If neither parameter is set in the
init.ora, they will use default values.
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 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. 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.
NOTE:
====
DDL's will not be affected by SET TRANSACTION USE ROLLBACK SEGMENT.
Example:
-------
create table as select, alter table..
Create table as select is a data definition (DDL) statement, and as such it
does an implicit commit. A transaction ends once a commit is processed, so
any statements following a DDL command will not be part of the previous
transaction, and therefore commands following the DDL statement may use a
different rollback segment.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26104736/viewspace-707095/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26104736/viewspace-707095/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
