Summary
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.
Parameter check, Oracle
You are using Oracle Release 8.1, 9.2., 10.1., 10.2. or 11.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).
Three SQL statements are attached to this note.
- parameter_check_101_or_lower.txt
for Oracle Releases 8.1, 9.2, and 10.1.
- parameter_check_102.txt
for Oracle Release 10.2.
- parameter_check_112.txt
for Oracle Release 11.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 RSORADJV
- SQLPLUS
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 '
Example for the replacement for OLAP systems (as mentioned above, several replacements may be required):
before: ... substr(upper('
after: ... substr(upper('y'),1,1),'Y','B','R' ...
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 parameter
- Each underscore parameter that is recommended
- Each 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 suitable
- automatic check ok; doublecheck if value ... is suitable
- check if default value ... is suitable
- automatic check ok; doublecheck if default value ... is suitable
- check 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
- 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 or, particularly with Basis Release 6.20, may report "The length of the current statement is greater than the maximum statement length", even if the statement has a correct syntax. These old versions break statement lines at incorrect places and result in syntax errors or cannot deal with SQL statements greater than approximately 30KB. If an error occurs, try to use the other two options. If there is no SQL Plus access and if the SQL editor reports a statement that is too long, as a workaround, you can shorten the statement, for example, by removing all lines with _FIX_CONTROLS and EVENTS and their indented subsequent lines as follows:
||'_FIX_CONTROL...
||'...
||'...
or
||'EVENT,...
||'...
It may be possible to execute a statement that is shortened in this way, but it does not return valid recommendations for _FIX_CONTROL and EVENT. Therefore, a new execution must be performed, but this time other parameters must be removed and the _FIX_CONTROL parameters and EVENT parameters must be retained so that their recommendation is generated.
For extremely old editors, execution is not guaranteed after future enhancements of the statement. The statement must then be executed in SQL Plus. This variant will work.
An actual syntax error occurs only if the error occurs in SQL Plus itself.
- 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.
- The parameter checks for Release 10.2 and 11.2 contain information about whether the checks of the event parameters or _fix_control parameters are reliable or not in the output header. Usually, the relevant checks are marked with "reliable".
Exceptions:
- Events
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.ora, use the following syntax (recommended):
Event=''
...
Event=''
OR
Event='',...,' '
Do NOT use:
Event=':...: '
To assign several values to the event parameter in spfile.ora, use the following syntax:
Alter system set EVENT='',...,' ' scope=spfile;
Do NOT use:
Alter system set EVENT=':...: ' scope=spfile;
- _fix_control
Although _fix_control parameters are set and are correctly displayed, Oracle may ignore the settings due to a bug. The check displays whether a bug is relevant or not. For more information about this problem, see Note 1454675.
- 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*10
- pga_aggregate_target
Maximum used memory since startup between 75% and 90% of pga_aggregate_target
- processes
Maximum processes since DB start <= 75% of processes
- sessions
Maximum sessions since DB start <= 75% of sessions
- shared_pool_size
Maximum used memory in history between 75% and 90%
- undo_retention
unexpired stolen blocks since in v$undostat = 0
- Oracle 11.2: The "Merge Fix" line in the header of the output contains information about the MINIMUM optimizer merge fix level that exists in the system. Note:
- Not every optimizer merge fix changes the content of v$system_fix_control. Therefore, the current version of the merge fix cannot always be queried uniquely using SQL. The optimizer merge fix that is contained in the system may therefore also be newer than displayed.
However, the optimizer merge fix that exists in the system is usually transferred to the system with the patch bundle that is displayed.
Exceptions:
The system cannot differentiate between the optimizer merge fix from the October 2010 patch bundle and the optimizer merge fix from the September 2010 patch bundle. In both cases, it displays the September patch bundle.
- A patch bundle does not necessarily contain a new optimizer merge fix. Therefore, the patch bundle that is contained in the system may be newer.
Change history parameter_check_112.txt/parameter_check_102.txt:
- 26.09.2011
- 11.2: Parameter adjustments in accordance with Note/Version 1431798/36; shared_pool, open_cursors parameter is checked in detail (in particular in RAC); pseudo warnings are no longer issued for dynamically changed NLS parameters
- 10.2: Parameter adjustments in accordance with Note/Version 830576/218; shared_pool, open_cursors parameter is checked in detail (in particular in RAC)
- 12.08.2011
- 11.2: Optimizer merge fix from August: Patch bundle is recognized (11.2.0.1, 11.2.0.2); check script is synchronized with Note/Version 1431798/35
- 10.2: Warning for Version 10.2.0.2. with regard to parameter check desupport because the underlying parameter note 830576 is no longer maintained for Version 10.2.0.2; check script is synchronized with Note/Version 830576/217.
- 19.07.2011
- 11.2: Parameter adjustments in accordance with Note/Version 1431798/35
- 15.06.2011
- 11.2: Parameter adjustments in accordance with Note/Version 1431798/34. Optimizer merge fix from June patch bundle is recognized (11.2.0.2)
- 10.2: Optimizer merge fix from June patch bundle is recognized (10.2.0.4 and 10.2.0.5)
- 31.05.2011
- 11.2: Parameter adjustments in accordance with Note/Version 1431798/33
Optimizer merge fix from May patch bundle is recognized (11.2.0.1 and 11.2.0.2)
- 10.2: Parameter adjustments in accordance with Note/Version 830576/216. Optimizer merge fix from May patch bundle is recognized (10.2.0.4 and 10.2.0.5)
- 21.04.2011
- Adjustment to extensive parameter note changes
- 11.2: Parameter adjustments in accordance with Note/Version 1431798/31. Optimizer merge fix from March patch bundle is recognized (11.2.0.2)
- 10.2: Parameter adjustments in accordance with Note/Version 830576/214. Optimizer merge fix from April patch bundle is recognized (10.2.0.4)
- 22.02.2011
- ABAP stack and ASM systems are recognized.
- 11.2: Parameter adjustments in accordance with Note/Version 1431798/26
- 10.2: Parameter adjustments in accordance with Note/Version 830576/211. Optimizer merge fix from February patch bundle is recognized (10.2.0.4)
- 12.01.2011
- 11.2: Parameter adjustments in accordance with Note/Version 1431798/21
- 10.2: Parameter adjustments in accordance with Note/Version 830576/210
- 14.10.2010
- 11.2: Parameter adjustments in accordance with Note/Version 1431798/15
- 10.2: Parameter adjustments in accordance with Note/Version 830576/205. Optimizer merge fix from October patch bundle is recognized
- 13.09.2010
- 11.2: Parameter adjustments in accordance with Note/Version 1431798/14
- 10.2: Optimizer merge fix from September: Patch bundle is recognized (synchronized with Note/Version 830576/204)
- 10.08.2010
- 11.2: No parameter adjustments; check script is synchronized with Note /Version 1431798/12.
- 10.2: Parameter adjustments in accordance with Note/Version 830576/203
- 10.07.2010
- 11.2: Optimizer merge fix from June: Patch bundle is recognized (synchronized with Note/Version 1431798/12)
- 10.2: Optimizer merge fix from July: Patch bundle is recognized; parameter adjustments in accordance with Note/Version 830576/202
- 10.06.2010
- 11.2: Parameter adjustments in accordance with Note/Version 1431798/11
- 10.2: Parameter adjustments in accordance with Note/Version 830576/200
- 10.05.2010
- 11.2: Parameter adjustments in accordance with Note/Version 1431798/10
- 10.2: Parameter adjustments in accordance with Note/Version 830576/198
- Adjustments of the merge fix output to the SAP bundle patches
- Implementation of two new columns that contain additional information about the parameter classification
U)sage - provides information about what is influenced by the parameter
"p": Performance
"f": Functions
" ": No further classification
I)mportance - ROUGH indicator of the extent to which the parameter influences the database behavior
"1": Extreme effects, for example, performance parameters that affect most of the queries or functional parameters that must be set due to Hot News notes.
"2"/"3": Strong effect, for example performance parameters that affect specific queries
" ": No further classification
The following still applies: ALL parameters must be set in accordance with the parameter note. The I)mportance is only an INDICATOR for the urgency. Only parameters that are included in parameter notes are classified. For parameters that have not been tested by SAP, the importance or usage cannot be specified.
- 10.04.2010
- 11.2: Parameter adjustments in accordance with Note/Version 1431798/5
- 10.2: No parameter adjustments; check script is synchronized with Note /Version 830576/197.
- In addition, the INFORMATION section contains the DB SID.
- In addition, the INFORMATION section contains information about whether the _fix_control check could be reliably executed (see above).
- Minor changes in text output
- The "add" recommendation is suppressed in case of dynamically changed underscores or _fix_control parameters.
- 10.03.2010
- No changes (check scripts are consistent with parameter note and patch note)
- 10.02.2010
- 11.2: Initial version
- Correct recommendation for _CURSOR_FEATURES_ENABLED
- Correct recommendation if the _fix_control value is specified in the format
': , ..., : '
instead of in the recommended format
': ', ..., ' : '
- 10.01.2010
- Parameter adjustment in accordance with Note 830576 (Version 193).
- 20.12.2009
- The optimizer merge fix 10.2.0.4/16 is recognized
- Warning if the number of data files in the database is greater than 90% of the parameter db_files
- There is no division by 0 if incorrect parameter settings are used.
- 10.12.2009
- No changes (Check script is consistent with parameters and patch note)
- 10.11.2009
- No changes (Check script is consistent with parameters and patch note)
- 10.10.2009
- The optimizer merge fix 10.2.0.4/15 is recognized
- Parameter adjustment in accordance with Note 830576.
- 10.09.2009
- No changes (Check script is consistent with parameters and patch note)
- 10.08.2009
- Workaround for the SQL editor problem:
The ORA-00909 problem in old editors (for example, 6.20/Support Package 63) is solved using a line break with the multiplication sign and by interpreting the leading "*" as a comment character.
- 23.07.2009
- Workaround for the SQL editor problem:
Solution of the problem of long statement length in old
editors (Functional enhancements of the check statement exceeded the maximum statement length again in the old editors)
- Optimizer merge fix 10.2.0.4/14 is recognized (will be published on August 10, 2009)
- 10.07.2009
- Parameter adjustment in accordance with Note 830576.
- Parameters that were changed using "alter system set ... scope=memory" are listed at the beginning of the parameter list. No recommendations are specified for these parameters.
- For the merge fix level, the date on which the merge fix was released is displayed. This enables you to easily check the initial date of the optimizer source code in the customer system.
- 10.06.2009
- Parameter adjustments in accordance with Note 830576
- 10.05.2009
- Parameter adjustments in accordance with Note 830576
- 29.04.2009
- Use 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.2009
- Parameter correction for Windows (_fix_control 6660162:ON only on Unix to be set)
- 10.04.2009
- Parameter adjustments in accordance with Note 830576
- 23.03.2009
- Workaround for the SQL editor problem:
Solution of the problem with long statement length in old
editors
- 10.03.2009
- Parameter adjustment in accordance with Note 830576.
- RAC enabling
- Heuristics for sizing parameters
Header Data
| Release Status: | Released for Customer |
| Released on: | 13.10.2011 14:01:26 |
| Master Language: | German |
| Priority: | Recommendations/additional info |
| Category: | Installation information |
| Primary Component: | BC-DB-ORA Oracle |
| Secondary Components: | BW-SYS-DB-ORA BW ORACLE |
| SV-BO-DB-ORA Oracle Performance Problems |
No comments:
Post a Comment