opt_param hint used in Oracle database

We use the opt_param hint parameter for setting the parameter for SQL Statement as we use ALTER SESSION command for setting the parameter for complete session.

Syntax:

select /*+ opt_param(<parameter_name> [,] <parameter_value>) */

Example:

 select /*+ opt_param('optimizer_mode','first_rows_10') */  select /*+ opt_param('_optimizer_cost_model','io') */  select /*+ opt_param('optimizer_index_cost_adj',20) */  select /*+ opt_param('optimizer_index_caching',20) */  select /*+ opt_param('optimizer_features_enable','11.2.0.4')*/    -- Example of using multiple in single command   SELECT /*+ OPT_PARAM('_always_semi_join' 'off')        OPT_PARAM('_b_tree_bitmap_plans' 'false')        OPT_PARAM('query_rewrite_enabled' 'false')        OPT_PARAM('_new_initial_join_orders' 'false')        OPT_PARAM('optimizer_dynamic_sampling' 1)        OPT_PARAM('optimizer_index_cost_adj' 1) */
This post is ad-supported
​