Help Center/ DataArts Fabric/ Developer Guide/ Performance Tuning/ SQL Tuning/ Hint-based Tuning/ Configuration Parameter Hints
Updated on 2025-09-18 GMT+08:00
View PDF
Share

Configuration Parameter Hints

Function

A hint, or a GUC hint, specifies the value of a configuration parameter when a plan is generated.

Precautions

  • If the configuration parameter set by a hint takes effect at the statement level, the hint must be written in the top-level query instead of the subquery. For UNION, INTERSECT, EXCEPT, and MINUS statements, statement-level GUC hints can be written on any SELECT clause that participates in set calculation, the configuration parameters set by this GUC hint take effect for each SELECT clause involved in set calculation.
  • When a subquery is lifted, all GUC hints on that subquery are discarded.
  • If a configuration parameter is set by both a statement-level GUC hint and a subquery-level GUC hint, the subquery-level GUC hint takes effect in the corresponding subquery, statement-level GUC hints take effect in other subqueries of the statement.

Syntax

set [global]([@block_name] guc_name guc_value)

Parameter Description

  • global indicates that the parameter set by a hint takes effect at the statement level. If global is not specified, the parameter takes effect only in the subquery where the hint is.
  • block_name indicates the block name of the statement block. For details, see block_name.
  • guc_name indicates the name of the configuration parameter specified by a hint.
  • guc_value indicates the value of a configuration parameter specified by a hint.

Currently, GUC hints support only certain configuration parameters, and some configuration parameters cannot be set at the subquery level but only at the statement level. Below is the list of supported parameters.

Table 1 Configuration parameters supported by GUC hints

Parameter

Configurable at Subquery Level

agg_redistribute_enhancement

Yes

best_agg_plan

Yes

cost_model_version

No

cost_param

No

enable_broadcast

Yes

enable_redistribute

Yes

enable_extrapolation_stats

Yes

enable_hashagg

Yes

enable_hashjoin

Yes

enable_nestloop

Yes

from_collapse_limit

Yes

join_collapse_limit

Yes

join_num_distinct

Yes

qual_num_distinct

Yes

query_dop

No

rewrite_rule

No

skew_option

Yes

Examples

Hint the query plan in Examples as follows:

explain
select /*+ set global(query_dop 0) */ i_product_name product_name
...

This hint indicates that the query_dop parameter is set to 0 during the plan generation of the entire statement, meaning SMP adaptation is enabled.

Parent topic: Hint-based Tuning