SAP Basis Tech Supports

Symptom
You require more information about certain Oracle parameters.

Reason and Prerequisites
This note provides descriptions and background information about certain Oracle parameters.
See Note 830576 for recommended parameter settings when using Oracle 10g.
Solution
BACKGROUND_DUMP_DESTPath for alert log and background trace filesCOMPATIBLEDefines the Oracle version whose features can be used to the greatest extentAs 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.2.0) is recommended to avoid changing the parameter as part of a patch set installation.If an ORA-00201 error occurs when you try to convert the value with five parts 10.2.0.2.0 to 10.2.0, you can leave the value 10.2.0.2.0 (independent of the patch set used).CONTROL_FILESPath and name of the control files that are usedCONTROL_FILE_RECORD_KEEP_TIMEDefines how many days historic data is retained in the control filesHistoric data is required by RMAN, for example.May cause control files to increase in size (see Note 904490)CORE_DUMP_DESTPath under which core files are storedDB_BLOCK_SIZE.Size of an Oracle blockCan 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_SIZESize of the Oracle data buffer (in bytes)Optimal size depends on the available memory (see Notes 789011 and 617416)DB_FILESMaximum number of Oracle data filesDB_NAMEName of the databaseDB_WRITER_PROCESSESNumber of DBWR processesEVENTActivation of internal control mechanisms and functionsTo set events in SPFILE, refer also to Note 596423.If many events are set, data sources such as V$PARAMETER, DBA_HIST_PARAMETER, or “SHOW PARAMETER” may supply an incomplete value. This is only a display problem. The values that are included in V$PARAMETER2 are the relevant values.FILESYSTEMIO_OPTIONSActivation 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.HPUX_SCHED_NOAGEOptimized scheduling policy for Oracle processes on HP-UX.The privileges RTSCHED and RTPRIO must be assigned to the dba group to enable you to use the functions (see Note 1285599).LOG_ARCHIVE_DESTHistoric variant of LOG_ARCHIVE_DEST_1, which is not compatible with features such as the Flash Recovery Area and which should therefore no longer be used.LOG_ARCHIVE_DEST_1Path/prefix for offline redo logsThe syntax differs with an additional “LOCATION=” string of LOG_ARCHIVE_DEST; if this difference is ignored, ORA-16024 occurs concerning LOG_ARCHIVE_DEST_1.LOG_ARCHIVE_FORMATName format of the offline redo logsTo avoid the problems described in Note 132551, it must be explicitly set to WINDOWS at least.LOG_BUFFERMinimum 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_ALERTDefines whether checkpoints are to be logged in the alert logMAX_DUMP_FILE_SIZEMaximum size of Oracle trace files (in operating system blocks)A limitation is useful to avoid file system overflows and to reduce the duration of the dump generation.You can increase it temporarily if required.OPEN_CURSORSMaximum number of cursors opened in parallel by one sessionOPTIMIZER_DYNAMIC_SAMPLINGDetermines how much data is to be read to determine the access plan.Level 2 (the default setting for Oracle 10g): Dynamic sampling is performed only if tables do not have any statistics.Level 6: As level 2 and includes dynamic sampling of 128 table blocks if literals are used and there are no bind variables.OPTIMIZER_INDEX_COST_ADJAdjusts the calculated index costs; when there is a value of 20 (percent), index costs are reduced by a factor of 5, for example.A value lower than 100 is advisable so that index accesses are preferred instead of full table scans.PARALLEL_EXECUTION_MESSAGE_SIZEDefines size of the memory area for parallel query messages (in bytes)PARALLEL_MAX_SERVERSDefines the maximum number of parallel execution processes (see Note 651060)Based on the number of CPU Cores of the database serverThe number of CPU Cores generally corresponds to the default value for the Oracle parameter CPU_COUNT. If you are unsure in individual cases, you can use the value of the parameter CPU_COUNT (for example, in transaction DB26).If the database shares the server with other software (for example, SAP central instance, other Oracle instances), you can also select a lower value (for example, 8 CPU Cores, the SAP central instance and the Oracle database should share resources 50:50 -> PARALLEL_MAX_SERVERS = 8 * 0.5 * 10 = 40).PARALLEL_THREADS_PER_CPUDefines the number of parallel query processes that can be executed in parallel for each CPUInfluences the DEFAULT level of parallel processing during a parallel execution (see Note 651060).PGA_AGGREGATE_TARGETChecks the available PGA memory (see Notes 789011 and 619876)PROCESSESDefines the maximum number of Oracle processes that exist in parallelThe 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.<max-connections> 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_ENABLEDDefines whether query transformations are also factored in when the access path is determinedRECYCLEBINEnables access later on to objects that have already been droppedNot supported by SAP (see Note 105047)REMOTE_OS_AUTHENTDefines whether TCP database access via OPS$ users is allowed (see Note 400241)REPLICATION_DEPENDENCY_TRACKINGDefines whether the system has to create replication information when the database is accessedPerformance improves if it is deactivatedSESSIONSDefines 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_SIZEDefines the size of the Oracle shared pool (see Notes 690241 and 789011)STAR_TRANSFORMATION_ENABLEDSpecifies to what extent STAR transformations can be usedUNDO_MANAGEMENTDefines whether automatic undo management is used (see Note 600141)UNDO_TABLESPACEDefines the undo tablespace to be used (see Note 600141)USER_DUMP_DESTPath for trace files of Oracle shadow processes_B_TREE_BITMAP_PLANSDefines whether data of a B*TREE index can be transformed into a bitmap display during a database access._BLOOM_FILTER_ENABLEDDetermines whether bloom filters may be used during joins._DB_BLOCK_NUMAControl use of NUMA optimizations._ENABLE_NUMA_OPTIMIZATIONControl use of NUMA optimizations._FIX_CONTROLActivates or deactivates individual CBO fixesIf several values are to be set for _FIX_CONTROL, you must use the following syntax (example):
init<SID>.ora:
_fix_control=’6660162:ON’,'6440977:ON’,…
OR
in consecutive lines
_fix_control=’6660162:ON’
_fix_control=’6440977:ON’
_fix_control=…
spfile<SID>.ora
alter system set “_fix_control”=’6660162:ON’,'6440977:ON’,… scope= spfile;If many _FIX_CONTROL values are set, data sources such as V$PARAMETER, DBA_HIST_PARAMETER, or “SHOW PARAMETER” may supply an incomplete value. This is only a display problem. The values that are included in V$PARAMETER2 are the relevant values._CURSOR_FEATURES_ENABLEDWith a value of 10 and in connection with fix 6795880, the following is prevented: sporadic hanging during parsing_FIRST_SPARE_PARAMETERThis is a generic parameter that can be used for different purposes in certain cases.With Oracle 10.2.0.4 and fix 6904068, this parameter is used to introduce a break between two “cursor: pin S” mutex requirements instead of continually executing requests. This may help to avoid critical CPU bottlenecks._INDEX_JOIN_ENABLEDControls whether index joins can be used or not; within an index join, two indices of a table are directly linked together._IN_MEMORY_UNDOControls whether the In Memory Undo feature (IMU) is used or not_OPTIM_PEEK_USER_BINDSDefines whether Oracle takes the contents of the bind variables into account during parsingMay cause various problems (Notes 755342, 723879) if not set to FALSE._OPTIMIZER_BETTER_INLIST_COSTINGControls the cost calculation for IN listsIf the parameter is set to OFF, IN lists are evaluated more favorably (this is an advantage in the SAP environment)._OPTIMIZER_MJC_ENABLEDControls whether Cartesian merge joins are used._PUSH_JOIN_UNION_VIEWControls whether join predicates may be used in a UNION ALL construct beyond the view boundaries._SORT_ELIMINATION_COST_RATIOControls rule-based CBO decision in connection with the FIRST_ROWS hint and ORDER BY (see Note 176754)._TABLE_LOOKUP_PREFETCH_SIZEControls whether table prefetching is used (a value of zero means no table prefetching).