{"id":1386,"date":"2008-02-27T12:00:00","date_gmt":"2008-02-27T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1386"},"modified":"2008-02-27T12:00:00","modified_gmt":"2008-02-27T12:00:00","slug":"optimiser-upgrading-from-9i-to-10g","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2008\/02\/27\/optimiser-upgrading-from-9i-to-10g\/","title":{"rendered":"Optimiser &#8211; Upgrading from 9i to 10g"},"content":{"rendered":"<p>On the one hand, I regret the fact I seem to be posting a lot of links to other people&#8217;s work at the moment but I couldn&#8217;t let this one by, if only for those who don&#8217;t use OraNA or the like and might miss it.<\/p>\n<p>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.<\/p>\n<p>I noticed via the <a href=\"http:\/\/optimizermagic.blogspot.com\/2008\/02\/upgrading-from-oracle-database-9i-to.html\">Optimizer Development Group&#8217;s blog<\/a> that there&#8217;s <a href=\"http:\/\/www.oracle.com\/technology\/products\/bi\/db\/10g\/pdf\/twp_bidw_optimizer_10gr2_0208.pdf\">a white paper on this topic<\/a> (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&#8217;m being picky.) It&#8217;s not particularly deep but I&#8217;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&#8217;ll affect you. Even if you know the issues already, it might be useful revision when you&#8217;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 <a href=\"http:\/\/18.133.199.212\/?p=1382\">this post<\/a>, that can really throw things if you&#8217;re not careful.<\/p>\n<p>(Of course, there&#8217;s a funny side to this too, in that it discusses the new <em>default<\/em> approach to Histogram generation that caught me out the other day. Well, it wasn&#8217;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!)<\/p>\n<p>It also mentions the change in the default value of parallel_max_servers that I&#8217;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&#8217;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&#8217;m sure I remember <a href=\"http:\/\/pjsrandom.wordpress.com\/\">Peter Scott<\/a> blogging about in the past).<\/p>\n","protected":false},"excerpt":{"rendered":"<p>On the one hand, I regret the fact I seem to be posting a lot of links to other people&#8217;s work at the moment but I couldn&#8217;t let this one by, if only for those who don&#8217;t use OraNA or the like and might miss it. The one consistent message I kept hearing from other&hellip; <a class=\"more-link\" href=\"http:\/\/orcldoug.com\/blog\/2008\/02\/27\/optimiser-upgrading-from-9i-to-10g\/\">Continue reading <span class=\"screen-reader-text\">Optimiser &#8211; Upgrading from 9i to 10g<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1386","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1071,"url":"http:\/\/orcldoug.com\/blog\/2006\/08\/30\/the-devaluation-of-training\/","url_meta":{"origin":1386,"position":0},"title":"The Devaluation of Training","date":"August 30, 2006","format":false,"excerpt":"Howard Rogers has an interesting post on his blog at the moment that is particularly timely from my perspective and that I agree with whole-heartedly. Companies seem increasingly loathe to commit adequate funds and, probably more significantly, staff time to training (although that could be my imagination).Having said that, a\u2026","rel":"","context":"With 20 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1383,"url":"http:\/\/orcldoug.com\/blog\/2008\/02\/24\/reviewing-parameter-files\/","url_meta":{"origin":1386,"position":1},"title":"Reviewing Parameter Files","date":"February 24, 2008","format":false,"excerpt":"One of the headline recommendations during the Real World Performance groups presentations at Openworld 2007 was to review your parameter files as part of your upgrade process. It sounds like simple common sense, but they suggested that inappropriate parameter settings that had been implemented during version 7 days for long\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1062,"url":"http:\/\/orcldoug.com\/blog\/2006\/08\/21\/resumable-storage-allocation\/","url_meta":{"origin":1386,"position":2},"title":"Resumable Storage Allocation","date":"August 21, 2006","format":false,"excerpt":"At the moment I'm writing a short 9i\/10g New Features seminar for the developers at work. The idea is to run through lots of new features very quickly to see if anything lights their candle and they can then go off and investigate further afterwards.It occurred to me while I\u2026","rel":"","context":"With 9 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":933,"url":"http:\/\/orcldoug.com\/blog\/2005\/10\/17\/ansi-join-syntax\/","url_meta":{"origin":1386,"position":3},"title":"ANSI Join Syntax","date":"October 17, 2005","format":false,"excerpt":"A couple of days ago one of my colleagues raised the question - 'ANSI join syntax, does anyone actually use that?' I have pretty strong views on this and so a brief dynamic conversation followed (and that isn't a euphemism for 'stand-up row', I should add!) So, what about ANSI\u2026","rel":"","context":"With 20 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":972,"url":"http:\/\/orcldoug.com\/blog\/2005\/06\/12\/dba_registry\/","url_meta":{"origin":1386,"position":4},"title":"dba_registry","date":"June 12, 2005","format":false,"excerpt":"It's true I learn something new all the time. Is it just me? Did everyone know about this?I was talking to one of the other DBAs at work on Friday who was telling me that one of the dbs we've taken over looks like it wasn't upgraded properly. I connected\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":866,"url":"http:\/\/orcldoug.com\/blog\/2006\/02\/08\/updates\/","url_meta":{"origin":1386,"position":5},"title":"Updates","date":"February 8, 2006","format":false,"excerpt":"Just a couple of small updates on some previous blogs.The database that was suffering from network latency problems between it and the app servers was moved down South last week. It all went very smoothly and that particular problem has been resolved. The 20+ hour job runs over night very\u2026","rel":"","context":"Similar post","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1386","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1386"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1386\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1386"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1386"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1386"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}