RE: Optimizer question after upgrade

From: Michael McMullen <>
Date: Fri, 20 Mar 2009 07:57:54 -0400
Message-ID: <BAY141-DAV10CFFE2B5508BAC946A77EA6970_at_phx.gbl>
Message-ID: <0F314B782F1E45C49B584E380D32BB0D_at_vpmcm01>

I'll try to find the article (I think it's an oracle white paper) where they went through an 9i to 10g upgrade. It started out being 10-20% slower for a key batch job and then by the time they were done it was 10% faster.

I believe the key steps were.

  1. Take out any hidden parameters ie start with base 10g pfile, nothing from 9i
  2. Take out hints from your sql. Really you want to start out cold, nothing left from 9i
  3. Have system statistics -- I myself hate system statistics. You get a brand new box, how do you set them to mimic your full prod without running your processes. It seems like a swag to me. They vary depending on the workload of the system. I like to bring over one business area at a time to a new box, get things running smoothly and then the next. But if I run system stats after I bring a bunch of new biz areas over, they can change which could change the execution plans of the first biz area I brought over. I find it very much a chicken/egg sort of thing.
  4. Be really depressed, as the article was long and involved a lot of steps and if it was a live production system you would have been screwed.
Received on Fri Mar 20 2009 - 06:57:54 CDT

Original text of this message