RE: connection pooling from an application server to oracle

But Mladen, sooner or later, somebody is going to wise-up and catch you in your attempt at passing the blame. When this happens to me, I quote to 80-20 rule. 80% gain in application thruput is brought thru Sql tuning (or rewrite). If they say "The database is slow", I say "Show me the sql that is slow". If they say "I can't because it is coming from the app", i say "Then I can't help you".

This focuses everyones' energy into the correct place. If they built a monster that they can't debug, it's not my fault. If they can show me the sql, I can show them either how poorly it is written, or provide a new index to support them.

In my mind, this is a much more productive way of doing business. And, as I said, if they choose a specific app server without my input, then it's not my problem.

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Monday, December 15, 2003 10:39 AM To: Multiple recipients of list ORACLE-L

Such app server side connection pooling is a nightmare for a DBA. Http pages ([aj]sp junk,php,mod_plsql,mod_perl using Apache::DBI) will each open one connection per page, as well as most CGI scripts (Adam von Nieda's
exquisite oracle tool on http://www.oracletool.com is what comes to mind). If there is a problem with SQL, DBA has a way of finding out who is doing what because each session is connected to a single server process, which can then be located. With app server pooling, you have a single user logged in multiple times and each dedicated server can be used to execute any
single SQL statement. With iPlanet, it looks like this: Application invokes bean, pumpkin or servlet which has something to do with oracle. The app server pooling mechanism will allocate one of the already established dedicated server connections, which will perform the work. When the same user executes another vegetable (EJB or servlet), he or she may do so within a different dedicated server connection. We have a situation in which a single user utilizes several dedicated server connections
within a single logical screen. Whenever faced with a choice, I try preventing
that from happening and make users connect to dedicated servers with different
usernames and sessions which can be tied to the user. If I can't prevent it, I always use cache hit ratio (which tells me nothing) for blame shifting game.
Buffer cache hit ratio can always be interpreted in such a way that you can blame someone else. It's a DBA version of sun flares excuse found on Simon Trevaglia's wonderful BOFH web site. I am a DBA and it is my job to interpret
database statistics. The database statistics tells me that application server
pooling is naughty and sinful and should not be utilized. Q.E.D. It's not me,
it's BCHR. If I am persistent enough, I can get it off the system. Who was saying that BCHR isn't useful?

On 12/15/2003 09:49:26 AM, Justin Cave wrote:
> At 05:59 AM 12/15/2003, ryan_oracle_at_cox.net wrote:
> >The software engineers here are using an application server with
> >connection pooling to connect to our oracle instances.
> >They are doing it with a dedicated connection to Oracle. No MTS.
> >
> >This concerns me. how do you handle transaction control in this type of
> >environment? in this type of environment do you have to commit after
every
> >DML statement? since multiple users will access the database with the
same
> >conneciton?
>
> You may have only, say, 10 connections that are servicing 100 users, but
> each user will appear to have his or her own, dedicated connection. This
> works because users are going to tend to "open" a connection, do some
work,
> and "close" a connection in short order.
>
> The classic example of the utility of a connection pool is a web
> site. Since HTTP is a stateless protocol, most JSP pages will open and
> close a connection each time a user requests a page. Within the scope of
> generating the page, the JSP engine will issue a few SELECT statements and

> may do a little DML. Since it takes a fraction of a second (we hope) to
> generate a single web page, opening & closing a physical connection would
> obviously take an order of magnitude more time than generating the
contents
> of the page, the application server can be set to maintain a set of
> permanent database connections which it will parcel out to the individual
> JSP engines. As an added benefit, you now only need a number of
> connections in the connection pool equal to the number of truly concurrent

> sessions you have. Since users spend far more time digesting page
contents
> than the servlet engine spends building them, this typically means that
you
> can service hundreds of users with tens of connections. From a
transaction
> control standpoint, however, the important thing to remember is that each
> connection will be assigned to a particular user for the length of that
> user's transaction.
> Justin Cave
> Distributed Database Consulting
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Justin Cave
> INET: jcave_at_cableone.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

Mladen Gogala
Oracle DBA

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mladen_at_wangtrading.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mercadante, Thomas F
  INET: thomas.mercadante_at_labor.state.ny.us

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).