performance for multiple tablespaces

Printable View

06-26-2003, 11:39 AM
raf

performance for multiple tablespaces

Hi,
I have 8 oracle instances with an only user for every instance.
My customer asked me to create an only instance with 8 tablespaces and 8 different users.
These instances could also further increase.
I think that avoiding creating all these tablespaces on an only instance, because I think that it would be possible to have a reduction of the performances, considering that the tablespaces INDX, TEMP and SYSTEM are the same.

According to you what problem would it be possible to verify afterwards doing an operation of the kind?

Thanks
Raf
06-26-2003, 04:12 PM
TomazZ

You have 8 instances meaning 8 databases on one box?
For performance sake it would definitely be better to have one database with 8 tablespaces.
06-26-2003, 11:52 PM
adewri

Re: performance for multiple tablespaces

Quote:

Originally posted by raf
My customer asked me to create an only instance with 8 tablespaces and 8 different users.

Ur customer seems to be more intelligent that u. 8 tablespace is peanuts.
06-27-2003, 03:27 AM
richyip

This is really not recommended. Why your customer needs to have 8 databases?
06-27-2003, 03:38 AM
abhaysk

Quote:

Originally posted by richyip
This is really not recommended. Why your customer needs to have 8 databases?

His customer wants to have N tablespaces for N users and NOT N databases from N users...as he is having now
06-27-2003, 04:07 AM
pando

seems like his customer is better dba :D
06-27-2003, 08:32 AM
raf

Re: Re: performance for multiple tablespaces

Quote:

Originally posted by adewri
Ur customer seems to be more intelligent that u. 8 tablespace is peanuts.

I'don't believe that my customer is a good dba.
please read this link:

http://asktom.oracle.com/pls/ask/f?...D:2246908001297

wait your answer!

Raf
06-27-2003, 08:34 AM
raf

Re: Re: Re: performance for multiple tablespaces

Quote:

Originally posted by raf
I'don't believe that my customer is a good dba.
please read this link:

http://asktom.oracle.com/pls/ask/f?...D:2246908001297

wait your answer!

Raf

sorry link is this:

http://asktom.oracle.com/pls/ask/f?p...:2246908001297
06-27-2003, 08:50 AM
marist89

C'mon guys, this is a legitimate question. If I can paraphrase; "What are the advantages and disadvantages of putting multiple schemas on one database instead of on seperate databases?"

Quote:

Originally posted by TomazZ
You have 8 instances meaning 8 databases on one box?
For performance sake it would definitely be better to have one database with 8 tablespaces.

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

Quote:

Originally posted by richyip
This is really not recommended. Why your customer needs to have 8 databases?

Again, Why?

Quote:

Originally posted by raf
I think that avoiding creating all these tablespaces on an only instance, because I think that it would be possible to have a reduction of the performances, considering that the tablespaces INDX, TEMP and SYSTEM are the same.

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.

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.

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. You can limit some of the resource contention by spreading out your data on seperate physical devices, but there will always be contention for some resources.

That being said, the customer is always right, right?
06-27-2003, 09:54 AM
jmodic

Re: Re: Re: performance for multiple tablespaces

Quote:

Originally posted by raf
I'don't believe that my customer is a good dba.
please read this link:

http://asktom.oracle.com/pls/ask/f?...D:2246908001297

wait your answer!

Raf

Maybe your client is not a good dba, but it seems to me he is far better DBA as you are! :D

Your problem can hardly be compared to the one that Tom Kyte si dealing with in the link you provided.

Your question was:
- I have 8 customers, data for each is stored in separate database (!!!!)
- Q: Is it better to have one instance with 8 separate tablespaces, one for each customer?

The question in asktom was:
- I have one database
- I have 1000 customers, data for all of them is stored in single schema (all of them share same tables and are sepparated by some kind of client identifiers)
- Q: should I separate them in their own tablespaces (as a result there will be more than 1000 tablespaces

So if you want to apply your situation to the one at asktom, the questions to Tom Kyte should read:
-Q1: Should I have 1000 databases?
or
-Q2: Should I have 1 database with each customer in his own tablespace?
or
-Q3: Should I have 1 database with all 1000 customers data sharing one tablespace?

Tom's answers would probably be something like:

-A1: You must be kidding, right (or are you out of your mind)?
-A2: Doable, but it's hardly manageble. However it doesn't require (almost) no recoding of your aplications
-A3: The best options, however it probably involves some tables redesign and application recoding
06-27-2003, 10:17 AM
abhaysk

Quote:

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.

Quote:

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

Quote:

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.

Quote:

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.
06-27-2003, 10:49 AM
marist89

Quote:

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.

Quote:

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

I never assume worst-case scenarios.

Quote:

* 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?

Quote:

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).
06-27-2003, 11:14 AM
abhaysk

Quote:

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).....

Quote:

Originally posted by marist89

I never assume worst-case scenarios.

Comon WIN OS isnt that bad.

Quote:

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.

Quote:

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.

Quote:

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.

Quote:

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.
06-27-2003, 11:26 AM
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)?
06-27-2003, 11:53 AM
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?
06-27-2003, 12:21 PM
DaPi

Quote:

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 . . .

:confused:
06-27-2003, 11:04 PM
abhaysk

Quote:

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.