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) */