This discussion is archived
1 2 Previous Next 28 Replies Latest reply: Nov 21, 2012 11:18 AM by jgarry Go to original post RSS
  • 15. Re: How Much tablespeces for 10 Schemas
    Maahjoor Newbie
    Currently Being Moderated
    I think there is a reason for many tablespaces, e.g

    1. Separate data of one application from the data of another to prevent multiple applications from being affected if a tablespace must be taken offline.
    2. Store different the datafiles of different tablespaces on different disk drives to reduce I/O contention.
    3. Take individual tablespaces offline while others remain online, providing better overall availability.
    4. Optimizing tablespace use by reserving a tablespace for a particular type of database use, such as high update activity, read-only activity, or temporary segment storage.
    5. Back up individual tablespaces.

    and so on...

    i ask for a better database design, or may be i cant describe my question clearly.

    and one more thing hopefully no one will mind , since i am not pointing out anybody...

    as per my experience, this forum is full of polite, active and good people, anyone who want to comment anybody's post, should take care of his words wether it is hurting anybody? how beautifull and soft words could i choose?

    all the best.
  • 16. Re: How Much tablespeces for 10 Schemas
    Aman.... Oracle ACE
    Currently Being Moderated
    Maahjoor wrote:
    I think there is a reason for many tablespaces, e.g

    1. Separate data of one application from the data of another to prevent multiple applications from being affected if a tablespace must be taken offline.
    Why would you take tablespace offline in a running database?
    2. Store different the datafiles of different tablespaces on different disk drives to reduce I/O contention.
    Why you need different tablespaces for that? Why can't you have different datafiles be stored of one tablespace on different drives or using the principle of SAME on the same disk using different partitions?
    3. Take individual tablespaces offline while others remain online, providing better overall availability.
    Again, why would you take a whole tablespace offline, what for?
    4. Optimizing tablespace use by reserving a tablespace for a particular type of database use, such as high update activity, read-only activity, or temporary segment storage.
    For temporary segments, oracle already provides a separate temporary tablespace . For high updates(writes) or less writes , what benefit you would be getting by dividing the schema's into different tablespaces? Are you planning to have schemas in your application which are going to be read-only ? If yes, what you are going to do when you are going to laod data into their tables?
    5. Back up individual tablespaces.
    So? What's the benefit of having a different tablespace in it? Be in archivelog mode and use RMAN and you would be doing just fine!
    and so on...
    Nope, no reason that you mentioned actually suggests that you would be really getting anything significant as a benefit by dividing each schema in a different tablespace.
    i ask for a better database design, or may be i cant describe my question clearly.
    Aman....
  • 17. Re: How Much tablespeces for 10 Schemas
    Girish Sharma Guru
    Currently Being Moderated
    Aman,

    But below experts have quoted like this ones :

    "Generally, each "important" schema should have 1 or more tablespaces to itself. If you have 800 schemas for individual users that may create a couple of views for their own purposes, it would make sense to create a single USERS tablespace that each user shares."
    [url https://forums.oracle.com/forums/thread.jspa?messageID=886363&#886363]Justin Cave

    "In my experience, small applications (with respect to number of objects and total data size) use generally one or 2 schemas and only one tablespace (sometimes 2 if they want to separate table data and table index). But large applications like ERP software tend to use several schemas and several tablespaces."
    [url https://forums.oracle.com/forums/thread.jspa?messageID=4077218&#4077218]P. Forstmann

    "It would make sense to have a tablespace/schema -

    you can recover tablespaces to different points in time (so they are relatively independent of each other recovery wise - you can recover one client to an hour ago without affecting everyone else).

    you can transport self contained sets of tablespaces - this would make each schema nicely "self contained". If a client decides "I want my own database" or "a different database on a different machine", you can move them (and not anyone else).

    you can back them up on different schedules."
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1707924400346897396

    So, in conclusion we can say that if schema objects are not shared between users then one tablespace for one schema is best. Here performance will be same, i.e. as said, we can not says that there is any relationship of performance vs tablespace, because IO tuning is part of performance tuning, not tablespace tuning.

    I am waiting to see your comments please.

    Regards
    Girish Sharma
  • 18. Re: How Much tablespeces for 10 Schemas
    Maahjoor Newbie
    Currently Being Moderated
    if you do not reply, then i was going to consider the aman reply as the best answer.

    you said if schema objects are not shared between users then one tablespace for one schema is best.

    i have and HRMS schema, which obviously all the schema will have to select for employees details for example, so on and so forth.
    what exactly you mean by object sharing?

    and the question is that if user shares object, what is wrong if we have more tablespaces?

    Thanks for the very helpfull post. and to Aman also.

    Regards.
  • 19. Re: How Much tablespeces for 10 Schemas
    Girish Sharma Guru
    Currently Being Moderated
    Aman's replies are always best, no doubt about it. I can repeat it with 100 times.

    Here object sharing means, suppose in user1 schema there is a table which is being selected, updated by many users in different applications, and suppose there some IO problem happend or datafile corrupted, then many users as well as other applications will also get broken, untill and unless issue do not resolves. In this case, if objects are not being shared and accessed by other application users, then those apps users will continue to their work.

    I just posted to get more and concise answer by Aman, to help me as well as to you too, because when there is a conflict/confusion happens between two experts views, its best to ask to remove doubt please; or may be I am not getting the point of view properly.

    Regards
    Girish Sharma
  • 20. Re: How Much tablespeces for 10 Schemas
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Actually Girish is right .

    Aman answer is Perfect answer but we need to understand what other means by their doubts !! also thank you Girish for posting
  • 21. Re: How Much tablespeces for 10 Schemas
    Aman.... Oracle ACE
    Currently Being Moderated
    Girish Sharma wrote:
    Aman,

    But below experts have quoted like this ones :

    "Generally, each "important" schema should have 1 or more tablespaces to itself. If you have 800 schemas for individual users that may create a couple of views for their own purposes, it would make sense to create a single USERS tablespace that each user shares."
    [url https://forums.oracle.com/forums/thread.jspa?messageID=886363&#886363]Justin Cave
    "In my experience, small applications (with respect to number of objects and total data size) use generally one or 2 schemas and only one tablespace (sometimes 2 if they want to separate table data and table index). But large applications like ERP software tend to use several schemas and several tablespaces."
    [url https://forums.oracle.com/forums/thread.jspa?messageID=4077218&#4077218]P. Forstmann

    "It would make sense to have a tablespace/schema -

    you can recover tablespaces to different points in time (so they are relatively independent of each other recovery wise - you can recover one client to an hour ago without affecting everyone else).

    you can transport self contained sets of tablespaces - this would make each schema nicely "self contained". If a client decides "I want my own database" or "a different database on a different machine", you can move them (and not anyone else).

    you can back them up on different schedules."
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1707924400346897396

    So, in conclusion we can say that if schema objects are not shared between users then one tablespace for one schema is best. Here performance will be same, i.e. as said, we can not says that there is any relationship of performance vs tablespace, because IO tuning is part of performance tuning, not tablespace tuning.
    Girish,

    I believe, in general, all the experts are mentioning the same what I said before too-in general, its a good idea to keep the tablespace count to be on a minimum level and it shoudn't be 1:1. If you have lesser schema's to manage and you know that there won't be a time that you would need to create more than what you have right now, you may stick with one schema per tablespace but that's purely going to be a decision that one may take on his own will. The point to put the datafiles of the tablespaces on different disks is something that again can be argumented , for example what if you are not running on a normal storage but on ASM where you have no control of what goes on where? For the recovery of the tablespace also . you can always do it on a different database using the transportable tablespace feature can't you? Purely for that reason or stating that as one of the prime reasons, I wouldn't advocate the idea of one schema per tablespace myself but again, Justin, Pierre are far more experienced and knoweldgeable than I am so if they are mentioning it, looking at all the pro's and cons , if the set up suits one, they definitely should go for it. In general, too many moving pieces is a bad idea for performance and manageability.

    Regards
    Aman....
  • 22. Re: How Much tablespeces for 10 Schemas
    Maahjoor Newbie
    Currently Being Moderated
    Aman,

    you have a great sense of database, i appriciate it.

    could you kindly comment the justin cave comments ( find the link in sharma post)

    justin cave says;

    "Assuming each of the 15 schemas is independent, yes, you'll want at least 15 tablespaces."

    we will wait for your comments.
  • 23. Re: How Much tablespeces for 10 Schemas
    Girish Sharma Guru
    Currently Being Moderated
    Please read Aman's response... he has written " _if the set up suits one_, they definitely should go for it. In general, too many moving pieces is a bad idea for performance and manageability."

    It means, if count of user is less (i.e. suppose below 30-35) then 1:1 rule seems good, but if count is suppose 40 and above, then having 1:1 tablespace:schema not good; i.e. it depends upon count of users as well as nature of business data. Since there is no thumb rule, so we can say, if you can manage the count then you can go with your wish and will.

    http://www.rdbconsulting.com/files/SchemaIsolation.pdf
    http://dba.stackexchange.com/questions/2574/what-are-the-benefits-of-using-a-different-tablespace-for-a-new-user-schema
    These links are saying benefits of different tablespace, but if I search more for demerits of such things, then definately I may found too.... :)

    @Aman,
    Thank you very much for your reply.

    Regards
    Girish Sharma
  • 24. Re: How Much tablespeces for 10 Schemas
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Thank you very much aman for the amazing clarification
  • 25. Re: How Much tablespeces for 10 Schemas
    Aman.... Oracle ACE
    Currently Being Moderated
    Maahjoor wrote:
    Aman,

    you have a great sense of database, i appriciate it.
    Thanks for the kind words but I know very very less!
    could you kindly comment the justin cave comments ( find the link in sharma post)

    justin cave says;

    "Assuming each of the 15 schemas is independent, yes, you'll want at least 15 tablespaces."
    IMHO Justin seems to mean that if your application is running with database schemas used the same as application schema-thus having each one managing its own objects separately , its better to use 1:1 configuration of schema and tablespace.

    Aman....
  • 26. Re: How Much tablespeces for 10 Schemas
    sb92075 Guru
    Currently Being Moderated
    Maahjoor wrote:
    Aman,

    you have a great sense of database, i appriciate it.

    could you kindly comment the justin cave comments ( find the link in sharma post)

    justin cave says;

    "Assuming each of the 15 schemas is independent, yes, you'll want at least 15 tablespaces."

    we will wait for your comments.
    http://en.wiktionary.org/wiki/KISS_principle
  • 27. Re: How Much tablespeces for 10 Schemas
    Max Seleznev Explorer
    Currently Being Moderated
    In general it's always better to have a separate tablespace for every schema. This way you're much more flexible in many aspects of database management including performance, data availability, backup etc.
    It's also just a tiny bit easier to go from many tablespaces to one tablespace than vise versa should you change your mind.
  • 28. Re: How Much tablespeces for 10 Schemas
    jgarry Guru
    Currently Being Moderated
    I'm with Girish on this one. With all these schemata, it's bound to happen that someone is going to mess things up so badly a PITA PITR will be needed, much easier with tablespaces coordinated with users.

    Another possible benefit is additional parallelism during backups, though of course that is platform/configuration/physical layout dependent.

    To OP: remember, job 1 of the dba is to not lose data, so all of this ought to be thought out in terms of recovery. We don't know your configuration, which can make a difference as you might have noticed with the argument about ASM, SAME and separate disks. Don't be put off by sb's manner, he is very helpful and tends to cut right to the heart of the matter, whether you want to hear it or not. There is something to be said for treating the db as one big pot, and worrying later about any physical layout changes if you see an actual problem, especially if you are SAME (Stripe And Mirror Everything on a SAN) on some commodity box and don't really have guidance for service levels. We all tend to flavor our advice based on our experience, which may be different than your situation.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points