Before going through the below, you may try this note first to understand automated way to check recommended parameters and this is one of the best practise
SAP Note 830576 - Parameter recommendations for Oracle
10g
This note contains SAP's recommendations for the configuration of Oracle Database 10g.
This note contains SAP's recommendations for the optimal configuration
of the Oracle database with Release 10g in SAP environments.
For Oracle Release 9i or lower, refer to Note 124361 and the notes referenced there.
Note that the recommendations given in this note may be changed.
Therefore, we recommend that you check the latest version of this note
once a month and make the necessary changes.
Previously, some parameter settings for the Oracle database (for
example, for the cost-based optimizer) depended on whether your system
was a normal R/3 system or a BW-based system. As of Oracle 10g, there is
a uniform parameterization recommendation for all systems, which is
described in this note. A few exceptions to this are indicated
explicitly.
General recommendations
There are two types of parameters that should not be set in the profile:
1. Parameters that are never changed (except to analyze problems in diagnosis situations)
2. Derived parameters, which do not normally have to be changed since
the Oracle server automatically calculates reasonable values for them
'Underscore' parameters and events are usually adjusted to a particular
Oracle release. Therefore, after a database upgrade, you should delete
this kind of parameter from the profile (exceptions are listed below).
The DBUA does not remove this type of parameter.
You should delete obsolete initialization parameters from the profile.
When you carry out a database upgrade with the DBUA, obsolete parameters
are automatically removed from the SPFILE (if an SPFILE is used). When
the instance is started, the Oracle alert log also informs you if
obsolete parameters exist. The Oracle upgrade documentation provides a
complete list of those parameters that are obsolete as of 10g. To
determine which obsolete parameters are currently set, proceed as
follows:
SELECT NAME FROM V$OBSOLETE_PARAMETER WHERE ISSPECIFIED = 'TRUE';
You should not set any parameters that are not explicitly mentioned in this note. Exceptions:
. The parameter is recommended as the solution or workaround for a problem in another note.
. The parameter is required for implementing an individual configuration
(for example, multiple archiver destinations, check functions, special
memory settings).
Further comments on parameterization:
. For detailed information about the maintenance of parameters with SFILEs, see Note 601157.
. If several EVENT parameters are specified in init.ora, they must appear in consecutive rows.
. You should not set parameters that are indicated with "Do not set!"
and parameters that are not mentioned at all in the note (and for which
there is no individual customer requirements). In this case, you use the
Oracle default value, which then also appears in V$PARAMETER or in the
ST04 parameter overview. This is the intended behavior. If you want to
ensure that a parameter has not been explicitly set, you can enter the
following query (
in lower case):
SELECT ISDEFAULT FROM V$PARAMETER2
WHERE NAME = '
';
If this returns the TRUE statement, then the parameter has not been explicitly set.
. You can only optimize memory parameters and resource parameters such
as DB_CACHE_SIZE or DB_WRITER_PROCESSES individually. Therefore, this
note cannot give any general recommendations. However, you can determine
options for optimization on the basis of a database performance
analysis (see Notes 618868, 619188, 789011).
. The parameterization described below is directed towards the use of
the features of the dynamic SGA (Note 617416) and the automatic PGA
administration (Note 619876).
. refers to the value of the environment variable SAPDATA_HOME.
. Paths are given in UNIX syntax. On WINDOWS, you must replace the forward slashes ("/") with back slashes ("").
. The terms OLAP system and OLTP system have the following meaning:
OLAP system: These are systems with mainly BW functions (BW / BI, APO with mainly DP usage, SEM-BPS, BW-based SEM-BCS).
OLTP system: These are systems with mainly non-BW functions.
. Configure systems with a pure Java stack as you would an OLTP system.
. Configure double stack systems (that is, systems with both ABAP and
JAVA stacks) as you would an OLTP or OLAP system, depending on degree to
which you use BW functions (see above).
. In individual cases, if you have a system without OLAP, you can
refrain from setting OLAP specific parameters such as
STAR_TRANSFORMATION_ENABLED, _FIX_CONTROL or _INDEX_JOIN_ENABLED to
avoid problems (for example, ORA-04031 due to _FIX_CONTROL, Note 997889)
or to use functions (for example, index joins).
Solution
The standard parameter recommendations for Oracle 10.2 are listed below.
They are equally valid for all SAP products. At the end of the list, we
include specific recommendations for specific SAP or Oracle products.
You must consider these as well as the Oracle 10.2 standard
recommendations:
Attached to this note is an SQL statement that you can execute using the
RSORADJV report, for example, (or alternatively using tools such as
SQLPLUS), which automatically checks that the current parameter settings
are correct. You can execute the command without changes for OLTP
systems. For OLAP systems, you need to replace "" with "y" in both places.
. Standard parameter recommendation for Oracle 10.2
. Specific settings
. Oracle 10.1
Notes on the recommended parameters
STANDARD PARAMETER RECOMMENDATIONS FOR ORACLE 10.2
**********************************************
Parameter Recommendation
------------------------------- ------------------------------------
BACKGROUND_DUMP_DEST /saptrace/background
COMMIT_WRITE Do not set
COMPATIBLE 10.2.0
CONTROL_FILES At least three copies on
different disk areas
CONTROL_FILE_RECORD_KEEP_TIME 30 or higher
CORE_DUMP_DEST /saptrace/background
DB_BLOCK_SIZE 8192
DB_CACHE_SIZE Size depends on the available
memory (Notes 789011, 617416)
DB_FILES Larger than the number of data files
to be expected in the short term
DB_FILE_MULTIBLOCK_READ_COUNT Do not set
DB_NAME
DB_WRITER_PROCESSES Only set in case of increased
DBWR load (Notes 79341, 789011)
EVENT
"10027 trace name context forever, level 1" (Note 596420)
"10028 trace name context forever, level 1" (Note 596420)
"10162 trace name context forever, level 1" (Notes 977319,
1040300)
"10183 trace name context forever, level 1" (Note 128648)
"10191 trace name context forever, level 1" (Note 128221)
"10629 trace name context forever, level 32" (Note 869521,
other settings of events 10626 / 10629 also allowed)
"14532 trace name context forever, level 1" (Note 1031682,
>= 10.2.0.2, Fix from Note 1031682 must be implemented)
"38068 trace name context forever, level 100" (Note 176754)
FILESYSTEMIO_OPTIONS SETALL (Note the restrictions from
Note 999524)
LOG_ARCHIVE_DEST /oraarch/arch
LOG_ARCHIVE_FORMAT %t_%s_%r.dbf
LOG_BUFFER 1048576
LOG_CHECKPOINTS_TO_ALERT TRUE
MAX_DUMP_FILE_SIZE 20000
OPEN_CURSORS 800 (up to a maximum of 2000)
OPTIMIZER_FEATURES_ENABLE Do not set
OPTIMIZER_INDEX_CACHING OLTP: 50
OLAP: Do not set.
OPTIMIZER_INDEX_COST_ADJ OLTP: 20
OLAP: Do not set.
OPTIMIZER_MODE Do not set
PARALLEL_EXECUTION_MESSAGE_SIZE 16384
PARALLEL_MAX_SERVERS #DB-CPU-Cores * 10
PARALLEL_THREADS_PER_CPU 1
PGA_AGGREGATE_TARGET OLTP: 20 % of available memory
OLAP: 40 % of available memory
PROCESSES #ABAP work processes * 2 +
#J2EE server processes *
+
PARALLEL_MAX_SERVERS + 40
QUERY_REWRITE_ENABLED FALSE
RECYCLEBIN OFF
REMOTE_OS_AUTHENT TRUE
REPLICATION_DEPENDENCY_TRACKING FALSE (if no replication
is used)
SESSIONS 2 * PROCESSES
SHARED_POOL_SIZE 400 MB or greater, refer to Note 690241
STAR_TRANSFORMATION_ENABLED TRUE
UNDO_MANAGEMENT AUTO (Note 600141)
UNDO_RETENTION set if required (refer to Note 600141)
UNDO_TABLESPACE PSAPUNDO (Note 600141)
USER_DUMP_DEST /saptrace/usertrace
_B_TREE_BITMAP_PLANS FALSE
_FIX_CONTROL 4728348:OFF (10.2.0.2, if
merge fix 5984705 from Note 981875
is not implemented; see Notes
964858 and 997889)
_INDEX_JOIN_ENABLED FALSE (10.2.0.2, refer to Note
964858)
_IN_MEMORY_UNDO FALSE (up to and including 10.2.0.3, if fixes from
Notes 980805 and 1013476
are not implemented)
_OPTIM_PEEK_USER_BINDS FALSE (see Note 755342)
_OPTIMIZER_MJC_ENABLED FALSE (Note 176754 (30))
_SORT_ELIMINATION_COST_RATIO 10 (See Note 176754 (16))
ORACLE 10.1: ADDITIONAL/ALTERNATIVE PARAMETER SETTINGS *********************************************************
Parameter Recommendation
------------------------------- ------------------------------------
COMPATIBLE 10.1.0
EVENT 10040 (Level 1, Note 899070)
RECYCLEBIN Do not set
_OPTIMIZER_OR_EXPANSION DEPTH (10.1.0.5 or higher, Note 849229)
_PGA_MAX_SIZE Only for BW:
400MB as of PGA_AGGREGATE_TARGET>4G
600MB as of PGA_AGGREGATE_TARGET>8G
800MB as of PGA_AGGREGATE_TARGET>12G
_RECYCLEBIN FALSE
NOTES ON THE RECOMMENDED PARAMETERS
*****************************************
BACKGROUND_DUMP_DEST
. Path for alert log and background trace files
COMPATIBLE
. Defines the Oracle version whose features can be used to the greatest extent
. As a rule, it must not be reset to an earlier release (see Note 598470).
. A value with three parts (such as 10.2.0) rather than five parts (such
as 10.2.0.3.0) is recommended to avoid changing the parameter as part
of a patch set installation.
CONTROL_FILES
. Path and name of the control files that are used
CONTROL_FILE_RECORD_KEEP_TIME
. Defines how many days historic data is retained in the control files
. Historic data is required by RMAN, for example.
. May cause control files to increase in size (see Note 904490)
CORE_DUMP_DEST
. Path under which core files are stored
DB_BLOCK_SIZE.
. Size of an Oracle block
. Can be set to a value higher than 8K in well-founded individual cases
after it has been approved by SAP Support (see Note 105047)
DB_CACHE_SIZE
. Size of the Oracle data buffer (in bytes)
. Optimal size depends on the available memory (see Notes 789011 and 617416)
DB_FILES
. Maximum number of Oracle data files
DB_NAME
. Name of the database
DB_WRITER_PROCESSES
. Number of DBWR processes
EVENT
. Activation of internal control mechanisms and functions
. To set events in SPFILE, refer also to Note 596423.
. On Oracle 10.1, set the events for 10.1 in addition to, and not instead of, the events for 10.2.
FILESYSTEMIO_OPTIONS
. Activation of file system functions (see Note 999524 and Note 793113)
. If you previously used a large file system cache (>= 2 * Oracle
Buffer Pool), the performance may get worse after you activated the
direct I/O if you set FILESYSTEMIO_OPTIONS to SETALL. Therefore, it is
important that you enlarge the Oracle buffer pool to replace the file
system cache that is no longer available.
LOG_ARCHIVE_DEST
. Path/prefix for offline redo logs
LOG_ARCHIVE_FORMAT
. Name format of the offline redo logs
. To avoid the problems described in Note 132551, it must be explicitly set to WINDOWS at least.
LOG_BUFFER
. Minimum size of the Oracle redo buffer (in bytes)
. Oracle internally determines the buffer's actual size, so it is normal
for "SHOW PARAMETER LOG_BUFFER" or a SELECT on V$PARAMETER to return
values between 1MB and 16MB.
LOG_CHECKPOINTS_TO_ALERT
. Defines whether checkpoints are to be logged in the alert log
MAX_DUMP_FILE_SIZE
. Maximum size of Oracle trace files (in operating system blocks)
OPEN_CURSORS
Maximum number of cursors opened in parallel by one session
PARALLEL_EXECUTION_MESSAGE_SIZE
Defines size of the memory area for parallel query messages (in bytes)
PARALLEL_MAX_SERVERS
Defines the maximum number of parallel query processes (see Note 651060)
Based on the number of CPU Cores of the database server
If the database shares the server with other software (for example, SAP
central instance, other Oracle instances), only the part of the CPU
Cores that is mathematically available to the database should be
considered in the calculation (for example, 8 CPU Cores, the SAP central
instance and the Oracle database are to share resources 50:50 ->
PARALLEL_MAX_SERVERS = 8 * 0.5 * 10 = 40).
PARALLEL_THREADS_PER_CPU
Defines the number of parallel query processes that can be executed in parallel for each CPU
PGA_AGGREGATE_TARGET
Checks the available PGA memory (see Notes 789011 and 619876)
PROCESSES
Defines the maximum number of Oracle processes that exist in parallel
The component relating to ABAP work processes is only relevant in
systems with ABAP stacks. The component relating to J2EE server
processes is only relevant in systems with Java stacks.
indicates the maximum number of connections (also
called pool size) of the J2EE system DataSource. You can set the value
of this parameter using the VisualAdmin tool or other J2EE
administration tools.
QUERY_REWRITE_ENABLED
Defines whether query transformations are also factored in when the access path is determined
RECYCLEBIN
Enables access later on to objects that have already been dropped
Not supported by SAP (see Note 105047)
REMOTE_OS_AUTHENT
Defines whether TCP database access via OPS$ users is allowed (see Note 400241)
REPLICATION_DEPENDENCY_TRACKING
Defines whether the system has to create replication information when the database is accessed
Performance improves if it is deactivated
SESSIONS
Defines the maximum number of Oracle sessions that exist in parallel -
must be configured larger than PROCESSES, since single processes can
serve several sessions (for example, in the case of multiple database
connections from work processes)
SHARED_POOL_SIZE
Defines the size of the Oracle shared pool (see Notes 690241 and 789011)
STAR_TRANSFORMATION_ENABLED
Specifies to what extent STAR transformations can be used
UNDO_MANAGEMENT
Defines whether automatic undo management is used (see Note 600141)
UNDO_TABLESPACE
Defines the undo tablespace to be used (see Note 600141)
USER_DUMP_DEST
Path for trace files of Oracle shadow processes
_B_TREE_BITMAP_PLANS
Defines whether data of a B*TREE index can be transformed into a bitmap display within a database access
_OPTIM_PEEK_USER_BINDS
Defines whether Oracle takes the contents of the bind variables into account during parsing
May cause various problems (Notes 755342, 723879) if not set to FALSE.
_SORT_ELIMINATION_COST_RATIO
Controls rule-based CBO decision in connection with the FIRST_ROWS
hint and ORDER BY (Note 176754)
