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 184.108.40.206.
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…)