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