Note 1431798 - Oracle 11.2.0: Database Parameter Settings

Symptom

This note contains SAP's recommendations for the optimal configuration of Oracle Database Release 11.2. These parameter recommendations are relevant for all SAP products.

This note will be updated regularly once a month. Beyond that, changes will only be made in exceptional cases for critical Oracle parameters.

Change history:

• October 27, 2011:

• add WIN 11.2.0.1 to _OPTIMIZER_USE_CBQT_STAR_TRANSFORMATION

• October 26, 2011:

• updated STAR_TRANSFORMATION_ENABLED ( HotNews 1636936 )

• added _OPTIMIZER_USE_CBQT_STAR_TRANSFORMATION (HotNews 1621425)

• September 13, 2011:

• added _FIX_CONTROL '6055658:OFF' for Windows

• July 13, 2011:

• updated setting for LOG_ARCHIVE_DEST_1 for ASM

• June 10, 2011:

• added parameter recommendations for Exadata and Grid Infrastructure

• May 18, 2011: modified description for _ENABLE_NUMA_SUPPORT

• May 12, 2011:

• Parameter recommendations adjusted for Windows 11.2.0.2

• adjusted _FIRST_SPARE_PARAMETER for Unix 11.2.0.2

• adjusted _FIX_CONTROL '4728348:OFF' for Windows 11.2.0.1

• added _FIX_CONTROL '6055658:OFF' for UNIX

• added _MUTEX_WAIT_TIME and _MUTEX_WAIT_SCHEME for 11.2.0.2

• April 12, 2011:

• added parameter _B_TREE_BITMAP_PLANS

• added event 10995

• March 18, 2011:

• corrected the text in brackets for _FIRST_SPARE_PARAMETER and _SECOND_SPARE_PARAMETER

• corrected the date values for _fix_control = '4728348:OFF'

• March 15, 2011: formal change (Change history March 10, 2011)

• March 10, 2011:

• _FIRST_SPARE_PARAMETER  for UNIX 11.2.0.1 added

• _SECOND_SPARE_PARAMETER for UNIX 11.2.0.2 added

• Parameter recommendations adjusted for Windows based on the Windows Patch Collections and for UNIX based on the SBP:

                    For Windows platforms, the new notation is as follows:

                    n := number of the Windows Patch Collection

                    E.g. : WIN 11.2.0.1.nP ( n >= 3)

                    

                    For Unix platforms, the new notation is as follows:

                    x := Placeholder for the UNIX PSU (correlates to date)

                    date := Release Date of UNIX SBP

                    E.g. :  UNIX SBP 11202x_date ( date >= 201103 )

• Feburary 10, 2011: added parameter recommendation for Automatic Storage Management (ASM), modified recommendation for remote_os_authent, added recommendation for _enable_NUMA_support, removed _fix_control '5483301:OFF'

• January 11, 2011: adjusted _fix_control settings for 11.2.0.1 and 11.2.0.2

• November 10, 2010: adjusted _fix_control settings for 11.2.0.2.0

• October 18, 2010: mention of ORA-32006

• October 12, 2010: removed _OPTIMIZER_BETTER_INLIST_COSTING

• September 13, 2010: removed DB_RECOVERY_FILE_DEST, DB_RECOVERY_FILE_DEST_SIZE, DB_WRITER_PROCESSES, LOCAL_LISTENER, LOG_ARCHIVE_DEST

• September 9, 2010: added _FIX_CONTROL '9196440:ON'

• September 9, 2010: added _FIX_CONTROL '8937971:ON'

• June 10, 2010: added _FIX_CONTROL '9495669:ON'

• May 10, 2010: added _FIX_CONTROL '6972291:ON'

• April 19, 2010: Enhanced syntax for EVENT

• March 30, 2010: Note released

Other terms

init.ora, SPFILE, server parameter file, profile

Reason and Prerequisites

This note contains SAP's recommendations for the optimal configuration
of the Oracle database with Release 11.2 in SAP environments.

Please 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.

General recommendations

Some general recommendations on which parameters should be set:

• Obsolete initialization parameters should be removed from the profile.

• You should not set any parameters that are not explicitly mentioned in this note unless the parameter is recommended as the solution or workaround for a problem in another note or it is required to set the parameter in order to implement an individual configuration.

• For information about the maintenance of parameters in an SFILE, see Note 601157.

• If several EVENT parameters are specified in init<sid>.ora, they must appear in consecutive rows.   You must avoid entering several events separated by ":" in one row.

• 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 (<parameter_name> in lower case):
  SELECT ISDEFAULT FROM V$PARAMETER2
    WHERE NAME = '<parameter_name>';
  If this returns the TRUE statement, then the parameter has not been explicitly set.

• You are allowed to 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).

• <SAPDATA_HOME> 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: Systems with mainly non-BW functions (this also includes, for example, Bank Analyzer systems)

• 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 a few exceptional 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). Note that such scenarios are only relevant in very rare situations. Therefore, you do not usually have to deviate from the standard recommendations.

• If you set parameters depending on a bugfix implementation, the relevant bugfix is specified. The notes referenced contain dependent fixes such as WINDOWS patches or merge fixes.

• With REMOTE_OS_AUTHENT=TRUE you will see one of the following messages when starting the instance :
  ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
  ORA-32006: REMOTE_OS_AUTHENT initialization parameter has been deprecated
  

Automatic Parameter Check

For information about the automatic check of the parameter, see Note 1171650.

Description and Explanation of Parameters

For more information on individual parameters, see Note 1289199.

Solution

STANDARD PARAMETER RECOMMENDATIONS FOR ORACLE 11.2.0

Parameter	Recommendation
-------------------------------	------------------------------------
AUDIT_FILE_DEST	<SAPDATA_HOME>/saptrace/audit
BACKGROUND_DUMP_DEST	Deprecated. Do not set!
	see DIAGNOSTIC_DEST
COMMIT_LOGGING	Do not set!
COMMIT_WAIT	Do not set!
COMMIT_WRITE	Deprecated. Do not set!
COMPATIBLE	11.2.0
CONTROL_FILE_RECORD_KEEP_TIME	30 or higher
CONTROL_FILES	At least three copies on
	different disk areas
CORE_DUMP_DEST	Do not set!
	see DIAGNOSTIC_DEST
DB_BLOCK_SIZE	8192
DB_CACHE_SIZE	Size depends on the available memory
	(Notes 789011, 617416)
DB_FILE_MULTIBLOCK_READ_COUNT	Do not set!
DB_FILES	Larger than the number of data files
	to be expected in the short term
	(> 200)
DB_NAME	<db_name>
DIAGNOSTIC_DEST	<SAPDATA_HOME>/saptrace
EVENT
	'10027' (Note 596420)
	'10028' (Note 596420)
	'10142' (Note 1284478)
	'10183' (Note 128648)
	'10191' (Note 128221)
	'10995 level 2' (Note 1565421)
	'38068 level 100' (Note 176754)
	'38085' (Note 176754)
	'38087' (Note 948197)
	'44951 level 1024' (Note 1166242)
FILESYSTEMIO_OPTIONS	SETALL
	(see the restrictions from Note 999524)
HPUX_SCHED_NOAGE	178 (HP-UX only, without RAC)
LOG_ARCHIVE_DEST_1
	'LOCATION=<SAPDATA_HOME>/oraarch/<sid>arch'
LOG_ARCHIVE_FORMAT	%t_%s_%r.dbf
LOG_BUFFER	Do not set! Use the default.
LOG_CHECKPOINTS_TO_ALERT	TRUE
MAX_DUMP_FILE_SIZE	20000
NLS_LENGTH_SEMANTICS	Do NOT set!!
OPEN_CURSORS	800 (up to a maximum of 2000)
OPTIMIZER_DYNAMIC_SAMPLING	OLTP: Do not set!
	OLAP: 6
OPTIMIZER_FEATURES_ENABLE	Do not set! Use the default.
OPTIMIZER_INDEX_CACHING	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
	<max-connections> +
	PARALLEL_MAX_SERVERS + 40
QUERY_REWRITE_ENABLED	FALSE
RECYCLEBIN	OFF
REMOTE_OS_AUTHENT	TRUE (only on Unix/Linux platforms
	and only for SAP systems with ABAP or
	double stack)
REPLICATION_DEPENDENCY_TRACKING	FALSE
	(if replication is not used)
SESSIONS	2 * PROCESSES
SHARED_POOL_SIZE	400 MB or greater, refer to Note 690241
STAR_TRANSFORMATION_ENABLED	TRUE (Note 1636936)
	UNIX SBP 11202x_date ( date >= 201110 )
	UNIX SGR 11202x_date ( date >= 201110 )
	UNIX SXD 11202x_date ( date >= 201110 )
	WIN      11.2.0.2.nP (    n >= 12 )
UNDO_RETENTION	set if required (refer to Note 600141)
UNDO_TABLESPACE	PSAPUNDO (Non-RAC, Note 600141)
USER_DUMP_DEST	Deprecated. Do not set!
	see DIAGNOSTIC_DEST
_B_TREE_BITMAP_PLANS	FALSE (11.2 ohne Fix 9770451,
	Hinweis 1461804)
	UNIX SBP 11201x_date ( date <= 201009 )
	WIN  11.2.0.1.nP ( n <= 5 )
_ENABLE_NUMA_SUPPORT	Use Oracle default (=not set)
	Optional: TRUE after testing (**1)
_FIRST_SPARE_PARAMETER	1
	(UNIX: 11.2.0.2 with fix 6904068
	from Note 1527505)
	UNIX SBP 11202x_date
	( 201011 <= date <= 201104 )
_FIX_CONTROL
	'4728348:OFF' (Note 1547676)
		UNIX SBP 11201x_date   ( date  <=  201101 )
		UNIX SBP 11202x_date   ( date  <=  201102 )
		WIN  11.2.0.1.nP   ( n <= 10 )
	'5099019:ON'
	'5705630:ON'
	'6055658:OFF'
		UNIX SBP 11201x_date   ( date  >=  201105 )
		UNIX SBP 11202x_date   ( date  >=  201105 )
		UNIX SGR 11202x_date   ( date  >=  201106 )
		UNIX SXD 11202x_date   ( date  >=  201106 )
		WIN  11.2.0.1.nP ( n >= 12 )
		WIN  11.2.0.2.nP ( n >=  6 )
	'6399597:ON'
	'6430500:ON'
	'6440977:ON'
	'6626018:ON'
	'6972291:ON'
	'8937971:ON'
		UNIX SBP 11201x_date   ( date  >=  201009 )
		UNIX SBP 11202x_date   ( date  >=  201011 )
		UNIX SGR 11202x_date   ( date  >=  201106 )
		UNIX SXD 11202x_date   ( date  >=  201106 )
		WIN  11.2.0.1.nP   ( n  >=  6 )
		WIN  11.2.0.2
	'9196440:ON'
		UNIX SBP 11201x_date   ( date  >=  201009 )
		UNIX SBP 11202x_date   ( date  >=  201011 )
		UNIX SGR 11202x_date   ( date  >=  201106 )
		UNIX SXD 11202x_date   ( date  >=  201106 )
		WIN  11.2.0.1.nP   ( n  >=  4 )
		WIN  11.2.0.2
	'9495669:ON'
		UNIX SBP 11201x_date   ( date  >=  201006 )
		UNIX SBP 11202x_date   ( date  >=  201011 )
		UNIX SGR 11202x_date   ( date  >=  201106 )
		UNIX SXD 11202x_date   ( date  >=  201106 )
		WIN  11.2.0.1.nP   ( n  >=  4 )
		WIN  11.2.0.2
_IN_MEMORY_UNDO	FALSE
	UNIX 11.2.0.1
	WIN  11.2.0.1
_MUTEX_WAIT_TIME	10 (Note 1588876)
	UNIX SBP 11202x_date (date >= 201105)
	UNIX SGR 11202x_date (date >= 201106)
	UNIX SXD 11202x_date (date >= 201106)
_MUTEX_WAIT_TIME	4 (Note 1588876)
	WIN  11.2.0.2
_MUTEX_WAIT_SCHEME	1 (Note 1588876)
	UNIX SBP 11202x_date (date >= 201105)
	UNIX SGR 11202x_date (date >= 201106)
	UNIX SXD 11202x_date (date >= 201106)
	WIN  11.2.0.2
_OPTIM_PEEK_USER_BINDS	FALSE (Note 755342)
_OPTIMIZER_ADAPTIVE_CURSOR_SHARING	FALSE
_OPTIMIZER_EXTENDED_CURSOR_SHARING_REL	NONE
_OPTIMIZER_USE_CBQT_STAR_TRANSFORMATION	FALSE (Note 1621425)
	UNIX     11.2.0.1
	UNIX SBP 11202x_date ( date <= 201109 )
	UNIX SGR 11202x_date ( date <= 201109 )
	UNIX SXD 11202x_date ( date <= 201109 )
	WIN      11.2.0.1
	WIN      11.2.0.2.nP (    n <= 12 )
_OPTIMIZER_USE_FEEDBACK	FALSE
_SECOND_SPARE_PARAMETER	1
	(UNIX: 11.2.0.1 with fix 6904068
	from Note 1527504)
	UNIX SBP 11201x_date (date >= 201011)

(**1) By default, this parameter is not set (=FALSE) and Oracle NUMA support (Non Uniform Memory Architecture) is disabled. Before you enable Oracle NUMA support in a production system by setting this parameter to TRUE it is necessary to run additional (performance) tests: you need to evaluate the performance before and after enabling NUMA in a test environment before you go into production. Additional information can be found in My Oracle Support note 864633.1.

STANDARD PARAMETER RECOMMENDATIONS FOR ASM FROM 11.2.0.2 ON

Note that ASM is only certified for SAP from 11.2.0.2 on (see SAP note 1550133). In SAP installations with ASM, the following database parameters need to be set.

Parameter	Recommendation
-------------------------------	------------------------------------
COMPATIBLE	11.2.0.2.0
CONTROL_FILES	At least three copies on
	different disk groups in ASM
	'+ARCH/<DBNAME>/cntrl<DBNAME>.dbf' (1st copy)
	'+DATA/<DBNAME>/cntrl<DBNAME>.dbf' (2nd copy)
	'+RECO/<DBNAME>/cntrl<DBNAME>.dbf' (3rd copy)
DB_CREATE_FILE_DEST	'+DATA' (data files/temp files)
DB_CREATE_ONLINE_LOG_DEST_1	'+DATA' (online redo 1st copy)
DB_CREATE_ONLINE_LOG_DEST_2	'+RECO' (online redo 2nd copy)
DB_RECOVERY_FILE_DEST	'+RECO' (Fast Recovery Area/FRA)
DB_RECOVERY_FILE_DEST_SIZE	<size of fast recovery area>
LOG_ARCHIVE_DEST_1	'LOCATION=+ARCH'
	(for Single Instance)
LOG_ARCHIVE_DEST_1	'LOCATION=+<DGNAME>/<DBNAME>/ORAARCH'
	(for RAC)

Appendix

SQL command syntax to specify parameter EVENT in the SPFILE
SQL> ALTER SYSTEM SET EVENT=
       '<event_1>',
       '<event_2>',
       ...
       '<event_n>'
     SCOPE = SPFILE;

Example:
SQL> ALTER SYSTEM SET EVENT=
    '10027',
    '10028',
    '38068 level 100'
    SCOPE=SPFILE;

Entry in SPFILE:
*.event='10027','10028','30068 level 100'

SQL command syntax to specify _FIX_CONTROL in the SPFILE
SQL> ALTER SYSTEM SET "_FIX_CONTROL" =
      '<bugid_1>:<val_1>',
      '<bugid_2>:<val_2>',
      ...
      '<bugid_n>:<val_n>'
     SCOPE=SPFILE;

Example:
SQL> ALTER SYSTEM SET "_FIX_CONTROL" =
    '5099019:ON','5705630:ON',
    '6399597:ON','6430500:ON',
    '6440977:ON','6626018:ON'
    SCOPE=SPFILE;

Entry in SPFILE:
*._fix_control='5099019:ON','5705630:ON','6399597:ON','6430500:ON','6440977:ON','6626018:ON'