Subject: Tuning Oracle with INIT.ORA Parameters
Last Revision Date: 11 September 1994
Author:John R. Pack
Tuning Oracle
With INIT.ORA Parameters
The setting of the INIT.ORA Parameters customizes the performance
of each ORACLE instance to its particular needs. While default
settings often yield adequate performance, the peak performance
ORACLE is capable of delivering can often be attained only by
careful tuning of each parameter.
While most of the parameters can be adjusted only after the
database is in use, the following parameters can be adjusted
immediately upon installation:
o DC_TABLES
o DC_COLUMNS
o DB_BLOCK_BUFFERS
o PROCESSES
o SORT_AREA_SIZE
o ROLLBACK_SEGMENTS
o FREE_PROC_LIST
o LOG_ALLOCATION
o SEQUENCE_CACHE_ENTRIES
Each of these parameters is discussed in the following sections.
Adjusting these parameters will make later tuning easier and
initial performance better. The other parameters documented
should only be adjusted after initial performance can be
monitored.
The parameters in the sebsequent sections are listed in order of
maximum performance gain. The most important of these are the
Data Dictionary Cache Parameters.
Tuning the Data Dictionary Cache
All of the Data Dictionary INIT.ORA parameters begin with "DC_".
Each cache should be large enough to hold the most often accessed
parts of the Data Dictionary. The Data Dictionary Caches should
be tuned any time the Recursive Calls category of the SQL*DBA
MONITOR STATISTICS USER display is greater than zero.
The performance of each element in the Data Dictionary Cache can
be monitored using the dynamic performance table SYS.V$ROWCACHE.
The following is a sample query of V$ROWCACHE:
INIT.ORA PARAMETER GETS GETMISSES HIT % COUNT USAGE
-------------------- ------- --------- -------- ------- -------
DC_FREE_EXTENTS 2060 981 52.4 100 100
DC_USED_EXTENTS 1494 1112 25.6 50 50
DC_SEGMENTS 4010 969 75.8 50 50
DC_TABLESPACES 43377 3 100.0 25 3
DC_TABLESPACES 932 3 99.7 25 2
DC_TABLESPACE_QUOTAS 3023 56 98.1 25 25
DC_FILES 0 0 100.0 25 0
DC_USERS 10724 44 99.6 50 44
DC_ROLLBACK_SEGMENTS 4385 3 99.9 25 4
DC_OBJECTS 101734 4525 95.6 100 100
DC_CONSTRAINTS 2264 1181 47.8 150 150
DC_OBJECT_IDS 30554 518 98.3 50 7
DC_TABLES 119943 1904 98.4 100 100
DC_SYNONYMS 2567 263 89.8 50 50
DC_SEQUENCES 1908 106 94.4 20 20
DC_USERNAMES 8011 75 99.1 50 50
DC_COLUMNS 527359 34421 93.5 300 300
DC_TABLE_GRANTS 2228 517 76.8 50 31
DC_COLUMN_GRANTS 0 0 100.0 50 0
DC_INDEXES 83789 1053 98.7 50 50
DC_CONSTRAINT_DEFS 128 15 88.3 200 11
DC_SEQUENCE_GRANTS 26 6 76.9 20 0
DC_TABLESPACES appears twice because that parameter affects the
size of two caches. The query that produces the preceeding table
is found in the appendix.
Optimal Values: When the hit ratio, the percentage of requests
satisfied by the cache (the HIT % column above), is less than
ninety percent, increasing the cache size (by increasing the
corresponding INIT.ORA parameter) will improve the performance of
the RDBMS. If the hit ratio is 100% and the usage is substant-
ially lower than the count, the value of the INIT.ORA parameter
can be reduced without affecting performance negatively.
Two of these parameters can be adjusted prior to starting the
database, DC_TABLES and DC_COLUMNS. The defaults are often too
low.
DC_TABLES
This parameter determines the number of table definitions that
will be kept in the data dictionary cache.
Optimal Value: The maximum number of distinct tables referenced
by all concurrent users. Range: 30+ Default: 100.
DC_COLUMNS
This parameter determines the number of column definitions that
will be kept in the data dictionary cache.
Optimal Value: The maximum number of distinct columns referenced
by all concurrent users. Default: 300.
Tuning the Data Buffer Cache
DB_BLOCK_BUFFERS
Each buffer in the cache contains one Oracle block. The most
recently used data from indexes, tables, views, etc. is stored in
the buffer cache. The performance of the RDBMS is enhanced
dramatically when requested data is found in the buffer cache.
Optimum Value: Increase the number of buffers when the
Cumulative Hit Ratio from the SQL*DBA MONITOR IO display
consistently falls below 70%. Range: 4 - 65535 Default: 32.
DB_BLOCK_BUFFERS can be set prior to warm-starting the database
for the first time. A good initial value for this parameter is
70% of the available SGA Pad (keep in mind that this parameter is
expressed in ORACLE blocks).
To determine the number of buffers to add to DB_BLOCK_BUFFERS,
set the INIT.ORA parameter DB_BLOCK_LRU_EXTENDED_STATISTICS to
the maximum number of buffers which could be added without
causing paging. After using the instance with this parameter
set, you should examine the SYS$X.KCBRBH table. The table
contains the accumulated information about how many additional
cache hits each successive buffer would yield. This table can be
examined as shown on page 3-21 of the RDBMS Performance Tuning
Guide V6.0 or by creating a copy of the X.KCBRBH table and then
running the following update:
Update Copy_KCBRBH A
Set Count = (Select A.Count + Sum(Count)
From Copy_KCBRBH
Where Indx <= A.Indx)
The copy will then contain information like
INDX COUNT
---- -----
0 30
1 55
2 65
3 70
4 71
5 71
6 90
7 120
The desired number of buffers to add is the number that optimizes
memory usage per hit gained. In this case, it would not be wise
to add four through six (add 1 to INDX) buffers. Three gives a
good yield as does six. The original table contains one row of
information about each additional buffer.
If paging and swaping are excessive, this parameter can be
reduced. Setting the DB_BLOCK_LRU_STATISTICS parameter to TRUE
and then examining the SYS.X$KCBCBH table (as above) will show
the number of cache hits lost with each reduction.
Additional Parameters to Examine When Tuning
PROCESSES
This parameter limits the number of users which can concurrently
access the instance. This parameter does not effect performance
but is a useful starting point in defining expected requirements
for ORACLE.
Optimum Value: The maximum number of users which will ever use
the instance simultaneously plus six. Range: 7+ Default: 25.
SORT_AREA_SIZE
This parameter determines how much process memory Oracle
allocates for sort operations.
Optimal Value: The minimum value required for anticipated SQL
statements using GROUP BY or ORDER BY and for large index
creations. The default is adequate for most operations.
Increase this parameter when the SQL*DBA MONITOR FILE display
shows excessive I/O to the user's temporary tablespace during
large operations.
Tuning Buffer Acquisition
ROLLBACK_SEGMENTS
This parameter is a list of all the rollback segments available
to user processes. The system rollback segment should never
appear in this parameter's list. All of the user rollback
segments should be the same size since they are allocated
randomly. Rollback segments should be large enough to contain
all of the rollback information for any anticipated transaction.
Optimal Value: An on-line transaction processing system (i.e.,
with many small transactions) should have as many as one small
rollback segment per user, while systems running large queries
should have fewer larger rollback segments. There should be one
rollback segment for every two to four concurrent transactions.
Range: 2 - 50.
Create more rollback segments when the SQL*DBA MONITOR STATISTICS
CACHE display shows that the ratio of
Busy_Buffer_Waits/(DB_Block_Gets + Consistent_Gets)
is greater than 10% and the following query shows more waits for
Undo Segment Headers and Undo Blocks (which involve rollback
segments) than for Data Blocks:
Select Class, Sum(Count) TOTAL_WAITS
From Sys.V$WaitStat
Where Operation = 'buffer busy waits'
and Class in ('undo segment header',
'undo block',
'data block')
Group By Class;
Note: If the ratio is greater than 10% and there are more waits
for Data Blocks, then increasing FREE_LIST_PROC will improve
performance.
FREE_LIST_PROC
At the time each table is created, this parameter determines the
number of lists of available blocks.
Optimum Value: The minimum value that permits all users to
concurrently obtain an available block for inserting. If many
users access the same tables concurrently, increase this para-
meter to the number of concurrent users. See ROLLBACK_SEGMENTS.
Range: 1 - 32 (for single-instance mode). Default: 1.
Tuning Redo Logs
LOG_ALLOCATION
This parameter determines the number of blocks in the redo log
file allocated to the instance.
Optimum Value: In single-instance mode, set this parameter to a
value larger than the size of the largest log file (so that the
instance will be able to allocate the entire log file in one
step). Default: 200 blocks.
LOG_SIMULTANEOUS_COPIES
This parameter determines the maximum number of redo latches
available for simultaneous log writing.
Optimal Value: Twice the number of available CPUs. Increase the
default value when the SQL*DBA MONITOR LATCH display shows that
either of the two following ratios exceeds 10%:
Redo Allocation Latch Timeouts/Total
Redo Copy Latches Timeouts/Total
If this parameter is adjusted or is greater than zero, adjust the
next two parameters as well. Range: 0+ Default: CPU_COUNT.
LOG_SMALL_ENTRY_MAX_SIZE
This parameter determines the maximum size of a copy to the log
buffers without obtaining a redo copy latch.
Optimal Value: To reduce contention for the single Redo
Allocation Latch and increase contention for the Redo Copy
Latches, decrease this parameter. Default: 800 bytes (on VMS).
LOG_ENTRY_PREBUILD_THRESHOLD
This parameter determines the size of the redo entry created
before a Redo Copy Latch is requested.
Optimal Value: The largest value supported by available memory.
Redo Copy Latches are held for shorter periods when this
parameter is increased. Range: 0, 2048, or 4096 bytes/user
Default: 0.
Tuning the SQL Statement Parsing
OPEN_CURSORS
This parameter determines the maximum number of cursors a single
process can have at once. The SGA maintains this number of
pointers for each user's cursors. Cursors themselves are maint-
ained in the PGA.
A cursor contains the parse of a single SQL statement. Maintain-
ing a cursor eliminates the need to re-parse a SQL statement when
it is re-executed. Cursors for SQL statements which will not be
executed again in the near future should be closed.
Optimum Value: The maximum number of cursors which will ever be
required to maintain parsing. Large or poorly written forms
often require high values. Range: 5 - 255 Default: 50
Suggested: 255.
CONTEXT_AREA
This parameter determines how much space is initially allocated
to a cursor. Increasing this parameter makes it less likely that
context space will have to be increased dynamically.
Optimum Value: The minimum size which will handle nearly all of
the SQL statements parsed on the system. Increase this parameter
when the Data Dictionary Caches have been tuned and the SQL*DBA
MONITOR STATISTICS USER display shows a positive number of
recursive calls. Range: 1024 - 131072 Default: 4096.
CONTEXT_INCR
This parameter determines how much space will be allocated each
time the initial CONTEXT_AREA must be dynamically increased to
accomodate the parsing of large SQL statements.
Optimum Value: The minimum size that will permit larger SQL
queries to be parsed without further increments but does not
allocate more memory than required. Range: 1024 - 32768
Default: 4096.
Tuning Sequence Generators
SEQUENCE_CACHE_ENTRIES
This parameter controls the number of sequence definitions cached
in memory. Each definition requires approximately 110 bytes in
the SGA.
The number of sequence elements stored in memory is specified
when the sequence is created. Sequences created with the NOCACHE
option do not reside in this cache. Select this option when
values from a sequence will be used very infrequently.
Optimum Value: The maximum number of sequences used on an
instance at one time plus two. The value of the CACHE clause of
the CREATE SEQUENCE command should be at least twice the number
of processes accessing the sequence simultaneously.
Range: 10 - 32000 Default: 10.
Effects on the SGA
Many of the INIT.ORA parameters which affect performance affect
the amount of memory used by the System Global Area (SGA). Since
an over-sized SGA will cause user processes to page and swap
excessively (substantially decreasing system performance), the
DBA should not allocate too much memory to the SGA Pad when the
code is linked. On the other hand, allocating too little memory
may require re-linking ORACLE to increase its size later and
will result in slower performance in the meantime.
The current size of the SGA can be seen by executing the
following SQL*DBA command:
SHOW SGA
The current value of the INIT.ORA parameters can be learned by
executing the following SQL*DBA command:
SHOW PARAMETERS
REFERENCES
(1) ORACLE RDBMS Performance Tuning Guide V6.0
(2) ORACLE RDBMS Database Administrators Guide V6.0
(3) ORACLE for DEC VAX/VMS Installation and User's Guide V6.0
APPENDIX
To examine the Data Dictionary Cache, execute the following SQL
script:
Set Numwidth 7
Set Pagesize 40
Column Parameter Heading "INIT.ORA PARAMETER"
Column Parameter Format a22
Column "HIT %" Format 99990.0
Select Upper(Parameter) Parameter,
Gets,
Getmisses,
(Decode(Gets,0,1,Gets)-Getmisses)
*100/Decode(Gets,0,1,Gets) "HIT %",
Count,
Usage
From Sys.V$RowCache
/
Note: The system account does not have select priviledges on the
Sys.V$RowCache table. This query must be run from the SYS
account or privileges must be granted.