ch08.htm

Teach yourself Oracle8 in 21 day

	rollback segment.
	<P>
	<LI><TT>SYSTEM UNDO BLOCK</TT>--Same as <TT>UNDO BLOCK</TT> for the <TT>SYSTEM</TT>
	rollback segment.
</UL>

<P>The system rollback segment is the original rollback segment that was created
when the database was created. This rollback segment is used primarily for special
system functions but is sometimes used when no other rollback segment is available.
Typically the <TT>SYSTEM</TT> rollback segment is not used, and you do not need to
be concerned about it.</P>
<P>You can view these values with the SQL statement shown in Listing 8.1.
<H4><FONT COLOR="#000000"><B>INPUT :</B></FONT></H4>
<H4><FONT COLOR="#000077"><B>Listing 8.1. Rollback segment waits.</B></FONT></H4>
<PRE><FONT COLOR="#0066FF">SQL&gt; SELECT class, count
  2  FROM V$WAITSTAT
  3  WHERE class IN
  <TT>4 (`undo header', `undo block', `system undo header', `system undo block');</TT></FONT></PRE>
<PRE><FONT COLOR="#0066FF">
OUTPUT:
CLASS                  COUNT
------------------ --------
system undo header         0
system undo block          0
undo header                0
undo block                 0
</FONT></PRE>
<P>Compare these values with the total number of requests for data. Remember (from
earlier in the chapter) that the number of requests for data is equal to the sum
of <TT>DB BUFFER GETS</TT> and <TT>CONSISTENT GETS</TT> from <TT>V$SYSSTAT</TT>.
Also remember that you can extract that information with the query shown in Listing
8.2.
<H4><FONT COLOR="#000000"><B>INPUT :</B></FONT></H4>
<H4><FONT COLOR="#000077"><B>Listing 8.2. Total number of rollback requests.</B></FONT></H4>
<PRE><FONT COLOR="#0066FF">SQL&gt; SELECT SUM(value) &quot;Data Requests&quot;
  2  FROM v$sysstat
  <TT>3 WHERE name IN (`db block gets', `consistent gets');</TT></FONT></PRE>
<PRE><FONT COLOR="#0066FF">
OUTPUT:Data Requests
------------

         5105
</FONT></PRE>
<P><B>ANLYSIS:</B></P>
<P>If the number of waits for any of the rollback segment blocks or headers exceeds
more than 1% of the total number of requests, you should reduce the contention by
adding more rollback segments.</P>
<P>In this example the total number of requests is 5,105, as shown in Listing 8.2,
whereas the number of waits were all 0, as indicated in Listing 8.1. This indicates
that there was no contention.
<H4><FONT COLOR="#000077"><B>Determining the Size of Rollback Segments</B></FONT></H4>
<P>The advantage of small rollback segments is that they tend to remain cached, but
roll- back segments that are too small will grow and shrink unnecessarily. Long-running
transactions tend to like larger rollback segments because they usually generate
more rollback information.</P>
<P>It is possible to create several different sizes of rollback segments. Each type
of rollback segment should be used by the application developer based on the type
and length of the transaction (see the following).

<UL>
	<LI>OLTP--OLTP transactions are characterized by many concurrent transactions, each
	modifying perhaps only a small amount of data. These types of transactions benefit
	from a reduction of contention and quick access from cached rollback segments. Try
	to create many small rollback segments of perhaps 10KB to 20KB in size, each with
	2 to 4 extents (optimally with a rollback segment available for each transaction).
	<P>
	<LI>The small size of the rollback segments provides for a better chance of being
	cached in the SGA. There is probably very little dynamic growth of the extents.
	<P>
	<LI>Long Queries--For long queries where read consistency calls for quite a bit of
	rollback information to be accessed, use a larger rollback segment. A good rule of
	thumb is to create rollback segments approximately 10% the size of the largest table
	(most SQL statements affect only about 10% of the data in a table).
	<P>
	<LI>Large Updates--For transactions that update large amounts of data, you should
	also use a larger rollback segment. As is the case with the long queries, it is appropriate
	to create rollback segments approximately 10% the size of the largest table.
</UL>

<H4><FONT COLOR="#000077"><B>Determining the Size and Number of Extents</B></FONT></H4>
<P>In general, the best performance of rollback I/O performance can be obtained when
there are approximately 10 to 20 extents of equal size per rollback segment. To determine
the size and number of extents, use the following formula:</P>
<P>Rollback segment size = <TT>Rsize</TT> = Size of largest table / 10</P>
<P>Number of extents = <TT>NE</TT> = 10</P>
<P>Size of extents = <TT>Esize</TT> = <TT>Rsize</TT> / <TT>NE</TT></P>
<P>When creating the rollback segments, use the value of <TT>Esize</TT> for <TT>INITIAL</TT>
and <TT>NEXT</TT>; use the value of <TT>NE</TT> for <TT>MINEXTENTS</TT>. Even when
using these rules, you might not achieve the most effective size for your rollback
segments. If dynamic growth is occurring, you might be losing performance.
<H4><FONT COLOR="#000077"><B>Avoiding Dynamic Growth</B></FONT></H4>
<P>As stated earlier, you want to avoid the dynamic space management that causes
additional overhead and transactional delays. To determine whether rollback segments
are a problem, look in the dynamic performance table, <TT>V$ROLLSTAT</TT>. The following
columns are of particular interest:

<UL>
	<LI><TT>EXTENTS</TT>--Number of rollback extents.
	<P>
	<LI><TT>RSSIZE</TT>--The size (in bytes) of the rollback segment.
	<P>
	<LI><TT>OPTSIZE</TT>--The size to which <TT>OPTIMAL</TT> was set.
	<P>
	<LI><TT>AVEACTIVE</TT>--The current average size of active extents. <I>Active extents</I>
	are defined as extents with uncommitted transaction data.
	<P>
	<LI><TT>AVESHRINE</TT>--The total size of free extents divided by the number of <I>shrinks</I>
	(see the second item following).
	<P>
	<LI><TT>EXTEND</TT>--The number of times the rollback segment added an extent.
	<P>
	<LI><TT>SHRINK</TT>--The number of times the rollback segment shrank. Each shrink
	may be one or more extents at a time.
	<P>
	<LI><TT>HWMSIZE</TT>--The high water mark of rollback segment size. This is the largest
	that the segment size ever grew to be.
</UL>

<P>You can look at these statistics by using a SQL statement like the one shown in
Listing 8.3.
<H4><FONT COLOR="#000077"><B>INPUT: Listing 8.3. Check statistics.</B></FONT></H4>
<PRE><FONT COLOR="#0066FF">
SQL&gt; SELECT substr(name,1,40), extents, rssize, aveactive,
<IMG SRC="../buttonart/ccc.gif" WIDTH="14" HEIGHT="9" ALIGN="BOTTOM" BORDER="0" BOTTOM">aveshrink, extends, shrinks
  2  FROM v$rollname rn, v$rollstat rs
  <TT>3 WHERE rn.usn = rs.usn;</TT></FONT></PRE>

<PRE><FONT COLOR="#0066FF"><TT></TT></FONT></PRE>
<P><FONT COLOR="#000000"><B>OUTPUT :</B></FONT></P>
<PRE><FONT COLOR="#0066FF">SUBSTR(NAME,1,40)      EXTENTS  RSSIZE AVEACTIVE AVESHRINK   EXTENDS  SHRINKS
---------------------- ------- ------- --------- --------- ---------  -------
SYSTEM                       4  202752         0         0         0        0
RB_TEMP                     53  540672     23929         0         0        0
RB1                          2  202752         0         0         0        0
RB2                          2  202752     55193         0         0        0
</FONT></PRE>
<P>If the average size is close to the size set for <TT>OPTIMAL</TT>, <TT>OPTIMAL</TT>
is set correctly. If either <TT>extends</TT> or <TT>shrinks</TT> is high, you must
increase the value for <TT>OPTIMAL</TT>.
<H2><FONT COLOR="#000077"><B>Summary</B></FONT></H2>
<P>You have now seen all the basic files that are used by Oracle. The Oracle instance
is made up of the Oracle files, the processes, and the memory used by Oracle. The
Oracle files are made up of the datafiles, the redo log files, the control files,
and the parameter file. There are more files used in your day-to-day operations such
as archive log files, export files, and so on, but they are not critical to the operation
of the Oracle instance.</P>
<P>This chapter covers the redo log files, control files, and rollback segments.
The redo log files are used to store system redo information, which is used to recover
the database in the event of a catastrophic system failure. The redo log, in conjunction
with the archive log files, enables Oracle to recover transactions that were committed
before the failure.</P>
<P>The control file is used to keep internal Oracle control information. This file
is used to tell Oracle where the data and redo log files are in the operating system.
The control files are critical to the operation of the instance.</P>
<P>Finally, you learned about rollback segments. Rollback segments keep undo information
that is used to roll back transactions and for read consistency. The rollback segments
have a lot of tuning options, which you learned about in this chapter.
<H2><FONT COLOR="#000077"><B>What's Next?</B></FONT></H2>
<P>In tomorrow's lesson, &quot;Managing Data,&quot; you will learn how to use several
of the data-manipulation tools that come with your server: Export, Import, and the
SQL*Loader. Export and Import are used to move data in and out of the database. The
SQL*Loader utility is just used for loading data into the database.
<H2><FONT COLOR="#000077"><B>Q&amp;A</B></FONT></H2>

<DL>
	<DD><B>Q What files are used in the Oracle instance?</B>
	<P><B>A</B> There are several different files used in the Oracle instance. There
	are one or more datafiles, two or more redo log files, the control files, and the
	parameter file.</P>
	<P><B>Q What Oracle object is used for recovery operations?</B></P>
	<P><B>A</B> The redo log files and the archive log files are used for database recovery.</P>
	<P><B>Q What Oracle object is used for read consistency?</B></P>
	<P><B>A</B> The rollback segments are used for read consistency.</P>
	<P><B>Q What is read consistency?</B></P>
	<P><B>A</B> Read consistency allows a long-running transaction to always obtain the
	same data within the query.
</DL>

<H2><FONT COLOR="#000077"><B>Workshop</B></FONT></H2>
<P>The workshop provides quiz questions to help you solidify your understanding of
the material covered and exercises to provide you with experience in using what you've
learned. For answers to quiz questions, see Appendix A, &quot;Answers.&quot;
<H3><FONT COLOR="#000077"><B>Quiz</B></FONT></H3>

<DL>
	<DD><B>1.</B> How many redo log files do you need?
	<P><B>2. </B>What is a log group?</P>
	<P><B>3. </B>What is a control file used for?</P>
	<P><B>4. </B>Can you have more than one control file?</P>
	<P><B>5.</B> Can the instance be started without a control file?</P>
	<P><B>6. </B>What can you do to help re-create the control file?</P>
	<P><B>7. </B>Does a log switch force a checkpoint?</P>
	<P><B>8. </B>Does a checkpoint force a log switch?</P>
	<P><B>9. </B>What is a rollback segment used for?</P>
	<P><B>10. </B>How big is a rollback segment?
</DL>

<H3><FONT COLOR="#000077"><B>Exercises</B></FONT></H3>

<DL>
	<DD><B>1.</B> Back up your control file to trace.
	<P><B>2. </B>Use the Storage Manager to determine how much space your rollback segments
	use.</P>
	<P><B>3.</B> Add another control file to your system.
</DL>

<CENTER>
<P>
<HR>
<A HREF="../wk2/wk2.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../ch09/ch09.htm"><IMG
SRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A> <BR>
<BR>
<BR>
<IMG SRC="../buttonart/corp.gif" WIDTH="284" HEIGHT="45" ALIGN="BOTTOM" ALT="Macmillan Computer Publishing USA"
BORDER="0"></P>

<P>&#169; <A HREF="../copy.htm">Copyright</A>, Macmillan Computer Publishing. All
rights reserved.
</CENTER>


</BODY>

</HTML>