Re: OPT_PARAM

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: