More tricks with OPT_PARAM

Did you know you can set most parameters for the execution of a single statement without using an Alter Session by using an OPT_PARAM hint? For example, regular parameters (here forcing the storage clause in the query plan):

SQL> select /*+ OPT_PARAM('cell_offload_plan_display' 'always') */ col1 From table1;

and underscore parameters:

SQL>  select /*+ OPT_PARAM('_arch_comp_dbg_scan',2048) */ n_name from nation;

However if you try conflicting settings that set a different value in an inner query block, the value you set in the outermost query block will prevail. In this trvial example _arch_comp_dbg_scan=2048 will prevail:

SQL> select /*+ OPT_PARAM('_arch_comp_dbg_scan',2048) */ n_name from nation
where n_nationkey = (
    select /*+ OPT_PARAM('_arch_comp_dbg_scan',0) */ min(r_regionkey)
    from region where n_name < r_name); 

Another point to remember is that not all parameters get their value from the cursor environment. For example the buffer cache gets the raw value of _serial_direct_read so it cannot be overruled in this way: 

SQL> select /*+ OPT_PARAM('_serial_direct_read', always) */ n_name from nation; 

 will not force a DR scan of the table but an alter session will.

Just one more tool to keep in your toolkit for when you need it.

—
Roger