Hi Tom

thanks for the response.
what i want to know is the difference and the architectural point of view under which database residential connection pooling is beneficial. I have studied the documentation provided by you thank. what i learn is, in Drcp dispatcher uses different sessions than using different server pocessess(as in Mts) but in MTS dispatcher resides on database server. while for sessions to be pooled dispatcher must be on the client side. i hope i am right on concepts here

regards
Amir Riaz

But how the "Connection Broker" communicates with the "Pooled Servers" - by using queues allocated from the SGA as Shared Server does, or by using, say, OS sockets(IPC) ?

TIA of course.

Hi Tom

DRCP is wonderful I see the use of this feature greatly with proxy authentication. That way you dont have to authenticate the user time and again we can keep the connection open. normal connection pooling and connection pooling with proxy authentication have different connection times. for example if with simple connection pooling our getConnection procedure take 2ms then with connection pooling with proxy authentication it takes around about 25ms so by keeping the connection open we can increase application response time. Also i think it will provide performance better than simple conneciton pooling because we dont have to open and close conection for each statement. But currently i have seen no java based frame work who can support this feature. all the frame work getConnection at statement start and closes when statment ends.

Now my question is:

1. Can we have more than one connection Broker on the database

2 execute dbms_connection_pool.configure_pool (null,nsize=>10,maxsize=>100,inactivity_timeout=>300,max_think_time=>600);

inactivity_timeout=300 what will happen if i have a transcation on a certain session. i forget to commit. How my transcation will behave. rollback or commit because when the inactivity_timeout time run out the session has to be broken between client and server(as its been a dedicated session).
regards
Amir Riaz

hi Tom

i have read the oracle documentation about DRCP. In most of the example i have seen its on php giving me the feelings that DRCP is only for the use with php. my question is

Can i use DRCP connection pooling in place of application server connection pooling and with java web application.

I asked this question because java applications already have a connection pooling environment and i see no point in creating a connection pooling system in database unless we achieve some performance improvements. what are those performace goals which oracle tried to achieve with DRCP.

Marry Chrimas.

regards
Amir Riaz

that 's really interesting and creative.

Are you saying that DRCP is a better connection pooling system then we currently have in our application servers and it can be used with JAVA JDBC. But i am not able to understand if we use DRCP connection pooling where will the application server connection go will it integrate into DRCP or we dont use application server connection pooling?

Can you give us an example in jdbc. i am also having trouble with setting up DRCP so an example is needed.

With a J2EE Server, you configure minimum and maximum number of connections in the pool, so you can say "start with 10 and go to 30 if needed." You can also say "drop each extra connection after so many minutes of inactivity".

I just don't see how DRCP can make a significant difference. If you :

- configure connection pooling and statement caching;
- set fetch size to 100;
- set autocommit off and manage your transactions properly;
- use bind variables properly;

will DRCP make your "good" application much better? I doubt it.

And if you don't do all the above, will DRCP improve your "bad" application? Surely not as much as doing all of the above would.

DRCP is fabulous for PHP or for anybody who doesn't have an App Server, but those who do are already good to go.

okay understood

with j2ee we create extra connections which sits idle. so what they are idle and not consuming any CPU. so Why DRCP is better?

Another issue i see is with transcation. How DRCP handles transcations. Take the senerio where we have same connection pooling pool for the two application servers where in j2ee the transcation is handle separately by the two application servers in case of j2ee but in case of DRCP how application server handles the transcation because now connection pooling is in database.

hi

this is exactly what we need. How can i get a complete lists of optimizations done in oracle 11g. I visited otn but they show only the most obvious enhancements only. Does not shows what optimizations are done in execution plan like the one i saw in nested loops on this site.

In a RAC environment, when a node goes down, would DRCP throws out those idle dead connections immediately, and creates new ones on the surviving nodes to replace ?

A J2EE connection pool would not realize immediately that a connection is no longer available until it starts or tries to actually use it, right ?

Hi Tom, in 10gR2 however, a J2EE connection pool would not realize immediately that a connection is no longer available until
it starts or tries to actually use it.
Is my understanding correct?

The concept of connection pool can be deployed both in database and app server. Because database hadn't it several years ago, app server took the chance to implement it and declared it the best way. And now Oracle implements it. Because Oracle is more close to data than App server, it is more advantageous in this competition. An app pool above a database pool doesn't harm a lot if it improves a lot, similar to a database cache of an OS cache, or a database buffer above a disk IO buffer.

I guess we CANNOT say, now that the DRCP is available we DO NOT need the application server provided connection pool. Becuase for a 3 tier application architecture, the DRCP is still a remote hosted resource and the application will first need a connection to access DRCP, before it can use any of the connections maintained in it.

Therefore my conclusion is even if DRCP is available you still need the connection pool on the applicaion server. Without the application server connection pool, every call to the database/DRCP will encounter the same overheads as when connecting to a remote resource.

DRCP is good for database scalability but it will not help in application (3 tier web based) scalability.

Hello Tom,

My platform is Oracle 11.2.0.1.0 (32 bits) on Windows 2003 Server Standard Edition (32 bits).

My Web Application is PHP FastCGI on IIS Web Server with this configuration:

DRCP
execute dbms_connection_pool.configure_pool( pool_name =>'SYS_DEFAULT_CONNECTION_POOL', minsize => 10, maxsize => 200, incrsize => 2, session_cached_cursors => 50, inactivity_timeout => 300, max_think_time => 600, max_use_session => 500000, max_lifetime_session => 86400);

TNSNAMES.ORA
SID_POOL.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.5)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = POOLED)
(SERVICE_NAME = SID_POOL)
)
)

php.ini
oci8.connection_class = "SYS_DEFAULT_CONNECTION_POOL"
oci8.old_oci_close_semantics = Off

My php pages have this structure:

<?php
$strConnDB = OCILogon("USER", "PASSWORD", "SID_POOL", "WE8ISO8859P1");
.......
OCILogOff ($strConnDB);
?>

I tested my application with many requests and these were the results:

SELECT * FROM SYS.V$CPOOL_CC_STATS;

CCLASS_NAME
--------------------
USER.SYS_DEFAULT_CONNECTION_POOL

NUM_REQUESTS
--------------------
369273

NUM_HITS
--------------------
0

NUM_MISSES
--------------------
369273

NUM_WAITS
--------------------
236

WAIT_TIME
--------------------
0

CLIENT_REQ_TIMEOUTS
--------------------
0

NUM_AUTHENTICATIONS
--------------------
580

I don´t understand:
- Why Is NUM_HITS equal to zero?
- Why Does requests not match in the pool, if the user, password and class are the same?

Thanks for your help.

Regards

i had the same problem. DRCP. num_hits=0.

i change the ocilogon by oci_pconnect and it worked!

in this document you can find more information.

PHP Scalability and High Availability
Database Resident Connection Pooling and Fast Application Notification
An Oracle White Paper
April 2008

best regards.

Hello Tom,

I am testing for DRCP use

I made configuration on server and client as mentioned in document

Server:
executing
exec dbms_connection_pool.start_pool;
client:
editing tns entry for dedicated with
(SERVER = POOLED)

DRCP seems to be working as views belows shows rows which was blank before setting up drcp

SELECT * FROM v$cpool_cc_info;

POOL_NAME
--------------------------------------------------------------------------------
CCLASS_NAME
--------------------------------------------------------------------------------
SYS_DEFAULT_CONNECTION_POOL
myname.SHARED




SELECT pool_name, num_requests, num_hits, num_misses FROM v$cpool_stats;


POOL_NAME
--------------------------------------------------------------------------------
NUM_REQUESTS NUM_HITS NUM_MISSES
------------ ---------- ----------
SYS_DEFAULT_CONNECTION_POOL
3 0 3
but i am not able to demonstrate how it is pooling connections
like if i run multiple connections from same client(same client machine running multiple sqlplus connecting to remote server where drcp is connected using same tns entry)
at server side its shown as seperate sid,for each connection as usual,should after setup DRCP all connections
should use only 1 sid or other way to demonstrate its using pool of connection

in short how to test after setup to see its using connection pool

thanks

Hi, Tom,
we have several kinds of transactions against the same database, which is RAC (n=2), how can I assign the different transactions go to the different DRCP, or by tagging?
can I have DRCP by instance?

Thanks,

Hank

Hi Tom,
In one of your presentations, you recommended to let the application server to take care of the connection pooling because that's what the application server is for, and let the database server just do the database work. With the new feature DRCP in 11g, do you recommend to shift the task back to the database server from the app server? How do I know if my app server will work with the DRCP?

Thanks,
Danny

Hi Tom,

I have read and re-read the DRCP white paper, this thread, your links (one is broken btw) - and I am not a dba but an app developer - and almost all questions already refer to an existing system (and probably performing poorly else why be here?). I know many many answers on asktom have an 'it depends' clause (as I have read a lot of them!) but...

Designing/building a totally new system from scratch, to operate statelessly over the internet, withOUT a middle tier (just client and server), with best extensibility (we start with 10 users, expand to 100 and hope to extend it to 1000 or 10000 concurrent users - who will obviously be mostly idle in terms of db activity, since they leave their browser open whilst doing other things) which connection management do we choose:-

* DRCP (doesn't seem to fit but maybe I've missed the point)
* Shared Server (maybe)
* Re-think: actually, we should consider middle-tier pooling

Apache is the web server. Do we configure it to hold connections, or connect/ disconnect from the db on each user request?

The white paper particularly identifies PHP as a target user of DRCP, but what about Perl? Ruby? Python?

Basically: What, in your experience, is the best connection methodology for stateless internet connections to Oracle?

Thank you!