chap5_4.html

Oracle资料大集合

<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">5.4	How do I匘etect unbalanced indexes?</font></b>

<P>

<B>Problem</B>

<P>

I have indexes on tables that are heavily inserted to, updated, or deleted from. I know that it is important to periodically examine all of my indexes to determine if they have become unbalanced. How do I detect
unbalanced indexes?

<P>

<B>Technique</B>

<P>

An unbalanced index has many records clumped close together on the index tree due to their similar indexed values. When an index is unbalanced, parts of an index are accessed more frequently than others, from which disk contention may occur, creating a bottleneck in performance. Statistics can be gathered on an index by issuing ANALYZE INDEX INDEX_NAME COMPUTE|ESTIMATE STATISTICS. The statistics appear in the USER_INDEXES, ALL_INDEXES_, and DBA_INDEXES data dictionary views. Also, additional statistics can be placed into the INDEX_STATS virtual table by using the VALIDATE INDEX INDEX_NAME statement.

<P>

<B>Steps</B>

<P>

<B>1.</B>	Connect to SQL*Plus as the WAITE user account. If you have not created the EMPLOYEE05 table, run the CHP5_1.SQL script to create it. Also, if you have not created the SSN_INDEX on the EMPLOYEE05 table, run the CHP5_2.SQL script to create it.

<P>

Initially, the EMPLOYEE05 table will have no records, and neither will the index have index entries. To determine statistics, you must either validate the index or generate statistics. This must be done manually, unless a program is written to automatically gather statistics. This How-To will demonstrate both methods.

<P>

<B>2.</B>	Gather statistics on the SSN_INDEX. Run the CHP5_6.SQL script to generate statistics. When prompted for an owner, enter WAITE; and when prompted for an index, enter SSN_INDEX. This script may be reused for any index in the database. For large indexes (over one hundred thousand records in the underlying table), use ESTIMATE instead of COMPUTE for faster response time.

<P>

<code><b>SQL> GET CHP5_6

<P>

1  ANALYZE INDEX &owner..&index_name

<P>

2  COMPUTE STATISTICS

<P>
SQL> /
<P>
Enter value for owner: WAITE
<P>
Enter value for index_name: SSN_INDEX
<P>
Index Analyzed.
<P>
SQL></b></code>
<P>
<B>3.	</B>At this point, statistics will indicate that the index is well-balanced, because there are no records in the underlying EMPLOYEE05 table. You can determine statistics by querying the DBA_INDEXES data dictionary view. Later in this How-To is the description and explanation of the DBA_INDEXES view. Run the CHP5_7.SQL script to gather statistics on how balanced the index is. Enter SSN_INDEX for the INDEX_NAME, and WAITE for the owner.
<P>
<code><b>SQL> GET CHP5_7.sql
<P>
1 SELECT BLEVEL, DECODE(BLEVEL,0,'OK BLEVEL',1,'OK BLEVEL',
<P>
2                       2,'OK BLEVEL',3,'OK BLEVEL',
<P>
3                       4,'OK BLEVEL','BLEVEL IS TOO HIGH') OK
<P>
4  FROM DBA_INDEXES
<P>
5  WHERE INDEX_NAME='&index_name' AND
<P>
6        OWNER='&owner'
<P>
SQL> /
<P>
Enter name for index_name: SSN_INDEX
<P>
Enter name from owner: WAITE
<P>
HEIGHT      OK
<P>
------      ---------
<P>
0           OK BLEVEL
<P>
1 row selected.</b></code>
<P>
You can see that the BLEVEL 0 is okay. The BLEVEL (or branch level) is part of the B-Trieve index format and relates to the number of times Oracle has to narrow its search on the index while searching for a particular record. In some cases, a separate disk hit is requested for each BLEVEL. If the BLEVEL were to be more than four, it is recommended to rebuild the index, as shown in How-To 5.5.
<P>
<B>4.</B>	At this point, only the BLEVEL has been investigated. To further determine if an index is unbalanced, further statistics should be gathered. To do this, use the VALIDATE INDEX command by running CHP5_8.SQL. Enter SSN_INDEX for the INDEX_NAME and WAITE for the OWNER.
<code><b><p>
SQL> GET CHP5_8.SQL
<p>
1  VALIDATE INDEX &owner..&index_name
<p>
SQL> /
<p>
Enter value for owner: WAITE
<p>
Enter value for index_name: SSN_INDEX<p>

Index analyzed.
<p>
SQL></b></code>
<p>
<B>5.</B>	At this point, the INDEX_STATS virtual table is populated with current statistics for the SSN_INDEX index. Query information on the deleted leaf rows, as compared to the total rows by running the CHP5_9.SQL script. Again, enter SSN_INDEX for index_name. The INDEX_STATS table does not contain an OWNER column and assumes you are lo