This discussion is archived
7 Replies Latest reply: Jun 2, 2008 5:29 AM by kmohan RSS

Database Resident Connection Pooling (DRCP)

638745 Newbie
Currently Being Moderated
Hi there,

Im playing with DRCP on SLES but its not acting as I would expect. Given the lack of any hard info out there I thought I would check my understanding as its probably/possibly flawed!

I thought that with DRCP you would have lots of sessions in the database with a small pool of server processes (These are the "L000" processes).

If I set my pool to have a max-size of 3 for example then I can create TWO sessions (via SQLPLUS11g) using the POOLED processes (SERVER=POOLED).
The third process is used for "Authenitication". Ok. Fine. Good. All sessions are sat idle doing nothing and I can see in v$session and v$process that the sessions have picked up one of the "Lnnn" processes..

Now when I come to add another session , the create-session/connect from SQLplus hangs and waits for one of the other sessions to either time-out or be closed.

I thought DRCP would allow multiple sessions to share the same processes. Ie if session A is idle then its pooled server-process is released back to the pool - and hence would be available to be used by another session. IE a bit like shared-servers in well, Shared Server I guess!

If the above isnt the case then is this really "Pooling" or just a case of there being pre-spawned server processes knocking about to speed up connection requests?

Yours,
Bob
  • 1. Re: Database Resident Connection Pooling (DRCP)
    277993 Journeyer
    Currently Being Moderated
    See Differences Between Dedicated Servers, Shared Servers, and Database Resident Connection Pooling

    http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/manproc002.htm

    Configuring Database Resident Connection Pooling
    http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/manproc004.htm
  • 2. Re: Database Resident Connection Pooling (DRCP)
    638745 Newbie
    Currently Being Moderated
    Hi there,

    Thanks for that, but I have already read both of those articles. As I say - the impression from the docs is that the server processes are shared, but in my tests this doesnt seem to be the case. If you have 10 sessions then you need 10 server processes, regardless of whether the associates sessions are active/inactive.

    Is there anyone out there who has actually tested this? It is so simple to set up yet doesnt seem to quite work as per the documents

    I can't reconcile the line "scale to thousand of connections" from the docs with reality if each session grabs and holds a pooled server whether th session is idle or active. Otherwise it really is just Dedicated Server but with faster connect times.

    -Bob
  • 3. Re: Database Resident Connection Pooling (DRCP)
    436423 Explorer
    Currently Being Moderated
    Hi,

    I think the misunderstanding is on your part. Any connection pool (Oracle or not) simply provides a dedicated connection from a pool. The application grabs a connection, does something with it, then let's it go, back into the pool. Ideal for web connections which have no concept of state, grab a connection, produce the html from the database then let go, so the user can sit there looking at the page. So it only takes a few connections to service many times that number of actual end users. That's the sense they are shared in, each connection is used by any number of clients but while that client has got it it's their dedicated connection to do work with.

    The client application in your case is SQL*Plus. You've started two instances of SQL*Plus and typed 'connect' so grabbed a connection each, but your SQL*Plus prompts are sitting there and you haven't done anything with them, never mind disconnected to release the connection back to the pool!

    SQL*Plus is the wrong client to be testing this functionality with.

    So in short, what you are describing is exactly what I would expect.

    HTH

    Chris
  • 4. Re: Database Resident Connection Pooling (DRCP)
    638745 Newbie
    Currently Being Moderated
    Hi there,
    interesting - I see what you are saying. Im wondering if its just a case of bad documentation then.

    I was using http://www.oracle.com/technology/tech/oci/pdf/oracledrcp11g.pdf as one of my main sources of info. The two parts that led me to thionk that the server processes are shared as;

    "Clients are persistently connected and authenticated to the Broker. Clients
    request the Broker to provide pooled servers when they need to perform some database activity, use them, and then release them back for reuse by other clients." - IE when active then the pooled process is in use, when inactive its released back to the pool.

    "When the pooled servers are in use, they are equivalent to dedicated servers. Upon a
    request from the client on the persistent channel, the broker picks the appropriate pooled
    server and hands-off the client to that pooled server. The client directly communicates
    with the pooled server for all its database activity. The pooled server is handed back to
    the broker when the client releases it."

    The diagram in that document also definitely shows Applications having IDLE connection that are NOT using a pooled server.

    The charts that are in this document also shows number of connections vs memory usage. If it was the case as you say then these graphs wouldnt make sense.

    I have raised an SR with Oracle.
  • 5. Re: Database Resident Connection Pooling (DRCP)
    436423 Explorer
    Currently Being Moderated
    Hi,

    I can't believe no-one else on this forum was not able to jump in while I was busy!

    I wasn't aware of that OTN article I merely read the docco. However, having read this now and read your comments, I still don't get where you are coming from as (to me) the article just backs up what I think. But I could be wrong.

    In fact to quote exactly what you've posted:
    "Clients are persistently connected and authenticated to the Broker. Clients
    request the Broker to provide pooled servers when they need to perform some database activity, use them, and then release them back for reuse by other clients."

    I read this as clients get a connection from the broker, uses it and releases it. With emphasis on the client. So if the client hasn't released it, it's still got it.

    "...The pooled server is handed back to the broker when the client releases it."

    I mean again, emphasis on the client releasing it. If the client doesn't release it, it's still got it and so can't be used by any other client.

    I do believe the diagram is misleading, it seems to imply that an idle (grey) client is just 'connected' to the broker, implying for a pooled connection to be back in the pool then the client just has to be merely idle rather than releasing it. The Busy/Idle w.r.t. DB activity doesn't help either as we all know a client can be connected but not doing anything with that connection. So I see how that can cause confusion.

    The noddy little graph just shows to me a linear correlation between memory and pooled connections. What a suprise. This is exactly what I would expect. The more connections in your pool, the more memory you need.

    Lastly, if the DRCP didn't work this way, it would work differently to every other connection pool I've ever met. With a connection pool governed by the mid tier, it opens a bunch of dedicated connections to the DB with a common username. The mid tier language (java, .nyet, whatever) makes it transparent that a connection is coming from this pool and normally makes it transparent to release them back (the developers never need know).

    But as I said I could be wrong and I'm interested in what you find from the SR.
  • 6. Re: Database Resident Connection Pooling (DRCP)
    638745 Newbie
    Currently Being Moderated
    Hi Chris,
    thanks for the response :-) I see what you are saying. I have the SR open with Oracle but no response on it yet. I will put the results of the SR on this thread and then hang my head in shame :-)

    -Bob
  • 7. Re: Database Resident Connection Pooling (DRCP)
    kmohan Explorer
    Currently Being Moderated
    Hi, doesn't the white paper say
    "DRCP pools database server processes and sessions (the combination is known as a pooled server), which are shared ...."
    So it is not a large number of sessions using small number of servers. A large number of clients share a small number of pooled servers (server process + session).

    This white paper gives more details..
    http://www.oracle.com/technology/tech/php/pdf/php-scalability-ha-twp.pdf