Optimizing Access

Central instances, or optimizers, can be used to optimize database access.

Optimizers can be used in two ways and are configured on different levels.

The old method of rule-based optimization selects access operations based on fixed, predefined rules.

The cost-based method uses previously generated statistics.

The optimizer is activated for the current session using the entry OPTIMIZER_GOAL. Four values are used to activate the optimizer:

  • ALL_ROWS: This value enables cost-based optimization and optimizes the entire throughput.
  • FIRST_ROWS: This value also enables cost-based optimization and optimizes response times, i.e. the first rows are output as quickly as possible.
  • RULE: This value enables rule-based optimization.
  • CHOOSE: This value enables cost-based optimization if statistics are available, otherwise it enables rule-based optimization. CHOOSE is the default value.

The OPTIMIZER_GOAL parameter is no longer supported by Oracle 10g and must be replaced with the OPTIMIZER_MODE parameter (OPTIMIZER_MODE was also supported prior to Oracle version 9i). The following settings can apply to the optimizer:

  • ALL_ROWS: enables the statistical optimization for the entire throughput.
  • FIRST_ROWS: also works based on statistical information and optimizes for the optimum response time; i.e. the onus is on outputting the first rows as quickly as possible.
  • FIRST_ROWS_n
  • RULE: no longer supported
  • CHOOSE: no longer supported

Statistics

Statistics, such as the number of rows per table or the distribution of values in a table's column provide the optimizer with valuable core values that allow decisions on the access strategy to be made according to a concrete situation and not based on general rules.

Tuning

You can use the following parameters in the [MM ORA] section to influence the formation of statements:

TUNE_STMT_ORDER=on

All statements receive an order by clause.

TUNE_STMT=<flags>

The following flags are available:

1: All statements receive an ORDER BY clause (corresponds to TUNE_STMT_ORDER=on).

2: The ROWNUM clause is nested for readfirst/readlast (safe, but slow method).

4: The ROWNUM clause is nested for readgreater/readless (safe, but slow method).

7: The ROWNUM clause is applied outside of the order statement. You can use the settings in the [LOCAL INDEX] section to disable index hints in the subselect statement, >> [LOCAL INDEX].

128: An index hint is generated in the statement that includes the keyword "Rule". This causes the Oracle optimizer to process this statement as rule-based. This flag only works up to Oracle version 9.x. As of version 10g, there is no rule-based optimizer, and the hint is ignored.

Flags can be combined. TUNE_STMT=6 would mean that flags 2 und 4 are set.

TUNE_STMT_REL=TABLE_INDEX_flagAllgemein_FirstFlag_ GreaterFlag ; ...

Transfers all table index combinations with special flags, e.g. FI_1_1_F2_G4.

If you start mmba.exe with the parameter -q stmttune, suitable TUNE_STMT_REL settings are determined automatically, >> Aurea CRM Parameters.