SAP Parameter dbs/ora/use_hints - Generierung von Optimizer Hints bei Oracle


Short text
Generation of optimizer hints for Oracle

Parameter description
The parameter determines whether hints for the cost-based optimizer aregenerated for certain types of SQL statement.
Parameter value 0 means that no optimizer hints are generated. Hints areusually generated for other values. The values '1' / '-1' activate hintsaccording to standard parameterization. You can also specify in detail,as a parameter, when one of the supported hints is to be generated orpassed to the database.

Work area

Parameter unit
-1/1/0 / Hint detail specification as described below

Default value

Who is allowed

Limitation for db

Other parameter
In the detailed form, the parameter value consists of individualcomponents 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 beanalyzed by the interface itself. These entries are either ignored oranalyzed.
      • "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 thiscall.
          • "concat_fae -> <(><<)>number>" A USE_CONCAT hint is generated if at
          • least <(><<)>number> rows of a FOR ALL ENTRIES table are processed bythis call.
            • "upto -> <(><<)>number>" A FIRST_ROWS hint is generated if the SQL
            • statement generated by the R/3 database interface contains a conditionfor the virtual ROWNUM field. In addition, an UPTO limit must be set forthe database statement, whereby a SELECT SINGLE implicitly sets thelimit 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 ofthis, 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
                  • '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 RULEhint is generated for large IN clauses and for FOR ALL ENTRIESstatements. In addition, a FIRST ROW hint is generated for ROWNUMstatements.