performance for multiple tablespaces
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: performance for multiple tablespaces

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    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

  2. #2
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    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

  3. #3
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796

    Re: performance for multiple tablespaces

    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.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  4. #4
    Join Date
    Apr 2003
    Location
    Kuala Lumpur
    Posts
    59
    This is really not recommended. Why your customer needs to have 8 databases?

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    seems like his customer is better dba

  7. #7
    Join Date
    Jul 2002
    Posts
    228

    Re: Re: performance for multiple tablespaces

    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

  8. #8
    Join Date
    Jul 2002
    Posts
    228

    Re: Re: Re: performance for multiple tablespaces

    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

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: Re: Re: performance for multiple tablespaces

    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!

    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width