SAP Basis Tech Supports

Symptom
You want the system to automatically check the Oracle DB parameter.
In doing so, the system must compare the current recommendations of the relevant parameter note (depending on the Oracle release, patch set and/or system type) with the actual DB parameters.
Other terms
Parameter check, Oracle
Reason and Prerequisites
You are using Oracle Release 8.1, 9.2., 10.1. or 10.2.
The system type (OLAP, OLTP) is known:
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 and systems with a pure Java stack)
Configure double stack systems (that is, systems with both ABAP stacks and JAVA stacks)
as you would an OLTP or OLAP system, depending on the degree to which you use BW functions (see above).

Solution
Two SQL statements are attached to this note.
parameters_up_to_oracle_101.txt
for Oracle Releases 8.1, 9.2, and 10.1.parameters_as_of_oracle_102.txt
For Oracle Release 10.2.
They can be executed using:
The SQL Editor in ST04N or in the DBA Cockpit of the system to be checked.The SQL Editor in ST04N or in the DBA Cockpit of a system (for example, Solution Manager) to which the system to be checked is connected in accordance with Note 1256322. If required, this enables you to avoid the restrictions of the SQL Editor (see the notes below) in the system to be checked.The report RSORADJVSQLPLUS
To ensure an acceptable output with SQLPLUS, execute the following SQLPLUS commands in advance
set linesize 360
set pagesize 1000
COLUMN name FORMAT a40
COLUMN set FORMAT a8
COLUMN remark FORMAT a60
COLUMN recommendation FORMAT a120
COLUMN is_value FORMAT a50
COLUMN should_be_value FORMAT a50
You can execute the relevant statement without adjustments for OLTP systems. For OLAP systems, you need to replace the string “<For OLAP enter: y>” with “y”. Depending on the statement, the string may occur several times.
The statement is maintained synchronously to the parameter notes.
The system automatically checks whether the current parameterization is correct and issues a recommendation or an “OK” for
Each official parameterEach underscore parameter that is recommendedEach underscore parameter that is set but not recommended
The recommendations are organized according to the type of recommendation. After the first “OK”, there are only “OK”s.
Since there are some checks that either cannot be implemented or that are very time-consuming to implement in an individual SQL statement, you must manually check the remaining parameters. The error message is then:
check if value … is suitableautomatic check ok; doublecheck if value … is suitablecheck if default value … is suitableautomatic check ok; doublecheck if default value … is suitablecheck why set but not mentioned in note
You can use the statements on all of the Oracle releases mentioned above. The system determines the release except for the patch level. As a result, generally, the system cannot automatically check patch-dependent parameters within a patch set. The optimizer merge fixes as of Oracle 10.2 are an exception to this. The optimizer merge fix level (and therefore also the parameters that only have to be set as of or up to a certain optimizer merge fix level) can be checked automatically and is checked automatically.
Remarks
The statements that are attached to this note replace the attachment from Note 830576.Old versions of the SQL editor in ST04N or in the DBA Cockpit or old versions of the report RSORADJV may have execution problems and may report Oracle syntax errors, even if the statement has a correct syntax. These old versions break statement lines at incorrect places and result in syntax errors. If an error occurs, try to use the other two options. An actual syntax error occurs only if the error also occurs in SQL Plus.New versions of the SQL editor in ST04N or in the DBA Cockpit, and new versions of the report RSORADJV may have execution problems and may report
“Oracle Diagnostics Package not licensed. See SAP-note 1028068″ OR return the SID of the system as an uninformative error message.
The reason for this is that the system also accesses the dba_hist_ views when you execute the parameter check. These may be accessed only if the Oracle diagnostic package is licensed. This is usually the case so that a relevant indicator can be set in accordance with Note 1028068. The parameter check then works.There is a syntax error even though the statement is syntactically correct. These old versions break statement lines at incorrect places and result in syntax errors. If an error occurs, try to use the other two options. An actual syntax error occurs only if the error also occurs in SQL Plus.The parameter check for Release 10.2 and higher provides information about whether the event parameter check is reliable or not in the output header. Usually, this is the case. Only in systems in which several events are separated using ‘:’, a reliable event check cannot be performed because SQL cannot be used to read all events if the value string is longer than 512 characters. For this reason, you should not use the ‘:’ syntax. Instead, use one of the following options:
To assign several values to the event parameter in init<sid>.ora, use the following syntax (recommended):
Event=’<value 1>’
…
Event=’<value n>’
OR
Event=’<value 1>’,…,’<value n>’
Do NOT use:
Event=’<value 1>:…:<value n>’
To assign several values to the event parameter in spfile<sid>.ora, use the following syntax:
Alter system set EVENT=’<value 1>’,…,’<value n>’ scope=spfile;
Do NOT use:
Alter system set EVENT=’<value 1>:…:<value n>’ scope=spfile;A recommendation is issued for sizing parameters
“automatic check ok; doublecheck if … is suitable”
This recommendation is principally equivalent to
“check if … is suitable”
However, certain system-specific heuristics have been met that indicate that the value has probably been set. For this reason, a different recommendation is made. A manual (double)check is still required. Parameters and heuristics are:parallel_max_servers
= CPU_COUNT*10pga_aggregate_target
Maximum used memory since startup between 75% and 90% of pga_aggregate_targetprocesses
Maximum processes since DB start <= 75% of processessessions
Maximum sessions since DB start <= 75% of sessionsshared_pool_size
Maximum used memory in history between 75% and 90%undo_retention
unexpired stolen blocks since in v$undostat = 0
Change history parameters_as_of_oracle_102.txt:
10.06.2009Parameter adjustments in accordance with Note 83057610.05.2009Parameter adjustments in accordance with Note 83057629.04.2009Use an outer join statement for gv$undostat join. Otherwise, the recommendations when using manual undo management are as follows:
“check why set but mentioned with other prerequisites/not mentioned in note”.21.04.2009Parameter correction for Windows (_fix_control 6660162:ON only on Unix to be set)10.04.2009Parameter adjustments in accordance with Note 83057623.03.2009Solution of the problem with long statement length in old
editors10.03.2009Parameter adjustment in accordance with Note 830576.RAC enablingHeuristics for sizing parameters