11g New Feature: (DRCP) Database Resident Connection Pooling

Posted at Monday, February 04, 2008
Oracle 11g introduces a Database Server-side Connection pooling feature which holds much promise.

A great overview of this new feature can be found in this excellent article:
Oracle Database 11g:
The Top New Features for DBAs and Developers
by Arup Nanda: Caching and Pooling


I followed Arup's example, and just extended upon it:

Here is the tnsnames.ora entry I created just for connections that are going to use the database resident connection pool (DRCP):

[/u03/app/oracle/product/db/11g/network/admin cubs2@rac2]$ tail -19 tnsnames.ora

CUBS_POOL.COLESTOCK.TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip)(PORT = 1522))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVER = POOLED)
(SERVICE_NAME = cubs.colestock.test)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)

Of course, I did this on both of my instances.

Next, I cut and pasted Arup's Java class and modified it for my purposes:

[/tmp cubs2@rac2]$ more CacheTest.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class CacheTest {
private String jdbcURL = "jdbc:oracle:oci8:@CUBS_POOL";
private Connection conn = null;
public CacheTest( ) throws ClassNotFoundException {
Class.forName("oracle.jdbc.driver.OracleDriver");
}
public static void main(String[] args) throws ClassNotFoundException, SQLException {
CacheTest check = new CacheTest();
check.dbconnect();
check.doSomething();
}
public void dbconnect() throws SQLException {
System.out.println("Connecting with URL="+jdbcURL+" as scott/tiger");
try {
conn = DriverManager.getConnection( jdbcURL, "scott" , "tiger");
System.out.println("Connected to Database");
} catch (SQLException sqlEx) {
System.out.println(" Error connecting to database : " + sqlEx.toString());
}
}
public void doSomething() throws SQLException {
Statement stmt = null;
ResultSet rset = null;
try {
stmt = conn.createStatement();
System.out.println("Created Statement object");
rset = stmt.executeQuery("select /*+ result_cache */ count(*) from scott.dept");
System.out.println("Retrieved ResultSet object");
if(rset.next())
System.out.println("Result:"+rset.getString(1));
} catch (SQLException sqlEx) {
} finally {
try {
System.out.println("Closing Statment & ResultSet Objects");
if (rset != null) rset.close();
if (stmt != null) stmt.close();
if (conn != null) {
System.out.println("Disconnecting...");
conn.close();
System.out.println("Disconnected from Database");
}
} catch (Exception e) { }
}
}
}

Then I compiled it:

[/tmp cubs2@rac2]$ export ORACLE_SID=cubs2
[/tmp cubs2@rac2]$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11g is /u03/app/oracle
[/tmp cubs2@rac2]$ $ORACLE_HOME/jdk/bin/javac -verbose -classpath .:$ORACLE_HOME/jdbc/lib/jodbc5.jar CacheTest.java
[parsing started CacheTest.java]
[parsing completed 52ms]
[search path for source files: [.]]
[search path for class files: [/u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/jsse.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/jce.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/charsets.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/ext/localedata.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/ext/sunjce_provider.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/ext/sunpkcs11.jar, /u03/app/oracle/product/db/11g/jdk/jre/lib/ext/dnsns.jar, .]]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/Connection.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/DriverManager.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/ResultSet.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/SQLException.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/sql/Statement.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Object.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/String.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/ClassNotFoundException.class)]
[checking CacheTest]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Exception.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Throwable.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Class.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/System.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/io/PrintStream.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/io/FilterOutputStream.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/io/OutputStream.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/util/Properties.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Error.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/RuntimeException.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/StringBuilder.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/AbstractStringBuilder.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/CharSequence.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/io/Serializable.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/Comparable.class)]
[loading /u03/app/oracle/product/db/11g/jdk/jre/lib/rt.jar(java/lang/StringBuffer.class)]
[wrote CacheTest.class]
[total 423ms]

Now, the sample Java Class is ready to go. I created a wrapper script which calls this Class from a bash while loop:

[/tmp cubs2@rac2]$ more run_CacheTest.bsh
#!/bin/bash

export ORACLE_SID=cubs2;
. oraenv ;

while true
do
$ORACLE_HOME/jdk/bin/java -classpath /tmp/:.:$ORACLE_HOME/jdbc/lib/ojdbc5.jar CacheTest
done

Next, we have to start the default connection pool:

SQL> exec dbms_connection_pool.start_pool;

PL/SQL procedure successfully completed.

I placed one on these scripts on each of my database hosts and adjusted the $ORACLE_SID value as appropriate. I will call many instances of this script (using nohup to the background) from each database server to perform a load test; for example:

[/tmp cubs1@rac1]$ nohup /tmp/run_CacheTest.bsh &
[5] 24453
nohup: [/tmp cubs1@rac1]$ appending output to `nohup.out'

The combination of the DRCP as well as the results cache, should allow me to scale well beyond prior limits.

Here are the some of the results:

As you can see, I am simply using the default connection pool:

SQL> select pool_name, inst_id, num_open_servers, num_busy_servers, num_requests, num_waits, historic_max, num_purged from gv$cpool_stats;


POOL_NAME                         INST_ID NUM_OPEN_SERVERS NUM_BUSY_SERVERS NUM_REQUESTS  NUM_WAITS HISTORIC_MAX NUM_PURGED
------------------------------ ---------- ---------------- ---------------- ------------ ---------- ------------ ----------
SYS_DEFAULT_CONNECTION_POOL 1 14 2 2448 0 14 0
SYS_DEFAULT_CONNECTION_POOL 2 10 1 562 0 10 0


Note the high 'Find Count' for each instance:

SQL> select * from gv$result_cache_statistics order by name, inst_id;



INST_ID ID NAME VALUE
---------- ---------- ---------------------------------------- ----------
1 3 Block Count Current 32
2 3 Block Count Current 32
1 2 Block Count Maximum 1728
2 2 Block Count Maximum 1728
1 1 Block Size (Bytes) 1024
2 1 Block Size (Bytes) 1024
1 6 Create Count Failure 0
2 6 Create Count Failure 0
1 5 Create Count Success 1
2 5 Create Count Success 3
1 9 Delete Count Invalid 0
2 9 Delete Count Invalid 0
1 10 Delete Count Valid 0
2 10 Delete Count Valid 0
1 7 Find Count 3426
2 7 Find Count 1292
1 8 Invalidation Count 0
2 8 Invalidation Count 0
1 4 Result Size Maximum (Blocks) 86
2 4 Result Size Maximum (Blocks) 86

As you can see, I was able to perform over a thousand executions on the cluster with virtual no I/O over an 11-minute period:

select * from
2 (select instance_number, executions_total - lag(executions_total,1) over (order by instance_number, snap_id) "EXECUTIONS_TOTAL",snap_id
3 from dba_hist_sqlstat
4 where sql_id='1gy9qq7yjm054')
5 where snap_id=96;


INSTANCE_NUMBER EXECUTIONS_TOTAL    SNAP_ID
--------------- ---------------- ----------
1 662 96
2 529 96

Here are the AWR reports from the test (both instances) - notice the efficiency:

CUBS1 Instance AWR Report
CUBS2 Instance AWR Report


Labels: , ,