Question: We
have many individual clients using the same software. SaaS
model. The current number of clients is around 60, with some
very large, and many moderately small in data volumes.
1)
Securing client data to protect data privacy and to eliminate
the possibility of accidental cross-client access. In some cases
we are contractually bound to separate information.
2)
Hosting firm charges support per instance, and Oracle licensing
costs are through the roof.
We've used VPD in the past,
and are moving away from it for performance reasons and ease of
individual client maintenance tasks.
My question is, with
11g and Workload Management, can multiple schemas (1 per
client), perform? I
have numerous reasons why multiple schemas would be better from
a database administration perspective, but I don't want to hurt
performance.
Answer:
I liked VPD from a reduced administrative perspective,
but if you don’t have a problem maintain multiple schemas, then
there should be no problem with many separate schemas, all
running inside the same instance!
In some cases, separate schemas make it easier to
manipulate performance.
For example, with multiple schemas, I can assign one
table for one schema owner to the KEEP pool, while excluding
other schemas.
I’ve worked
in many shops with “cloned” schemas, no problem,
and I have details in my book “Oracle
Tuning: The Definitive Reference”:
>> In some cases we are contractually bound
to separate information.
Ha!
I’ve seen that before, and it’s largely about ignorance!
Separation of data does not improve security, but I’ve
seen it used as a lame argument against VPD’s!
