Parameter
dbs/ora/use_hints
Short text
Generation of optimizer hints for Oracle
Parameter Description
The parameter determines whether hints for the cost-based optimizer are generated for certain types of SQL statement.
Parameter value 0 means that no optimizer hints are generated. Hints are usually generated for other values. The values ‘1’ / ‘-1’ activate hints according to standard parametrization. You can also specify in detail, as a parameter, when one of the supported hints is to be generated or passed to the database.
Application Area
Database
Parameter Unit
-1/1/0 / Hint detail specification as described below
Default Value
-1
Who is permitted to make changes?
SAP
Database System Restrictions
Oracle
Are other parameters affected or dependent?
No
Values allowed
In the detailed form, the parameter value consists of individual components separated by “,”. The format of each sub-specification is “[name] -> [number]”, where
• “abap -> 0/1” In ABAP programs for Open SQL statements, specified hints are either ignored or passed to the database.
• “dbif -> 0/1” The R/3 database interface analyzes hints entered for Open SQL statements to determine whether entries were made that can be analyzed by the interface itself. These entries are either ignored or analyzed.
• “rule_in -> [number]” A RULE hint is generated if the SQL statement contains an IN clause with at least [number] entries.
• “rule_fae -> [number]” A RULE hint is generated if at least [number] rows of a FOR ALL ENTRIES table have been processed by this call.
• “concat_fae -> [number]” A USE_CONCAT hint is generated if at least
• “upto -> [number]” A FIRST_ROWS hint is generated if the SQL statement generated by the R/3 database interface contains a condition for the virtual ROWNUM field. In addition, an UPTO limit must be set for the database statement, whereby a SELECT SINGLE implicitly sets the limit to 1.
• The value “0” switches off the generation of the corresponding hints.
For each SQL statement, multiple ABAP hints can be transferred and up to two additional hints can be generated:
• FIRST_ROWS, and independently of this, RULE or USE_CONCAT, whereby usability is tested in the order RULE –> USE_CONCAT.
The above short forms correspond to the following details:
• ‘use_hints = -1′ ->’abap->1,dbif-> 1,rule_in->0,rule_fae->0,concat_fae->0,upto->0’ -> All hints are deactivated except for Open SQL hints.
• ‘use_hints = 0′ ->’abap->0,dbif-> 0,rule_in->0,rule_fae->0,concat_fae->0,upto->0’ -> All hints are deactivated.
• ‘use_hints = 1’ -> ‘abap->1, dbif->1, rule_in->10, rule_fae->1, concat_fae->0, upto->[MAXINT]’ -> Open SQL hints are activated, a RULE hint is generated for large IN clauses and for FOR ALL ENTRIES statements. In addition, a FIRST ROW hint is generated for ROWNUM statements.
