I'm working on an 11.2.0.3 database that was just upgraded from 10.2 and struggling to understand why SQL Plan Baselines that we've put in place aren't forcing the optimizer to pick the associated plan. From everything I can see, the baseline should be forcing the optimizer to use the plan_hash_value 936892606. But everything I see says that Oracle is actually using a different plan 1071343316. And I can't explain how the optimizer is discounting the baseline.
If I look in SQL_PLAN_BASELINES, I see that there is a baseline for a particular SQL statement
select *
from dba_sql_plan_baselines a,
dba_hist_sqltext b
where dbms_lob.compare(a.sql_text, b.sql_text) = 0
and sql_id = <<sql id>>
sql_handle = SQL_21e38740216b4aa2
created = May 27 6:00 PM
last_modified = May 28 6:20 AM
last_Executed = <<null>>
last_verified = <<null>>
enabled, accepted, fixed = YES
reproduced = NO
I've got a fixed baseline for the particular SQL statement. But it doesn't appear that this baseline has ever been used.
The SQL statement in question is part of a batch load process that runs overnight. As expected, it ran this morning. And I can see from the AWR that all 4 executions of that SQL statement today had a plan_hash_value of 1071343316.
select trunc(begin_interval_time) dt, plan_hash_value, (case when sum(buffer_gets_delta) = 0 then 0 else round(sum(disk_reads_delta)*100/sum(buffer_gets_delta), 2) end) pct_gets_physical, sum(executions_delta), sum(cpu_time_delta/1000000), sum(elapsed_time_delta/1000000), sum(buffer_gets_delta), sum(px_servers_execs_delta), sum(disk_reads_delta),
sum(iowait_delta/1000000), sum(clwait_delta/1000000), sum(apwait_delta/1000000), sum(ccwait_delta/1000000), sum(direct_writes_delta), sum(plsexec_time_delta/1000000),
sum(javexec_time_delta/1000000), sum(physical_read_bytes_delta), sum(physical_write_bytes_delta)
from dba_hist_sqlstat
left outer join dba_hist_snapshot using (snap_id,dbid,instance_number)
join dba_hist_sqltext using (sql_id)
where sql_id = <<sql id>>
group by trunc(begin_interval_time), plan_hash_value
order by trunc(begin_interval_time) desc;
If I look at the plan from the cursor cache,
select *
from table( dbms_xplan.display_cursor(<<sql id>> ));
the only thing in the Note section is talking about dynamic sampling, nothing indicates that the baseline was used
Note
-----
- dynamic sampling used for this statement (level=6)
If I look at the baseline
select *
from table(dbms_xplan.display_sql_plan_baseline(
sql_handle => 'SQL_21e38740216b4aa2'));
I see that the plan in the baseline is 936892606
------------------------------------------------------------------
Plan name: SQL_PLAN_23sw780hqqkp291fd6c6b Plan id: 2449304683
Enabled: YES Fixed: YES Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 936892606
The optimizer is set to use baselines but not capture them
NAME TYPE VALUE
-------------------------------------------------- ----------- ----------------------------------------------------------------------------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
It seems highly likely that I'm just overlooking something relatively obvious here. But I'm at a loss to figure out why the optimizer isn't using the baseline. I'm hoping someone can point out where I've gone off the rails here.
Justin