RE: Optimizer question after upgrade
From: Michael McMullen <ganstadba_at_hotmail.com>
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.
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.
- Take out any hidden parameters ie start with base 10g pfile, nothing from 9i
- Take out hints from your sql. Really you want to start out cold, nothing left from 9i
- 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.
- 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.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 20 2009 - 06:57:54 CDT