Re: OPT_PARAM

From: LS Cheng <exriscer_at_gmail.com>
Date: Tue, 24 Nov 2009 11:14:00 +0100
Message-ID: <6e9345580911240214q70b99da0p61889aa4425d134f_at_mail.gmail.com>

So basically it does not work for optimizer_features_enable?

Does anyone know if there is a list which contains parameters supported by OPT_PARAM :-? Thanks!

On Tue, Nov 24, 2009 at 8:51 AM, Dion Cho <ukja.dion_at_gmail.com> wrote:

> 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_at_yahoo.co.uk>
>
> 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_at_gmail.com>* wrote:
>>
>>
>> From: LS Cheng <exriscer_at_gmail.com>
>> Subject: OPT_PARAM
>> To: "Oracle Mailinglist" <oracle-l_at_freelists.org>
>> 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
>>
>>
>>
>>
>>
>>
>>
>>
>

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

Received on Tue Nov 24 2009 - 04:14:00 CST