Tom,

You mentioned "There is no reason to break up a database because of some number of schemas", so if it is not by the number of schemas, is there any other gain/reason that you know of, where running four instances in same the box is best and only for that case?

The background on this question is because I currently have a performance tuning project under one of those 4 instances. I want to recommend the possibility of merging all instances into one. But before that happens this far huge progress have been done by de-normalizing the data model just a bit and re-writing the queries with the goal of minimizing the plan cost.

Thanks,

The Only issue with Single database having Millions of Schema supporting Hundreds of Application is
if Database is Crashed or Needs Upgrade or Due to any xyz reason it is down , all application will suffer.
DBA's job becomes a little challenging if Your single very large database supports Multiple Applications and Sometime just couple bad New sql¿s can create a major problem for the entire database and hence all applications suffers.
Otherwise a Single Database is a great thing . you do not have to worry about replicating data across several database and keeping them in Synch.

Adarsh Kumar
Oracle DBA

Tom,

The gentleman above replied with the same description my senior DBA described the reason why we have 5 instances in one box ( Not 4 but 5 ). It is convenience of not affecting more people/applications when something goes wrong in 1 DB.

Is this enough reason to break the cardinal rule of "one instance per box"?

( Off course the ideal would be 5 boxes, but that means ask the company to break the bank. )

what about running each instance in a VM?
Although, i believe, the only VM officially supported by Oracle is the one that Oracle sells, it still is an option.
You'd have to do your own ROI.

The other question is "what will happen if something on the one machine FUBARs and takes down the whole system?"

If you are worried about bad SQL, look into RMAN, flashback query, and other DR type technologies.....you DO have development on a different box, right?!?

Double check your security policies on each 'user'. Make sure they don't have things like DBA role or DROP ANY TABLE.

I think you are confusing it unnecessarily. Tom is not saying you should run all your applications in one database or that you should run only one application per database - it is totally upto you (btw, from your question it sounded like you are equating applications with schemas - not quiet right, one application can have many schemas and many applications can share one schema depending on how you design it). It is a logical division based on your applications design and ofcourse you can squeeze things in or spead out liberally or have something in between depending on what you can afford. But you should alway "run only one database at the most per box" - simple.

If you do a simple search you can find we have had these discussions many times over in the forum. I hate to see Tom's time being wasted for silly stuff like this over and over...

how do i know the number of tables,constraints and triggers in my schema?

  1  select object_type, count(*) from user_objects where object_type in ('TRIGGER', 'TABLE' )
  2  group by object_type
  3  union all
  4* select 'CONSTRAINTS', count(*) from user_constraints
ops$tkyte%ORA10GR2> /

OBJECT_TYPE           COUNT(*)
------------------- ----------
TRIGGER                      1
TABLE                       25
CONSTRAINTS                117

Dear "A reader",

My question was directed from a high level perspective. We are balancing Schemas against Instances. Nothing more. If you need to elaborate on how many schemas per application or how many applications to a schema, then that is out of the scope of the original question. Derailing from the original question is a waste of time to anyone.

Fortunately as always, Tom took it from the higher perspective and replied accordingly.

Do not de-merit a question if the subject matter in it is not clearly in your understanding.

Tom,

How about if we have a (10.2) physical standby database on a given server, and we want to put another instance (say a logical standby) on that same server.

I think that might be an exception to the problem of mysterious, unbridled resource consumption, since the resource consumptoin of the physical standby would be pretty stable. It would almost be like just having a single instance on the server.

What do you think?

Thanks,

Robert.

That was the only problem I could think of.
Wow, matching answers with Tom Kyte - my day is made :)

Hi Tom.
Is there some particular tuning parameters that one should configure to support hundreds of schemas? In particular I have an instance set up for test with about 45 schemas and nightly I tear down all objects in each schema and then refresh all objects per schema (with production export of tables, indexes, views, etc.) If all these run at the same time it seems the I/O will be affected but won't the data dictionary in the instance churn and cause waits if all schemas kick off the re-init at the same time? If I had a few instances I'd think I would have same I/O issue but the data dictionary one would go away. Assuming I have plenty of RAM.

The instance which has the 45 schemas are basically empty production tables except for necessary "static dml". Applied to the production schemas are various deltas that go out in deployment. Each schema is used by various build servers (regression, bugfix, current) that run suites of tests. At the end of each test cycle a quick DELETE from (list of tables) is executed (instead of the nightly tear down). I guess there's not much to do other than try to make everything play nicely like don't tear down all 45 at same time, spread them out. But I was hoping for something to tune in this respect. I think the disk I/O would be one of the main bottlenecks but I was just curious about the data dictionary. We do significant partitioning of tables as well global partitioned indexes. Just curious, thank you for your time. -Rob