18Jun/1013
ORACLE 11g SQL Plan Management: The Dark Side of SPM. Part 4
You are currently browsing comments. If you would like to return to the full story, you can read the full entry here: “ORACLE 11g SQL Plan Management: The Dark Side of SPM. Part 4”.
July 28th, 2011 - 06:25
Some excellent points raised here, presented with clarity.
I hadn’t even considered Scenario 2 but based on the signature match I suppose it’s obvious. Although I imagine that’s a loophole that will get closed somehow in the not too distant future
May 12th, 2012 - 15:40
Nice article. However I have seen most of the large OLTP database system never goes for all these automatic tuning features provided by latest version including automatic memory management.
Intact we never gather stats on our OLTP system to maintain plan stability & and performance.
May 13th, 2012 - 13:09
Thanks Amit. I agree: large OLTP systems should almost never use automatic tuning features. However, SQL plan management is not an automatic tuning feature (unless you want it to be), it is rather the tool that prevents automated management from kicking in by “freezing” SQL plans in place. We use it extensively to make sure plans are managed by humans … but as with any tool – you need to be aware of all the little quirks that it has to prevent weird problems (and yes, some of them are pretty weird)
June 15th, 2012 - 10:55
Wow, nice info! I was researching this for the company i work for and find this information very useful and informative!! To close #2 Loophole, don’t let anyone but dba’s have privs to create objects in the DB and create a cron job to validate! done.
September 18th, 2012 - 17:43
On Scenario 1,please check the column “FIXED”,which is used to set the plan that can be used even if both plans are accepted.
Thanks
September 18th, 2012 - 18:10
Antony,
I’m not sure that scenario 1 is strictly about baseline attributes, such as FIXED.
My point is: indexes can be ignored, because baselines require EXPLICIT operations (which is a bit weird).
I.e. if you have a baseline on your query that uses a full table scan and then you add an index, ORACLE will not automatically switch to using that index, even though a new plan with the index will likely be generated and be vastly more efficient.
You have to do something to enable the new plan, by either evolving it or simply forcing it (or dropping the baseline).
As for FIXED attribute, it can be used to force the plan that you want (again, explicitly), but I found it to be somewhat unreliable, so I’m always forcing the plan by disabling all the other plans in the baseline (through ENABLED attribute).
Cheers,
Maxym Kharchenko
December 6th, 2012 - 12:49
I have a scenario where there is an accepted baseline, but at runtime Oracle is still using the worse plan, why would this be ?
December 6th, 2012 - 12:54
Hello Vikas,
There could be multiple reasons.
First of all, look at the v$sql.SQL_PLAN_BASELINE field for your query cursor to see what/whether baseline is actually used (it will be empty if it’s not) or running dbms_xplan on your cursor and looking at the “notes” section. Depending on the results you can look deeper by i.e. dumping 10053 trace and seeing why baseline was (not) chosen.
January 10th, 2013 - 09:06
Hi,
intresting notes in this article, but 2 questions!
Q1:
scenario 1: what if we remove the index on ‘t’ and still let the “best” plan be ACCEPTED within the baselines?
Q2:
I have read that by having DIAGNOSTIC PACK lisence oracle would/should evolve and ACCEPT the best plan among the different ones within the baselines. I mean is there any way to enforce oracle to implicitly choose the best plan – let me say any automatically evolve process available?
My question is based on the cases, where we enable the SPM to capture the baselines and deploye a new release and we want to let oracle choose the best plans AFTER the deployment process.
If not the case , i could only imagine the best way to do that is running a script over the baselines and SET all the attributes of the column “ACCEPTED” to YES -> thus we might enforde oracle to ALWAYS choose the best plans !!
What do you mean?
Thanks!
January 12th, 2013 - 10:33
Hi Alireza,
1. I’m assuming you are asking: “what if ACCEPTED SPM baseline is using an index and you remove the index” ?
The way SPM works is: it tries to reproduce the plan from baseline during parse. If it cannot (i.e. if the index is not there), it cannot use this plan, even if it is ACCEPTED and needs to either try other ACCEPTED plans from baseline or parse a new one.
The plan with the index remain ACCEPTED, I believe (did not check on that) but will not be used.
Keep in mind that you can have several ACCEPTED plans for the same statement and ORACLE can choose between them during parse. ACCEPTED does not mean BEST, nor does it mean THE ONLY ONE that can be used. Rather it means: it was approved at some point (which you can do manually, btw)
2. SPM is not a part of diagnostic license – it is available as a part of enterprise edition. What you are probably thinking about is DBMS_SQLTUNE package, which deals with profiles (close cousins to baselines), and does require tuning pack. It has a job that looks through sqls and finds profile recommendations as well as evolve baselines. But it is not necessary if you only want to evolve baselines – you can create your own “evolve” script quite easily.
Speaking of upgrades. You want to be real careful here. I think the main benefit of SPM during upgrades is the fact that you can “freeze” plans, not so much that you can make them better. While it is nice to improve the plans after upgrade, to me it is much more important not to make them worse … Unfortunately SPM evolution can do both.
What is, arguably, even worse, it can also populate baselines with several ACCEPTED plans for the same sql, meaning that your sql will execute sometimes with plan A and sometimes with plan B. These plan flips can be disastrous if your users are relying on stable execution of that sql (there are ways to fix it by i.e. working with ENABLED attribute, but it’s better not to have this problem in the first place).
So, be extra careful with auto evolutions – they can do both good and bad.
Regards,
Maxym Kharchenko
January 14th, 2013 - 13:31
Hi Maxym,
?
I got it
As I assume that :
a) one feature has mostly nothing to do with another
b) SPM requires much more manually DBA tasks, whereas dbms_sqltune mostly doesn’t!
Thanks!
Alireza
January 15th, 2013 - 00:27
Hi Maxym,
sorry! the last memo was not completed – so here we go with the completed one:
I got it! YES what i exactly meant is “Automatic Plan Evolution of SQL Plan Management” which is abviously an interaction between the SQL Tune Advisor’s nightly tuning task and SPM!! which would be proceeded through DBMS_SQLTUNE sub routines and requires tuning pack lisence (what we own!).
My questions are:
1)would you mean it is preferable to use DBMS_SQLTUNE once we got the Tunung Pack lisence for an autom. evolve process?
2)obviously SPM requires much more DBA manually works rather than DBMS_SQLTUNE, true?
3)can you recommand any oracle docu which describes more about the interactive engine of both features?
Many Thanks!
Alireza
March 13th, 2013 - 12:40
Thank you for your POV. It was informative about what to consider.
Regarding Scenerio 2, MOS note SQL Plan Baselines Clarified from a Security Perspective. [ID 1469099.1] discusses that “SQL plan baselines are user-agnostic by design”. It explains the details. But our point is valid – something to be aware of! (Oracle just doesn’t see it as a bug.)