Re: same application on multiple schemas
From: Michael Austin <maustin_at_firstdbasource.com>
Date: Tue, 14 Apr 2009 21:49:04 -0500
Message-ID: <vRbFl.5496$Lr6.2298_at_flpi143.ffdc.sbc.com>
Palooka wrote:
>> On Apr 14, 12:09 pm, Alberto <alberto.rol..._at_gmail.com> wrote:
>>> i'm the original poster. i think that i explained the situation with
>>> too much hurry. here is a better explanation
>>>
>>> As for the "comparison" with Sql Server, it was not intended to ask
>>> why Oracle does "not" support sql server behaviour :) I know oracle (I
>>> worked with it before using Sql Server) and I know that the concepts
>>> behind are slightly different from those in Sql Server.
>>> From you, I would just like to know if having a different schema for
>>> each application is ok or not (see below the description of the
>>> situation).
>>>
>>> we have a web application. it uses a set of tables to store its data.
>>>
>>> For every customer that will use our application, we will clone it.
>>> Customer A will have url customerA.ourcompany.com. The application
>>> will use schema "SchemaA"
>>> Customer B will have url customerB.ourcompany.com. The application
>>> will use schema "SchemaB".
>>>
>>> Every schema will be blind to any other (that is, it will not need and
>>> will not be able to access any other database).
>>> In the beginning, all customers will be on a small number of server
>>> (maybe one, for example). If any of the customers will grow very much
>>> (the application is one that can have very different sizes among
>>> customers), it could happen that we will need to move it to another
>>> server. This why I asked if Oracle supports detach and attach. But to
>>> do this, every schema must use its separate datafiles and tablespaces.
>>> Or we can use exp and imp.
>>
>> This is very common with commercial packages. Up until recently,
>> however, there were bugs that would do things like update the wrong
>> schema only under certain heavy load conditions (! non-public bug
>> 5458753, referred to elsewhere like bug 6351293) or give performance
>> problems.
>>
>>> Anyway, after my post, I studied a bit Oracle RAC, and we feel that
>>> this is a technology that could fit very well our needs.
>>
>> Only true IF you spend the time and money to have a staff that
>> understands what is going on AND write the app properly. An
>> improperly written app will scale the problems faster than the
>> performance, and insufficient staff will compound that. Google around
>> for the classic paper called "you probably don't need RAC." That
>> paper's perhaps less true now that there are more people with
>> appropriate experience floating about, but still, too many places
>> create some inappropriate expectations.
>>
>>> Just as an additional question, if using a schema for each application
>>> is NOT the correct solution... which is the correct one ? We do not
>>> want to use a single schema putting all customer's data in the same
>>> physical table, because every customer can have its own data and we
>>> would have to put everytime a "where customer = current customer".
>>
>> VPD (Virtual Private Database, also called FGAC - Fine Grained Access
>> Control) is designed to address this, I'm not convinced, since it has
>> some strange limitations and requirements. Some people swear it is
>> great, google around. Even so, I don't think it is appropriate for
>> your requirements, but it basically puts everytime a "where customer =
>> current customer" automatically behind the scenes.
>>
>> Whatever you do, use bind variables and understand the "bind variable
>> peeking" problem (search asktom.oracle.com if that makes no sense).
>>
Date: Tue, 14 Apr 2009 21:49:04 -0500
Message-ID: <vRbFl.5496$Lr6.2298_at_flpi143.ffdc.sbc.com>
Palooka wrote:
> joel garry wrote:
>> On Apr 14, 12:09 pm, Alberto <alberto.rol..._at_gmail.com> wrote:
>>> i'm the original poster. i think that i explained the situation with
>>> too much hurry. here is a better explanation
>>>
>>> As for the "comparison" with Sql Server, it was not intended to ask
>>> why Oracle does "not" support sql server behaviour :) I know oracle (I
>>> worked with it before using Sql Server) and I know that the concepts
>>> behind are slightly different from those in Sql Server.
>>> From you, I would just like to know if having a different schema for
>>> each application is ok or not (see below the description of the
>>> situation).
>>>
>>> we have a web application. it uses a set of tables to store its data.
>>>
>>> For every customer that will use our application, we will clone it.
>>> Customer A will have url customerA.ourcompany.com. The application
>>> will use schema "SchemaA"
>>> Customer B will have url customerB.ourcompany.com. The application
>>> will use schema "SchemaB".
>>>
>>> Every schema will be blind to any other (that is, it will not need and
>>> will not be able to access any other database).
>>> In the beginning, all customers will be on a small number of server
>>> (maybe one, for example). If any of the customers will grow very much
>>> (the application is one that can have very different sizes among
>>> customers), it could happen that we will need to move it to another
>>> server. This why I asked if Oracle supports detach and attach. But to
>>> do this, every schema must use its separate datafiles and tablespaces.
>>> Or we can use exp and imp.
>>
>> This is very common with commercial packages. Up until recently,
>> however, there were bugs that would do things like update the wrong
>> schema only under certain heavy load conditions (! non-public bug
>> 5458753, referred to elsewhere like bug 6351293) or give performance
>> problems.
>>
>>> Anyway, after my post, I studied a bit Oracle RAC, and we feel that
>>> this is a technology that could fit very well our needs.
>>
>> Only true IF you spend the time and money to have a staff that
>> understands what is going on AND write the app properly. An
>> improperly written app will scale the problems faster than the
>> performance, and insufficient staff will compound that. Google around
>> for the classic paper called "you probably don't need RAC." That
>> paper's perhaps less true now that there are more people with
>> appropriate experience floating about, but still, too many places
>> create some inappropriate expectations.
>>
>>> Just as an additional question, if using a schema for each application
>>> is NOT the correct solution... which is the correct one ? We do not
>>> want to use a single schema putting all customer's data in the same
>>> physical table, because every customer can have its own data and we
>>> would have to put everytime a "where customer = current customer".
>>
>> VPD (Virtual Private Database, also called FGAC - Fine Grained Access
>> Control) is designed to address this, I'm not convinced, since it has
>> some strange limitations and requirements. Some people swear it is
>> great, google around. Even so, I don't think it is appropriate for
>> your requirements, but it basically puts everytime a "where customer =
>> current customer" automatically behind the scenes.
>>
>> Whatever you do, use bind variables and understand the "bind variable
>> peeking" problem (search asktom.oracle.com if that makes no sense).
>>
> Forgive me if I am speaking out of turn. > > Quite seriously, as far as Oracle is concerned, fsck FGAC, or even > separate schemas. If I am dealing with multiple customers, each will > have his own database. They might well be on the same server, though. > > Palooka
The difference, however is available memory for SGA space. If I need 10 databases with an SGA of 5G (yeah rather large for most stuff), then I need a minimum of 50GB+free memory for things like - say - the OS and database connections. If I have separate schema, then I only need one very large SGA/process counts etc... to handle those same 10 databases/1000 users. VPD/FGAC is a way around it, but, like anything in Oracle, YMMV and test, test test and test some more... Received on Tue Apr 14 2009 - 21:49:04 CDT