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?
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.
Tomaž "A common mistake that people make when trying to design something completely
foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams
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?"
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.
Originally posted by richyip This is really not recommended. Why your customer needs to have 8 databases?
Again, Why?
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?
Maybe your client is not a good dba, but it seems to me he is far better DBA as you are!
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
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Reply With Quote
Bookmarks