Re: OPT_PARAM
- From: LS Cheng <exriscer@xxxxxxxxx>
- To: hrishy <hrishys@xxxxxxxxxxx>
- Date: Tue, 24 Nov 2009 13:11:07 +0100
Errr read again my original post, I said that in outline section you do see
the optimizer being changed however later on it still uses 11.1.0.7
optimizer!
I also know it does not work because my query runs lightning fast with 9.2.0
optimizer however with OPT_PARAM forcing the optimizer it is still slow so I
know the hint is being ignored. If I do allter session set
optimizer_features_
enable = '9.2.0' query is fast as wellso clearly the hint is not working
Thanks!
On Tue, Nov 24, 2009 at 1:07 PM, hrishy <hrishys@xxxxxxxxxxx> wrote:
> Hi
>
> I tested it in 11g and it works for me
>
> select /*+ opt_param('optimizer_features_enable', '9.2.0')
> gather_plan_statistics */
> hash_value,count(*)
> from STATS$SQLTEXT
> group by hash_value
>
> /
> select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED LAST'));
>
> select /*+ opt_param('optimizer_features_enable', '9.2.0')
> gather_plan_statistics */
> hash_value,count(*) from STATS$SQLTEXT
>
> group by hash_value
> Plan hash value: 1660368011
>
> ------------------------------------------------------------------------------------------
>
> | Id | Operation | Name | Rows | Bytes | Cost
> (%CPU)| Time |
>
> ------------------------------------------------------------------------------------------
> | 0 | SELECT STATEMENT | | | | 19
> (100)| |
> | 1 | HASH GROUP BY | | 1565 | 10955 | 19
> (11)| 00:00:01 |
> | 2 | INDEX FAST FULL SCAN| STATS$SQLTEXT_PK | 7305 | 51135 | 17
> (0)| 00:00:01 |
>
> ------------------------------------------------------------------------------------------
> Query Block Name / Object Alias (identified by operation id):
> -------------------------------------------------------------
> 1 - SEL$1
> 2 - SEL$1 / STATS$SQLTEXT@SEL$1
> Outline Data
> -------------
> /*+
> BEGIN_OUTLINE_DATA
> IGNORE_OPTIM_EMBEDDED_HINTS
> OPTIMIZER_FEATURES_ENABLE('9.2.0')
> DB_VERSION('11.1.0.6')
>
>
>
> --- On *Tue, 24/11/09, LS Cheng <exriscer@xxxxxxxxx>* wrote:
>
>
> From: LS Cheng <exriscer@xxxxxxxxx>
> Subject: Re: OPT_PARAM
> To: "Dion Cho" <ukja.dion@xxxxxxxxx>
> Cc: hrishys@xxxxxxxxxxx, "Oracle Mailinglist" <oracle-l@xxxxxxxxxxxxx>
> Date: Tuesday, 24 November, 2009, 10:14
>
>
> 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@xxxxxxxxx<http://uk.mc237.mail.yahoo.com/mc/compose?to=ukja.dion@xxxxxxxxx>
> > 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@xxxxxxxxxxx<http://uk.mc237.mail.yahoo.com/mc/compose?to=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<http://uk.mc237.mail.yahoo.com/mc/compose?to=exriscer@xxxxxxxxx>
>>> >* wrote:
>>>
>>>
>>> From: LS Cheng
>>> <exriscer@xxxxxxxxx<http://uk.mc237.mail.yahoo.com/mc/compose?to=exriscer@xxxxxxxxx>
>>> >
>>> Subject: OPT_PARAM
>>> To: "Oracle Mailinglist"
>>> <oracle-l@xxxxxxxxxxxxx<http://uk.mc237.mail.yahoo.com/mc/compose?to=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:
