SQL Optimization Techniques - Troubleshooting Oracle Performance - page 365

Then for each user requiring a particular configuration, you insert one row in the configuration table for each
initialization parameter. For example, the following two INSERT statements change and define two parameters at the
session level when the user named Alberto logs in:

INSERT INTO exec_env_conf VALUES ('ALBERTO', 'optimizer_mode', 'first_rows_10')

INSERT INTO exec_env_conf VALUES ('ALBERTO', 'optimizer_dynamic_sampling', '0')

Of course, you could also define the trigger for a single schema or perform other checks based, for example,
on the userenv context.

SQL Statement Level
The execution environment at the SQL statement level is changed through the initialization parameter hints. Because
hints are used, the behavior and properties of hints previously described apply in this case as well.
Not all initialization parameters making up the query optimizer configuration can be changed at the SQL
statement level. Table 11-3 summarizes which parameters and values have corresponding initialization parameter
hints in order to achieve the same configuration at the SQL statement level. Note that for some initialization
parameters (for example, cursor_sharing ), not all values can be set with hints.

Table 11-3. Hints That Change the Query Optimizer Configuration at the SQL Statement Level

Initialization Parameter Hint

cursor_sharing=exact cursor_sharing_exact

optimizer_dynamic_sampling=x dynamic_sampling(x)

optimizer_features_enable=x optimizer_features_enable('x')

optimizer_features_enable not set optimizer_features_enable(default)

optimizer_index_caching=x opt_param('optimizer_index_caching' x)

optimizer_index_cost_adj=x opt_param('optimizer_index_cost_adj' x)

optimizer_mode=all_rows all_rows

optimizer_mode=first_rows first_rows

optimizer_mode=first_rows_x first_rows(x)

optimizer_secure_view_merging=x opt_param('optimizer_secure_view_merging' 'x')

optimizer_use_pending_statistics=x opt_param('optimizer_use_pending_statistics' 'x')

result_cache_mode=manual no_result_cache

result_cache_mode=force result_cache

star_transformation_enabled=x opt_param('star_transformation_enabled' 'x')