Updated this other thread but then moderators locked it.
991149
.
In any case, here is the issue that we are trying to fix with SQL Plan Baselines. Oracle ver 11.2.0.2 on AIX.
.
We have a query that is fine when it uses the index, however oracle occasionally thinks a table access full is the best way to get the data. This brings our system to a grinding halt. Probably due to a bind variable peek issue - but want to try and fix it using SQL Plan Baselines (rather than dropping histogram for the column).
.
Parameters are set to:
optimizer_capture_sql_plan_baselines FALSE
optimizer_use_sql_plan_baselines TRUE
.
So we loaded the 1 (good) plan from cursor cache (this automatically sets to "Enabled:YES", "Accepted:YES") and then we manually set the Baseline to "Fixed:YES" and AutoPurge:NO". However, it seems as if oracle doesn't even see this baseline. When checking v$sql SQL_PLAN_BASELINE column for the given sql_id being executed, it is always null - but it is usually using the good exection plan. But we have also seen oracle choose the bad execution plan again - even when baselines were in place (our emergency fix was to drop the histogram).
.
Question - Do we need to add this "bad" exection plan to the SQL PLan Baseline and mark it as "Enabled:NO" ?