Feb 27: Optimiser - Upgrading from 9i to 10g

On the one hand, I regret the fact I seem to be posting a lot of links to other people's work at the moment but I couldn't let this one by, if only for those who don't use OraNA or the like and might miss it.

The one consistent message I kept hearing from other attendees at Openworld this year was that a lot of sites seem to have run into performance problems when upgrading from 9i to 10g due to changes in the CBO and DBMS_STATS. Yes, there are some Oracle 9i databases out there, you know.

I noticed via the Optimizer Development Group's blog that there's a white paper on this topic (PDF) on OTN by Maria Colgan which summarises some of the issues in a very readable form. (Well, it could maybe have used one more round of proof-reading for missing words etc., but I'm being picky.) It's not particularly deep but I'm glad to see it, if only to highlight some of the issues that you might face and can then investigate in more depth on your own systems if you think they'll affect you. Even if you know the issues already, it might be useful revision when you're about to upgrade to 10g. I know that I constantly come across people forgetting the automatic GATHER_STATS_JOB when they work with a mixture of versions and as Noons pointed out in the comments on this post, that can really throw things if you're not careful.

(Of course, there's a funny side to this too, in that it discusses the new default approach to Histogram generation that caught me out the other day. Well, it wasn't so much the new approach as me being hasty and seeing what I wanted to see, but the article does discuss the difference in Histogram generation using the default METHOD_OPT!)

It also mentions the change in the default value of parallel_max_servers that I've seen more than one system suffer from and discusses a SQL Test Case Builder that will be in 10.2.0.4 to help in submitting Metalink SRs. I'll have to play around with that at work because there are times when building a test case has proved a pain in the behind (as I'm sure I remember Peter Scott blogging about in the past).
Posted by Doug Burns Comments: (15) Trackbacks: (0)
Defined tags for this entry:
Related entries by tags:
Today's Links
Jonathan Lewis Articles

Trackbacks
Trackback specific URI for this entry

No Trackbacks

Comments
Display comments as (Linear | Threaded)

#1 - Cristian Cudizio said:
2008-02-27 11:03 - (Reply)

I've found very interesting this post and the previous about "useful diagnostic/optimisation tools" and the following discussion with explanation of histogram generation mode (by Jonathan Lewis).
I want to add only a note about a thing that i've saw yesterday in the list of bugs fixed by patchset 10.2.0.4: Bug 5082178 Bind peeking may occur when it should not.
Bind peeking may be not a perfect solution, but this bug that maybe i've encountered (i've never been able to produce a test case) is even worst.

more, i totally agree with you about oracle diagnostic and optimiSation tools.

Forgive me for my english and thank you for good information.

Regards,
Cristian

#1.1 - Doug Burns said:
2008-02-28 05:22 - (Reply)

Thanks Cristian and first things first ...

Forgive me for my english

It's fine and, as I always say, much better than my French, German, Spanish, Italian, Russian or erm... anything really! (Including my English at times ;-))

5082178 Bind peeking may occur when it should not.

Thanks for drawing my attention to that. This site, so far, has been very lucky in avoid bind variable peeking problems as far as I know but other sites I've worked at have had bad problems with it but I've certainly never hit that bug yet.

One of the main reasons we're interested in 10.2.0.4 is because Oracle are going to be a lot more aggressive about which versions they release Critical Patch Updates for. My understanding is that the JAN 08 CPU will be the last one for 10.2.0.2 and we need to apply CPUs for compliance reasons.

#2 - Noons said:
2008-02-27 14:13 - (Reply)

I really wish this paper had come out last year instead of Feb 2008.

Its copyright dates from 2007, so I can probably assume it WAS available then but only released now, probably as a result of the latest loud outbursts from folks undergoing the potentially painful 9i->10g route.

Jonathan and many others have repeatedly alerted to the differences in default settings but it's better when these things show up as an "official" white paper.

It's a pity the paper doesn't warn also of the potential problems with 9i->10.2.0.2 upgrades, particularly with canned-app users such as Peoplesoft: the number of patches and undocumented parameters required for that level of Oracle is so large, one ends up with a cbo that behaves worse than the 9i one!

A much safer route for Peoplesoft and other apps users is to upgrade to 10.2.0.3: it has a much more stable cbo and only needs one undocumented parameter to resolve a minor side effect of Peoplesoft inline views. It has other problems, but these don't usually affect vanilla apps sites.

Jury still out on 10.2.0.4, at this stage.

The parallel_max_servers parameter has a bit of a mistery history with us: for some reason I can't fathom, it was hard-set to 200 in all our dbs when I arrived at current workplace.

First true parallel execution would have caused an interesting situation, with 200 "slaves" taking off...

I know no one set it in our site, so it must have been one of the many prior external "experts" or alternatively the result of some tool-based tuning.

Whatever: it's now set to a much more reasonable value.

Now: onto battle for a partitioning option licence for our DW dbs...

#2.1 - Greg Rahn said:
2008-02-28 01:05 - (Reply)

The copyleft date is from the whitepaper template. I can vouch for its recently, hence the "0208" at the end of the file name. Any bets on when the 10g to 11g one will come out? :-) Almost hard to believe 10gR1 was released 4 years ago.

#2.1.1 - Doug Burns said:
2008-02-28 05:46 - (Reply)

(and to David below ...)

Any bets on when the 10g to 11g one will come out?

I reckon it might take a while. I think the 9i/10g document drips 'problems our customers ran into' which will take a while to flush out. Which is a shame because although, as Noons says, this information has been available from other sources, it's nice to be able to point colleagues to a single document to get them started.

Almost hard to believe 10gR1 was released 4 years ago.

Oh, come on! Please, that's not fair. Next you'll be telling me when 8.1.7 was released. Not that I would be concerned with that because we have no 8.1.7 databases at our site. None at all - honestly! :-(

#2.1.1.1 - Noons said:
2008-03-03 02:50 - (Reply)

I'll trade one of your 8.1.7 dbs for three of my 7.1.3 ones: good value and going cheap!


don't ask...
:-(

#2.2 - Doug Burns said:
2008-02-28 05:37 - (Reply)

Jonathan and many others have repeatedly alerted to the differences in default settings but it's better when these things show up as an "official" white paper.

It's a nice concise summary, too, all in one document. It's not as deep an investigation as Mr. Lewis might produce, but perhaps DBAs are more likely to squeeze a quick read of this during the working day - that's what I thought would prove useful and I agree that earlier would have been better. Still, it's a good step forward to see a document like this, isn't it?

(JOKE ALERT)

Peoplesoft

I do wish you'd stop mentioning Peoplesoft in blogs - I'm still trying to get that one out of my head! What a nightmare that thing is.

safer route for Peoplesoft and other apps users is to upgrade to 10.2.0.3

A useful tip from the coal-face, which I know you've blogged about before. Hopefully it'll save someone some grief.

one of the many prior external "experts"

Can I place my bet on that, please? ;-)

#3 - David Aldridge said:
2008-02-28 00:49 - (Reply)

That is indeed a useful document. One can only hope that a 10g-11g version is in the works.

#3.1 - Doug Burns said:
2008-02-28 05:48 - (Reply)

I was so pleased with myself for resisting the temptation to bemoan the fact the URL seems to imply this is a Business Intelligence/Data Warehouse-related paper (because other applications don't use the optimiser, apparently).

Then you showed up!

#3.1.1 - Cristian Cudizio said:
2008-02-28 08:37 - (Reply)

i'm not involved with datawarehouse and BI so i would never found that document on that website, but i've found it through your blog. more, i've tried to reach it by the OTN but i've not been able to found it (is this correct english?). Oracle technology web site is focused a lot on 11g.
however i work with an OLTP application and it seems useful to me too that document :-)

Thanks,
Cristian

#3.1.1.1 - Doug Burns said:
2008-02-28 21:16 - (Reply)

i've not been able to found it (is this correct english?).

Nearly ... 'find it'

i work with an OLTP application and it seems useful to me too that document

I think it's useful whatever your application. Dave and I are just having fun with each other ;-)

#3.1.2 - David Aldridge said:
2008-02-28 13:54 - (Reply)

You're welcome.

But isn't it the case that the majority of innovative and new features are BI related? In fact, if one were in a pure OLTP environment then the RBO would cover 95% of cases very nicely -- index access etc.. It's when you get into selecting significant proportions of your data that the wizardry kicks in ;-)

I'm just playing devil's advocate. http://www.theonion.com/content/news_briefs/man_who_plays_devils

#3.1.2.1 - Doug Burns said:
2008-02-28 13:57 - (Reply)

You're right, of course, I just wish you weren't ;-)

#3.1.2.2 - Cristian Cudizio said:
2008-02-28 15:09 - (Reply)

Now i understand why we on 9iR2 have always used RBO without relevant performance problems (with some limited exception) :-) indeed our application in mainly OLTP (not purely)

#4 - Carol D 2008-02-28 22:51 - (Reply)

I agree that the paper was needed about 2.5 years ago. How many of us where blind-sided with some of the changes right out of the box early on? Guess it is up to us to start the 10g->11g paper.


Add Comment

Standard emoticons like :-) and ;-) are converted to images.
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
BBCode format allowed