Re: Multiple Instances, or one big one...

In article <3ivrj0$8da_at_butch.lmsc.lockheed.com>, stroup_at_grove.ops.lmsc.lockheed.com () wrote:

> O.K. Please help settle a debate...
>
> Currently we have two Oracle Instances, one for development and one for
> production (and ocasionally we'll add a test instance when we're doing
> kernel upgrades).
>
> This has been a source of debate since several of our developers want
> their application to have its own instance (they site the ability to tune
> the database separately and the ability to bring it up and down at will
> as the reason).
>
> I'd prefer to stick with the current configuration since it minimizes the
> memory and process overhead (and make less work for me, the DBA, as well
> as the folks in operations).
>
> But what do you'all think? What are the pros and cons of either
> configuration.

I prefer to have fewer rather than more instances. Having a separate rollback, temp, and system tablespaces for each developer is a major headache and eats space like I eat my girlfriends stuffed peppers. The developers; however, need to be able to either recreate a separate schema for themselves or share a common schema in either a test, dev, or prod instance. I also encourage the use of a shared data schema in the dev instance that contains a recent snapshot of the real data (you may want to use less then the full production data but you don't catch all the sql sins then). Be sure to use enough data in this integration test schema to give all the transactions a chance to show their stuff (good or bad) and to ensure that your indexing is adequate.

I suggest creating the following scripts:

drop_schema.sql
create_schema.sql [ full_size | min_size ]

drop_synonyms.sql [ public | private ]
create_synonyms.sql schema_owner [ public | private ]

This gives the developers control over their data content, but also provides some consistency in creating the schema. These scripts can be generated from any decent CASE tool.

> P.S. We are currently using Oracle 7.0.15.4 on VAX.VMS 5.5-2. We will be
> moving our software to an Hp9000/T500 with two processors and plan to
> implement the parallel server option at that time. Does this change make
> any difference in regards to the above issue?

I wish I was still working on a VMS system. Unix has its charms but it's a system admin headache, lacks ACLs, RCS and SCCS don't compare well to CMS, the Unix help (man) is ugly compared to VMS help, Unix Security is Root or nothing, and vi is pathetic when compared to Eve. Oh well. I suspect that NT is in all of our futures anyway. I just hope they have Perl and piping.

Let me get off my soapbox and address your question though. I think it's best to keep the developers on different machines if possible. Runaway processes and poorly constructed queryies can sure slow things down in production. My last client used one machine, and the risk to production is pretty low if you have some reasonable processes for installing your software. I havn't used the new parallel server (I used Oracle 6.2 in Cluster mode back in the good old VMS days) but I havn't heard of any serious problems.

I hope this helps.

-- 
Jay L. Faylore         faylore_at_halcyon.com
Oracle DB Designer     If you've got the money, I've got the time.