OPT_PARAM

From: LS Cheng <exriscer_at_gmail.com>
Date: Tue, 24 Nov 2009 08:14:31 +0100
Message-ID: <6e9345580911232314h67c2405dke0c99786558f1c98_at_mail.gmail.com>

Hi

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

I am testing it and it seems that it gets ignored

enable event 10132

SELECT
/*+ opt_param('optimizer_features_enable', '9.2.0') */ x, y, z
 FROM v_lsc
WHERE x = 'S' OR y_date > to_date(:V1, 'MM/DD/YY HH24:MI:SS');

Content of other_xml column

---

  db_version     : 11.1.0.7
  parse_schema   : SIMO
  plan_hash      : 2064770634
  plan_hash_2    : 1803173905

Peeked Binds

---

  Bind variable information
    position=1
    datatype(code)=1
    datatype(string)=VARCHAR2(32)
    char set id=31
    char format=1
    max length=32
    value=11/20/09 14:01:27
  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS
*      OPTIMIZER_FEATURES_ENABLE('9.2.0')*

..............

Optimizer state dump:
..............

*optimizer_features_enable = 11.1.0.7*

Although in outline section it sees 9.2.0 optimizer afterwards it still uses 11.1.0.7

Thanks

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 24 2009 - 01:14:31 CST