Any thoughts on stats validation?
September 7, 2011 4 Comments
There is an application running on 9.2.0.8 and this application’s approach to object statistics is that they are gathered along with each quarterly release of code and do not change outside of the release schedule.
There are a number of other additional issues with some elements of this particular implementation, discussion of which are outside the scope of this.
Now, as part of the upgrade to 11.2, it’s highly likely that we will move to the default built-in stat proc, albeit gathering in a weekend window not daily.
One of the biggest advantages of the current situation is that the stats are tested along with the release and released with the release (of course, plans can still change depite stats being fixed – even because they are fixed due to time ticking ever onwards, sequences increasing, etc).
And so, as part of a possibly non-negotiable comfort blanket to move to the default 11g stats job, there is a management requirement to have some sort of validation routine – i.e. a set of sql statements broadly representing important or problem areas of the application – that will be run before/after and compared on some sort of metric and judgement passed on whether the stats are “good”.
There are a number of nifty features like pending stats that we can use here but my initial thoughts on this was that this sounded like a fairly easy job for SQL Tuning Sets (STS) and the SQL Performance Analyzer (SPA).
SPA requires the additional Real Application Testing (RAT) license and, now that our licensing has been negotiated, this isn’t something we’ve got.
So… hand-rolling a validation routine.
I would see SQL Tuning Sets with the sql statements and bind sets as still part of a bespoke solution
(STS are available with the Tuning Pack license which is on top of EE both of which we have).
I could picture a simple approach that would execute these statements in parallel sessions probably via DBMS_SQL, fetch all the rows, record the session stats before/after, pick a metric e.g. buffer gets, elapsed time, or some sort of ratio between those sorts of things and then and do some sort of metric comparison – yes, that run was broadly the same or better, or at least not a catastrophic degradation – these stats are ok.
(Of course, being able to picture a possible approach does not mean that I advocate it)
But I just wondered whether this is something anyone else has done?
I’d be very interested in hearing more if you have.
Edit:
I’ve tried to reword this to avoid some confusion.
Alex – I’ve deleted your comments and my replies because a) they reduce the chance of getting the input the article was aiming for b) they were based on nested recursive misunderstandings and c) much of what you were saying is preaching to the choir.
OraStory 
No one has answered – so I’ll try 2 theoretical ideas which may deserve exploring.
Without RAT&SPA, another way to ensure plan stability when migrating would be SPM, but since you are on 9i an *indirect* method should be used – like the one described by Mike Dietrich in “Upgrading from Oracle9i to Oracle Database 11g – A Real World Customer Experience” on page 14: upgrade to 11g and run the workload with optimizer_features_enable set to ‘9.2.0’ and optimizer_capture_sql_plan_baselines=true (probable the other parameters as in 9i). The paper implies this worked – but I have my doubts that this way you can get the 9i execution plans – hardware might be changed, system stats, etc (but he is talking from experience).
Another path to explore would be creating stored outlines in 9i and then migrating them to Sql Plan Baselines in 11g.
HTH
Hi Myron,
Thanks for your comments.
I put the lack of response down to two things:
1.According to Doug Burns, blogging is dead and no-one reads blogs anymore (presumably they’re just drowning in tweats).
2.It being a bit of a left-field requirement.
Now I’m not asking about preserving 9i plans, etc.
The whole application regression testing has been done, any required sql tuning tuned.
So it’s not plan migration or stability that I’m explicitly talking about.
However, the problem can be looked at from that direction and so you’re quite right that one solution to the underlying issues is sql plan management and baselines which should preserve a known execution plan whilst checking for better alternatives as well as allowing a controlled evolution.
Theoretically the best of both worlds – stability as well as controlled improvement.
But baselines aren’t a no-brainer in my opinon – they’re still immature if you ask me and then have been a couple of really nasty performance bugs recently related to their recursive operations.
There aren’t many people I know who would be happy with vast quantities of their code under baseline protection … yet.
Having said that there are a handful of statements which are using baselines for this upgrade.
But, as I said, this is really a question about doing things a bit of the wrong way round and without using the features that are designed explicitly to do that – no wonder response is thin. Most people have probably read it and thought “what the heck is he going on about?”.
Oh well.
Another feature worth mentioning, given that this is really about the threat of performance degradations after stats collection with automatic histogram behaviour, is that if an exercise had been undertaken to rationalise all the histograms previously created by FOR ALL INDEXED COLUMNS SIZE AUTO – deleting them all for example and only creating the ones that were obviously required – then using a method_opt of FOR ALL COLUMNS SIZE REPEAT would remove one of the main threats. But this exercise which I recommended was not encouraged.
Thanks again.
Cheers,
Dominic
Hi Dominic,
What triggered my response was the quote you added in the post: “pick a metric e.g. buffer gets, elapsed time, or some sort of ratio between those sorts of things”.
This got me thinking if there really is such a metric on which a comparison could be safely safely: I mean – between combinations of {buffer gets, latches, sorts, elapsed time} with different values – which would be best? (I tend usually not to base on elapsed times – I saw enough different ones for the same execution plan). And moreover – we reach yet again the great old discussion (at least in the mixed workload environments): response time or throughput, which to choose?
I wish such a *magic* metric would exist – something like “database time per statement” – and I think it could be computed (by adding CPU time + non-idle waits or extracting idle waits from elapsed time for a statement execution time), but this would be in fact dependent on the system’s health at that moment – so possibly varying (maybe significantly – I encountered such cases) even for the same execution plan – so it would be a false alarm.
Maybe a simpler and more effective metric would be the cost of the execution plan.
What do you think?
Thanks
PS
Please delete this comment if you consider it irrelevant.
If you don’t want to pollute the comments section, but still want to respond – please email me.
Funny you should mention cost – that’s exactly what baseline evolution uses, the lowest cost.
I’m not a fan of cost. At least not for this system.
SQL Performance Analyzer allows you to pick which metric you want to use for comparison – same challenge.