Online Tutorials & Training Materials | STechies.com
Register Login

Parameter Recommendations for Oracle 10g Optimal Configuration of the Oracle Database

|| || 0

Parameter Recommendations for Oracle 10g Optimal Configuration of the Oracle Database
Stechies

The following information contains SAP's recommendations for the optimal configuration of the Oracle database.
For Oracle 9i or lower, refer to 124361 which referenced there.

that the recommendations given in this may be changed. Therefore, we recommend that you check the latest version of this 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 . 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 Version. 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 . Exceptions:

. The parameter is recommended as the solution or workaround for a problem in another .
. 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 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 (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 cannot give any general recommendations. However, you can determine options for optimization on the basis of a database performance analysis (see s 618868, 619188, 789011).
. The parameterization described below is directed towards the use of the features of the dynamic SGA ( 617416) and the automatic PGA administration ( 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, 997889) or to use functions (for example, index joins).

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 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
s 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 (s 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 (s 79341, 789011)
EVENT
"10027 trace name context forever, level 1" ( 596420)
"10028 trace name context forever, level 1" ( 596420)
"10162 trace name context forever, level 1" (s 977319,
1040300)
"10183 trace name context forever, level 1" ( 128648)
"10191 trace name context forever, level 1" ( 128221)
"10629 trace name context forever, level 32" ( 869521,
other settings of events 10626 / 10629 also allowed)
"14532 trace name context forever, level 1" ( 1031682,
>= 10.2.0.2, Fix from 1031682 must be implemented)
"38068 trace name context forever, level 100" ( 176754)
FILESYSTEMIO_OPTIONS SETALL ( the restrictions from
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 690241
STAR_TRANSFORMATION_ENABLED TRUE
UNDO_MANAGEMENT AUTO ( 600141)
UNDO_RETENTION set if required (refer to 600141)
UNDO_TABLESPACE PSAPUNDO ( 600141)
USER_DUMP_DEST /saptrace/usertrace
_B_TREE_BITMAP_PLANS FALSE
_FIX_CONTROL 4728348:OFF (10.2.0.2, if
merge fix 5984705 from 981875
is not implemented; see s
964858 and 997889)
_INDEX_JOIN_ENABLED FALSE (10.2.0.2, refer to
964858)
_IN_MEMORY_UNDO FALSE (up to and including 10.2.0.3, if fixes from
s 980805 and 1013476
are not implemented)
_OPTIM_PEEK_USER_BINDS FALSE (see 755342)
_OPTIMIZER_MJC_ENABLED FALSE ( 176754 (30))
_SORT_ELIMINATION_COST_RATIO 10 (See 176754 (16))

ORACLE 10.1: ADDITIONAL/ALTERNATIVE PARAMETER SETTINGS *********************************************************
Parameter Recommendation
------------------------------- ------------------------------------
COMPATIBLE 10.1.0
EVENT 10040 (Level 1, 899070)
RECYCLEBIN Do not set
_OPTIMIZER_OR_EXPANSION DEPTH (10.1.0.5 or higher, 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

S 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 Version (see 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 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 105047)

DB_CACHE_SIZE
. Size of the Oracle data buffer (in bytes)
. Optimal size depends on the available memory (see s 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 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 999524 and 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 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 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 s 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 105047)

REMOTE_OS_AUTHENT
Defines whether TCP database access via OPS$ users is allowed (see 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 s 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 600141)

UNDO_TABLESPACE
Defines the undo tablespace to be used (see 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 (s 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 (176754)

Read Here Oracle Forum to Get Answers to More Questions related to it.


Related Articles