OPT_PARAM and OPTIMIZER_FEATURES_ENABLE

Here’s a question appeared on the oracle-l mailing list recently:

Does anyone know if opt_param hint works with optimizer_features_enable in 11.1.0.7?

My first thought was “it should work”. Even though documentation doesn’t confirm that OPT_PARAM works for many CBO-related parameters, usually it works fine. I’ve written a case to check this out for OPTIMIZER_FEATURES_ENABLE, but it revealed that OPT_PARAM is useless in such case, because effectively it can’t change OPTIMIZER_FEATURES_ENABLE session parameter (thought it tries).

select * from v$version;

drop table t cascade constraints purge;
create table t as select * from all_objects where rownum <= 5000;

explain plan for
select object_type, count(*)
  from t
 group by object_type;
select * from table(dbms_xplan.display(null,null,'outline'));

explain plan for
select /*+ opt_param('optimizer_features_enable' '9.2.0') */
    object_type, count(*)
  from t
 group by object_type;
select * from table(dbms_xplan.display(null,null,'outline'));

I’ve used OUTLINE as a third parameter of dbms_xplan to get the hints Oracle has “put” to the query during optimization phase. This is what I’ve got:

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 47235625

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  5121 | 56331 |    22   (5)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |  5121 | 56331 |    22   (5)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |  5121 | 56331 |    21   (0)| 00:00:01 |
---------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Note
-----
   - dynamic sampling used for this statement (level=2)

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
Plan hash value: 47235625

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  5121 | 56331 |    22   (5)| 00:00:01 |
|   1 |  HASH GROUP BY     |      |  5121 | 56331 |    22   (5)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T    |  5121 | 56331 |    21   (0)| 00:00:01 |
---------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      USE_HASH_AGGREGATION(@"SEL$1")
      FULL(@"SEL$1" "T"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      OPT_PARAM('_fix_control' '4488689:1 4308414:1 4569940:1 4631959:1
              4519340:1 4550003:1 4554846:1 4602374:1 4584065:1 4545833:1 4611850:1
              4663698:1 4663804:1 4666174:1 4567767:1 4556762:15 4728348:1 4708389:1
              4175830:1 4752814:1 4583239:1 4386734:1 4887636:1 4545802:1 4605810:1
              4704779:1 4900129:1 4924149:1 4663702:1 4878299:1 4658342:1 4881533:1
              4676955:1 4273361:1 4967068:1 4705343:1 4716096:1 4722900:1 4615392:1
              5096560:1 4134994:1 4904890:1 5104624:1 5014836:1 4768040:1 4600710:1
              5129233:1 4595987:1 4908162:1 5139520:1 5084239:1 5099909:1 5240607:1
              5195882:1 5220356:1 5263572:1 5385629:1 5302124:1 5391942:1 5384335:1
              5482831:1 4158812:1 5387148:1 5383891:1 5466973:1 5396162:1 5394888:1
              5395291:1 5236908:1 5509293:1 5449488:1 5567933:1 5570494:1 5288623:1
              5505995:1 5505157:1 5112460:1 5554865:1 5112260:1 5112352:1 5547058:1
              5618040:1 5585313:1 5547895:1 5634346:1 5620485:1 5483301:1 5657044:1
              5694984:1 5650477:1 5611962:1 4279274:1 5741121:1 5714944:1 5391505:1
              5762598:1 5578791:1 5259048:1 5882954:1 5707608:1 5891471:1 5884780:1
              5680702:1 5371452:1 5838613:1 5949981:1 5624216:1 5741044:1 5976822:1
              6006457:1 5872956:1 5923644:1 5844495:1 4168080:1 6020579:1 5996801:1
              5593639:1 6133948:1 3151991:1 6146906:1 6239909:1 6267621:1 6279918:1
              6141818:1 6151963:1 6251917:1 6282093:1 6119510:1 6119382:1 5944076:1
              5406763:1 6070954:1 6282944:1 6138746:1 6082745:1 3426050:1 599680:1
              6062266:1 6087237:1 6122894:1 6377505:1 5893768:1 6163564:1 6073325:1
              6188881:1 6007259:1 6239971:1 6042205:1 6051211:1 6434668:1 6438752:1
              5936366:1 6439032:1 6438892:1 6006300:1 5947231:1 6365442:1 6239039:1
              6502845:1 6913094:1 6029469:1 5919513:1 6057611:1 6469667:1 6368066:1
              6329318:1 6656356:1 4507997:1 6671155:1 6694548:1 6688200:1 6612471:1
              6326934:1 6714199:1 6681545:1 6748058:1 6167716:1 6674254:1 6468287:1
              6503543:1 6766962:1 6120483:1 6670551:1 6771838:1 6530596:1 6778642:1
              6699059:1 6376551:1 6429113:1 6782437:1 6776808:1 6765823:1 6768660:1
              6782665:1 6610822:1 6514189:1 6818410:1 6827696:1 6773613:1 5902962:1
              6956212:1 3056297:1 6904146:1 6221403:1 6845871:1 5468809:1 6917633:1
              6994194:1 6951776:1 5648287:3 7132036:1 6980350:1 5199213:1 7138405:1
              7148689:1 6820988:1 7032684:1 7155968:1 7127980:1 6982954:1 7241819:1
              6897034:1 7236148:1 7298570:1 7249095:1 7314499:1 7324224:1 7289023:1
              7237571:1 7116357:1 7345484:1 7375179:1 5897486:1 6774209:1 7306637:1
              6451322:1 7208131:1 7388652:1 7127530:1 6751206:1 6669103:1 7430474:1
              6990305:1 7043307:1 3120429:1 6838105:1 6769711:1 6528872:1 7295298:1
              5922070:1 7259468:1 6418552:1 4619997:1 6942476:1 6418771:1 7375077:1
              5400639:1 4570921:1 7426911:1 7528216:1 7521266:1 7385140:1 7576516:1
              7576476:1 7165898:1 7263214:1 3320140:1 7555510:1 7613118:1 7597059:1
              7558911:1 5520732:1 7449971:1 3628118:1 4370840:1 7281191:1 7519687:1
              5029592:3 6012093:1 7696414:1 7272039:1 7834811:1 7341616:1 7168184:1
              399198:1 7831070:1 7414637:1 7585456:1 8202421:1 8251486:1 7132684:1
              6972987:1 7199035:1 8243446:1 7650462:1 7592673:1 7718694:1 7534027:1
              7708267:1 5716785:1 7356191:1 7679161:1 7597159:1 7499258:1 8328363:1
              7452863:1 8284930:1 7298626:1 7657126:1 8371884:1 7135745:1 8356253:1
              8323407:1 8289316:1 8447850:1 7675944:1 8355120:1 7176746:1 8442891:1
              8373261:1 7679164:1 8408665:1 8491399:1 8348392:1 8508056:1 8335178:1
              8515269:1 8247017:1 7325597:1 8531490:1 8557992:1 7556098:1 8580883:1')
      OPT_PARAM('_optimizer_try_st_before_jppd' 'true')
      OPT_PARAM('_optimizer_use_feedback' 'true')
      OPT_PARAM('_optimizer_distinct_placement' 'true')
      OPT_PARAM('_and_pruning_enabled' 'true')
      OPT_PARAM('_optimizer_table_expansion' 'true')
      OPT_PARAM('_optimizer_use_cbqt_star_transformation' 'true')
      OPT_PARAM('_optimizer_join_factorization' 'true')
      OPT_PARAM('_connect_by_use_union_all' 'true')
      OPT_PARAM('_optimizer_eliminate_filtering_join' 'true')
      OPT_PARAM('_optimizer_connect_by_elim_dups' 'true')
      OPT_PARAM('_aggregation_optimization_settings' 0)
      OPT_PARAM('_optimizer_distinct_agg_transform' 'true')
      OPT_PARAM('_optimizer_unnest_corr_set_subq' 'true')
      OPT_PARAM('_optimizer_unnest_disjunctive_subq' 'true')
      OPT_PARAM('_optimizer_fast_access_pred_analysis' 'true')
      OPT_PARAM('_optimizer_fast_pred_transitivity' 'true')
      OPT_PARAM('_optimizer_coalesce_subqueries' 'true')
      OPT_PARAM('_bloom_folding_enabled' 'true')
      OPT_PARAM('_optimizer_extended_stats_usage_control' 224)
      OPT_PARAM('_nlj_batching_enabled' 1)
      OPT_PARAM('_optimizer_fkr_index_cost_bias' 10)
      OPT_PARAM('_optimizer_enable_extended_stats' 'true')
      OPT_PARAM('_optimizer_native_full_outer_join' 'force')
      OPT_PARAM('_optimizer_connect_by_combine_sw' 'true')
      OPT_PARAM('_optimizer_enable_density_improvements' 'true')
      OPT_PARAM('_optimizer_improve_selectivity' 'true')
      OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'true')
      OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'simple')
      OPT_PARAM('_optimizer_rownum_bind_default' 10)
      OPT_PARAM('_optimizer_group_by_placement' 'true')
      OPT_PARAM('_optimizer_multi_level_push_pred' 'true')
      OPT_PARAM('_optimizer_distinct_elimination' 'true')
      OPT_PARAM('_px_ual_serial_input' 'true')
      OPT_PARAM('_bloom_pruning_enabled' 'true')
      OPT_PARAM('_first_k_rows_dynamic_proration' 'true')
      OPT_PARAM('_replace_virtual_columns' 'true')
      OPT_PARAM('_px_minus_intersect' 'true')
      OPT_PARAM('_globalindex_pnum_filter_enabled' 'true')
      OPT_PARAM('_gby_hash_aggregation_enabled' 'true')
      OPT_PARAM('_optimizer_connect_by_cost_based' 'true')
      OPT_PARAM('_optimizer_complex_pred_selectivity' 'true')
      OPT_PARAM('_optimizer_star_tran_in_with_clause' 'true')
      OPT_PARAM('_dimension_skip_null' 'true')
      OPT_PARAM('_selfjoin_mv_duplicates' 'true')
      OPT_PARAM('_optimizer_outer_to_anti_enabled' 'true')
      OPT_PARAM('_optimizer_order_by_elimination_enabled' 'true')
      OPT_PARAM('_optimizer_or_expansion' 'depth')
      OPT_PARAM('_optimizer_better_inlist_costing' 'all')
      OPT_PARAM('_optimizer_rownum_pred_based_fkr' 'true')
      OPT_PARAM('_optimizer_filter_pred_pullup' 'true')
      OPT_PARAM('_optimizer_enhanced_filter_push' 'true')
      OPT_PARAM('_optimizer_cbqt_no_size_restriction' 'true')
      OPT_PARAM('_optimizer_join_elimination_enabled' 'true')
      OPT_PARAM('_px_pwg_enabled' 'true')
      OPT_PARAM('_optimizer_transitivity_retain' 'true')
      OPT_PARAM('_optimizer_cost_hjsmj_multimatch' 'true')
      OPT_PARAM('_optimizer_extended_cursor_sharing' 'udo')
      OPT_PARAM('_bloom_filter_enabled' 'true')
      OPT_PARAM('_sql_model_unfold_forloops' 'run_time')
      OPT_PARAM('_optimizer_extend_jppd_view_types' 'true')
      OPT_PARAM('_optimizer_null_aware_antijoin' 'true')
      OPT_PARAM('_optimizer_push_pred_cost_based' 'true')
      OPT_PARAM('_optimizer_join_order_control' 3)
      OPT_PARAM('_optimizer_correct_sq_selectivity' 'true')
      OPT_PARAM('_query_rewrite_setopgrw_enable' 'true')
      OPT_PARAM('_push_join_union_view2' 'true')
      OPT_PARAM('_optimizer_compute_index_stats' 'true')
      OPT_PARAM('_optimizer_dim_subq_join_sel' 'true')
      OPT_PARAM('_mmv_query_rewrite_enabled' 'true')
      OPT_PARAM('_optimizer_join_sel_sanity_check' 'true')
      OPT_PARAM('_optimizer_squ_bottomup' 'true')
      OPT_PARAM('_right_outer_hash_enable' 'true')
      OPT_PARAM('_optimizer_cost_based_transformation' 'linear')
      OPT_PARAM('_local_communication_costing_enabled' 'true')
      OPT_PARAM('_remove_aggr_subquery' 'true')
      OPT_PARAM('optimizer_dynamic_sampling' 2)
      OPT_PARAM('query_rewrite_enabled' 'true')
      OPT_PARAM('_partition_view_enabled' 'true')
      OPT_PARAM('_optimizer_undo_cost_change' '11.2.0.1')
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('9.2.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Note
-----
   - dynamic sampling used for this statement (level=2)

155 rows selected.

Everything is OK with the first plan. The second plan is not what I’ve expected: it contains HASH GROUP BY and says that dynamic sampling was used – both of them were introduced in 10g. Outline Data section is really huge in the 2nd plan. But look at the very end of it: there’s a hint to set OPTIMIZER_FEATURES_ENABLE to the requested 9.2.0 level! Combined with numerous OPT_PARAMs modifying many CBO parameters to the settings which are available in 10g/11g only (for ex., hash aggregation, ORDER BY elimination, etc.) – this is most likely a bug.

Anyway, since OPT_PARAM for OPTIMIZER_FEATURES_ENABLE is not supported, you have no other choice as to use an undocumented hint OPTIMIZER_FEATURES_ENABLE. It works as expected (and BTW shorter :-)):

explain plan for
select /*+ optimizer_features_enable('9.2.0') */
    object_type, count(*)
  from t
 group by object_type;

Explained.

SQL> select * from table(dbms_xplan.display(null,null,'outline'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1476560607

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  5718 | 62898 |    23 |
|   1 |  SORT GROUP BY     |      |  5718 | 62898 |    23 |
|   2 |   TABLE ACCESS FULL| T    |  5718 | 62898 |    12 |
-----------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "T"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('9.2.0')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Note
-----
   - cpu costing is off (consider enabling it)
About these ads

One Trackback

  1. […] 27-OPT_PARAM hint behaviour for optimizer_features_enable Timur Akhmadeev-OPT_PARAM and OPTIMIZER_FEATURES_ENABLE […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 290 other followers