Re: VPD vs Multiple Schemas

From: joel garry <joel-garry_at_home.com>
Date: 5 Oct 2006 15:15:35 -0700
Message-ID: <1160086535.732603.260870@b28g2000cwb.googlegroups.com>

Tim Arnold wrote:
> "Shailesh" <shailesh.saraff_at_gmail.com> wrote in message
> news:1160046794.284345.196060_at_e3g2000cwe.googlegroups.com...
> > We are into healthcare software and now customers are requesting new
> > feature from our application, they want to run application in their
> > group of hospitals on single Oracle database. Available options are
> > listed below. what do you suggest? if I am missing any point please let
> > me know
> >
> > Single Schema (using Virtual Private Database)
> > ----------------------------------------------
> > Maintaining all database objects in the main schema and create user for
> > each hospital to access data from main schema. Hospital id will be
> > defined for each hospital and will part of PK of all tables. Security
> > policy based on hospital id can be defined for each table, which allows
> > other users to do DML (data-manipulation) as well as SELECT operations
> > only on those records in a table, for which a user has access.
> >
> > Application Level
> > -------------------
> > Advantages: No changes are required in our application.
> >
> >
> > Database Level
> > -----------------
> > Advantages: Easy to manage and maintain
> > Easy to upgrade and patch (Oracle)
> > SGA can be defined adequately
> > Advantage of sharing Sqls
> >
> > Disadvantages: VPD concept may sound complicated (to Management)
> > Downtime of All hospitals will be the same incase our application
> > version needs to be upgraded
> > Performance overhead (10%?) for SELECT while doing join operation or
> > sub select
> > Backup time will be more
> >
> >
> > Multiple Schema's:
> > ------------------
> > Create Schema for each hospital and maintain all schemas in single
> > database. Each User will access own schema objects.
> >
> > Application Level
> > -----------------
> > Disadvantages: All References to main schema needs to be removed from
> > our application. (e.g. Schemaname.TableName)
> >
> >
> > Database Level
> > ---------------
> > Advantages: Looks Simple and easy to understand (to Management)
> > Downtime can be planed for each Hospital for upgrading our application
> > version
> > All hospitals can run on different versions of our application
> > Schema level backup can be scheduled
> >
> > Disadvantages: Difficult to manage and maintain xx schema's
> > Difficult to upgrade and patch (Oracle)
> > SGA (Oracle Memory) to increase xx times (shared sql will not work)
> > Consumes more disk space (data, archive logs)
> > Accessing the Oracle data dictionary will have performance overhead
> > latching on the shared pool will be more
> >
> >
> > Please let me know.
> >
> > Thanks & Regards.
> >

> VPD is the way to go.
> I don't really follow why backup time would be increased.
> It can be a little tricky, but your biggest task is to sell it to
> management.
> The are a lot of HA solutions.

>

I speculate that comes from not being able to run backups on different servers at the same time.

VPD tables don't go in the recycle bin, not that anyone uses it anyways. Do they? Direct path exports ignore VPD too. Some replication must ignore VPD. FGAC eliminates online redefinition of tables.

Of course, we don't know how big the application is, codewise, nor what strangeness may lie within.

I like Jonathan's suggestion (assuming the partitioning option is financially viable).

Not sure why schema will use more disk space. Won't duplicate pctfree in schema be offset by extra table qualifier data in VPD? Isn't log file size dictated by transaction volume?

An advantage of schema is that you can arbitrarly give each schema its own tablespaces. Backups are by tablespace (or datafile...), so you might be able to do some parallelization across schemata by simply throwing hardware (ie, multiple tape drives or disk devices) at it. Even though centralization is a big thing right now, if the schemata are separate you can more easily move one to another machine when one customer grows faster than the others. So you can charge them more, right?

>From what I've seen, different customers requiring different versions is usually a controlling business requirement. That's a management decision, of course. Another controlling requirement is backup scheduling.

You might also search the bug database for VPD to see if anything might affect your particular application.

--
@home.com is bogus.
http://bbemporium.blogspot.com/2006/09/cult-of-capsaicin-50-cents-pdftxt.html

Received on Thu Oct 05 2006 - 17:15:35 CDT