Re: Performance concerns with a single db functions servicing all data requests

On Aug 8, 12:17 am, "Ana C. Dent" <anaced..._at_hotmail.com> wrote:
> ar..._at_hotmail.com wrote innews:1186542440.579754.35890_at_w3g2000hsg.googlegroups.com:
>
>
>
>
>
> > On Aug 7, 10:52 pm, "Ana C. Dent" <anaced..._at_hotmail.com> wrote:
> >> ar..._at_hotmail.com wrote in news:1186539537.565447.282590
> >> @w3g2000hsg.googlegroups.com:
>
> >> > I am considering data access solution where every data request from
> >> > the applications invokes a single stored function, and I am
> >> > wondering if may result in resource contention.
>
> >> > The single generic function will accept a CLOB containing the id of
> >> > the calling process and the list of arguments - all recorded as XML
> >> > document. Based on this information the function will figure out
> >> > what stored programs to call, will execute them, and will return
> >> > all requested data in XML format (as CLOB).
>
> >> > The objective of this approach is to move all data access logic
> >> > from the mid-tier to the database, and to simplify mid-tier
> >> > development by having just a single servlet.
>
> >> > Can many simultaneous calls to the same PL/SQL object (disregarding
> >> > any possible data contention) in any way affect performance?
>
> >> The "simultaneous calls" will NOT be accessing the "same PL/SQL
> >> object". Each session will be using its own instantiated PL/SQL
> >> object.- Hide quoted text -
>
> >> - Show quoted text -
>
> > Thanks Ana.
> > Or in other words simultanious calls will occure only when two or more
> > sessions simoultaniously make the first call to the object?
>
> NO.
> Each session maintains its own address space & separate objects.

> I seriously question the scalability of this approach.
> If you are not utilizing bind variables, your application won't scale.- Hide quoted text -

All calls to the generic function will be identical: SELECT fn_generic(p_xml_params) FROM dual

How do bind variables come into play here? Received on Wed Aug 08 2007 - 14:06:03 CDT