Upgrading shortly to 12.1.0.2 from 11.2.0.4. Full d&t pack
In testing in 12 we found a query that has marked regressed performance and in testing its the plan that has changed, wrong index being selected. Full stats gathered in 12 on the objects. Setting optimizer features to 11.2.0.4 gets it to work but we dont want to use that if possible so we want to use baselines as we may have a canned piece of SQL coming in and if we hit an issue than a baseline will be least intrusive fix as we have full tuning sets for all SQL.
The SQL_ID that is problematic here is in a stored procedure, the proc takes in a date and passes out a ref cursor. I create the baseline with the good hash, I can see it associated with the ID.
Rerun the SQL, still takes bad plan.
optimizer_use_sql_plan_baselines is TRUE
Flushed the shared pool in effort to force the parse, Go to EM, sql plan control, baselines, my baseline is there, the plan is the good one, I set it to FIXED. Still wont take,
When SQL is running, I can navigate in EM to the SQL, plan control and it sees the baseline there so its definitely associated with it.
Anyone offer suggestion why, Ive used baselines many times before, albeit not in 12, so am I missing something?