performance for multiple tablespaces

[abhaysk]

Originally posted by marist89
Why? Each database would have it's own memory dedicated to it. You could tune each instance for maximum performance.

You will end up in sharing resources if you see at OS point of view.
Which will unessarly degrade OS performance & hence all your DBs running on it.

Originally posted by marist89

Depends. I would keep each schema in it's own instance if:
1. my schema didn't depend on data in other schemas.
2. my future growth would overwhelm my current hardware platform.
3. I have adequate computing resources; memory, I/O throughput, network bandwidth.

Valid points, but if you see much broader, these points creeps rest...
I assume OS to be WIN

* All the Oracle Background Processes run as thread by OS..& say if you have N instances
will end up in N*6 ( MIN ) threads will be running, conuming memory ( Paging ) as per parameters set.
SO if no DB activity is going on most of the times for some instances,
this Memory will not deallocated by OS and hence there will be some contention in resources ( Unneccesary )

* Since, OS has to share resources with N instances, performace will obiviously degraded.
Due to which all I/O, Network, Oracle Processes and so many other related things will be degraded.

* Managability with having N instances is obiviously a concern.

And below as you said

Originally posted by marist89

I would combine the schemas into one instance if:
1. I needed data from another schema periodically.
2. I don't forsee much growth on my existing platform.

Originally posted by marist89

When putting multiple schemas on one database you end up sharing some resources. This ultimately ends up with one user consuming more than his fair share.

Well having multiple schemas on one database will end in sharing some resources...but this will not be severe as with resource shaaring at OS level.

Abhay.

[marist89]

Originally posted by abhaysk
You will end up in sharing resources if you see at OS point of view.
Which will unessarly degrade OS performance & hence all your DBs running on it.

Now that's a reason why. I think if you have adequate resources your OS should be able to handle any contention better than Oracle could.

Valid points, but if you see much broader, these points creeps rest...
I assume OS to be WIN

I never assume worst-case scenarios.

* Managability with having N instances is obiviously a concern.

Not when N is a small number. Sure, if N>100 you might have an issue, but 8?

Well having multiple schemas on one database will end in sharing some resources...but this will not be severe as with resource shaaring at OS level.

I disagree. At 17:00, end of day processing happens for all schemas. Because there is so much activity, data gets aged from the buffer cache prematurely thus causing more physical I/O's. If each instance had it's dedicated buffer cache these blocks would last in memory longer and be re-used.

Hey, I'm not saying it would be right or wrong. I don't think we have enough information available to make an informed opinion. I was just trying to spark debate instead of the continuation of "You're stupid" threads (on a valid question).

[abhaysk]

Originally posted by marist89
Now that's a reason why. I think if you have adequate resources your OS should be able to handle any contention better than Oracle could.

Obiviously OS will do, but if you see at the ORACLE perspective as well its the OS that manages resources & not Oracle.
But let me make some point here...say if you have adequate resources to manage 8 DBs with some contention say...
If you consider resource in reference number say 100..its always better if you allot 50 to one instance rather than 50/N Instances...

Obiviously 50 to 1 Will outperform the contentions caused due 50/N (provided N>5).....

Originally posted by marist89

I never assume worst-case scenarios.

Comon WIN OS isnt that bad.

Originally posted by marist89

Not when N is a small number. Sure, if N>100 you might have an issue, but 8?

I am seeing N just greater that 5, i personnaly feel a headache.

Originally posted by marist89

I disagree. At 17:00, end of day processing happens for all schemas. Because there is so much activity, data gets aged from the buffer cache prematurely thus causing more physical I/O's. If each instance had it's dedicated buffer cache these blocks would last in memory longer and be re-used.

As, i mentioned above, if adequate resource is avialable then 50/1 will suerly have less I/O compared to 50/N at peak times.

When you have adequate resource, then why do one think of having N instances which eventually lead to sharing of resources rather allot as much resource to 1 instance which is meant to allot to N instances.

Originally posted by marist89

Hey, I'm not saying it would be right or wrong. I don't think we have enough information available to make an informed opinion.

Ahh, yes we dont have enough info to decide which will be better.....
but i would stick on to the point that its would be better if instances are less than 5, 8 would be OK, but depends.

Originally posted by marist89
I was just trying to spark debate instead of the continuation of "You're stupid" threads (on a valid question).

Thas why, i have tried to make some points.
Thru debates is what one will learn more about anything.

Abhay.

[DaPi]

Hi Jeff,

You said "At 17:00, end of day processing happens for all schemas." but isn't that just another worst case scenario? If everything has to happen at the same time, any advantages of sharing (at application or OS level) are going to get lost. Wouldn't you loop over schemas (inside the db) OR over databases ("inside" the OS)?

[marist89]

Not necessarily. I got the feeling that the 8 databases were different instances of the same application. Would it be unreasonable to assume that end of day processing on one would need to happen at the same time as the others?

Sure, if you're talking small databases you could do one and then the other. What if your end-of-day takes 3 hours?

[DaPi]

Originally posted by marist89
What if your end-of-day takes 3 hours?

I'm sure that 8 copies of a 3 hour job running at the same time won't finish in 3 hours! You're right, you will need some degree of concurrency - I exepct the optimum will be neither 8-at-once nor 1-at-a-time - but that can be achieved at either the application or the OS level.

How about this as a game plan:
- if you're short on memory, use several db's, on the assumption that paging is faster than Oracle reloading the data buffers.
- if you're short on disk, use several schemas to share RBS, Temp etc
- if you're short on CPU power, . . . I'm not sure - I'd go for several schemas to minimise the number of processes running, but . . .

[abhaysk]

Originally posted by DaPi
How about this as a game plan:
- if you're short on memory, use several db's, on the assumption that paging is faster than Oracle reloading the data buffers.

Not sure i get you here....

You mean if SYSTEM is short of primary memory ( RAM, PAGING etc ), then you wana use multiple DBs?

-- If this is what you have thought, I disaggree...

Or the other way, if DB(N) needs Considerably less memory, you want to go for multiple DB?

-- Very much valid.