Labeling Connections in UCP – Adjusting with the API

After decompiling the ucp jar file, I was able to understand the labeling connection callback better and got to know that the implementation didn’t completely match with the jdbc developer guide. This post will detail out on a couple of mismatch’d points from the developer guide.

Advertisement

Oracle Case Insensitivity and JDBC Drivers

Natively Oracle is case sensitive which means if you executed a query for e.g.

select emp_id, emp_salary from employee where emp_name = 'amit';

it would search for the string ‘amit’ in lowercase. We can switch this to make the search behave case insensitively by setting the NLS_COMP and NLS_SORT parameters from Oracle’s globalization support which was previously called National Language Support (NLS).

Labelling Connections In Oracle UCP

Oracle’s Universal Connection Pool (UCP) provides a feature called connection labeling where an application could attach arbitrary name/value pairs to a connection.By associating particular labels with particular connection states, an application can retrieve an already initialized connection from the pool and avoid the time and cost of re-initialization. The initialization varies and could include simple state re-initialization that requires method calls within the application code or database operations that require round trips over the network.

Connection labeling is application-driven and requires the application to implement an callback interface called oracle.ucp.ConnectionLabelingCallback. The callback determines whether or not a connection with a requested label already exists. If no connections exist, the interface allows current connections to be configured as required.

Autogenerated Keys with Oracle JDBC Driver

JDBC 3.0’s autogenerated keys feature provides a way to retrieve values from columns that are part of an index or have a default value assigned. The method Statement.getGeneratedKeys can be called to retrieve the value of such a column. This method returns a ResultSet object with a column for each automatically generated key. Calling ResultSet.getMetaData on the ResultSetobject returned by getGeneratedKeys produces a ResultSetMetaData object that can be used to determine the number, type, and properties of the generated keys. A flag indicating that any auto-generated columns should be returned is passed to the methods execute, executeUpdate, or prepareStatement when the statement is executed or prepared.

How Does OCI JDBC Application Communicate With Oracle Database Server

This post details out on what happens behind the scenes when an JDBC application using OCI driver communicates with the oracle database server.

A sample jdbc program looks like

import java.sql.*;
class DatabaseAccess{
  public static void main (String args []) throws Exception
  {
        Class.forName ("oracle.jdbc.OracleDriver");
        Connection conn = DriverManager.getConnection
             ("jdbc:oracle:oci:@hostname:1521:service_name", "scott", "tiger");

        ....
        ....
  }
}

As you can see, the program uses the JDBC OCI driver (a type 2 JDBC driver) to create a database connection. The Oracle JDBC OCI driver converts the JDBC invocations to calls to OCI (Oracle Call Interface) which are then sent over by Oracle Net to the Oracle database server.

Database Connections Are Performance Expensive

While reading the Data Access Handbook I came across some facts about database connections. These connections are established by any JEE application to the database server.

• Creating a connection is performance-expensive compared to all other tasks a database application can perform.
• Open connections use a substantial amount of memory on both the database server and database client machines.
• Establishing a connection takes multiple network round trips to and from the database server.
• Opening numerous connections can contribute to out-of-memory conditions, which might cause paging of memory to disk and, thus, overall performance degradation.
• In today’s architectures, many applications are deployed in connection pooled environments, which are intended to improve performance.However, many times poorly tuned connection pooling can result in performance degradation. Connection pools can be difficult to design, tune, and monitor.

Oracle JDBC driver and connection thread safety

Oracle JDBC developer guide recommends not to share connections across multiple threads. Here is small excerpt from the Oracle guide

“Oracle JDBC drivers provide full support for, and are highly optimized for, applications that use Java multithreading. Controlled serial access to a connection, such as that provided by connection caching, is both necessary and encouraged. However, Oracle strongly discourages sharing a database connection among multiple threads. Avoid allowing multiple threads to access a connection simultaneously. If multiple threads must share a connection, use a disciplined begin-using/end-using protocol.”

Oracle Database Change Notification and OCI JDBC Driver

Oracle supports a feature called query change notification which allows an application to register queries with the database for either object change notification (default) or query result change notification. This feature is a good candidate for an application to implement caching.

The JDBC drivers can register SQL queries with the database and receive notifications in response to the following:

• DML or DDL changes on the objects associated with the queries
• DML or DDL changes that affect the result set

The notifications are published when the DML or DDL transaction commits (changes made in a local transaction do not generate any event until they are committed).

To use Oracle JDBC driver support for Database Change Notification, perform the following:

1. Registration: You first need to create a registration.
2. Query association: After you have created a registration, you can associate SQL queries with it. These queries are part of the registration.
3. Notification: Notifications are created in response to changes in tables or result set. Oracle database communicates these notifications to the JDBC drivers through a dedicated network connection and JDBC drivers convert these notifications to Java events.

Some good resources to understand this feature can be found from the oracle documentation.

This feature is not supported yet by the OCI JDBC driver (version 11.2.0.1.0). The registration step fails with an exception “java.sql.SQLException: Unsupported feature”. This can also be verified by decompiling the driver and having a look at the oracle.jdbc.driver.PhysicalConnection doRegisterDatabaseChangeNotification() method.
Note that the thin driver supports this feature.

Oracle DRCP and OCI JDBC Driver

Database Resident Connection Pooling(DRCP), a new feature of Oracle Database 11g pools database server processes and sessions (the combination is known as a pooled server), which are shared across connections from multiple application processes from the same host or from different hosts. A Connection Broker process (a background process in oracle) manages the pooled servers in the database instance. Clients are persistently connected and authenticated to the Broker.

A good resource to understand DRCP can be found here .

Enabling and Configuring the pool:

Every database instance of 11g has a default pool. The pool needs to be started before the clients can request for connections. The command below brings up the Broker, which registers itself with the database listener. This command needs to be execute using the DBA account.

SQL> execute dbms_connection_pool.start_pool;

Here dbms_connection_pool is the package name and start_pool is a stored procedure. A similar procedure named “stop_pool” exists to stop the connection pool.

Routing client connections to DRCP:

Currently DRCP interface is available for OCI and OCCI clients using TCP/IP protocol and simple database authentication (userid/password based). The clients must specify the server type as POOLED via the connect string as shown below:

myhost.dom.com:1521/sales:POOLED

OR

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp) (HOST=myhost.dom.com) (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sales) (SERVER=POOLED)))

Using OCI JDBC Driver

Currently the OCI JDBC Driver (version 11.2.0.1.0) does not have the support to make appropriate calls to OCI for DRCP and the strange fact is that the driver does not throw any exceptions which states that this feature is not supported.

If your java application tries to connect to the oracle database server instance using the above connection string after starting the connection pool, the application would be able to create connections to the database but on the database server every connection request would create a new connection/process instead of reusing the pooled one. This can be verified by querying the system view named ‘V$CPOOL_STATS’

Tracing OCI JDBC Driver

If you are using the Oracle’s OCI JDBC Driver to connect to the database, you might what to turn on logging/tracing to know what’s happening behind the scenes. Since the Oracle’s OCI JDBC driver internally calls OCI (Oracle Call Interface) there are two steps involved to turn on tracing.

1. Enabling the OCI JDBC driver logs
– To enable the driver logging, a different ojdbc jar needs to be used(i.e. the debug jar – ojdbc_g.jar). The new jar would have the detailed log statements. In addition to this one needs to add two system properties namely

•        -Doracle.jdbc.Trace=true and
•        -Djava.util.logging.config.file=/jdbc/demo/OracleLog.properties

This oracle documentation link gives an detailed insight on the steps to enable driver logs

2. Tracing the OCI dll function calls
– The OCI JDBC driver calls the oci dll to interact with the oracle server. To know what dll function calls are made use flextracer (evaluation version :)).