IT/Database

Note 1431798 - Oracle 11.2.0: Database Parameter Settings

SAP_BASIS 2011. 11. 1. 11:28

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'