Blog Archives

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).

Read the rest of this entry

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. Read the rest of this entry

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 Client Result Query Cache

Oracle 11g introduces a number of features to expand the use of memory caching in order to improve query performance and decrease the overall memory requirements of queries and applications. In addition to the features that support server-side caching, there is also a new feature to support a client-side result cache that allows OCI clients to fetch result sets stored in process memory instead of requiring access to the server’s memory. The “Client Result Cache” is enabled and configured at the database level and available to all OCI-based clients.

Using the client’s memory to cache query result sets significantly improves the performance of repetitive queries by OCI-based clients that would otherwise require a cache hit on the server-side. The client cache utilizes per-process memory on the OCI client and its contents can be shared across multiple sessions and threads. In certain situations, this will greatly reduce the number of round-trips between the client and the database server, reducing CPU consumption on the server and greatly increasing the response times. Situations that will benefit most from the client cache are queries that use small lookup tables or mostly read-only tables. 

Note that this feature is not supported in Standard Edition.

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 :)).