 |
|
Oracle opt_param SQL hint tips
Oracle Tips by Burleson Consulting |
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>).
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) */
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
