chap15_7.html

Oracle资料大集合

</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=6 height=12 alt="" border="0"></td></tr>
<tr>
<td><img src="../../../images/arrow.gif" width=10 height=17 alt="" border="0"></td>
<td><a href="http://www.itlibrary.com/reference/dir.funandgames1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Fun &amp; Games</b></font></td>
</tr>
</table>


<!-- <IMG SRC="/images/leftnav.gif" WIDTH=111 HEIGHT=404 ALT="nav" border="0"  ISMAP USEMAP="#leftnav"> -->
<p>
<a href="http://www.earthweb.com/jobs/" target=new><IMG SRC="../../../images/dice-105x30.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.earthwebdirect.com/" target=new><IMG SRC="../../../images/earthwebdirect.gif" WIDTH=105 HEIGHT=30 ALT="EarthWeb Direct" border="0"></a>
<a href="http://www.fatbrain.com/home.html?from=UUX592" target=new><IMG SRC="../../../images/fatbrain.gif" WIDTH=105 HEIGHT=30 ALT="Fatbrain" border="0"></a>
<a href="http://auctions.earthweb.com" target=new><IMG SRC="../../../images/auctions_105x30.gif" WIDTH=105 HEIGHT=30 ALT="Auctions" border="0"></a>
<a href="http://www.supportsource.com" target=new><IMG SRC="../../../images/ss2000sneak2.gif" WIDTH=105 HEIGHT=30 ALT="Support Source Answers" border="0"></a>
<P>

<b><font face="verdana,helvetica" size="2">EarthWeb sites</FONT></b><br>
  <FONT SIZE="1">
  <a href="http://www.crossnodes.com"><font face="verdana,helvetica" size="1" color="000000">Crossnodes</font></a><br>
  <a href="http://www.datamation.com"><font face="verdana,helvetica" size="1" color="000000">Datamation</font></a><br>
  <a href="http://www.developer.com"><font face="verdana,helvetica" size="1" color="000000">Developer.com</FONT></A><br>
  <a href="http://www.dice.com"><font face="verdana,helvetica" size="1" color="000000">DICE</font></a><br>
  <a href="http://www.earthweb.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb.com</font></a><br>
  <a href="http://www.earthwebdirect.com"><font face="verdana,helvetica" size="1" color="000000">EarthWeb Direct</font></a><br>
  <a href="http://www.erphub.com"><font face="verdana,helvetica" size="1" color="000000">ERP Hub</font></a><br>
  <a href="http://www.gamelan.com"><font face="verdana,helvetica" size="1" color="000000">Gamelan</font></a><br>
  <a href="http://www.gocertify.com"><font face="verdana,helvetica" size="1" color="000000">GoCertify.com</font></a><br>
  <a href="http://www.htmlgoodies.com"><font face="verdana,helvetica" size="1" color="000000">HTMLGoodies</font></a><br>
  <a href="http://www.intranetjournal.com"><font face="verdana,helvetica" size="1" color="000000">Intranet Journal</font></a><br>
  <a href="http://www.itknowledge.com"><font face="verdana,helvetica" size="1" color="000000">IT Knowledge</font></a><br>
  <a href="http://www.itlibrary.com"><font face="verdana,helvetica" size="1" color="000000">IT Library</font></a><br>
  <a href="http://www.javagoodies.com"><font face="verdana,helvetica" size="1" color="000000">JavaGoodies</font></a><br>
  <a href="http://www.jars.com"><font face="verdana,helvetica" size="1" color="000000">JARS</font></a><br>
  <a href="http://www.javascripts.com"><font face="verdana,helvetica" size="1" color="000000">JavaScripts.com</font></a><br>
  <a href="http://www.opensourceit.com"><font face="verdana,helvetica" size="1" color="000000">open source IT</font></a><br>
  <a href="http://www.roadcoders.com"><font face="verdana,helvetica" size="1" color="000000">RoadCoders</font></a><br>
  <a href="http://www.Y2Kinfo.com"><font face="verdana,helvetica" size="1" color="000000">Y2K Info</font></a>
</FONT>

</td>



<!--End Left Navigation column -->
<!-- spacer column  -->

<TD width="40">
<img src="../../../images/dotclear.gif" WIDTH="40" HEIGHT="1" border=0>
</TD>

<!--Begin Content Column -->

<TD VALIGN="TOP" width="500">

<P>




<blockquote>
<p>
<b><font size="+1">15.8 How do I匢dentify rollback segment contention?</font></b>
<p>
Problem
<p>
I administer a database with significant amounts of update, delete, and insert activity. I know that rollback segment contention can degrade performance in a database environment like mine. How can I determine if my database is suffering from rollback segment contention?
<p>
Technique
<p>
Oracle8 uses rollback segments for all transactions that change the database and assigns every such transaction to one of the available rollback segments. Every rollback segment has a transaction table in its header and every write transaction, moreover, must periodically acquire update access to the transaction table of its rollback segment.
<p>
Contention becomes an issue when Oracle8 has assigned more than one transaction to any given rollback segment, and those transactions attempt to access the rollback segment抯 transaction table at the same time.
<p>
The UTLESTAT report contains a section on rollback segment statistics, which appears in Listing 15.10.
<p>
Listing 15.10 An excerpt from the rollback segment performance section of the UTLESTAT report
<p>
UNDO_SEGMENT        TRANS_TBL_GETS      TRANS_TBL_WAITS
<p>
------------------- ------------------- -------------------
<p>
0                   7                   0
<p>
1                 412                  42
<p>
2                 652                  57
<p>
3                1121                 127
<p>
4                  65                   7
<p>
5                  72                   9
<p>
The column TRANS_TBL_GETS contains the number of number of times that Oracle accessed the header of the rollback segment specified (by rollback segment number, unfortunately) in the UNDO_SEGMENT column. The column TRANS_TBL_WAITS contains the number of these gets, which results in a temporary wait state because another transaction already accessed the rollback segment抯 header.
<p>
A school of thought maintains that any non-zero value in the TRANS_TBL_WAITS column should prompt the DBA to add rollback segments. This is too conservative for most production installations and a better guideline to follow is that whenever the ratio of TRANS_TBL_WAITS to TRANS_TBL_GETS is greater than 2 percent for any rollback segment, there is rollback segment contention.
<p>
Another way to detect rollback segment contention is to use the V$ROLLSTAT table. It appears in Table 15.7. <p>
Table 15.7 The structure of the V$ROLLSTAT table<p>
<table cellpadding=3 cellspaceing=3 border=0 bgcolor="cdcdcd">

<TR><td>Column</td>
<td>Column Description</td></TR>

<TR><td>USN</td>
<td>Rollback segment number</td></TR>

<TR><td>EXTENTS</td>
<td>Number of rollback extents</td></TR>

<TR><td>RSSIZE</td>
<td>Rollback segment size in bytes</td></TR>
<TR>
<td>WRITES</td>
<td>Number of bytes written to rollback segment</td></TR>

<TR><td>XACTS</td>
<td>Number of currently active transactions</td></TR>

<TR><td>GETS</td>
<td>Number of header gets</td></TR>

<TR><td>WAITS</td>
<td>Number of header waits</td></TR>

<TR><td>OPTSIZE</td>
<td>Optimal size of rollback segment</td></TR>

<TR><td>HWMSIZE</td>
<td>High water mark of rollback segment size</td></TR>

<TR><td>SHRINKS</td>
<td>Number of times the size of a rollback segment decreases</td></TR>

<TR><td>WRAPS</td>
<td>Number of times rollback segment is wrapped</td></TR>

<TR><td>EXTENDS</td>
<td>Number of times rollback segment size is extended</td></TR>

<TR><td>AVESHRINK</td>
<td>Average shrink size</td></TR>

<TR><td>AVEACTIVE</td>
<td>Current size of active extents, averaged over time</td></TR>

<TR><td>STATUS</td>
<td>Rollback segment status</td></TR>

<TR><td>CUREXT</td>
<td>Current extent</td></TR>

<TR><td>CURBLK</td>
<td>Current block</td></TR>
</table>
<p>

Like the rollback performance section of the UTLESTAT report, the V$ROLLSTAT view uses the rollback segment number instead of the name to identify each rollback segment. The V$ROLLNAME table, appearing in Table 15.8, maps the rollback segment number to the rollback segment name.

<p>

Table 15.8 The structure of the V$ROLLNAME table
<table cellpadding=3 cellspacing=3 border=0 bgcolor="cdcdcd">
<TR><td>Column</td>
<td>Column Description</td></TR>

<TR><td>USN</td>
<td>Rollback segment number</td></TR>

<TR><td>NAME</td>
<td>Rollback segment name</td></TR>

<!-- </td>

</td>
</tr>
</table> -->
</td>

</td>
</tr>
</table>

<!-- begin footer information -->

<MAP NAME="footer">
<AREA SHAPE=RECT COORDS="0,0,62,26" HREF="http://www.itlibrary.com/">
<AREA SHAPE=RECT COORDS="62,0,135,26" HREF="http://www.developer.com/about/">
<AREA SHAPE=RECT COORDS="135,0,199,26" HREF="http://www.itlibrary.com/search/">
<AREA SHAPE=RECT COORDS="200,0,274,26" HREF="http://www.itlibrary.com/subscribe/">
<AREA SHAPE=RECT COORDS="275,0,335,25" HREF="http://www.developer.com/contact/adinfo.html">
<AREA SHAPE=RECT COORDS="335,0,417,25" HREF="http://www.developer.com/contact/">
<AREA SHAPE=RECT COORDS="418,0,467,26" HREF="http://www.developer.com/about/faq.html">
</MAP>

<P>
<table width="640" cellpadding="0" cellspacing="0" border="0">
<tr>
<TD WIDTH="130"></TD>
<td width="468">
<IMG SRC="../../../images/footer/footerfile.gif" ALT="footer nav" width="467" height="26" BORDER="0" usemap="#footer" ismap>
</td>
</tr>
<tr>
<TD WIDTH="130"></TD>
<td width="468"> 
<font face=arial,helvetica size="1"> Use of this site is subject certain <a href="http://www.developer.com/legal/">Terms &amp; Conditions.</a><br>
Copyright (c) 1996-1999 <A HREF="http://www.earthweb.com/">EarthWeb, Inc.</A>.  All rights reserved.  Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited.
<a href="http://www.earthweb.com/corporate/privacy.html">Please read our privacy policy for details.</a>
</td>
</tr>
</table>

</BODY>
</HTML>