Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using Oracle 11g database server and now I am getting to see that OCI's persistent connection isn't much of a use, and a new connection is created anyway when a new user accesses the page. I would like to have a pool of connections which are used, and no new connections are not created. I came across DRCP (database resident connection pooling), but I am unable to understand how to set the number of connections that can be created per server.

Questions

  • From the documentation available on the Oracle website, I can set the minimum and maximum number of pooled servers, but where would I be setting the number of connections??
  • Has anyone worked with Oracle 11g's database resident connection pooling?
  • If the database servers are unix boxes, can we do any ODBC connection pooling??
share|improve this question

2 Answers 2

up vote 2 down vote accepted

where would I be setting the number of connections?

You can specify the min and max number of connections in the pool using

SQL>execute dbms_connection_pool.configure_pool(null, minsize=>10, maxsize=>100);

If the database servers are unix boxes, can we do any ODBC connection pooling?

I'm not quite sure why you're asking this. You can take advantage of DRCP via OCI in PHP, as long as you have the Oracle 11g client. Just specify your connection string like myhost.dom.com:1521/sales:POOLED and make your connections using oci_pconnect().


Here's a few resources that may be of help:

share|improve this answer
    
Hello BenV, thank you for answering my question, but minsize and maxsize are mentioned to be the number of servers in the pool, so this mean, number of connections would be equal to number of servers in the pool?? –  macha Jan 25 '11 at 14:54
    
@macha: Where are you seeing that those values are the number of servers? If you look at this article it defines the minsize and maxsize attributes as the "number of pooled sessions that should be kept alive in the pool". –  BenV Jan 25 '11 at 16:12
    
DRCP also works with oci_connect, although according to the "PHP Scalabitily and High Availability" white paper, a new database session will be created through the pool. In my experience, however, using the oci8.connection_class parameter is essential and appears to let both persistent (oci_pconnect) and standard (oci_connect) connections pick up an existing session. –  Roy Nov 14 '12 at 11:28

BenV is right.+1  Since it sounds like you don't want your pool size to change, you should set the minsize the same as the maxsize. Here are some additional Oracle entries on DRCP:

The first link has a comparison of Dedicated Server, Shared Server, and DRCP. Shared server is another route you could consider, but it sounds like DRCP might be a better fit.

share|improve this answer
    
Hello Leigh, thank you for answering my question, I have a question about the number of connections at a given instance, minsize and maxsize are mentioned to be the number of servers in the pool, so this mean, number of connections would be equal to number of servers in the pool?? –  macha Jan 25 '11 at 14:54
    
When a connection is established the Connection Broker picks an available pooled server and hands off the client connection to that server. From there on out the connection acts just like a dedicated connection with that server processing everything for the client. When the session ends the server goes back into the pool rather than being terminated. So, if you have 10 sessions active then you would need at least 10 servers to keep client requests from having to be put in the wait queue. The advantage comes when clients have short connections allowing the server to get reuse. –  Leigh Riffel Jan 25 '11 at 15:24
    
Thank you for answering my query. :) –  macha Jan 25 '11 at 15:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.