chap15_6.html

Oracle资料大集合

<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.security1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Security</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.webservices.html"><font face="verdana,helvetica" size="1" color="000000"><b>Web Services</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.networkservices1.html"><font face="verdana,helvetica" size="1" color="000000"><b>Network Services</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.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>
<p>
<b><font size="+1">	15.6	How do I匒ssess the impact of adding database buffers?</font></b>
<p>
<B>Problem</B>
<p>
I executed the steps in the preceding How-To and have discovered that I should increase the size of the database buffer cache. Is there some way of determining what sort of buffer cache hit rate improvement I can expect by devoting some number of additional buffers to the database buffer cache?
<p>
<B>Technique</B>
<p>
<B>Note-The UTLESTAT report does not address this topic.</B>
<p>
The database buffer cache is so important to the overall performance level of the Oracle server that a mechanism determines the improvements in the cache hit rate as a function of adding additional buffers to the buffer cache. Suppose, for example, that you know you can afford to devote 500 more buffers to the database buffer cache. (If the database block size is 2048 bytes, then these 500 additional buffers would consume 1 megabyte of memory). Oracle provides a way for you to assess the number of additional cache hits you will generate by increasing the buffer cache size by any amount between 1 and 500 database blocks.
<p>
Set the value of initialization parameter DB_BLOCK_LRU_EXTENDED_STATISTICS to the number of database block buffers you are considering for addition to the database buffer cache. Query the table X$KCBRBH to determine the number of additional cache hits generated by each additional database buffer block. Use this number to determine the improvement in the buffer cache hit rate, and, finally, increase the size of the initialization parameter DB_BLOCK_BUFFERS accordingly.
The structure of the X$KCBRBH table appears in Table 15.5.
<P>
Table 15.5 Two helpful columns in the X$KCBRBH dynamic performance table
<p>
<td>Column
<td>Column Description
<td>INDX
<td>New buffer identifier
<td>COUNT
<td>How many additional cache hits will be generated by adding the buffer to the buffer cache

<B>Steps</B>
<p>
<B>1.</B>	Check the value of the initialization parameter DB_BLOCK_LRU_EXTENDED_STATISTICS by manually reviewing the INIT.ORA file or by using the SHOW PARAMETER DB_BLOCK_LRU_EXTENDED_STATISTICS command in either the server manager or SQL Worksheet utilities.
<p>
<B>2.</B>	If you want to change the value of this parameter, shut down the database, alter the INIT.ORA file by setting DB_BLOCK_LRU_EXTENDED_STATISTICS to the number of maximum number of buffers you wish to consider adding to the buffer cache, and restart the database.
<p>
<B>3.</B>	Allow the database to run in normal mode during what is, for your installation, a typical period of database activity.
<p>
<B>4.</B>	Start a SQL*Plus session and connect to the database as the SYS user; only SYS has access to the X$KCBRBH table we are about to use. Use the start command to load and execute the script in CHP15_9.SQL. This script queries the X$KCBRBH table as shown in <A HREF="image/15oht07.gif" TARGET="_blank">Figure 15.7</A> and prompts the user for the number of additional buffers to consider.
<p>
Line 3 of the script contains a substitution variable for the number of additional buffers, the impact of which you want to assess. In <A HREF="image/15oht07.gif" TARGET="_blank">Figure 15.7</A>, the script reports that the addition of 25 buffers to the database buffer cache will result in 20 additional cache hits.
<p>
<B>5.</B>	Use the start command to load and execute the script CHP15_10.SQL. This SQL statement in this script is a modification of a statement from the last How-To. The script prompts the user for the number of additional cache hits reported in the previous step. The script and its output appear in <A HREF="image/15oht08.gif" TARGET="_blank">Figure 15.8</A>.
<p>
This script is a modification of CHP15_8.SQL. The change is in line 9, where the script reduces the number of physical disk reads by the number of additional cache hits generated in Step 4. The new cache hit rate, after the addition of 20 additional buffer blocks, will be 91%, an acceptable value.
<P>
<B>How It Works</B>
<p>
Oracle only populates the X$KCBRBH table if the DB_BLOCK_LRU_
EXTENDED_STATISTICS parameter is set to a non-zero value in the initialization parameter file. Steps 1 and 2 describe how to reset this parameter. The scripts in Steps 4 and 5 report the number of additional cache hits and the improvement in the overall database buffer cache hit rate that will be generated by additional cache buffer blocks.
<p>
<B>Comments</B>
<p>
One PL/SQL procedure can contain the functionality of each of the scripts in Steps 4 and 5. The procedure BC_GROW (for 揵uffer cache grow