apb.htm

Teach yourself Oracle8 in 21 day

The value <TT>ALWAYS</TT> specifies that only row locks are acquired when a table
is updated. If you set this value to <TT>INTENT</TT>, row locks are acquired on a
<TT>SELECT FOR UPDATE</TT>, but when the update occurs, a table lock is acquired.
SEQUENCE_CACHE_ENTRIES [10..32000] &lt;10&gt; This parameter specifies the number
of sequences that can be cached in the SGA. By caching the sequences, an immediate
response is achieved for sequences. Set a large value for <TT>SEQUENCE_CACHE_ENTRIES</TT>
if you have a high concurrency of processes requesting sequences. <BR>
</P>

<P><B>SEQUENCE_CACHE_HASH_BUCKETS [1..32000 (prime number)] &lt;7&gt; </B><BR>
This parameter specifies the number of buckets to speed up access to sequences in
the cache. The cache is arranged as a hash table. </P>

<P><B>SERIAL_REUSE [DISABLE/SELECT/DML/PLSQL/ALL/NULL] &lt;NULL&gt; <BR>
</B>This parameter specifies which type of SQL cursors should make use of serial-reusable
memory. </P>

<P><B>SERIALIZABLE [TRUE/FALSE] &lt;FALSE&gt; <BR>
</B>If this value is set to <TT>TRUE</TT>, queries obtain table-level read locks,
which prohibits other transactions from modifying that table until the transaction
has committed or rolled back the transaction. This mode provides repeatable reads
and ensures that within the transactions multiple queries to the same data achieve
the same result.</P>

<P>With <TT>SERIALIZABLE</TT> set to <TT>TRUE</TT>, degree-three consistency is provided.
You pay a performance penalty when you run in this mode. Running in this mode is
usually not necessary. </P>

<P><B>SESSION_CACHED_CURSORS [0..OS dependent] &lt;0&gt; <BR>
</B>This parameter specifies the number of session cursors to cache. If parse calls
of the same SQL statement are repeated, this can cause the session cursor for that
statement to be moved into the session cursor cache. Subsequent calls need not reopen
the cursor. </P>

<P><B>SESSION_MAX_OPEN_FILES [1..MAX_OPEN_FILES] &lt;10&gt; <BR>
</B>This parameter specifies the maximum number of <TT>BFILE</TT>s that can be opened
by any given session. The <TT>BFILE</TT> stores unstructured binary data in OS files
outside the database. </P>

<P><B>SHARED_POOL_RESERVED_MIN_ALLOC [5000..SHARED_POOL_RESERVE_SIZE] &lt;5000&gt;
<BR>
</B>Memory allocations larger than this value cannot allocate space from the reserved
list. </P>

<P><B>SHARED_POOL_RESERVE_SIZE [SHARED_POOL_RESERVE_MIN_ALLOC.. (SHARED_POOL_SIZE/2)]
&lt;5% of SHARED_POOL_SIZE&gt; </B><BR>
This parameter specifies the shared pool space that is reserved for large contiguous
requests for shared-pool memory. </P>

<P><B>SHARED_POOL_SIZE [300KB..OS dependent] &lt;3.5MB&gt; <BR>
</B>This parameter specifies the size of the shared pool in bytes. The shared pool
contains the data dictionary cache (row cache) and the library cache as well as session
information. Increasing the size of the shared pool should help performance, but
at the cost of memory. </P>

<P><B>SMALL_TABLE_THRESHOLD [0..OS dependent] &lt;4&gt; <BR>
</B>This parameter specifies the number of buffers available in the SGA for table
scans. A small table might be read entirely into cache if it fits in <TT>SMALL_TABLE_THRESHOLD</TT>
number of buffers. When scanning a table larger than this, these buffers are reused
immediately. This provides a mechanism to prohibit a single-table scan from taking
over the buffer cache. </P>

<P><B>SORT_AREA_RETAINED_SIZE [0..SORT_AREA_SIZE] &lt;SORT_AREA_SIZE&gt; <BR>
</B><TT>SORT_AREA_RETAINED_SIZE</TT> defines the maximum amount of session memory
in bytes that can be used for an in-memory sort. The memory is released when the
last row is fetched from the sort area.</P>
<P>If the sort does not fit in <TT>SORT_AREA_RETAINED_SIZE</TT> bytes, a temporary
segment is allocated and the sort is performed in this temporary table. This is called
an external (disk) sort. This value is important if sort performance is critical.
SORT_AREA_SIZE [number of bytes] &lt;OS dependent&gt; This value specifies the maximum
amount of PGA memory to use for an external sort. This memory is released when the
sorted rows are written to disk. Increasing this value increases the performance
of large sorts.</P>
<P>Remember that each user process has its own PGA. You can calculate the potential
memory usage if all the users are doing a large sort with the following formula:</P>
<PRE><FONT COLOR="#0066FF">Potential memory usage = SORT_AREA_SIZE * (number of users doing a large sort)
</FONT></PRE>
<P>If very large indexes are being created, you might want to increase the value
of this parameter. SORT_SPACEMAP_SIZE [bytes] &lt;OS dependent&gt; This parameter
specifies the size in bytes of the sort spacemap in the context area. If you have
very large indexes, increase the value of this parameter. Optimal performance is
achieved when this parameter has the following value:</P>
<PRE><FONT COLOR="#0066FF">SORT_SPACEMAP_SIZE = (total-sort-bytes / sort-area-size) + 64
</FONT></PRE>
<P>In this formula, <TT>total-sort-bytes</TT> has the following value:</P>
<PRE><FONT COLOR="#0066FF">total-sort-bytes = record-count * ( sum-of-average-column-sizes + ( 2 * number-of-columns ) )
</FONT></PRE>
<P><TT>number-of-columns</TT> includes the <TT>SELECT</TT> list for <TT>ORDER BY</TT>,
<TT>GROUP BY</TT>, and the key list for the <TT>CREATE INDEX</TT>. You should also
add 10 or 20 extra bytes for overhead. </P>

<P><B>SORT_WRITE_BUFFER_SIZE [32KB/64KB] &lt;32768&gt; <BR>
</B>This parameter specifies the size of the sort I/O buffer when <TT>SORT_DIRECT_WRITES</TT>
is set to <TT>TRUE</TT>. </P>

<P><B>SORT_WRITE_BUFFERS [2..8] &lt;1&gt; <BR>
</B>This parameter specifies the number of sort buffers when <TT>SORT_DIRECT_WRITES</TT>
is set to <TT>TRUE</TT>. </P>

<P><B>SPIN_COUNT [1..1,000,000] &lt;1&gt; <BR>
</B>This parameter specifies the number of times to spin on a latch before sleeping.
</P>

<P><B>STAR_TRANSFORMATION_ENABLED [TRUE/FALSE] &lt;FALSE&gt; <BR>
</B>This parameter specifies whether a cost-based query transformation will be applied
to star queries. </P>

<P><B>USE_ISM [TRUE/FALSE] &lt;TRUE&gt; <BR>
</B>This parameter specifies that the shared page table is enabled.
<H2><FONT COLOR="#000077">Parallel Query Option</FONT></H2>
<P>The following parameters affect the operation of the Parallel Query option, which
has been available in Oracle since version 7.1. The Parallel Query option can dramatically
affect the performance of certain operations. </P>

<P><B>PARALLEL_DEFAULT_MAX_SCANS [0..unlimited] &lt;OS dependent&gt; <BR>
</B>This value specifies the maximum number of query servers to be used by default
for a query. This valued is used only if there are no values specified in a <TT>PARALLEL</TT>
hint or in the <TT>PARALLEL</TT> definition clause. This limits the number of query
servers used by default when the value of <TT>PARALLEL_DEFAULT_SCANSIZE</TT> is used
by the query coordinator. </P>

<P><B>PARALLEL_DEFAULT_SCANSIZE [0..OS Dependent ] &lt;OS dependent&gt; <BR>
</B>This parameter is used to determine the number of query servers to be used for
a particular table. The size of the table divided by <TT>PARALLEL_DEFAULT_SCANSIZE</TT>
determines the number of query servers, up to <TT>PARALLEL_DEFAULT_MAX_SCANS</TT>.
</P>

<P><B>PARALLEL_MAX_SERVERS [0..100] &lt;OS dependent&gt; <BR>
</B>This parameter specifies the maximum number of query servers or parallel recovery
processes available for this instance. </P>

<P><B>PARALLEL_MIN_MESSAGE_POOL [0..(SHARED_POOLSIZE*.9)] &lt;equation&gt; <BR>
</B>This parameter specifies the minimum permanent amount of memory that will be
allocated from the shared pool for messages in parallel execution. </P>

<P><B>PARALLEL_MIN_PERCENT [0..100] &lt;0&gt; <BR>
</B>This parameter specifies the minimum percent of threads required for parallel
query. </P>

<P><B>PARALLEL_MIN_SERVERS [0..PARALLEL_MAX_SERVERS] &lt;0&gt; <BR>
</B>This parameter determines the minimum number of query servers for an instance.
It is also the number of query servers started at instance startup. </P>

<P><B>PARALLEL_SERVER_IDLE_TIME [0..unlimited] &lt;OS dependent&gt; <BR>
</B>This parameter specifies the number of minutes before Oracle terminates an idle
query server process. </P>

<P><B>RECOVERY_PARALLELISM [0..PARALLEL_MAX_SERVERS] &lt;OS dependent&gt; <BR>
</B>This parameter specifies the number of processes to be used for instance or media
recovery. A large value can greatly reduce instance recovery time. A value of <TT>0</TT>
or <TT>1</TT> indicates that parallel recovery will not be performed and that recovery
will be serial.
<H2><FONT COLOR="#000077">Analysis Tools</FONT></H2>
<P>These parameters turn on special features in Oracle for detailed analysis and
debugging. </P>

<P><B>DB_BLOCK_CHECKSUM [TRUE/FALSE] &lt;FALSE&gt; <BR>
</B>Setting this parameter to <TT>TRUE</TT> causes the DBWR and direct loader to
calculate a checksum for every block they write to disk. This checksum is written
into the header of each block. </P>

<P><B>DB_LOG_CHECKSUM [TRUE/FALSE] &lt;FALSE&gt; <BR>
</B>Setting this parameter to <TT>TRUE</TT> causes the LGWR to calculate a checksum
for every block it writes to disk. The checksum is written into the header of the
redo block. </P>

<P><B>DB_BLOCK_LRU_EXTENDED_STATISTICS [0..unlimited] &lt;0&gt; <BR>
</B>This parameter enables statistics in the <TT>X$KCBRBH</TT> table to be gathered.
These statistics estimate the increased number of database block buffer cache hits
for each additional buffer. Any value over zero specifies the number of buffers to
estimate the cache hits for. If you are interested in estimating the cache hits for
an additional 100 buffers, set this parameter to <TT>100</TT>.</P>

<P>This parameter affects performance and should be turned off during normal operation.
</P>

<P><B>DB_BLOCK_LRU_LATCHES [1.. number of CPUs] &lt;CPU_COUNT/2&gt; <BR>
</B>This parameter specifies the upper bound of the number of LRU latch sets. This
is the number of LRU latch sets that you want. Oracle decides whether to use this
number or a smaller one. </P>

<P><B>DB_BLOCK_LRU_STATISTICS [TRUE/FALSE] &lt;FALSE&gt; <BR>
</B>This parameter specifies whether statistics are gathered for database block buffer
cache hit estimates as specified in <TT>DB_BLOCK_LRU_EXTENDED_STATISTICS</TT>. Set
this parameter to <TT>TRUE</TT> when you want to gather these statistics. </P>

<P><B>DB_BLOCK_MAX_DIRTY_TARGET [100..all buffers or 0] &lt;all buffers&gt; <BR>
</B>This parameter specifies the number of buffers that can be dirty. If the number
of dirty buffers exceeds this, the DBWR writes out buffers to reduce the number of
dirty buffers. </P>

<P><B>EVENT &lt;NULL&gt; <BR>
</B>The <TT>EVENT</TT> parameter modifies the scope of <TT>ALTER SESSION SET EVENTS</TT>
commands so that they pertain to the entire instance rather than just the session.
This is an Oracle internal parameter and should be changed only at the direction
of Oracle support. </P>

<P><B>FIXED_DATE [date string] &lt;NULL&gt; <BR>
</B><TT>FIXED_DATE</TT> allows you to set as a constant the Oracle function <TT>SYSDATE</TT>
in the format YYYY-MM-DD-HH24:MI:SS. Use this parameter for debug only. This parameter
allows you to test your application's functionality with certain dates, such as the
turn of the century. </P>

<P><B>ORACLE_TRACE_COLLECTION_NAME [valid name] &lt;NULL&gt; <BR>
</B>This parameter specifies the Oracle Trace collection name. </P>

<P><B>ORACLE_TRACE_COLLECTION_PATH [valid path] &lt;NULL&gt; <BR>
</B>This parameter specifies the directory where Oracle Trace collection definition
and datafiles are located. </P>

<P><B>ORACLE_TRACE_COLLECTION_SIZE [0..4294967295] &lt;5242880&gt; <BR>
</B>The maximum size in bytes of the Oracle Trace collection file. </P>

<P><B>ORACLE_TRACE_ENABLE [TRUE/FALSE] &lt;FALSE&gt; <BR>
</B>Enables Oracle Trace collections for the server. </P>

<P><B>ORACLE_TRACE_FACILITY_NAME [valid name] &lt;OS Specific&gt; <BR>
</B>This parameter specifies the name of the Oracle Trace product definition file.
</P>

<P><B>ORACLE_TRACE_FACILITY_PATH [valid directory name] &lt;OS Specific&gt; <BR>
</B>This parameter specifies the directory where the Oracle Trace facility definition
files are located. </P>

<P><B>SQL_TRACE [TRUE/FALSE] &lt;FALSE&gt; <BR>
</B>This parameter specifies whether the SQL*Trace facility is enabled. The SQL*Trace
facility can provide valuable information but at the price of some overhead. Use
SQL*Trace only when you are tracking down a specific problem. </P>

<P><B>SORT_READ_FAC [integer] &lt;OS Dependent&gt; <TT><BR>
</TT></B><TT>SORT_READ_FAC</TT> defines a unitless ratio that describes the amount
of time to read a single database block divided by the block transfer rate. </P>

<P><B>TIMED_OS_STATISTICS [OFF/CALL/LOGOFF] &lt;OFF&gt; <BR>
</B>This parameter allows the system administrator to gather OS statistics when calls
are pushed or popped or when a user logs off. </P>

<P><B>TIMED_STATISTICS [TRUE/FALSE] &lt;FALSE&gt; <BR>
</B>When <TT>TIMED_STATISTICS</TT> is set to <TT>TRUE</TT>, the time-related statistics
in the dynamic performance tables are enabled. This information can be quite useful,
but there is considerable overhead involved. Only enable <TT>TIMED_STATISTICS</TT>