Re: OPT_PARAM

Unlike the document, many other optimizer parameters are controlled by
OPT_PARAM hint.

My assumption:
OPT_PARAM might work in the query block level as many other hints. So some
parameters which should be applied at the statement level(like
optimizer_features_enable) would not work with OPT_PARAM hint. It would be
by design, not mistake by developers.


================================
Dion Cho - Oracle Performance Storyteller

http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
http://dioncho.blogspot.com (japanese)
http://ask.ex-em.com (q&a)
================================


2009/11/24 hrishy <hrishys@xxxxxxxxxxx>

> Hi
>
>
> The opt_parm hint is valid
> OPTIMIZER_DYNAMIC_SAMPLING,
> OPTIMIZER_INDEX_CACHING,
> OPTIMIZER_INDEX_COST_ADJ,
> OPTIMIZER_SECURE_VIEW_MERGING, and
> STAR_TRANSFORMATION_ENABLED
>
> hence your hint is silently ignored as it is inavlid.
>
> You might want to try
> alter session set "optimizer_features_enable"= '9.2.0';
>
>
>
>
> --- On *Tue, 24/11/09, LS Cheng <exriscer@xxxxxxxxx>* wrote:
>
>
> From: LS Cheng <exriscer@xxxxxxxxx>
> Subject: OPT_PARAM
> To: "Oracle Mailinglist" <oracle-l@xxxxxxxxxxxxx>
> Date: Tuesday, 24 November, 2009, 7:14
>
> 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
>
>
>
>
>
>
>
>

Other related posts: