Establishing and maintaining a database connection involves a series of authentication, system and networking actions both at the client and at the database levels. This process consumes high resources and takes a relatively significant duration.
Connection pooling is a method for maintaining a cache of open database connections, that can be reused multiple times, without the need to establish a new connection for every new database client session. Popular DBMS APIs such as Microsoft ODBC and Java JDBC maintain connection pooling at database client level. Oracle database also features server side connection pooling named Database Resident Connection Pooling (DRCP). The use of DRPC is recommended when there is a big difference between the number of active connections and the number of open connections. Client or middle tier connection pooling keeps a minimum pool of open connections. Large scale database servers that handle dozens of applications, each keeps its own pool, creates many unused connections. DRCP enables maintaining a single pool on database server level that can be shared across multiple client pools.
On the server side DRCP is enabled by dbms_connection_pool.start_pool() command. The connection pool is terminated and disabled by dbms_connection_pool.stop_pool() command
The connection pool Pool can be configured using various procedures in the dbms_connection_pool package.
In order for clients to use DRCP it needs to add pooled value to server connection parameter, either at the end of EZConnect connection string, or at the server entry in tnsnames.ora configuration file.
Server parameter has three valid values: shared, dedicated and pooled. If the parameter value is not specified then server mode is determined by the Oracle listener. For each connection we can monitor the server mode by looking at the server column in v$session (or gv$session for Real Application Cluster) dynamic view.
This is how to connect as dedicated server using EZConnect:
And this how it looks on gv$session
So in order to connect to a pooled connection we just need to change the parameter above to pooled.
Only here we receive the above ORA-12520 error message, because the connection pooling was not started and initialized. So from other connection we will run start_pool() program.
and now we can retry to connect to the pool:
Looking at gv$session now, we can see that the connection is at POOLED status.
Understanding DRCP concept and implementation can be helpful to improve application performance and reduce resource consumption.