Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Performance concerns with a single db functions servicing all data requests

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

From: Steve Howard <>
Date: Thu, 09 Aug 2007 16:16:39 -0000
Message-ID: <>

On Aug 8, 8:02 pm, wrote:
> On Aug 8, 3:21 pm, Steve Howard <> wrote:
> > On Aug 7, 10:18 pm, wrote:
> > > 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?
> > > Thanks
> > > -Art
> > Hi Art,
> > I 'm not sure how a single servlet with only one CallableStatement is
> > any simpler than one servlet with say, ten CallableStatements?
> > I would also be concerned that a single function is a maintenance
> > nightnmare. If you have a PL/SQL package with different programs in
> > it, you can change one program at a time and not break the whole thing
> > (always a possibility).
> > If you are looking to move data processing logic to the database, you
> > can still do this without using only a single function.
> > In terms of performance, it seems like pay me now or pay me later.
> > You can overload packaged procedures with parameters if you like,
> > which will probably always be faster than parsing your own arguments
> > and deciding what to call.
> > I love questions like this, though, as it always leads to good design
> > discussion.
> > Regards,
> > Steve
> Without going into any details, I hope many would agree in principle
> that keeping data access logic in the database is overall a desirable
> objective. In that case, the proposed solution does not have to be
> simpler or faster. As long as it is not worse in these and other
> categories, it should be worth doing.
> I am now trying to figure out what the potential disadvantages of the
> proposed solution are. I asked about performance but I am glad people
> are brining up other concerns as well.
> As far as the concern about "maintenance nightmare", I think the
> opposite is true.
> The generic function is envisioned to be very thin. For every call it
> will do the same simple thing. It will query the table containing
> app_call_id / program_to_execute pairs to retrieve the name of the
> corresponding program and to execute it. (These calls will have to be
> assembled dynamically. Can that be a problem?)
> In a simple case that would be the end of it - the program_to_execute
> will return all requested data.
> In a more complex scenario the program_to_execute will consider
> calling fn_get_data1, fn_get_data2, and fn_get_data3. It may decide to
> execute any one, any two, or all three. This can get quite complex
> (just as it would if coded in the mid-tire) but these complexities
> will be coded in app_cal-specific functions rather than the single
> generic function.
> I think conceptually this is similar to many servlets, each having
> multiple callable statements, except that now the logic that
> determines what database programs to call is coded in the database.
> Thoughts?
> Thanks
> -Art

Hi Art,

You know, this kind of stuff always interests me, so I'm glad you posted this thread. I always want to make sure I understand why something is good or bad, rather than just parroting what I have always "known" (of which I am guilty, I am afraid).

It was such an unusual design you posted, I thought I would build a test case, just to make sure I understand what you are asking.

All I did was create two generic procedures (program1 and program2) that are called and return a different value from each other. The decision as to when each is called is based on input to a function (big_honking_function)

create or replace procedure program1(p_int in out number) is begin
  p_int := p_int * 100;

create or replace procedure program2(p_int in out number) is begin
  p_int := p_int * 2;

create or replace function big_honking_function (p_xml in xmltype)   return clob as
  l_num number := 2;
  l_clob1 varchar2(100) := p_xml.extract('/begin_xml/ foo2').getStringVal();
  l_clob2 varchar2(100) := '<foo2>number 2</foo2>' ||chr(10); begin
  if trim(l_clob1) = l_clob2 then
  end if;
  return 'value = ' || l_num;

I then created a very simple standalone java application that calls the function, with hardcoded values (I didn't spend *that* much time on it)...

import oracle.jdbc.OracleTypes;
import java.sql.*;
import oracle.jdbc.OracleCallableStatement;

public class testBigHonkingFunction {
  public static void main(String args[]) {     try {

      Connection conn =
      OracleCallableStatement cst =

(OracleCallableStatement)conn.prepareCall("{? = call big_honking_function(xmltype('<begin_xml><foo2>number 2</foo2></ begin_xml>'))}");
      cst.registerOutParameter (1, OracleTypes.VARCHAR);
    catch(Exception e) {


Other than the fact that I hardcoded the call logic, is that what you are referencing?

If so, I still think your calls to extract on the XMLType (I assume that is how you would parse the arguments in the database) would use a *lot* more CPU, as we have seen this when using extract on xmltypes.


Steve Received on Thu Aug 09 2007 - 11:16:39 CDT

Original text of this message