{"id":1339,"date":"2007-11-06T12:00:00","date_gmt":"2007-11-06T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=1339"},"modified":"2007-11-06T12:00:00","modified_gmt":"2007-11-06T12:00:00","slug":"the-reality-gap-3-a-single-instance-per-server","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2007\/11\/06\/the-reality-gap-3-a-single-instance-per-server\/","title":{"rendered":"The Reality Gap (3) &#8211; A Single Instance per Server"},"content":{"rendered":"<p>There will probably be about four or five of these posts in total. This is one I didn&#8217;t mention in the original presentation because I didn&#8217;t feel I could do it justice and because there was someone I wanted to have the right to reply. Step forward <a href=\"http:\/\/tkyte.blogspot.com\/\">Tom Kyte<\/a>.<\/p>\n<p>By coincidence, <a href=\"http:\/\/asktom.oracle.com\/pls\/asktom\/f?p=100:11:0::::P11_QUESTION_ID:529123700346381713\">this question<\/a> and answer appeared on <a href=\"http:\/\/asktom.oracle.com\/\">asktom<\/a> yesterday. At first I thought it was an incredible coincidence because I was planning to write about this last weekend, but in retrospect, this subject is something Tom had been banging on about for some time. As the first follow-up comment highlights, you can find many more examples of Tom discussing this question in the past with a simple search. In fact if you search for the precise string &#8220;Single Instance Per Server&#8221; you&#8217;ll get a couple of results!<\/p>\n<p>Tom&#8217;s recommendation is that you have a single Oracle instance per server. There are several good reasons that spring to <em>my<\/em> mind, but I recommend you read Tom&#8217;s own thoughts on the subject.<\/p>\n<ul>\n<li>You can allocate one large pot of memory to the single instance and allow Oracle to use it more efficiently, depending on which application needs it. If you have multiple, smaller instances, you&#8217;re limiting what each pot can be used for. As a general systems management approach, I prefer bigger pots as long as they remain efficient.<\/li>\n<li>Not only is resource usage more efficient, fewer instances reduce system maintenance overhead, which is always a good thing. We have enough instances to look after, thanks!<\/li>\n<li>If you don&#8217;t use a single instance per server, then you&#8217;re not going to get the best out of Resource Manager (although I&#8217;m still hearing anecdotal evidence of bugs there).<\/li>\n<\/ul>\n<p>All sounds good so far but what if your databases and applications are so small, they simply don&#8217;t justify a server of their own? That&#8217;s no problem. A well-engineered DBMS like Oracle is designed to store many different logical databases for different applications inside a single large database. Interestingly, that&#8217;s the way DBAs from other technology backgrounds (e.g. SQL Server and DB\/2) tend to see the world<sup>*<\/sup>. Let&#8217;s just have a single large database and associated instance containing multiple small application-related schemas.<\/p>\n<p>I&#8217;ve worked at several sites who do this to some degree. With the growing popularity of tools such as APEX, companies are keen to implement small &#8216;tactical apps&#8217; (not my phrase) to meet simple business requirements. A General Purpose database is a good home for them and I work on some at the moment. <\/p>\n<p>However, here are some problems with this approach.<\/p>\n<ul>\n<li>When you want to upgrade to a new version of Oracle, every application support or project team has to agree. The companies I&#8217;ve worked with over the past few years are buying off-the-shelf applications by the bucket-load and developing less of their own. Which means we need multiple companies to agree that their application can run on the new version. I&#8217;ve heard of a vendor recently who refuses to certify their app to 9.2.0.8. They agree to 9.2.0.3 (!) or 10.2.0.3. Brilliant &#8211; we can move to 10g! Erm, no actually, because one of the other vendors isn&#8217;t certified on 10.2 yet. It&#8217;s <em>incredibly frustrating<\/em>. Of course, we have this problem anyway and having multiple different instances on different versions will make our lives more difficult, but at least we don&#8217;t have every application sitting on an old, unsupported version of Oracle because of one piddling little app.<\/li>\n<li>When you want an outage for anything, you have to speak to all of the app owners to get it. If one of the app owners don&#8217;t ok it, for whatever reason, we have to wait &#8230; and wait &#8230; <\/li>\n<li>What happens when one application vendor wants you to set an instance parameter to a specific value and another doesn&#8217;t? How many vendors do you think state that they are happy to support their application co-existing with another in the same database instance? It really doesn&#8217;t matter whether I think they&#8217;re wrong (in fact I&#8217;ve shown more than one vendor that it was achievable with their app), the business will always listen to the vendor. They&#8217;re paying them money for (cough, splutter &#8230;) support.<\/li>\n<\/ul>\n<p>This could go on for some time but you get the picture. If this was just a technical decision, then I&#8217;d have no argument, but it isn&#8217;t. It&#8217;s a business decision and will be dictated by business needs in the end. You might think that it&#8217;s my job as an IT professional to offer the best solution, explain the reasons and implement it and I attempt that on a day to day basis but I see IT departments playing that role less often these days and just responding to whatever the business tell us to do.<\/p>\n<p>As I will keep repeating during this short series, this is <em>not<\/em> about the technical merits of the accepted wisdom, it&#8217;s about the complete gap between recommendations like &#8216;a single instance per server&#8217; and the systems I see daily. Remember, too, that as a contractor I didn&#8217;t build all of these systems; these sites are not following my personal mantra and they aren&#8217;t all idiots. So, should anyone suggest that I should go and work at a good site, please point me in the right direction.<\/p>\n<p>To finish this blog I tried to think of the last time I saw a server with a single Oracle instance on it. The only one I could think of is the ISP4400 downstairs in my house. Seriously, I can&#8217;t think of a server that has a single instance on it. <\/p>\n<p>(<strong>Updated later &#8211; While getting ready for work, I remembered one from my time at Pythian &#8211; a client with a two-node RAC cluster.<\/strong>) <\/p>\n<p>(<strong>Updated yet again &#8211; Oh, and Data Warehouses tend to be on their own server<\/strong>)<\/p>\n<p>Horror of horrors, I see servers with over 20 instances on them regularly.<\/p>\n<p>Sorry, Tom. I don&#8217;t disagree with the gist of your argument, but the reality I witness is <em>miles<\/em> apart.<\/p>\n<p><sup>* <\/sup>But maybe not? If you talk to DBAs who work with other DBMS, they often combine application databases into server-wide instances. So maybe I am imagining all of this and it&#8217;s about how I&#8217;m used to working with Oracle? I genuinely don&#8217;t know. Maybe because the business know that they can have a seperate instance and DBAs have encouraged it in some way, then we&#8217;ve created this problem for ourselves?<\/p>\n<p><strong>Updated later &#8211; Again, it took me a while to find a related link I remembered vaguely. Over on Howard&#8217;s site there was <a href=\"http:\/\/www.dizwell.com\/prod\/node\/970\">a discussion<\/a> about multiple instances on a server.<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>There will probably be about four or five of these posts in total. This is one I didn&#8217;t mention in the original presentation because I didn&#8217;t feel I could do it justice and because there was someone I wanted to have the right to reply. Step forward Tom Kyte. By coincidence, this question and answer&hellip; <a class=\"more-link\" href=\"http:\/\/orcldoug.com\/blog\/2007\/11\/06\/the-reality-gap-3-a-single-instance-per-server\/\">Continue reading <span class=\"screen-reader-text\">The Reality Gap (3) &#8211; A Single Instance per Server<\/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-1339","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":926,"url":"http:\/\/orcldoug.com\/blog\/2005\/10\/24\/intel-isp4400\/","url_meta":{"origin":1339,"position":0},"title":"Intel ISP4400","date":"October 24, 2005","format":false,"excerpt":"I've wanted to carry on investigating parallel execution for a while but, unless you happen to be working on a reasonably large data warehouse with a few spare test environments kicking around, it's difficult. Without wishing to reopen the whole debate about the value of single user test scripts (which\u2026","rel":"","context":"With 8 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1421,"url":"http:\/\/orcldoug.com\/blog\/2008\/06\/23\/northern-server-technology-day\/","url_meta":{"origin":1339,"position":1},"title":"Northern Server Technology Day","date":"June 23, 2008","format":false,"excerpt":"Last Thursday's Northern Server Technology Day organised by the UKOUG was one of the more enjoyable events I've attended. It helped that there were no hotels, driving or airports involved, just a simple walk to the train station, a 90 minute train journey (free wireless on hand) and a 10\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1314,"url":"http:\/\/orcldoug.com\/blog\/2007\/09\/09\/one-day-soon-ill-install-something\/","url_meta":{"origin":1339,"position":2},"title":"One Day Soon, I&#8217;ll Install Something","date":"September 9, 2007","format":false,"excerpt":"What a horrible week it's been from a technical perspective.I decided mid-way through the previous week that I was going to bite the bullet, give up on DTrace for a while and install Linux on the server so I could run the PX tests on 11g. Then Jeff Moss sent\u2026","rel":"","context":"With 20 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1374,"url":"http:\/\/orcldoug.com\/blog\/2008\/01\/08\/book-review-oracle-11g-new-features\/","url_meta":{"origin":1339,"position":3},"title":"Book Review &#8211; Oracle 11g New Features","date":"January 8, 2008","format":false,"excerpt":"I remember I said I wouldn't do this any more, but last October, someone from Oracle Press contacted me by email because they'd noticed the following words on my website. \"However, those aspects are well dealt with in Oracle Wait Interface: A Practical Guide to Performance Diagnostics and Tuning by\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1009,"url":"http:\/\/orcldoug.com\/blog\/2006\/07\/03\/rabbit-rocks\/","url_meta":{"origin":1339,"position":4},"title":"Rabbit Rocks","date":"July 3, 2006","format":false,"excerpt":"One day I might write something about Oracle but, in the meantime, Sunday was a special day for The Rabbit. As I think I've mentioned before, he has a fierce ambition to become a famous rock drummer one day. He's been pestering us for a drum kit for months and\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1122,"url":"http:\/\/orcldoug.com\/blog\/2006\/11\/06\/ukoug-agenda-2\/","url_meta":{"origin":1339,"position":5},"title":"UKOUG Agenda","date":"November 6, 2006","format":false,"excerpt":"Another late shift today followed a busy weekend attending an evening wedding reception on Friday and a friend's 40th birthday party in Newcastle on Saturday night, so that closing part of the Recovery Design series might have to wait for a day or two. I'm also in a growing panic\u2026","rel":"","context":"With 10 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1339","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=1339"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1339\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1339"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1339"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1339"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}