10gR2 new hint OPT_PARAM
OPT_PARAM是10gR2引入的一个新的hint
使用比较简单，opt_param(<parameter_name> [,] <parameter_value>)
其效果相当于 alter session set parameter_name =
对于隐藏参数同样有效，举例如下：
SQL> show parameter optimizer
NAME TYPE VALUE
———————————— ———– ——————————
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.2
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
SQL> select object_type,count(*) from test group by object_type;
34 rows selected.
Execution Plan
———————————————————-
Plan hash value: 1435881708
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 34 | 204 | 34915 (3)| 00:06:59 |
| 1 | HASH GROUP BY | | 34 | 204 | 34915 (3)| 00:06:59 |
| 2 | TABLE ACCESS FULL| TEST | 9999K| 57M| 34147 (1)| 00:06:50 |
—————————————————————————
SQL> select /*+ opt_param(‘_gby_hash_aggregation_enabled’,'false’) */ object_type,count(*) from test group by object_type;
34 rows selected.
Execution Plan
———————————————————-
Plan hash value: 2603667166
—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 34 | 204 | 34915 (3)| 00:06:59 |
| 1 | SORT GROUP BY | | 34 | 204 | 34915 (3)| 00:06:59 |
| 2 | TABLE ACCESS FULL| TEST | 9999K| 57M| 34147 (1)| 00:06:50 |
—————————————————————————
