📄 apb.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<TITLE>Teach Yourself Oracle 8 In 21 Days -- Appendix B -- Oracle Tuning Parameters</TITLE>
</HEAD>
<BODY TEXT="#000000" BGCOLOR="#FFFFFF">
<CENTER>
<H1><IMG SRC="../buttonart/sams.gif" WIDTH="171" HEIGHT="66" ALIGN="BOTTOM" BORDER="0"><BR>
<FONT COLOR="#000077">Teach Yourself Oracle 8 In 21 Days</FONT></H1>
</CENTER>
<CENTER>
<P><A HREF="../apa/apa.htm"><IMG SRC="../buttonart/previous.gif" WIDTH="128" HEIGHT="28"
ALIGN="BOTTOM" ALT="Previous chapter" BORDER="0"></A><A HREF="../apc/apc.htm"><IMG
SRC="../buttonart/next.gif" WIDTH="128" HEIGHT="28" ALIGN="BOTTOM" ALT="Next chapter"
BORDER="0"></A><A HREF="../index.htm"><IMG SRC="../buttonart/contents.gif" WIDTH="128"
HEIGHT="28" ALIGN="BOTTOM" ALT="Contents" BORDER="0"></A>
<HR>
</CENTER>
<CENTER>
<H1><FONT COLOR="#000077">- Appendix B -<BR>
Oracle Tuning Parameters</FONT></H1>
</CENTER>
<P>This appendix lists the Oracle tuning parameters, grouping them into general areas
of use and then sorting them alphabetically within the group. The section headings
are the syntax for the parameters. The syntax contains information in the following
format:</P>
<PRE><FONT COLOR="#0066FF">PARAMETER [option1, option2, option3, etc..] <DEFAULT VALUE>
</FONT></PRE>
<P>A value that is italicized indicates that the value should be replaced with one
of your own. A value that is italicized and in CAPS indicates a choice of this keyword.
The <TT>/</TT> character indicates an <TT>OR</TT> condition. A value enclosed in
brackets (<TT><></TT>) indicates the default value for that parameter.</P>
<P>These parameters are divided into sections based on whether the parameter affects
performance, enables system analysis, is a general parameter, and so on. There might
be some overlap, so if a parameter is not in the section you expected, keep looking.
<H2><FONT COLOR="#000077">Performance</FONT></H2>
<P>These parameters change the performance characteristics of the system. <B></B></P>
<P><B>ALWAYS_ANTI_JOIN [NESTED_LOOPS/MERGE/ HASH] <NESTED_LOOPS></B> This parameter
sets the type of anti-join that the Oracle server uses. This specifies the algorithm
chosen for the anti-join. <B></B></P>
<P><B>B_TREE_BITMAP_PLANS [TRUE/FALSE] <FALSE></B> <BR>
When set to <TT>TRUE</TT>, the optimizer considers a bitmap access path even though
a table might have only a regular B*-tree index. <B></B></P>
<P><B>BITMAP_MERGE_AREA_SIZE [System Dependent] <1MB></B> <BR>
This parameter specifies the amount of memory used to merge bitmaps retrieved from
a range scan of the index. Larger values typically improve performance. </P>
<P><B>CLOSE_CACHED_OPEN_CURSORS [TRUE/FALSE] <FALSE></B> <BR>
This parameter specifies whether cursors opened and cached in memory are automatically
closed at each commit. If you frequently use cursors, this should be set to <TT>FALSE</TT>.
</P>
<P><B>CPU_COUNT [0-unlimited] <Automatic></B> <BR>
This parameter specifies the number of CPUs used by Oracle. This parameter is set
automatically and should not be changed. </P>
<P><B>CREATE_BITMAP_AREA_SIZE [OS Dependent] <8MB></B> <BR>
This parameter specifies the amount of memory to be used for bitmap creation. A larger
value might provide greater bitmap-creation performance. If the cardinality is small,
this number can be small. </P>
<P><B>CURSOR_SPACE_FOR_TIME [TRUE/FALSE] <FALSE></B> <TT><BR>
CURSOR_SPACE_FOR_TIME</TT> causes the system to use more space for cursors, thus
increasing performance. This parameter affects both the shared SQL areas and the
user's private SQL area. This parameter speeds performance but uses more memory.</P>
<P>If <TT>CURSOR_SPACE_FOR_TIME</TT> is <TT>TRUE</TT>, the shared SQL areas remain
pinned in the shared pool as long as an open cursor references them. This parameter
should be used only if you have a sufficiently large shared pool to simultaneously
hold all the processes' cursors.</P>
<P>The user's private SQL area is also retained during cursor execution, thus saving
time and I/Os at the expense of memory. <B>DB_BLOCK_BUFFERS [4..65535] <32 buffers></B>
This parameter controls the number of database block buffers in the SGA. <TT>DB_BLOCK_BUFFERS</TT>
is probably the most significant instance tuning parameter because the majority of
I/Os in the system are generated by database blocks. Increasing <TT>DB_BLOCK_BUFFERS</TT>
increases performance at the expense of memory. You can calculate the amount of memory
that will be consumed with the following formula:</P>
<PRE><FONT COLOR="#0066FF">Buffer size = DB_BLOCK_BUFFERS * DB_BLOCK_SIZE
</FONT></PRE>
<P>A larger number of database block buffers in the system creates a higher cache-hit
rate, thus reducing the amount of utilized I/O and CPU and improving performance.
<B></B></P>
<P><B>DB_BLOCK_CHECKPOINT_BATCH [0..derived] <8></B> <BR>
This parameter specifies the number of blocks that the DBWR writes in one batch when
performing a checkpoint. Setting this value too high causes the system to flood the
I/O devices during the checkpoint, severely degrades performance, and increases response
times--maybe to unacceptable levels.</P>
<P>You should set <TT>DB_BLOCK_CHECKPOINT_BATCH</TT> to a level that allows a checkpoint
to finish before the next checkpoint occurs. Setting <TT>DB_BLOCK_CHECKPOINT_BATCH</TT>
to <TT>0</TT> causes the default value of 8 to be used. <B></B></P>
<P><B>DB_BLOCK_SIZE [1024..8192 (OS dependent)] <OS dependent></B> <BR>
This parameter specifies in bytes the size of the Oracle database blocks. The typical
values are 2048 and 4096. If you set the block size relative to the size of the rows
in a database, you can reduce I/O. In some types of applications in which large amounts
of sequential accesses are performed, a larger database block size can be beneficial.
This value is useful only at database-creation time. </P>
<P><B>DB_FILE_MULTIBLOCK_READ_COUNT [number (OS dependent)] <OS dependent></B><BR>
<TT>DB_FILE_MULTIBLOCK_READ_COUNT</TT> specifies the maximum number of blocks read
in one I/O during a sequential scan. The default is a function of <TT>DB_BLOCK_BUFFERS</TT>
and <TT>PROCESSES</TT>. Reasonable values are 4, 16, or 32. The maximum allowed values
are OS dependent.</P>
<P>This parameter can be especially useful if you perform a large number of table
scans, such as in a DSS system. </P>
<P><B>DB_FILE_SIMULTANEOUS_WRITES [1..24] <4></B> <BR>
This parameter specifies the number of simultaneous writes for each database file
when written by the DBWR. For disk arrays that handle large numbers of requests in
the hardware simultaneously, it is advantageous to set <TT>DB_FILE_SIMULTANEOUS_WRITES</TT>
to its maximum. <B></B></P>
<P><B>DISCRETE_TRANSACTIONS_ENABLED [TRUE/FALSE] <FALSE></B> <BR>
This parameter implements a simpler, faster rollback mechanism that, under certain
conditions, can improve performance. You can obtain greater efficiency in this mode,
but the qualification criteria for what kind of transactions can take advantage of
discrete transactions are quite strict. <B></B></P>
<P><B>DISK_ASYNCH_IO [TRUE/FALSE] <TRUE></B> <BR>
This parameter specifies that I/O to datafiles, control files, and log files are
asynchronous. This should be left enabled and not altered. </P>
<P><B>DML_LOCKS [20..unlimited,0] <4 * TRANSACTIONS></B> <BR>
This parameter specifies the maximum number of DML locks. A DML lock is used for
each table-modification transaction. DML locks are used in the <TT>DROP TABLE</TT>,
<TT>CREATE INDEX</TT>, and <TT>LOCK TABLE IN EXCLUSIVE MODE</TT> statements. If the
value is set to <TT>0</TT>, enqueues (Oracle locking mechanisms) are disabled, which
improves performance slightly. <B></B></P>
<P><B>DBWR_IO_SLAVES [0..OS Dependent] <0></B> <BR>
This parameter specifies the number of I/O slaves used by the DBWR process. <B></B></P>
<P><B>HASH_AREA_SIZE [0..OS Dependent] <2*SORT_AREA_SIZE></B> <BR>
This parameter specifies the maximum amount of memory to be used for hash joins.
<B></B></P>
<P><B>HASH_MULTIBLOCK_IO_COUNT [OS Dependent] <1></B> <BR>
This parameter specifies how many sequential blocks a hash join reads and writes
in one I/O. <B></B></P>
<P><B>LARGE_POOL_MIN_ALLOC [16K-64KB] <16KB></B> <BR>
This parameter specifies the minimum allocation size from the large pool. <B>LARGE_POOL_SIZE
</B></P>
<P><B>[300K or LARGE_POOL_MIN_ALLOC, whichever is larger] <0></B><BR>
This parameter specifies the size of the large pool allocation heap. </P>
<P><B>LGWR_IO_SLAVES [0..OS Dependent] <0></B> <BR>
This parameter specifies the number of I/O slaves used by the LGWR process. <B></B></P>
<P><B>LOG_ARCHIVE_BUFFER_SIZE [1..OS Dependent] <OS dependent></B> <BR>
When running in <TT>ARCHIVELOG</TT> mode, this parameter specifies the size of each
archival buffer in redo log blocks. This parameter can be used in conjunction with
the <TT>LOG_ARCHIVE_BUFFERS</TT> parameter to make the archiving speed faster or
slower to affect overall system performance. <B></B></P>
<P><B>LOG_ARCHIVE_BUFFERS [1..OS Dependent] <OS dependent></B> <BR>
When running in <TT>ARCHIVELOG</TT> mode, this parameter specifies the number of
buffers to allocate to archiving. This parameter is used with the <TT>LOG_ARCHIVE_BUFFER_SIZE</TT>
parameter to control the speed of archiving. <B></B></P>
<P><B>LOG_BUFFER [OS Dependent] <OS dependent></B> <TT><BR>
LOG_BUFFER</TT> specifies the number of bytes allocated to the redo log buffer. Larger
values reduce I/Os to the redo log by writing fewer blocks of a larger size. This
might help performance, particularly in a heavily used system. </P>
<P><B>LOG_CHECKPOINT_INTERVAL [2..unlimited] <OS dependent></B> <BR>
This parameter specifies the number of redo log file blocks to be filled to cause
a checkpoint to occur. Remember that a checkpoint always happens when a log switch
occurs. This parameter can be used to cause checkpoints to occur more frequently.
Sometimes, frequent checkpoints have less effect on the system than one large checkpoint
when the log switch occurs. </P>
<P><B>LOG_CHECKPOINT_TIMEOUT [0..unlimited] <OS dependent></B> <BR>
This parameter specifies the maximum amount of time that can pass before another
checkpoint must occur. This parameter can also be used to increase the frequency
of the checkpoint process, thus changing the overall system effect. <B></B></P>
<P><B>LOG_SIMULTANEOUS_COPIES [0..unlimited] <CPU_COUNT></B> <BR>
<TT>LOG_SIMULTANEOUS_COPIES</TT> specifies the number of redo buffer copy latches
simultaneously available to write log entries. You can have up to two redo copy latches
per CPU. This helps the LGWR process keep up with the extra load generated by multiple
CPUs.</P>
<P>If this parameter is <TT>0</TT>, redo copy latches are turned off and all log
entries are copied on the redo allocation latch. </P>
<P><B>LOG_SMALL_ENTRY_MAX_SIZE [number (OS dependent)] <OS dependent></B> <BR>
This parameter specifies the size in bytes of the largest copy to the log buffers
that can occur under the redo allocation latch without obtaining the redo buffer
copy latch. If <TT>LOG_SIMULTANEOUS_COPIES</TT> is zero, this parameter is ignored.
<B></B></P>
<P><B>OPTIMIZER_MODE [RULE/COST/FIRST_ROWS/ALL_ROWS] COST</B> <BR>
When set to <TT>RULE</TT>, this parameter causes rule-based optimization to be used,
unless hints are supplied in the query. When set to <TT>COST</TT>, this parameter
causes a cost-based approach for the SQL statement, providing that there are any
statistics in the data dictionary. When set to <TT>FIRST_ROWS</TT>, the optimizer
chooses execution plans that minimize response time. When set to <TT>ALL_ROWS</TT>,
the optimizer chooses execution plans that minimize total execution time. <B></B></P>
<P><B>OPTIMIZER_PERCENT_PARALLEL [0..100] <0></B> <BR>
This parameter specifies the amount of parallelism the optimizer uses in its cost
functions. <B></B></P>
<P><B>OPTIMIZER_SEARCH_LIMIT <5></B> <BR>
This parameter specifies the search limit for the optimizer. <B></B></P>
<P><B>PRE_PAGE_SGA [TRUE/FALSE] <FALSE></B> <BR>
When set to <TT>TRUE</TT>, this parameter specifies that at instance startup all
pages of the SGA are touched, causing them to be allocated in memory. This increases
startup time but reduces page faults during runtime. This is useful if you have a
large number of processes starting at once. This parameter can increase the system
performance in that case by avoiding memory-allocation overhead. <B></B></P>
<P><B>ROLLBACK_SEGMENTS [Any rollback segment names] <NULL><BR>
</B> <TT>ROLLBACK_SEGMENTS</TT> specifies one or more rollback-segment names to be
allocated to this instance. If <TT>ROLLBACK_SEGMENTS</TT> is not specified, the public
rollback segments are used. If you want to move your rollback segments to a different
disk device, you must specify it here. The parameter is specified as follows:</P>
<PRE><FONT COLOR="#0066FF">ROLLBACK_SEGMENTS = (roll1, roll2, roll3)
</FONT></PRE>
<P>If you use the Oracle Parallel Server option, you must name different rollback
segments for each instance. <B></B></P>
<P><B>ROW_CACHE_CURSORS [10..3300] <10></B> <BR>
This parameter specifies the number of cached recursive cursors used by the row cache
manager for selecting rows from the data dictionary. The default is usually sufficient
unless you have particularly high access to the data dictionary. <B></B></P>
<P><B>ROW_LOCKING [ALWAYS/INTENT] <ALWAYS></B> <BR>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -