Re: OPT_PARAM
- From: Dion Cho <ukja.dion@xxxxxxxxx>
- To: hrishys@xxxxxxxxxxx
- Date: Tue, 24 Nov 2009 16:51:13 +0900
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:
