A quick collation – and warning – for 11.2
Bottom line – be careful about what you do with system stats on 11.2
Footnote: the MOS link is a search string producing a list of references. I set it up like that because one of the articles referencing the bug is called “Things to consider before upgrade to 184.108.40.206” and it’s worth reading.
Addendum: one of the people on the two-day course I’ve just run in Berlin sent me a link for a quick note on how to set your own values for the system stats if you hit this bug. It’s actually quite a reasonable thing to do whether or not you hit the bug given the way that gathering the stats can produce unsuitable figures anyway: setting system stats. (I’ve also added their company blog to the links on the right, they have a number interesting items and post fairly regularly.)
Here’s an example of how the passing of time can allow a problem to creep up on you.
A recent client had a pair of logon/logoff database triggers to capture some information that wasn’t available in the normal audit trail, and they had been using these triggers successfully for many years, but one day they realised that the amount of redo generated per hour had become rather large, and had actually been quite bad and getting worse over the last few months for no apparent reason. (You’ve heard this one before … “honest, guv, nothing has changed”).
Here’s a link to a truly ambitious document on Metalink (if you’re allowed to log on):
Doc ID 421191.1: Complete checklist for manual upgrades of Oracle databases from any version to any version on any platform
(Actually it only starts at v6 – but I don’t think there are many systems still running v5 and earlier).
(The title’s a pun, by the way – an English form of humour that is not considered good unless it’s really bad.)
Very few people try to email me or call me with private problems – which is the way it should be, and I am grateful to my audience for realizing that this blog isn’t trying to compete with AskTom – but I do get the occasional communication and sometimes it’s an interesting oddity that’s worth a little time.
Today’s blog item is one such oddity – it was a surprise, it looked like a nasty change in behaviour, and it came complete with a description of environment, and a neatly formatted, complete, demonstration. For a discussion of the problem in Spanish you can visit the blog of John Ospino Rivas, who sent me the original email and has written his own blog post on the problem.
Here’s an example of how a bug-fix can create problems. It’s a code change in 11.2.x.x and (I believe) 10.2.0.5 relating to the costing of queries involving (but perhaps not restricted to) composite partitioned tables. I first saw this change in an email from Doug Burns, who sent me the 10053 traces from a very simple query that had started using the wrong index after an upgrade from 10.2.0.4 to 220.127.116.11.
As part of his testing he had set the optimizer_features_enable parameter back to 10.2.0.4 and found that not only did the choice of index change back to the expected index, but the costs of the two indexes changed dramatically. (The cost of using the “right” index changed from 15 to something in excess of 9,000 on the upgrade!)
I posted a summary of page hits a little while ago when the WordPress statistics hit 750,000 page views, and commented at the time that a couple of the most popular items seemed to be related to the types of issue you get when you upgrade to 10g.
It occurred to me this morning that I could take a closer look at the stats, because WordPress has a page which gives a fairly comprehensive break down of the views made over time of each page. I’ve repeated the top three from the previous list and added one more, with a brief comment about their popularity over time:
It would be very easy to read too much into these numbers – but one of the side effects of an upgrade to 10g is the impact of “Cost Based Query Transformation” and the clever (sometimes too clever) things it does to execution plans; and three of the top four articles are likely to be of interest to people seeing these side effects. On the other hand, 10g doesn’t really do anything dramatically different with bind variable issues and the views for that topic haven’t changed.
Would it be reasonable to suggest that this might be an indication of a surge of people migrating to 10g in the March time-frame ?
When you upgrade from 9i to 10g (or higher) you may find lots of execution plans suddenly “go wrong”. There are three main reasons for this. The first is that 10g automatically enables CPU costing, and if you weren’t using it in 9i this is likely to make a difference – often an improvement but you may be unlucky. The second is that there are numerous new optimisation strategies in 10g, and some of them may not be good for your particular data patterns, or your particular types of query. (more…)