ch08.htm

Teach yourself Oracle8 in 21 day

<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B><I>Rollback segments</I>
record transactional information that will be used in the event that the transaction
is rolled back. They also provide read consistency and are used for database recovery.
Rollback segments keep the undo information that is used in rollback operations.</P>
<P>Remember, a transaction can be finished by issuing either a <TT>COMMIT</TT> or
a <TT>ROLLBACK</TT> statement. These statements perform completely opposite operations.
A commit operation finishes a transaction by finalizing all the changes that have
been made. When the commit operation has finished, the changes cannot be undone.
In the event of a system failure, all changes made in this transaction will be recovered.
A rollback operation causes all the changes made during the transaction to be undone.
When the rollback operation has finished, you must resubmit the transaction to reproduce
the changes that were made. After a rollback, it is as if the transaction never occurred.</P>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B><I>Read consistency</I> allows
a long-running transaction to always obtain the same data within the query. During
the transaction, the data is consistent to a single point in time and does not change.
Even though the data might have been changed by another user and the DBWR might even
have written it out, other transactions do not see those changes until a <TT>COMMIT</TT>
has occurred. In fact, only transactions that start after this transaction has been
committed see those changes.</P>
<P>Rollback segments can be either public or private. A private rollback segment
can be used only by the instance that opened the database, and a public rollback
segment can be used by any instance. If you are not running Oracle Parallel server,
the private and public rollback segments are identical. Rollback segments must be
carefully watched and can be tuned in several ways. It is important not only to size
the rollback segments correctly but also to create the proper number of rollback
segments and properly distribute them according to the number of user processes that
require them.
<H3><FONT COLOR="#000077"><B>Understanding How Rollback Segments Work</B></FONT></H3>
<P>As a transaction is being processed, information relating to changes made to the
datafiles by that transaction is constantly being written to the rollback segments.
It is important that this information be saved because a rollback would require that
all data be restored to its original condition.</P>
<P>The information written by the transaction to the rollback segments is held in
<I>rollback entries</I>. Depending on the length of the transaction and the number
of changes to data, there might be more than one rollback entry for each transaction.
These entries are linked together so that they can easily be used in the event of
a rollback.</P>
<P>This information stored in the rollback segments include block information about
what blocks have been modified and the data as it was before the change occurred.
Remember that the redo log also records information about changes in the database.
The redo log, along with the rollback segments, can restore your data up to the point
of failure.</P>
<P>Rollback segments are used concurrently by one or more transactions. You can tune
the rollback segments to provide for optimal efficiency and space usage. Having more
transactions sharing rollback segments causes more contention and uses space more
efficiently. Having fewer transactions per rollback segment causes less contention
and wastes more space.</P>
<P><FONT COLOR="#000077"><B>New Term:</B></FONT><B> </B>Oracle maintains what is
called a <I>transaction table</I> for each rollback segment. The transaction table
stores information about what transactions use that rollback segment and the rollback
entries for each change done by those transactions.</P>
<P>Each time a new transaction begins, it is assigned to a rollback segment. This
can happen in one of two ways:

<UL>
	<LI>Automatically--Oracle automatically assigns the transaction a rollback segment.
	The assignment takes place when the first DDL or DML statement is issued. Queries
	are never assigned rollback segments.
	<P>
	<LI>Manually--The application can manually specify a rollback segment by using the
	<TT>SET TRANSACTION</TT> command with the <TT>USE ROLLBACK SEGMENT</TT> parameter.
	This allows the developer to choose the correct size of rollback segment for a particular
	task. The rollback segment is assigned for the duration of the transaction.
</UL>

<P>At the end of each transaction, when the commit operation has occurred, the rollback
information is released from the rollback segment but is not deleted so as to maintain
read-consistent views for other queries that started before the transaction was committed.
To retain this information as long as possible, the rollback segments are written
as a circular buffer.</P>
<P>You can think of rollback segments as a sort of <I>circular buffer</I>: A rollback
segment must have at least two extents (usually more). When a transaction fills up
one extent, it starts using the next extent in sequence. When it gets to the last
extent, the transaction continues with extent 1 again if it is available, as shown
in Figure 8.6.</P>
<P><A NAME="06"></A><A HREF="06.htm"><B>Figure 8.6.</B></A></P>
<P><I>A logical representation of a rollback segment.</I></P>
<P>If the transaction uses the last extent in the segment, it looks to see whether
the first extent is available. If it is not, another extent is created, as shown
in Figures 8.7 and 8.8. The number of extents used for rollback segments is determined
in the definitions of the rollback segments when you create them.</P>
<P><A NAME="07"></A><A HREF="07.htm"><B>Figure 8.7.</B></A></P>
<P><I>A rollback segment with all extents used.</I></P>
<P><A NAME="08"></A><A HREF="08.htm"><B>Figure 8.8.</B></A></P>
<P><I>A rollback segment showing dynamic growth.</I></P>
<P>
<H3><FONT COLOR="#000077"><B>Creating Rollback Segments</B></FONT></H3>
<P>Rollback segments are created graphically with Enterprise Manager or Storage Manager
or on the command line with the <TT>CREATE ROLLBACK SEGMENT</TT> command. Although
all three of these are functional, some have more functionality than others, as you
will learn in the following sections.
<H4><FONT COLOR="#000077"><B>Using the Enterprise Manager</B></FONT></H4>
<P>To create a rollback segment with Enterprise Manager, drill down through the database
that you will be operating on until you get to the Rollback Segment entry. When you
right-click this icon you are given a list of options.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>For this example I have chosen to
	display only the Navigator pane. 
<HR>


</BLOCKQUOTE>

<P>After you have selected Create from the options you will see the Create Rollback
Segment screen. Type a name for the rollback segment and choose a tablespace in which
you want to create the rollback segment.


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>TIP:</B></FONT><B> </B>I have clicked the Show SQL button
	to display the SQL used to create the rollback segment. 
<HR>


</BLOCKQUOTE>

<P>If you click the Online button, the SQL statement will be altered to put the rollback
segment online after it is created, as shown in Figure 8.9. As you can see, there
are not a whole lot of options available when creating rollback segments with the
Enterprise Manager. It is also possible to create rollback segments with the Storage
Manager, which gives you more flexibility and options.
<H4><FONT COLOR="#000077"><B>Using the Storage Manager</B></FONT></H4>
<P>The left side of the Storage Manager is similar to the Enterprise Manager, as
shown in Figure 8.10.</P>
<P>Click on the Rollback Segment icon, and the tree in the left side of the Storage
Manager will expand to show the existing rollback segments. The right side of the
Storage Manager shows the name, tablespace name, status, size, and high water mark
of the rollback segments. (The <I>high water mark</I> indicates how much rollback
data can be used in that extent before a new one is allocated.)</P>
<P><A NAME="09"></A><A HREF="09.htm"><B>Figure 8.9.</B></A></P>
<P><I>Clicking the Online button puts the rollback segment online after you create
it.</I></P>
<P><A NAME="10"></A><A HREF="10.htm"><B>Figure 8.10.</B></A></P>
<P><I>The Storage Manager.</I></P>
<P>By right-clicking on the Rollback Segment icon you will see a menu that includes
an option to create a rollback segment. By clicking the Create button you will see
a screen that is identical to the Create Rollback Segment screen used by the Enterprise
Manager. This is shown in Figure 8.11.</P>
<P><A NAME="11"></A><A HREF="11.htm"><B>Figure 8.11.</B></A></P>
<P><I>The Create Rollback Segment dialog box in the Storage Manager.</I></P>
<P>As you can see, with both the Enterprise Manager and the Storage Manager, the
options available when creating rollback segments are very limited. To create rollback
segments with more options, use the <TT>CREATE ROLLBACK SEGMENT</TT> command.
<H4><FONT COLOR="#000077"><B>Using the </B>CREATE ROLLBACK SEGMENT<B> Command</B></FONT></H4>
<P>If you create a rollback segment with the <TT>CREATE ROLLBACK SEGMENT</TT> command,
you have two advantages: the ability to create the rollback segments with more options
and the advantage of using cut and paste in your editor to create multiple rollback
segments. If you are adding 50 or 100 rollback segments, using Enterprise Manager
and Schema Manager can become quite tedious. By using cut and paste in your editor
and just changing the rollback segment name, you can more easily create a large number
of rollback segments. An example of creating a rollback segment with the <TT>CREATE
ROLLBACK SEGMENT</TT> command is shown here:</P>
<PRE><FONT COLOR="#0066FF">CREATE [ PUBLIC or PRIVATE ] ROLLBACK SEGMENT rsname
TABLESPACE tsname
STORAGE (
INITIAL number K or M
NEXT number K or M
OPTIMAL number K or M
MINEXTENTS number
MAXEXTENTS number
);
</FONT></PRE>
<P>The parameters of <TT>CREATE PUBLIC ROLLBACK SEGMENT</TT> are as follows:

<UL>
	<LI>rsname--The name of the rollback segment you are creating.
	<P>
	<LI><TT>TABLESPACE</TT>tsname--Specifies the name of the tablespace where that rollback
	segment will be created.
	<P>
	<LI><TT>INITIAL</TT>number <TT>K or M</TT>--The initial extent size in K (kilobytes)
	or M (megabytes).
	<P>
	<LI><TT>NEXT</TT>number<TT> K or M</TT>--The size of the second extent in K (kilobytes)
	or M (megabytes). With rollback segments, it is always a good idea to make all extents
	the same size because there is no distinction between different extents.
	<P>
	<LI><TT>OPTIMAL</TT>number<TT> K or M</TT>--Specifies the size that you would like
	the rollback segment to try to stay, in K (kilobytes) or M (megabytes). When extents
	are no longer needed, they are eliminated until this size is reached.
	<P>
	<LI><TT>MINEXTENTS</TT>number--The minimum number of extents. This is also the number
	allocated when the segment is created.
	<P>
	<LI><TT>MAXEXTENTS</TT>number--The maximum number of extents that can be dynamically
	allocated.
</UL>

<P>Initially, there are <TT>MINEXTENTS</TT> number of extents in the rollback segment.
As extents fill up, they are used in a circular fashion, returning to the first extent
when all others are filled. If a rollback segment has used all the space in all the
extents and <TT>MAXEXTENTS</TT> has not been reached, another extent is created.
If the size of the rollback segment is larger than <TT>OPTIMAL</TT> and there are
unused extents, the unused extents are dropped from the rollback segment.</P>
<P>Both the creation and destruction of a rollback segment extents cause overhead
in the system. In addition to the overhead created by the addition of extents to
a rollback segment, the transaction needing to write into that rollback segment must
wait for the extent to be created before it can continue. The following sections
explain how to tune your rollback segments.
<H3><FONT COLOR="#000077"><B>Tuning Rollback Segments</B></FONT></H3>
<P>To properly configure a system's rollback segments, you must create enough rollback
segments, and they must be of a sufficient size. That seems fairly simple, but it
is not. You can observe how the rollback segments are being used, and from that determine
what needs to be done.
<H4><FONT COLOR="#000077"><B>Determining the Number of Rollback Segments</B></FONT></H4>
<P>The number of rollback segments should be determined by the number of concurrent
transactions in the database. Remember--the fewer transactions per rollback segment,
the less contention. A good rule of thumb is to create about one rollback segment
for every four concurrent transactions.</P>
<P>Rollback contention occurs when too many transactions try to use the same rollback
segment at the same time, and some of them have to wait. You can tell whether you
are seeing contention on rollback segments by looking at the dynamic performance
table, <TT>V$WAITSTAT</TT>. Following is the data contained by <TT>V$WAITSTAT</TT>
that is related to rollback segments:

<UL>
	<LI><TT>UNDO HEADER</TT>--The number of waits for buffers containing rollback header
	blocks.
	<P>
	<LI><TT>UNDO BLOCK</TT>--The number of waits for buffers containing rollback blocks
	other than header blocks.
	<P>
	<LI><TT>SYSTEM UNDO HEADER</TT>--Same as <TT>UNDO HEADER</TT> for the <TT>SYSTEM</TT>