opt_param

At 10g without alter session you can change optimizer parameter with opt_param

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



The OPT_PARAM hint lets you set an initialization parameter for the duration of the current query only. This hint is valid only for the following parameters: OPTIMIZER_DYNAMIC_SAMPLING, OPTIMIZER_INDEX_CACHING, OPTIMIZER_INDEX_COST_ADJ, OPTIMIZER_SECURE_VIEW_MERGING, and STAR_TRANSFORMATION_ENABLED. For example, the following hint sets the parameter STAR_TRANSFORMATION_ENABLED to TRUE for the statement to which it is added:

SELECT /*+ OPT_PARAM('star_transformation_enabled' 'true') */ * FROM ... ;
Parameter values that are strings are enclosed in single quotation marks. Numeric parameter values are specified without quotation marks