Question: was the SQL Plan accepted? What does DBA_SQL_PLAN_BASELINES show?
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Mauro Pagano <mauro.pagano@xxxxxxxxx>
Sent: Wednesday, September 28, 2016 1:05:28 PM
To: r.aialavajjala@xxxxxxxxx
Cc: Dominic Brooks; neil_chandler@xxxxxxxxxxx; JBECKSTROM@xxxxxxxxx; Oracle-l;
oracle-db-l
Subject: Re: Usage of Baselines to prevent plan changes
My 2cents, get rid of the custom SQL Profile and create a baseline, then open a
SR with Oracle.
If the SQL doesn't fall into one of those categories that de-activate SPM (for
example, distributed SQL) then SPM must reproduce the plan, and if not Oracle
has ownership of the issue.
That means if the hintset is somehow not enough Oracle has to fix it, it
already happened for USE_CONCAT, UNNEST and MERGE hints.
As to why the good plan is chosen, it's probably because the baseline (or
custom SQL Profile) never fully worked so the few decisions left to the CBO
sometimes lead to the good plan, sometime the bad one :-)
On Wed, Sep 28, 2016 at 12:47 PM, Rajesh Aialavajjala
<r.aialavajjala@xxxxxxxxx<mailto:r.aialavajjala@xxxxxxxxx>> wrote:
Dominic,
Thank you for your reply.
Would this - "baseline hintset not sufficient to reproduce the desired plan" -
also be the cause for why the optimizer chooses the "bad" plan even when
there's a profile in place ? Given of course - that the existence or
application of a profile would not necessarily imply that the "good" plan will
always be chosen.
So - the baseline creation itself was done using the sub-optimal PHV because
the baseline hintset was insufficient - but that raises the question as to why
the "good" plan is chosen 90% of the time with the profile in place ?
Is this a candidate for SPM Tracing ?
Thanks,
--Rajesh
On Wed, Sep 28, 2016 at 12:11 PM, Dominic Brooks
<dombrooks@xxxxxxxxxxx<mailto:dombrooks@xxxxxxxxxxx>> wrote:
Rajesh - I would guess that this is a case where the baseline hintset is not
sufficient to reproduce the desired plan and quite possibly the same reason the
COE SQL profile doesn't work using, presumably, the same hintset
Sent from my Windows Phone
________________________________
From: Rajesh Aialavajjala<mailto:r.aialavajjala@xxxxxxxxx>
Sent: 28/09/2016 16:34
To: neil_chandler@xxxxxxxxxxx<mailto:neil_chandler@xxxxxxxxxxx>
Cc: JBECKSTROM@xxxxxxxxx<mailto:jbeckstrom@xxxxxxxxx>;
Oracle-l<mailto:oracle-l@xxxxxxxxxxxxx>;
oracle-db-l<mailto:oracle-db-l@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: Usage of Baselines to prevent plan changes
Not to try and hijack this thread - but since the subject of this was around
baselines - I thought I would tack on a question as relates to baseline
behavior.
I have a SQL statement that seems to "flop" between 2 PHVs'
good plan_hash_value=>461486239
bad plan_hash_value => 952719881
A baseline was created using the statement "exec
:cnt:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'1hzgfq62vat5h',plan_hash_value=>461486239);"
As of 2016-09-15 11:47 in SBD629:
Plan hash value: 461486239
SQL Tuning Set Name: 1hzgfq62vat5h_461486239
SQL profile "coe_1hzgfq62vat5h_461486239" used for this statement
The SQL Plan Baseline that was created in on 2016-09-15 11:47 with MODULE=and
OPTIMIZER_COST=29920 has the following attributes:
Plan hash value: 952719881
Plan name: SQL_PLAN_ac64bzwy69srx1ee46c4c Plan id: 518286412
SQL handle: SQL_a6188bff3c64e2fd
Note that the baseline appears to be using the "bad" PHV inspite of the fact
that it was created using "load_plans_from_cursor_cache" - is there a reason
for this behavior ?
Or am I missing something very obvious ?
I'd appreciate your thoughts...
Thanks,
--Rajesh
On Wed, Sep 28, 2016 at 11:24 AM, Neil Chandler
<neil_chandler@xxxxxxxxxxx<mailto:neil_chandler@xxxxxxxxxxx>> wrote:
Baselines are the best Oracle has come up with so far to lock down your plans.
If it's possible to reproduce the plan, it will (OK - there's the odd failure I
have heard reported but I've never hit one.)
- SQL Profiles tend to use OPT_ESTIMATE, which is basically advanced stats
changing cardinality ratios and as your data changes, it doesn't. I'm not a fan.
- Hints tend to be badly implemented and shouldn't be "specific" to drive a
plan but "generic" to influence how the optimizer rewrites the code. They
really should be a last resort.
Baselines allow one or more plans to be used. Any other plans that come along
with a better cost will be captured and kept but not "accepted" and therefore
not used.
A new index would indeed be ignored initially but the "better" plan would be
captured. By default, that night an autotask (SYS_AUTO_SPM_EVOLVE_TASK) will
run and auto-evolve the baseline and tomorrow you're going to be using a whole
new set of plans (the old plans will still be available to be chosen too).
Personally I would recommend you disable the autotask and run the evolve
yourself (using DBMS_SPM) so plans don't change "unexpectedly" overnight, and
only when you run an evolve manually. Adding a new index and adopting a new
plan would therefore be 1) add index 2) run SQL so it will hard parse and 3)
run a baseline evolve to accept the new plan.
Part of the real power of baselines is the ability to evolve them and have one
or more potential plans available to the optimizer, but only those which you
allow.
There is a hard parse overhead (cost is parsed with no hints and then parsed
again with hints to replicate the baseline) but unless you are really
parse-heavy, you're not going to notice.
regards
Neil C
https://chandlerdba.wordpress.com/
________________________________
Date: Wed, 28 Sep 2016 10:05:19 -0400
From: JBECKSTROM@xxxxxxxxx<mailto:JBECKSTROM@xxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx<mailto:oracle-l@xxxxxxxxxxxxx>;
oracle-db-l@xxxxxxxxxxxxxxxxxxxx<mailto:oracle-db-l@xxxxxxxxxxxxxxxxxxxx>
Subject: Usage of Baselines to prevent plan changes
Over the past year, we have encountered problems where a SQL statement suddenly
starts performing badly. In the past, we would either create a SQL Profile or
alter the SQL with hints to get the old plan back. Just wondering if people
used Baselines to prevent this form occurring and how well they work? This is
an Oracle EBS system. If we use Baselines and Oracle changes the table
structure adding an index to improve performance, would the new index be
ignored because of the Baseline?
Jeffrey Beckstrom
Lead Database Administrator
Information Technology Department
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
.
