Hints for Selecting the Custom Plan or Generic Plan

Description

For query statements and DML statements executed in PBE mode, the optimizer generates a custom plan or generic plan based on factors such as rules, costs, and parameters. You can use the hint of use_cplan or use_gplan to specify the plan to execute.

Syntax

• Specify that a custom plan is used.

  1

  use_cplan

• Specify that a generic plan is used.

  1

  use_gplan

• For SQL statements that are executed in non-PBE mode, setting this hint does not affect the execution mode.
• This hint has a higher priority than cost-based selection and the plan_cache_mode parameter. That is, this hint does not take effect for statements where plan_cache_mode cannot forcibly select an execution mode.

Examples

Forcibly use the custom plan:

1
2
3

create table t (a int, b int, c int);
prepare p as select /*+ use_cplan */ * from t where a = $1;
explain execute p(1);

In the following plan, the filtering condition is the actual value of the input parameter, that is, the plan is a custom plan.

Forcibly use the generic plan:

1
2
3

deallocate p;
prepare p as select /*+ use_gplan */ * from t where a = $1;
explain execute p(1);

In the following plan, the filtering condition is the input parameter to be added. That is, the plan is a generic plan.