5

I am actually reading Oracle-cx_Oracle tutorial.

There I came across non-pooled connections and DRCP, Basically I am not a DBA so I searched with google but couldn't found any thing.

So could somebody help me understand what are they and how they are different to each other.

Thank you.

Raja G
  • 5,973
  • 14
  • 49
  • 82

3 Answers3

1

DRCP stands for Database Resident Connection Pooling as opposed to "non-pooled" connections

In short, with DRCP, Oracle will cache all the connections opened, making a pool out of them, and will use the connections in the pool for future requests.

The aim of this is to avoid that new connections are opened if some of the existing connections are available/free, and thus to safe database ressources and gain time (the time to open a new connection).

If all connections in the pool are being used, then a new connection is automatically created (by Oracle) and added to the pool.

In non pooled connections, a connection is created and (in theory) closed by the application querying a database.

For instance, on a static PHP page querying the database, you have always the same scheme :

  1. Open DB connection
  2. Queries on the DB
  3. Close the DB connection

And you know what your scheme will be.

Now suppose you have a dynamic PHP page (with AJAX or something), that will query the database only if the user makes some specific actions, the scheme becomes unpredictable. There DRCP can become healthy for your database, especially if you have a lot of users and possible requests.

This quote from the official doc fairly summarize the concept and when it should be used :

Database Resident Connection Pool (DRCP) is a connection pool in the server that is shared across many clients. You should use DRCP in connection pools where the number of active connections is fairly less than the number of open connections. As the number of instances of connection pools that can share the connections from DRCP pool increases, the benefits derived from using DRCP increases. DRCP increases Database server scalability and resolves the resource wastage issue that is associated with middle-tier connection pooling.

Thomas G
  • 9,886
  • 7
  • 28
  • 41
1

Web tier and mid-tier applications typically have many threads of execution, which take turns using RDBMS resources. Currently, multi-threaded applications can share connections to the database efficiently, allowing great mid-tier scalability. Starting with Oracle 11g, application developers and administrators and DBAs can use Database Resident Connection Pooling to achieve such scalability by sharing connections among multi-process as well as multi-threaded applications that can span across mid-tier systems.

DRCP provides a connection pool in the database server for typical Web application usage scenarios where the application acquires a database connection, works on it for a relatively short duration, and then releases it. DRCP pools "dedicated" servers. A pooled server is the equivalent of a server foreground process and a database session combined.

DRCP complements middle-tier connection pools that share connections between threads in a middle-tier process. In addition, DRCP enables sharing of database connections across middle-tier processes on the same middle-tier host and even across middle-tier hosts. This results in significant reduction in key database resources needed to support a large number of client connections, thereby reducing the database tier memory footprint and boosting the scalability of both middle-tier and database tiers. Having a pool of readily available servers also has the additional benefit of reducing the cost of creating and tearing down client connections.

DRCP is especially relevant for architectures with multi-process single threaded application servers (such as PHP/Apache) that cannot perform middle-tier connection pooling. The database can still scale to tens of thousands of simultaneous connections with DRCP.

mohsen.b
  • 436
  • 2
  • 8
1

DRCP increases the level of "centralization" of the pools:

  • Classic connection pool are managed within the client middleware. This means that if for instance you have several independent web servers, likely each one will have their own server-managed connection pool. There is a pool per server and the server is responsible for managing it. For instance you may have 3 separate pools with a limit of 50 connections per pool. Depending on usage patterns it may be a waste, because you may end up using the total 150 connection very seldom, and on the other hand you may hit the individual limit of 50 connections very often.
  • DRCP is a single pool managed by the DB server, not the client servers. This can lead to more efficient distribution of the connections. In the example above, the 3 servers may share the same pool, database-managed, of less than 150 connections, say 100 connections. And if two servers are idle, the third server can take up all the 100 connections if needed.

See Oracle Database 11g: The Top New Features for DBAs and Developers for more details and About Database Resident Connection Pooling:

This results in significant reduction in key database resources needed to support a large number of client connections, thereby reducing the database tier memory footprint and boosting the scalability of both middle-tier and database tiers

In addition, DRCP compensates the complete lack of middleware connection pools in certain technologies (quoted again from About Database Resident Connection Pooling):

DRCP is especially relevant for architectures with multi-process single threaded application servers (such as PHP/Apache) that cannot perform middle-tier connection pooling. The database can still scale to tens of thousands of simultaneous connections with DRCP.

As a further reference see for instance Connection pooling in PHP - Stack Overflow for instance.

Community
  • 1
  • 1
Antonio
  • 1,178
  • 14
  • 14