OPT_PARAM Hint in oracle

The OPT_PARAM hint in oracle lets you set an initialization parameter for the duration of the current query only. As per oracle documentation following parameters supports with OPT_PARAM hint.

  • OPTIMIZER_DYNAMIC_SAMPLING
  • OPTIMIZER_INDEX_CACHING
  • OPTIMIZER_INDEX_COST_ADJ
  • OPTIMIZER_SECURE_VIEW_MERGING
  • STAR_TRANSFORMATION_ENABLED

I have tested some other parameters also and found working with OPT_PARAM  hint. Below is example for OPTIMIZER_FEATURES_ENABLE initialization parameter. Current value set for optimizer_features_enable parameter is 12.1.0.2. I will add OPT_PARAM hint for this parameter with value of 11.2.0.4 in the query.

SQL> show parameter optimizer_features_enable

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_features_enable            string      12.1.0.2
SQL>
SQL>
SQL> explain plan for
  2  SELECT * FROM TEST_TAB_1 WHERE object_name='TEST_TAB_1';

Explained.

SQL> select * from table(dbms_xplan.display(format => 'ADVANCED'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------

Plan hash value: 3316545227

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |   109 | 12644 | 26727   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB_1 |   109 | 12644 | 26727   (1)| 00:00:02 |
--------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "TEST_TAB_1"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('12.1.0.2')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

Outline Data from the above execution plan shows that optimizer has used value “12.1.0.2” for OPTIMIZER_FEATURES_ENABLE parameter. Now I am going to generate the execution plan this query with value of “11.2.0.4” for OPTIMIZER_FEATURES_ENABLE parameter.

SQL> explain plan for
  2  SELECT /*+ opt_param('optimizer_features_enable','11.2.0.4')*/ * FROM TEST_TAB_1 WHERE object_name='TEST_TAB_1';

Explained.

SQL> select * from table(dbms_xplan.display(format => 'ADVANCED'));

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------

Plan hash value: 3316545227

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |   109 | 12644 | 26727   (1)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| TEST_TAB_1 |   109 | 12644 | 26727   (1)| 00:00:02 |
--------------------------------------------------------------------------------

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

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "TEST_TAB_1"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('12.1.0.2')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

The above generated execution plan shows that optimizer has used 11.2.0.4 value for OPTIMIZER_FEATURES_ENABLE parameter.

Conclusion:
It is very riskily to change initialization parameter at system level for the sake of one query. By using this hint we can tell optimizer to set initialization parameter for the duration of the query only. So, this hint plays very important role in Query Tuning.

OPT_PARAM Hint in oracle
Tagged on:     

4 thoughts on “OPT_PARAM Hint in oracle

  • September 22, 2015 at 4:34 pm
    Permalink

    Thanks for update. Still it seems plan hash value not changed.

    Reply
  • May 16, 2018 at 11:10 am
    Permalink

    Can we use ALTER SYSTEM SET “_fix_control”=’12914055:OFF’; in 12.1.0.2

    Reply
    • July 23, 2018 at 1:59 pm
      Permalink

      Instead of setting the above parameter, you can set the performance patches for 12.1.0.2 version released by oracle support.

      Reply
  • May 16, 2018 at 11:11 am
    Permalink

    or it canin session level ALTER SESSION SET “_fix_control”=’12914055:OFF’; in 12.1.0.2

    Reply

Leave a Reply