<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2 Transitional//EN">
<HTML>
<HEAD>
  <!--
    *************************************************************************************
    * Copyright (c) 2005 VERITAS Software Corporation. All rights reserved. *

      For third-party license citations, please see the bottom of this file.
    *************************************************************************************
  -->
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta name="TEMPLATEBASE" content="vx_vxhelp_2005c">
<meta name="LASTUPDATED" content="06/13/05 10:36:40">
<meta name="PRODUCTNAME" content="VERITAS Software Documentation">
<meta name="FILENAME" content="oracle_hp_ch14tune.fm">
     
<title>Tuning Oracle Databases</title>

</HEAD>

<BODY bgcolor="#FFFFFF" link="#000066" vlink="#0000ff" marginheight="0" topmargin="0">
<basefont=1>

<a name="1023086"></a>

<div align="left">
<table border="0" cellspacing="0" cellpadding="0" width="100%">
 <tr>
   <td height="2" border="0" cellspacing="0" cellpadding="0" colspan="2" bgcolor="#000000">
   </td>
 </tr>
 <tr>
   <td height="15" width="50%" border="0" cellspacing="0" cellpadding="0" bgcolor="#cccccc">
    <img src="images/vxsymbol.gif" height="20" width="31" border="0" cellspacing="0" cellpadding="0" align="left">
     
     
   </td>
   <td align="right" width="50%" bgcolor="#cccccc">

<!-- Header Breadcrumbs -->
<font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="-2">&lt;&nbsp;<a href="oracle_hp_ch14tune_sf_dbag136_1026390.html" title="Previous - Tuning VxFS">Previous</a>&nbsp;&nbsp;|&nbsp;&nbsp;<a href="oracle_hp_ch14tune_sf_dbag138_1028834.html" title="Next - Tuning HP-UX">Next</a>
&nbsp;&gt;&nbsp;&nbsp;</font>
   </td>
 </tr>

 <tr>
  <td bgcolor="#CCCCCC" colspan="2" height="15" align="right">
  <font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="1">Product:  
<a href="sf_dbag_splashpage_docCD_sf_dbag1_103036.vxhd" title="Storage Foundation for Databases Guides title page ">Storage Foundation for Databases Guides</a>
  &nbsp;&nbsp;
  </font>
  </td>
 </tr>
 <tr>
  <td bgcolor="#CCCCCC" colspan="2" height="15" align="right">
  <font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="1">Manual:  
<a href="oracle_hp_dbag_titlepage_docCD_sf_dbag2_73700.vxhd" title="Storage Foundation 4.1 for Oracle Administrator's Guide title page ">Storage Foundation 4.1 for Oracle Administrator's Guide</a>
  &nbsp;&nbsp;
  </font>
  </td>
 </tr>


</table>
</div>

<div align="left">
 <table border="0" width="100%" bgcolor="#FFFFFF">
  <tr>
   <td width="100%" valign="top" bgcolor="#FFFFFF">

<!-- Start Main Topic Content -->
        <a name="startcontent"></a>
	
<h1><font face="Arial, Helvetica, Geneva, sansserif, sans serif, Verdana" size="+1" color="#000000"><a name="1023086"></a>Tuning Oracle Databases</font>
</h1>
 
  
  
  

<p>
<font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="2"><a name="1027931"></a>To achieve optimal performance on your Oracle database, the database needs to be tuned to work with VxFS. This section describes some of the Oracle parameters that you can tune to improve your Oracle database performance when using Quick I/O.</font>
</p>




<h2><font face="Arial, Helvetica, Geneva, sansserif, sans serif, Verdana" size="3" color="#000000"><a name="1027935"></a>Sequential Table Scans</font>
</h2>
 
<p>
<font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="2"><a name="1027938"></a>Quick I/O performs all I/O as direct I/O. In the case of single-threaded sequential scans (common in decision support system (DSS) workloads), using buffered reads can yield better performance. Because the file system detects these sequential reads and performs read-aheads, the next few blocks that Oracle requests are readily available in the system buffer cache and are simply copied to the Oracle system global area (SGA). Because access from memory is inherently faster than access from disk, this achieves a significant reduction in response time. </font>
</p>

<p>
<font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="2"><a name="1027942"></a>To handle large sequential scans when using Quick I/O, use the Oracle parallel query process to break the single large scan into multiple smaller scans. This is done by setting the Oracle parameters <font face="Courier New, Courier, sansserif, sans serif" size="2">PARALLEL_MAX_SERVERS</font>, <font face="Courier New, Courier, sansserif, sans serif" size="2">PARALLEL_MIN_SERVERS</font>, and <font face="Courier New, Courier, sansserif, sans serif" size="2">SORT_AREA_SIZE</font> suitably. </font>
</p>

<hr noshade size="2" width="100%" color="#000000">
<img src="images/note.gif" alt="Note " height="16" width="16">&nbsp;
<font face="Arial, Helvetica, Geneva, sansserif, sans serif, Verdana" size="2" color="#000000">
<a name="1027946"></a><b>Note  &nbsp;&nbsp;</b>
</font>
<font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="2" color="#000000">Consult the Oracle documentation for your system and version of Oracle, and use the settings recommended for these parameters when provided.
</font>
<hr noshade size="2" width="100%" color="#000000">



<h2><font face="Arial, Helvetica, Geneva, sansserif, sans serif, Verdana" size="3" color="#000000"><a name="1027958"></a>Asynchronous I/O</font>
</h2>
 
<p>
<font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="2"><a name="1027959"></a>Quick I/O and Oracle Disk Manager support kernel asynchronous I/O, which reduces CPU utilization and improves transaction throughput. Enabling the following parameters lets Oracle take advantage of asynchronous I/O and avoids having to configure multiple <font face="Courier New, Courier, sansserif, sans serif" size="2">DBWR</font> slaves:</font>
</p>

<p>
<ul type="disc"><li><font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="2"><a name="1027961"></a>If you are using Quick I/O datafiles with Oracle8i, set <font face="Courier New, Courier, sansserif, sans serif" size="2">DISK_ASYNCH_IO</font> to <font face="Courier New, Courier, sansserif, sans serif" size="2">TRUE</font> in <font face="Courier New, Courier, sansserif, sans serif" size="2">init.ora</font>.</font>
</li>
<li><font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="2"><a name="1027962"></a>If you are using ODM on Oracle9i, you do not need to change any <font face="Courier New, Courier, sansserif, sans serif" size="2">init.ora</font> parameters.</font>
</li>
</ul>
</p>
<p>
<font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="2"><a name="1027964"></a>Your Oracle Installation Guide provides detailed instructions on implementing asynchronous I/O on your system.</font>
</p>




<h2><font face="Arial, Helvetica, Geneva, sansserif, sans serif, Verdana" size="3" color="#000000"><a name="1027965"></a>Tuning Buffer Cache</font>
</h2>
 
<p>
<font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="2"><a name="1027966"></a>The UNIX buffer cache plays an important role in performance when using HFS or JFS in buffered I/O mode. When using Quick I/O, however, the database cache must be tuned as if raw devices are being used. You can allocate more memory to the database buffer cache because Quick I/O bypasses the file system cache to improve database performance. Memory pages normally allocated to the file system cache can be allocated to the database buffer cache (SGA). With Oracle9i, you can adjust the SGA size without shutting down the database.</font>
</p>




<h2><font face="Arial, Helvetica, Geneva, sansserif, sans serif, Verdana" size="3" color="#000000"><a name="1027969"></a>Setting Oracle Block Reads During Sequential Scans</font>
</h2>
 
<p>
<font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="2"><a name="1028007"></a>The <font face="Courier New, Courier, sansserif, sans serif" size="2">DB_FILE_MULTIBLOCK_READ_COUNT</font> parameter specifies the maximum number of blocks Oracle reads in one I/O operation during a sequential scan. When the file system is created on a striped volume, set this parameter to a value that is a multiple of the full stripe size divided by <font face="Courier New, Courier, sansserif, sans serif" size="2">DB_BLOCK_SIZE</font>. Using a full stripe size allows the read operations to take advantage of the full bandwidth of the striped disks during sequential table scan.</font>
</p>

<p>
<font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="2"><a name="1028009"></a>Set the <font face="Courier New, Courier, sansserif, sans serif" size="2">DB_FILE_MULTIBLOCK_READ_COUNT</font> to a value that is a multiple of <font face="Courier New, Courier, sansserif, sans serif" size="2">(read_pref_io*read_nstream)/DB_BLOCK_SIZE</font>, but the value should not exceed the value of <font face="Courier New, Courier, sansserif, sans serif" size="2">max_direct_iosz/DB_BLOCK_SIZE</font>. </font>
</p>

<p>
<font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="2"><a name="1028014"></a>Use the <font face="Courier New, Courier, sansserif, sans serif" size="2">vxtunefs</font> command to display the value of <font face="Courier New, Courier, sansserif, sans serif" size="2">read_pref_io</font>, <font face="Courier New, Courier, sansserif, sans serif" size="2">read_nstream</font>, and <font face="Courier New, Courier, sansserif, sans serif" size="2">max_direct_iosz</font>, for example:</font>
</p>

<br>
<font face="Courier New, Courier, sansserif, sans serif" size="2"><a name="1028015"></a><font face="Courier New, Courier, sansserif, sans serif" size="2">&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;</font><font face="Courier New, Courier, sansserif, sans serif" size="2"><b>vxtunefs&nbsp;/db01</b></font>
</font>
<p>
<font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="2"><a name="1028017"></a>The <font face="Courier New, Courier, sansserif, sans serif" size="2">vxtunefs</font> command displays output similar to the following: </font>
</p>

<br>
<font face="Courier New, Courier, sansserif, sans serif" size="2"><a name="1028018"></a><font face="Courier New, Courier, sansserif, sans serif" size="2">&nbsp;&nbsp;&nbsp;&nbsp;Filesystem&nbsp;i/o&nbsp;parameters&nbsp;for&nbsp;/db01&nbsp;</font>
</font>
<a name="1028019"></a><br><font face="Courier New, Courier, sansserif, sans serif" size="2"><font face="Courier New, Courier, sansserif, sans serif" size="2">&nbsp;&nbsp;&nbsp;&nbsp;read_pref_io&nbsp;&nbsp;=&nbsp;65536<br>&nbsp;&nbsp;&nbsp;&nbsp;read_nstream&nbsp;&nbsp;=&nbsp;4<br>&nbsp;&nbsp;&nbsp;&nbsp;read_unit_io&nbsp;&nbsp;=&nbsp;65536&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;write_pref_io&nbsp;&nbsp;=&nbsp;65536&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;write_nstream&nbsp;&nbsp;=&nbsp;4<br>&nbsp;&nbsp;&nbsp;&nbsp;write_unit_io&nbsp;&nbsp;=&nbsp;65536&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;pref_strength&nbsp;&nbsp;=&nbsp;10&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;buf_breakup_size&nbsp;&nbsp;=&nbsp;131072&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;discovered_direct_iosz&nbsp;&nbsp;=&nbsp;262144&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;max_direct_iosz&nbsp;&nbsp;=&nbsp;2097152&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;default_indir_size&nbsp;=&nbsp;8192&nbsp;</font></font>



<h2><font face="Arial, Helvetica, Geneva, sansserif, sans serif, Verdana" size="3" color="#000000"><a name="1028020"></a>Setting Slave Parameters</font>
</h2>
 
<p>
<font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="2"><a name="1028021"></a>Quick I/O and ODM provide support for asynchronous I/O, eliminating the need for multiple logwriter slaves or database writer slaves. This parameter is set to <font face="Courier New, Courier, sansserif, sans serif" size="2">0</font> by default.</font>
</p>

<p>
<font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="2"><a name="1028022"></a>It is not necessary to set the DBWR_IO_SLAVES settings if you are using Quick I/O. The number of <font face="Courier New, Courier, sansserif, sans serif" size="2">DBWR</font> writer processes is set within DB_WRITER_PROCESSES, which performs asynchronous I/O.</font>
</p>




<h2><font face="Arial, Helvetica, Geneva, sansserif, sans serif, Verdana" size="3" color="#000000"><a name="1028023"></a>Configuring Memory Allocation</font>
</h2>
 
<p>
<font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="2"><a name="1028024"></a>Never configure Oracle to make use of more memory than is physically available on the system. Oracle may have to compete with other processes for system memory resources, and all of these potential processes must be considered when sizing and allocating memory. In the ideal configuration, a system that is dedicated to Oracle simplifies the tuning and monitoring issues and ensures best performance.</font>
</p>

<p>
<font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="2"><a name="1028025"></a></font>
</p>


<!-- End Main Topic Content -->

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

<div>
<table border="0" cellspacing="0" cellpadding="0" width="100%">
 <tr>
  <td height="18" border="0" cellspacing="0" cellpadding="0" colspan="2" bgcolor="#FFFFFF">
  </td>
 </tr>
  <td align="left"><font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="-2">&nbsp;&#94;&nbsp;<a href="oracle_hp_ch14tune_sf_dbag137_1023086.html" title="Tuning Oracle Databases">Return to Top</a></font>

  </td>
  <td align="right"><font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="-2">&lt;&nbsp;<a href="oracle_hp_ch14tune_sf_dbag136_1026390.html" title="Previous - Tuning VxFS">Previous</a>&nbsp;&nbsp;|&nbsp;&nbsp;<a href="oracle_hp_ch14tune_sf_dbag138_1028834.html" title="Next - Tuning HP-UX">Next</a>
&nbsp;&gt;&nbsp;&nbsp;</font>
  </td>
 <tr>
   <td height="9" border="0" cellspacing="0" cellpadding="0" colspan="2" bgcolor="#FFFFFF">
   </td>
 </tr>

<!-- Footer Breadcrumbs -->
<tr>
<td bgcolor="#FFFFFF" colspan="2" height="15" align="right">
<font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="1">Product:  
<a href="sf_dbag_splashpage_docCD_sf_dbag1_103036.vxhd" title="Storage Foundation for Databases Guides title page ">Storage Foundation for Databases Guides</a>&nbsp;&nbsp;</font></td>
</tr>

<tr>
<td bgcolor="#FFFFFF" colspan="2" height="15" align="right">
<font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="1">Manual:  
<a href="oracle_hp_dbag_titlepage_docCD_sf_dbag2_73700.vxhd" title="Storage Foundation 4.1 for Oracle Administrator's Guide title page ">Storage Foundation 4.1 for Oracle Administrator's Guide</a>&nbsp;&nbsp;</font></td>
</tr>


 <tr>
  <td colspan="2" align="left"><font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" color="#000000" align="right" size="-2">
<em>VERITAS Software Corporation
</em></font><br>

<font face="Verdana, Arial, Helvetica, Geneva, sansserif, sans serif" size="-2">www.veritas.com</font><br>
  </td>
 </tr>
</table>
</div>

</BODY>
  <!--
    *************************************************************************************
    * Copyright (c) 2005 VERITAS Software Corporation. All rights reserved. *

    VERITAS, VERITAS SOFTWARE, the VERITAS Logo, and VERITAS Software Documentation 
    are trademarks or registered trademarks of VERITAS Software Corporation 
    or its affiliates in the U.S. and other countries. Other names may be 
    trademarks of their respective owners. 

    Apache Jakarta Lucene:
    Apache Software License
    Version 1.1

    Copyright (c) 2000 The Apache Software Foundation. All rights reserved.

    Redistribution and use in source and binary forms, with or without modification, 
    are permitted provided that the following conditions are met:

    1. Redistributions of source code must retain the above copyright notice, this 
    list of conditions and the following disclaimer.

    2. Redistributions in binary form must reproduce the above copyright notice, this 
    list of conditions and the following disclaimer in the documentation and/or other 
    materials provided with the distribution.

    3. The end-user documentation included with the redistribution, if any, must include 
    the following acknowledgment: "This product includes software developed by the Apache 
    Software Foundation (http://www.apache.org/)." Alternately, this acknowledgment may 
    appear in the software itself, if and wherever such third-party acknowledgments 
    normally appear.

    4. The names "Apache" and "Apache Software Foundation" must not be used to endorse or 
    promote products derived from this software without prior written permission. For 
    written permission, please contact apache@apache.org.

    5. Products derived from this software may not be called "Apache", nor may "Apache" 
    appear in their name, without prior written permission of the Apache Software Foundation.

    THIS SOFTWARE IS PROVIDED "AS IS" AND ANY EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, 
    BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A 
    PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE APACHE SOFTWARE FOUNDATION 
    OR ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, 
    OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS 
    OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND 
    ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING 
    NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF 
    ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

    This software consists of voluntary contributions made by many individuals on behalf 
    of the Apache Software Foundation. For more information on the Apache Software Foundation, 
    please see <http://www.apache.org/>. Portions of this software are based upon public domain 
    software originally written at the National Center for Supercomputing Applications, University 
    of Illinois, Urbana-Champaign.
    
    This product includes WebWindow Software developed by Horst Heistermann.
    *************************************************************************************
  -->
</HTML>
