Overview

This document explains parameters related to the Optimizer.

Method

1. CURSOR_SHARING

This parameter sets whether to convert literal values in SQL conditions into variables before parsing, so that cursors can be shared.

It is effective when there are many literal queries and SQL and execution plans are being cached out of memory.

If there is a heavy hard parsing load due to literal queries, you can temporarily reduce the hard parsing load by using the FORCE option.

However, since all literals will be converted to bind variables and the execution plan may change, and histogram information on columns cannot be used, use with caution. After modifying the application to use bind variables, change the option to EXACT.

Detailed information for the CURSOR_SHARING parameter is as follows.

2. OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

This parameter sets whether to enable capturing and storing repeatedly entered SQL.

Detailed information for the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter is as follows.

3. OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES_DURATION

This parameter sets the time so that the same SQL entered repeatedly within the set time is not captured.
Detailed information for the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES_DURATION parameter is as follows.

4. OPTIMIZER_LOG_OUTLINE

This parameter sets whether to log the Plan Outline.

Detailed information for the OPTIMIZER_LOG_OUTLINE parameter is as follows.

5. OPTIMIZER_MODE

This parameter sets the mode of the SQL query optimizer.

Detailed information for the OPTIMIZER_MODE parameter is as follows.

Determines the operation of the Optimizer's cost calculation, and there are five modes as follows.

• FIRST_ROWS_1
• FIRST_ROWS_10
• FIRST_ROWS_100
• FIRST_ROWS_1000
• ALL_ROWS

FIRST_ROWS_n selects the optimal plan for reading n rows, while ALL_ROWS selects the optimal plan for reading all rows in the result.

Even if the number of rows in the query result is greater than n, if the client only fetches n rows change to FIRST_ROWS_n mode.
 

6. OPTIMIZER_TRACE

This parameter sets whether to use the optimizer Trace Dump.

Detailed information for the OPTIMIZER_TRACE parameter is as follows.

7. OPTIMIZER_USE_INVISIBLE_INDEXES

This parameter sets whether to use invisible indexes.

Detailed information for the OPTIMIZER_USE_INVISIBLE_INDEXES parameter is as follows.

8. OPTIMIZER_USE_SQL_PLAN_BASELINES

This parameter sets whether to use the baseline captured in 1 when SQL is entered.

Detailed information for the OPTIMIZER_USE_SQL_PLAN_BASELINES parameter is as follows.

9. OPTIMIZER_USE_SQL_PROFILES

This parameter sets whether to use a specific profile.

If enabled, it searches for and applies any defined SQL Profile for all executed SQL.

Additionally, the "LPN Number Information" and the applied SQL profile name will be specified in the SQL plan output by autotrace in the "Note" section.

Detailed information for the OPTIMIZER_USE_SQL_PROFILES parameter is as follows.