Baselines don't know about PHV.
The internal plan id they have to reproduce is phv2 from
v$/dba_hist_sql_plan.other_xml
There are some features which may result in different PHV but same PHV2, e.g.
Different internal temp table names from materialized subqueried springs to
mind.
Check the PHV2 of the original plan.
Some more information on the specifics of the execution plan would be helpful.
Cheers
Dominic
Sent from my Windows Phone
________________________________
From: fmhabash@xxxxxxxxx<mailto:fmhabash@xxxxxxxxx>
Sent: 11/07/2016 15:04
To: Oracle-L Group<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: dbms_spm.load_plans_from_sqlset is loading wrong xplan into the
baseline
Using RDBMS EE 11024, I created an STS, populated it from AWR using a single
SQL_ID with its target plan hash value (PHV). I then loaded it into a baseline.
When I examine the baseline, It is not reporting the PHV in the STS. It is
showing some other PHV that I see nowhere in the cache or AWR. Not sure where
it came from.
You can see below, the STS has PHV 1012407739 while baseline has 3798909502.
Any idea why?
select SQLSET_NAME , sql_id, PLAN_HASH_VALUE FROM DBA_SQLSET_statements where
sqlset_name = 'fh_rollforward_rpt_070916'
SQLSET_NAME SQL_ID PLAN_HASH_VALUE
------------------------------ ------------- ---------------
fh_rollforward_rpt_070916 7sqajvpb6nbu4 1012407739
set serveroutput on
declare
my_integer pls_integer;
begin
my_integer := dbms_spm.load_plans_from_sqlset (
sqlset_name => 'fh_rollforward_rpt_070916',
basic_filter => 'sql_id=''7sqajvpb6nbu4'' and plan_hash_value=''1012407739''',
sqlset_owner => 'SYS',
fixed => 'YES',
enabled => 'YES');
DBMS_OUTPUT.PUT_line(my_integer);
end;
/
select sql_handle, plan_name, enabled, accepted, fixed,REPRODUCED from
dba_sql_plan_baselines where sql_handle='SQL_f882ed7d0f4c8b06'
SQL_HANDLE PLAN_NAME ENA ACC FIX REP
------------------------------ ------------------------------ --- --- --- ---
SQL_f882ed7d0f4c8b06 SQL_PLAN_gj0rdgn7nt2s62ada8df3 YES YES YES YES
select * from table(
dbms_xplan.display_sql_plan_baseline(
sql_handle=>'SQL_f882ed7d0f4c8b06',
format=>'all'));
Plan name: SQL_PLAN_gj0rdgn7nt2s62ada8df3 Plan id: 718966259
Enabled: YES Fixed: YES Accepted: YES Origin: MANUAL-LOAD
--------------------------------------------------------------------------------
Plan hash value: 3798909502
----------------------------------------
Thank you
