Oracle opt_param SQL hint tips Oracle Database Tips by Donald Burleson

Staring in Oracle 10g release 2, Oracle introduced the opt_param SQL hint, without documentation.  It appears that the opt_param hint is very similar to the "alter session" method for changing parameters, but it only applies to that specific SQL statement.

Troubleshooting tip!  For testing, you can quickly test the effect of another optimizer parameter value at the query level without using an 'alter session' command, using the new opt_param SQL hint:

select /*+ opt_param('optimizer_mode','first_rows_10') */ col1, col2 . . .

select /*+ opt_param('optimizer_index_cost_adj',20) */ col1, col2 . .

Syntax: opt_param(<parameter_name> [,] <parameter_value>).

Note: The opt_param hint does not work with all Oracle parameters, and it is largely undocumented.  The following parameters work with opt_param, but there many be many more:

• hash_join_enabled
• optimizer_dynamic_sampling
• optimixer_features_enable
• optimizer_index_caching
• optimizer_index_cost_adj
• optimizer_mode
• optimizer_secure_view_merging
• star_transformation_enabled

For example, the SQL below turn-off hash_join_enabled (to forced a nested loop, usually), but only for that SQL statement:

select /*+ opt_param('hash_join_enabled','false') */
dept_no,
emp_name,
empno
from
emp e, dept d
where e.ename=d.dname;

The opt_param hint is also useful for testing the effect of global parameters such as optimizer_mode and optimizer_cost_model.

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

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with almost 800 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get access to the code depot of Oracle tuning scripts.