Jeffrey,
we're using baselines to lock down plans for a few databases.
Oracle optimizer is great, and with regular/fresh statistics calculates
good plans in 99,99% cases, but this 0,1% wrong plans were causing big
performance problems for us due to nature of the application.
Even if I have to "fix" query using hints I will use fake baseline for that.
With locked plans you will get stable performance, but as database is
always changing these plans could become obsolete and not the best option.
Even if you create index it will be ignored when you're using baseline for
a query.
But you can check Oracle recommendations from tuning advisor, check AWR
reports, check SQL plan baseline history and evolve better plans.
Even gathering baselines could be difficult task, as you have to think
about managing/maintaining baselines which could be tedious task if you
have millions of them.
I would recommend locking plans only if you have frequent plan changes from
a different queries.
Also, it depends on your application can you lock plans without
consequences. I would recommend testing before going in production.
If you don't have to, then do not go that way.
Fix occasional performance problems checking stats, creating few baselines
or profiles, and let the optimizer to do the magic ;)
My 2cents...
Best Regards,
Marko
On Wed, Sep 28, 2016 at 7:05 PM, Mauro Pagano <mauro.pagano@xxxxxxxxx>
wrote:
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> 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>
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 <r.aialavajjala@xxxxxxxxx>
Sent: 28/09/2016 16:34
To: neil_chandler@xxxxxxxxxxx
Cc: JBECKSTROM@xxxxxxxxx <jbeckstrom@xxxxxxxxx>; Oracle-l
<oracle-l@xxxxxxxxxxxxx>; oracle-db-l <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=>'1hzgfq6
2vat5h',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> 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
To: oracle-l@xxxxxxxxxxxxx; 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
.