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> 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> > > 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 >> >> >> >> >> >> >> >> >