📄 ch2_tun.htm
字号:
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//en"
"http://www.w3c.org/TR/1999/PR-xhtml1-19991210/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" sml:lang="en" lang="en">
<head>
<!-- Run date = May 1, 2002 19:19:0 -->
<meta name="XHTML Generator" content="Oracle SGML to XHTML converter - Version 3.1.2">
<title> Tuning for Oracle9i on UNIX</title>
<link rel="next" href="ch3_sql.htm" title="Next">
</head>
<body bgcolor="#ffffff">
<a id="top" name="top"></a>
<a href="#BEGIN">Skip Headers</a><br><br>
<table cellspacing="0" cellpadding="0" width="100%">
<tr>
<td align="left" valign="top"><font face="helvetica, arial, sans-serif">
<b>Oracle9<em>i</em> Administrator's Reference </b>
<br />
<b>Release 2 (9.2.0.1.0) for UNIX Systems: AIX-Based Systems, Compaq Tru64 UNIX, HP 9000 Series HP-UX, Linux Intel, and Sun Solaris</b>
<br />
Part No. A97297-01
</font>
</td>
<td valign="bottom" align="right" width="144">
<table cellspacing="0" cellpadding="0" width="100%">
</tr>
<td align="center" valign="top" width="25%"><a href="toc.htm"><img src="../Support/gifs/conticon.gif" alt="Go To Table Of Contents" border="0" height="30" width="30"><br /><font size="-2">Contents</font></a></td>
<td align="center" valign="top" width="25%"><a href="index.htm"><img src="../Support/gifs/indxicon.gif" alt="Go To Index" border="0" height="30" width="30"><br /><font size="-2">Index</font></a></td>
</tr>
</table>
</td>
</tr>
</table>
<hr />
<p>
<a href="ch1_admin.htm">
<img src="../Support/gifs/larrow.gif" alt="Previous" border="0" height="30" width="30"></a>
<a href="ch3_sql.htm">
<img src="../Support/gifs/rarrow.gif" alt="Next" border="0" height="30" width="30"></a>
</p>
<a name="BEGIN"></a>
<div class="IND">
<!-- End Header --><a id="i17100" name="i17100"></a>
<h1>
<font face="arial, helvetica, sans-serif" color="#330099">
2
<br />
Tuning for Oracle9<em>i</em> on UNIX
</font>
</h1>
<p>The more your Oracle9<em>i</em> applications increase in complexity, the more you must tune the system to optimize performance and prevent data bottlenecks. This chapter describes how to configure your Oracle9<em>i</em> installation to optimize its performance. It contains the following sections:
</p>
<ul>
<li type="disc">
<p><a href="#i20765">Importance of Tuning</a>
</p>
</li>
<li type="disc">
<p><a href="#i27398">Operating System Tools </a>
</p>
</li>
<li type="disc">
<p><a href="#i19293">Tuning Memory Management </a>
</p>
</li>
<li type="disc">
<p><a href="#i23903">Tuning Disk I/O </a>
</p>
</li>
<li type="disc">
<p><a href="#i10932">Monitoring Disk Performance</a>
</p>
</li>
<li type="disc">
<p><a href="#i25471">Tuning UNIX Kernel Parameters</a>
</p>
</li>
<li type="disc">
<p><a href="#i23890">Tuning the Operating System Buffer Cache</a>
</p>
</li>
<li type="disc">
<p><a href="#i21051">Using Raw Devices/Volumes </a>
</p>
</li>
<li type="disc">
<p><a href="#i18497">Using Trace and Alert Files</a>
</p>
<br /><table summary="This is a layout table to format a tip" title="This is a layout table to format a tip" dir="ltr" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"><tbody>
<tr>
<td align="left" colspan="1" rowspan="1">
<p>
<font face="arial, helvetica, sans-serif">
<strong>See Also:</strong>
</font>
</p>The following documents and appendices for more information on system tuning:
<ul>
<li type="disc">
<p><em>Oracle9i Database Performance Methods</em>
</p>
</li>
<li type="disc">
<p><em>Oracle9i Database Performance Guide and Reference</em>
</p>
</li>
<li type="disc">
<p><em>Oracle9i Real Application Clusters Concepts</em>
</p>
</li>
<li type="disc">
<p><em>Oracle9i Real Application Clusters Administration</em>
</p>
</li>
<li type="disc">
<p><a href="appa_aix.htm#g640701">Appendix A, " Tuning for Oracle9<em>i</em> on AIX"</a>
</p>
</li>
<li type="disc">
<p><a href="appb_hp.htm#g639540">Appendix B, " Tuning for Oracle9<em>i</em> on HP"</a>
</p>
</li>
<li type="disc">
<p><a href="appc_ln.htm#CHDJHICE">Appendix C, " Tuning for Oracle9<em>i</em> on Linux"</a>
</p>
</li>
<li type="disc">
<p><a href="appd_tru.htm#g644272">Appendix D, " Tuning for Oracle9<em>i</em> on Tru64 "</a>
</p>
</li>
</ul>
</td>
</tr></tbody>
</table><br />
</li>
</ul><a id="i20765" name="i20765"></a>
<div class="sect1">
<!--
infolevel=all
infotype=general
--><a id="sthref278" name="sthref278"></a>
<h2>
<font face="arial, helvetica, sans-serif" color="#330099">Importance of Tuning
</font>
</h2>
<p>Oracle9<em>i</em> is a highly-optimizable software product. Frequent tuning optimizes system performance and prevents data bottlenecks. Although this chapter is written from the perspective of single-node computer systems, most of the performance tuning tips provided here are also valid when using Oracle9<em>i</em> Real Application Clusters and features available with Oracle9<em>i</em>.
</p>
<p>Before tuning the system, observe its normal behavior using the tools described in <a href="#i27398">"Operating System Tools "</a>.
</p>
<div class="sect2">
<!--
infolevel=all
infotype=general
--><a id="sthref279" name="sthref279"></a>
<h3>
<font face="arial, helvetica, sans-serif" color="#330099">
Types of Performance Bottlenecks
</font>
</h3>
<p>Performance bottlenecks are often caused by the following:
</p>
<ul>
<li type="disc">
<p>Memory contention
</p>
<p>Memory contention occurs when processes require more memory than is available. When this occurs, the system pages and swaps processes between memory and disk.
</p>
</li>
<li type="disc">
<p>Disk I/O contention
</p>
<p>Disk I/O contention is caused by poor memory management, poor distribution of tablespaces and files across disks, or a combination of both.
</p>
</li>
<li type="disc">
<p>CPU contention
</p>
<p>Although the UNIX kernel usually allocates CPU resources effectively, many processes compete for CPU cycles and this can cause contention. If you installed Oracle9<em>i</em> in a multiprocessor environment, there might be a different level of contention on each CPU.
</p>
</li>
<li type="disc">
<p>Oracle resources contention
</p>
<p>Contention is also common for Oracle resources such as locks and latches.
</p>
</li>
</ul>
</div class="sect2">
</div class="sect1"><a id="i27398" name="i27398"></a>
<div class="sect1">
<!--
infolevel=all
infotype=general
--><a id="sthref280" name="sthref280"></a>
<h2>
<font face="arial, helvetica, sans-serif" color="#330099">Operating System Tools <a id="sthref281" name="sthref281"></a>
</font>
</h2>
<p>Several operating system tools are available to help you assess database performance and determine database requirements. In addition to providing statistics for Oracle processes, these tools provide statistics for CPU usage, interrupts, swapping, paging, context switching, and I/O for the entire system.
</p>
<div class="sect2">
<!--
infolevel=all
infotype=general
--><a id="sthref282" name="sthref282"></a>
<h3>
<font face="arial, helvetica, sans-serif" color="#330099">
Common Tools
</font>
</h3>
<p>The following sections provide information on common tools:
</p>
<ul>
<li type="disc">
<p><a href="#CHEECBJC">vmstat </a>
</p>
</li>
<li type="disc">
<p><a href="#CHEGIDAE">sar </a>
</p>
</li>
<li type="disc">
<p><a href="#CHEHHDEG">iostat </a>
</p>
</li>
<li type="disc">
<p><a href="#CHEFADHA">swap, swapinfo, swapon, and lsps </a>
</p>
<br /><table summary="This is a layout table to format a tip" title="This is a layout table to format a tip" dir="ltr" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"><tbody>
<tr>
<td align="left" colspan="1" rowspan="1">
<p>
<font face="arial, helvetica, sans-serif">
<strong>See Also:</strong>
</font>
</p>The operating system documentation and UNIX man pages for more information on these tools.
</td>
</tr></tbody>
</table><br />
</li>
</ul><a id="CHEECBJC" name="CHEECBJC"></a>
<br /><strong><font face="arial, helvetica, sans-serif">vmstat <a id="sthref283" name="sthref283"></a><a id="sthref284" name="sthref284"></a>
</font></strong><br />
<p>Use the <code>vmstat</code> command to view process, virtual memory, disk, trap, and CPU activity, depending on the switches you supply with the command. Enter one of the following commands to display a summary of CPU activity eight times, at five-second intervals:
</p>
<ul>
<li type="disc">
<p>HP and Solaris:
</p>
<pre>$ vmstat -S 5 8
</pre>
</li>
<li type="disc">
<p>AIX, Linux, and Tru64:
</p>
<pre>$ vmstat 5 8<br />
</pre>
</li>
</ul>
<p>The following example shows sample output from the command on Solaris:
</p>
<pre>procs memory page disk faults cpu
r b w swap free si so pi po fr de sr f0 s0 s1 s3 in sy cs us sy id
0 0 0 1892 5864 0 0 0 0 0 0 0 0 0 0 0 90 74 24 0 0 99
0 0 0 85356 8372 0 0 0 0 0 0 0 0 0 0 0 46 25 21 0 0 100
0 0 0 85356 8372 0 0 0 0 0 0 0 0 0 0 0 47 20 18 0 0 100
0 0 0 85356 8372 0 0 0 0 0 0 0 0 0 0 2 53 22 20 0 0 100
0 0 0 85356 8372 0 0 0 0 0 0 0 0 0 0 0 87 23 21 0 0 100
0 0 0 85356 8372 0 0 0 0 0 0 0 0 0 0 0 48 41 23 0 0 100
0 0 0 85356 8372 0 0 0 0 0 0 0 0 0 0 0 44 20 18 0 0 100
0 0 0 85356 8372 0 0 0 0 0 0 0 0 0 0 0 51 71 24 0 0 100
</pre>
<p>The <code>w</code><strong> </strong>column, under the <code>procs</code> column, shows the number of potential processes that have been swapped out and written to disk. If the value is not zero, swapping is occurring and your system is short of memory.
</p>
<p>The <code>si</code> and <code>so</code> columns under the <code>page</code> column indicate the number of swap-ins and swap-outs per second, respectively. Swap-ins and swap-outs should always be zero.
</p>
<p>The <code>sr</code> column under the <code>page</code> column indicates the scan rate. High scan rates are caused by a shortage of available memory.
</p>
<p>The <code>pi</code> and <code>po</code> columns under the <code>page</code> column indicate the number of page-ins and page-outs per second, respectively. It is normal for the number of page-ins and page-outs to increase. Some paging always occurs even on systems with plenty of memory available.
</p>
<div align="center">
<br /><table summary="This is a layout table to format a note" title="This is a layout table to format a note" dir="ltr" border="1" width="80%" frame="hsides" rules="groups" cellpadding="3" cellspacing="0"><tbody>
<tr>
<td align="left" colspan="1" rowspan="1">
<p>
<font face="arial, helvetica, sans-serif">
<strong>Note:</strong>
</font>
</p>The output from the <code>vmstat</code> command differs between platforms. See the man page for information on interpreting the output on your platform.
</td>
</tr></tbody>
</table><br />
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -