More on the 9.2 to 10.2 upgrade

But first. It's good to see that two bloggers in the Washington DC area are back on-line. Welcome back Tom and Sponge

A few posts ago I mentioned my final 9 to 10g upgrade (unless someone else wants me to do a 9 to 10 upgrade project). The mechanics of the upgrade are done, I have a few doubts about the way the hardware talks to the disks - but I had those before the upgrade (a UNIX sys admin who couldn't be bothered to set up the disk the way I wanted as it would take time and it was only a test box). I was going to post about automatic stats collection but was beaten to it by Noons; some of my DW tables have specially crafted statistics - one-size-fits-all is rarely true in a real-world data warehouse; data is skewed and dimensions can be correlated, both which can wreck estimates of cardinality. In a reply to Nuno, Gary Myers (aka Igor) reminded us of the use of tables to keep a copy of the good stats so that they can be put back; something I'll have to play with in 10g, I found that a tad slow with Oracle 9.2, especially for partitioned tables with lots of partitions, but then again, I could be a bit wrong-headed with the way I tried it.

The thing I am going to write about is our query testing. First up, and this is so obvious that I feel a fraud just to type it out. The words used in query plans have changed between 9.2 to 10.2, so even if the plans do the same operations with similar cost the plan text will differ, this is especially the case with materialized view rewrite and parallel query.

By and large, most queries in our test sample pool behave as well if not better than before, nothing is significantly worse. Some are amazingly better; One of the users on the production system was looking for total sales of two product categories for two customer demographics over a time slice of two periods (eight weeks); the query rewrote to a suitable table but used a nasty mess of nested loop joins, which took a couple of hours to run. The same query on the test system rewrote to the same table and then used a very nice star transformation to get the results out in 10 seconds. On the other hand some of our old star transformations now use other techniques to get the results back

The only thing I foresee the users not liking is that 'order' of the result sets return may not look the way they expect. I have no sympathy, if they want the results in order then they should use ORDER BY.