Andrew Fraser DBA

Database Resident Connection Pooling

(Update: Firewalls can prevent DRCP functioning correctly – details at end of this post)

Database Resident Connection Pooling is new with 11g, and should be used for PHP applications. It will allow them to use the same type of connection pooling that java applications have had for a long time – largely equalising performance differences with those two programming languages.

It just needs two things done to use it:

1) In target database:

SQL> exec dbms_connection_pool.start_pool

2) In client easy connect, add “:pooled” at the end of the easy connect string, so it will look like:

scott/tiger@myserver/mydb:pooled

Or if you use client tnsnames.ora , then add “(server=pooled)” in the “connect_data” part of the “description”, so it will look like:

MYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver.my.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = POOLED)
      (SERVICE_NAME = mydb.my.com)
    )
  )

3) For PHP to use this, you must be using oci8 v1.3 or above. That comes bundled with PHP v5.3 and above, or can be downloaded separately from http://pecl.php.net/package/oci8. Use phpinfo(); to see what version you are currently using.

This SQL will let you see if the connection pool is working correctly:

col cclass_name form a30
select cclass_name, num_hits, num_misses from gv$cpool_cc_stats ;

Other useful SQL for this:

select * from gv$cpool_stats ;
select * from gv$cpool_cc_stats ;
select program from gv$process where program like 'oracle%(N%)' ;
select * from dba_cpool_info ;

Oracle Documentation on this is excellent, better than the blog or forum posts I saw:
Reference
Net Services Administrator’s Guide
Programmer’s Guide
Database Administrator’s Guide
The Underground PHP and Oracle Manual – Chapter 14. But note that this incorrectly states that PHP 5.3 has not yet been released.

For RAC, load balancing and transparent application failover should also be used. Load balancing is on by default according to the documentation (although I haven’t tested that), but transparent application failover need to be explicitly specified in tnsnames.ora to be used.

Load balancing would have “load_balance = on” in description part of client tnsnames.ora – supposedly that is on by default.

But Transparent Application Failover is not on by default. To use it, again in client tnsnames.ora, add
“(failover=on)” to the description, and “(failover_mode=(type=select)(method=basic))” to the connect_data. Alternatively use “(type=session)” for the more minimal type TAF, which will save some overhead on select operations.

The tnsnames.ora entry will end up looking like:

MYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver1.my.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver2.my.com)(PORT = 1521))
    (LOAD_BALANCE = on)
    (FAILOVER = on)
    (CONNECT_DATA =
      (SERVER = POOLED)
      (SERVICE_NAME = mydb.my.com)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 5)
        (DELAY = 1)
      )
    )
  )

Transparent Application Failover is documented in this part of the Net Services Administrator’s Guide.

Update: Firewalls can prevent DRCP functioning correctly. For details see:

• 12g: How to Configure DRCP Broker to Use a Specific Listening IP Address [ID 1333553.1]
• 11g: ORA-12170 With Combination RAC, DRCP and a Firewall [ID 953277.1]
• A Guide to CMAN Configuration for 10g and Newer Releases [ID 298916.1]
• IP Tables

Examples of commands to relax linux firewall for the note 953277.1 issue (replace below IP addresses with those of your web servers):

cp -p /etc/sysconfig/iptables /etc/sysconfig/iptables.mycopy
iptables -A INPUT -p tcp -s 123.4.5.6 --dport 1024:65535 -j ACCEPT
iptables -A INPUT -p tcp -s 123.4.5.7 --dport 1024:65535 -j ACCEPT
service iptables save