chap9_2.html

来自「Oracle资料大集合」· HTML 代码 · 共 358 行 · 第 1/2 页

</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=12 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.middleware.html"><font face="verdana,helvetica" size="1" color="000000"><b>Middleware</b></font></td>
</tr>
<tr><td colspan="2"><img src="../../../images/dotclear.gif" width=12 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.components.html"><font face="verdana,helvetica" size="1" color="000000"><b>Components</b></font></td>
</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.operatingsystems.html"><font face="verdana,helvetica" size="1" color="000000"><b>Operating Systems</b></font></td>
</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.userinterfaces.html"><font face="verdana,helvetica" size="1" color="000000"><b>User Interfaces</b></font></td>
</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.groupwareandcollaboration1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Groupware &amp; Collaboration</b></font></td>
</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.contentmanagement.html"><font face="verdana,helvetica" size="1" color="000000"><b>Content Management</b></font></td>
</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.productivityapplications1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Productivity Applications</b></font></td>
</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.hardware1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Hardware</b></font></td>
</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>

<b><font size="+1"><B>9.2	How do I匘etect row chaining and migration in tables?</B></font></b>
<p>
<B>Problem</B>
<p>I have reason to believe that my database is generating more I/O than it should. How can I find out whether particular tables are suffering from row chaining or row migration?
<p>
<B>Technique</B>
<p>
Row migration occurs when a database block does not contain enough free space to accommodate an update statement. Suppose, for example, that block number AAAAAB contains two rows. If a user issues an update statement that updates one of these rows, and the updated row no longer fits in block number AAAAAB, Oracle8 migrates the row. That is, the server moves the row to another block and maintains a pointer to the new block in the row抯 original block.
<p>
Row chaining, in contrast, occurs when no single database block is large enough to accommodate a particular row. This is common when a table contains several large CHAR or VARCHAR2 data types. If the database block size is 2048 bytes and a row is 3000 bytes long, for example, this row will not fit in any single database block; it will reside in multiple database blocks. An unpleasant side effect of both chaining and migration is that the Oracle8 server must read more than one database block to read a single row.
<p>
The ANALYZE command can reveal, on a table-by-table basis, which tables contain migrated and/or chained rows. A table to hold information about these migrated/chained rows must exist prior to using the ANALYZE command for this purpose, and you will build that table first.
<p>
<B>Steps</B>
<p>
<B>1.</B>	Run SQL*Plus and connect as the WAITE user. Use the START command to load and execute the UTLCHAIN.SQL script to create the CHAINED_ROWS table. This script is supplied by Oracle corporation; under Windows NT it resides in the \orant\rdbms80\admin subdirectory. The results of this operation appear in <A HREF="image/09oht03.gif" TARGET="_blank">Figure 9.3</A>.
<p>
<B>2.</B>	Use the START command to load and execute the CHP9_3.SQL script, which appears in Listing 9.1.
<p>
Listing 9.1 Creating the CHAIN and MIGRATE tables in the CHP9_3.SQL script
<p>
<code><b>1  DROP TABLE migrate09;
<p>
2  --
<p>
3  CREATE TABLE migrate09
<p>
4     (attr1  char(900),
<p>
5      attr2  char(200))
<p>
6  PCTFREE 0
<p>
7  PCTUSED 99;
<p>
8  --
<p>
9  INSERT INTO migrate09 (attr1) VALUES ('1');
<p>
10  INSERT INTO migrate09 (attr1) VALUES ('2');
<p>
11  --
<p>
12  UPDATE migrate09 set attr2 = 'a';
<p>
13  --
<p>
14  DROP TABLE chain09;
<p>
15  --
<p>
16  CREATE TABLE chain09
<p>
17     (attr1 char(2000),
<p>
18      attr2 char(2000));
<p>
19  --
<p>
20 insert into chain09 values ('a', 'b');</b></code>
<p>
In lines 3 through 7, the script creates a table called MIGRATE09. Script lines 16 through 18 create a second table called CHAIN09. The script also inserts two rows into CHAIN09 and one row into MIGRATE09.
<p>
The two tables CHAIN09 and MIGRATE09 are shown in <A HREF="image/09oht04.gif" TARGET="_blank">Figure 9.4</A> and are composed of attributes of data type CHAR. Oracle8 will right-pad fields of this type with blanks so that they assume their maximum length. The values of PCTFREE and PCTUSED are not very realistic, but they do help to induce row migration for the purposes of this example.
<p>
<B>3.</B>	Use the START command to load and execute the CHP9_4.SQL script to determine whether any chained or migrated rows exist in the CHAIN09 or MIGRATE09 tables.
<p>
<B>How It Works</B>
<p>
Step 1 creates the CHAINED_ROWS table in the WAITE account抯 default tablespace. Step 2 creates two tables that will subsequently suffer from row chaining, row migration, or both. In step 3, the CHP9_4.SQL script issues the ANALYZE command with the list chained rows into clause to insert the ROWIDs of any chained or migrated rows in the tables CHAIN09 or MIGRATE09 into the CHAINED_ROWS table. The final command in the script queries the CHAINED_ROWS table, exposing any chained or migrated rows. The script and its output appear in <A HREF="image/09oht05.gif" TARGET="_blank">Figure 9.5</A>.
<p>
The script results in <A HREF="image/09oht06.gif" TARGET="_blank">Figure 9.5</A> indicate that there is one chained or migrated row in both the CHAIN09 and MIGRATE09 tables.
<p>
<B>Comments</B>
<p>
This How-To indicated rows in the CHAIN09 and MIGRATE09 tables that Oracle8 either chained or migrated. The next logical question is 揂re the rows described in CHAINED_ROWS really chained rows, or might they be migrated rows?