RE: dbms_spm.load_plans_from_sqlset is loading wrong xplan into thebaseline

From: Dominic Brooks <dombrooks@xxxxxxxxxxx>
To: "fmhabash@xxxxxxxxx" <fmhabash@xxxxxxxxx>, Oracle-L Group <oracle-l@xxxxxxxxxxxxx>
Date: Mon, 11 Jul 2016 17:59:54 +0100

Correct, that is what I am saying.

Prior to 12c, there are no plan details stored in the baseline other than the
target PHV2.

When you run that api, you get an explain plan of the stored query with the
application of the hints in the baseline.

So for example if that baseline contains a hint for an index you've dropped,
then the call to display_sql_plan_baseline will not show the index access, etc.

Not sure I've explained that very well but limited by mobile...

Bottom line is that, from the details posted we can't tell whether it is that:

1. Baseline plan is reproducible with the right PHV2 but a different phv purely
because of a reason I mentioned above;

2. Baseline plan not reproducible because of invalid hint due to index change
(for example)

3. Baseline plan not reproducible because of bug (I.e. Rare but outline hints
not sufficient to reproduce target phv2)

Cheers
Dominic

Sent from my Windows Phone
________________________________
From: fmhabash@xxxxxxxxx<mailto:fmhabash@xxxxxxxxx>
Sent: ‎11/‎07/‎2016 17:09
To: Dominic Brooks<mailto:dombrooks@xxxxxxxxxxx>; Oracle-L
Group<mailto:oracle-l@xxxxxxxxxxxxx>
Subject: RE: dbms_spm.load_plans_from_sqlset is loading wrong xplan into
thebaseline

Thanks.
I realize the difference between plan_id and phv. The
‘display_sql_plan_baseline’ api shows both. This is how we verify the baseline
contains the correct phv. Are you saying this is not the case?

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

From: Dominic Brooks

RE: dbms_spm.load_plans_from_sqlset is loading wrong xplan into thebaseline

Other related posts: