E Troubleshooting

Memory Consumption for CHAR Columns Defined as OUT or IN/OUT Variables

In PL/SQL, when a CHAR or a VARCHAR2 column is defined as a OUT or IN/OUT variable, the driver allocates a CHAR array of 32512 chars. This can cause a memory consumption problem. JDBC Thin driver does not allocate memory when using VARCHAR2 output type. But JDBC OCI driver allocates memory for both CHAR and VARCHAR2 types. So, CPU load in OCI driver is higher than Thin driver.

At previous releases, the solution to the problem was to invoke the Statement.setMaxFieldSize method. A better solution is to use OracleCallableStatement.registerOutParameter. Oracle encourages you always to call registerOutParameter (int paramIndex, int sqlType, int scale, int maxLength) on each CHAR or VARCHAR2 column. This method is defined in oracle.jdbc.OracleCallableStatement. Use the fourth argument, maxLength, to limit the memory consumption. This parameter tells the driver how many characters are necessary to store this column. The column is truncated if the character array cannot hold the column data. The third argument, scale, is ignored by the driver.

Memory Leaks and Running Out of Cursors

If you receive messages that you are running out of cursors or that you are running out of memory, make sure that all your Statement and ResultSet objects are explicitly closed. Oracle JDBC drivers do not have finalizer methods. They perform cleanup routines by using the close method of the ResultSet and Statement classes. If you do not explicitly close your result set and statement objects, significant memory leaks can occur. You could also run out of cursors in the database. Closing a statement releases the corresponding cursor in the database.

Similarly, you must explicitly close Connection objects to avoid leaking and running out of cursors on the server-side. When you close the connection, the JDBC driver closes any open statement objects associated with it, thus releasing the cursor on the server-side.

Opening More than 16 OCI Connections for a Process

You may find that you are unable to open more than approximately 16 JDBC-OCI connections for a process at any given time. The most likely reasons for this would be either that the number of processes on the server exceeded the limit specified in the initialization file, or that the per-process file descriptors limit was exceeded. It is important to note that one JDBC-OCI connection can use more than one file descriptor (it might use anywhere between 3 and 4 file descriptors).

If the server allows more than 16 processes, then the problem could be with the per-process file descriptor limit. The possible solution would be to increase this limit.

Using statement.cancel

The JDBC standard method Statement.cancel attempts to cleanly stop the execution of a SQL statement by sending a message to the database. In response, the database stops execution and replies with an error message. The Java thread that invoked Statement.execute waits on the server, and continues execution only when it receives the error reply message invoked by the call of the other thread to Statement.cancel method.

As a result, the Statement.cancel method relies on the correct functioning of the network and the database. If either the network connection is broken or the database server is hung, the client does not receive the error reply to the cancel message. Frequently, when the server process dies, JDBC receives an IOException that frees the thread that invoked Statement.execute. In some circumstances, the server is hung, but JDBC does not receive an IOException. The Statement.cancel method does not free the thread that initiated the Statement.execute method.

When JDBC does not receive an IOException, Oracle Net may eventually time out and close the connection. This causes an IOException and frees the thread. This process can take many minutes. For information about how to control this time-out, see the description of the readTimeout property for OracleDatasource.setConnectionProperties. You can also tune this time-out with certain Oracle Net settings.

The JDBC standard method Statement.setQueryTimeout relies on the Statement.cancel method. If execution continues longer than the specified time-out interval, then the monitor thread calls the Statement.cancel method. This is subject to all the same limitations described previously. As a result, there are cases when the time-out does not free the thread that invoked the Statement.execute method.

The length of time between execution and cancellation is not precise. This interval is no less than the specified time-out interval but can be several seconds longer. If the application has active threads running at high priority, then the interval can be arbitrarily longer. The monitor thread runs at high priority, but other high priority threads may keep it from running indefinitely. Note that the monitor thread is started only if there are statements executed with non zero time-out. There is only one monitor thread that monitors all Oracle JDBC statement execution.

Using JDBC with Firewalls

Firewall timeout for idle-connections may sever a connection. This can cause JDBC applications to hang while waiting for a connection. You can perform one or more of the following actions to avoid connections from being severed due to firewall timeout:

• If you are using connection caching or connection pooling, then always set the inactivity timeout value on the connection cache to be shorter than the firewall idle timeout value.

• Pass oracle.jdbc.ReadTimeout as connection property to enable read timeout on socket. The timeout value is in milliseconds.

• For both JDBC OCI and JDBC Thin drivers, use net descriptor to connect to the database and specify the ENABLE=BROKEN parameter in the DESCRIPTION clause in the connect descriptor. Also, set a lower value for TCP_KEEPALIVE_INTERVAL.

• Enable Oracle Net DCD by setting SQLNET.EXPIRE_TIME=1 in the sqlnet.ora file on the server-side.

Frequent Abrupt Disconnection from Server

If the network is not reliable, then it is difficult for a client to detect the frequent disconnections when the server is abruptly disconnected. By default, a client running on Linux takes 7200 seconds (2 hours) to sense the abrupt disconnections. This value is equal to the value of the tcp_keepalive_time property. If you want your application to detect the disconnections faster, then you must set the value of the tcp_keepalive_time, tcp_keepalive_interval, and tcp_keepalive_probes properties to a lower value at the operating system level.

Also, you must specify the ENABLE=BROKEN parameter in the DESCRIPTION clause in the connection descriptor. For example:

jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS=(PROTOCOL=tcp)(PORT=5221)(HOST=myhost))(CONNECT_DATA=(SERVICE_NAME=orcl)))

Network Adapter Cannot Establish Connection

You may receive the following error while trying to establish a connection from a JDBC application to an Oracle instance:

java.sql.SQLException: Io exception: 
  The Network Adapter could not establish connection
 
SQLException: SQLState (null) vendor code (17002)

This error may occur even if all or any of the following conditions is true:

• You are able to establish a SQL*Plus connection from the same client to the same Oracle instance.

• You are able to establish a JDBC OCI connection, but not a JDBC Thin connection from the same client to the same Oracle instance.

• The same JDBC application is able to connect from a different client to the same Oracle instance.

• The same behavior applies whether the initial JDBC connection string specifies a host name or an IP address.

One or more of the following reasons can cause this error:

• The host name to which you are trying to establish the connection is incorrect.

• The port number you are using to establish the connection is wrong.

• The NIC card supports both IPv4 and IPv6.

• The Oracle instance is configured for MTS, but the JDBC connection uses a shared server instead of a dedicated server.

You can quickly diagnose these above-mentioned reasons by using SQL*Plus, except for the issue with the NIC card. The following sections specify how to resolve this error, and also contains a sample application:

• Oracle Instance Configured with MTS Server Uses Shared Server

• JDBC Thin Driver with NIC Card Supporting Both IPv4 and IPv6

• Sample Application

   "jdbc:oracle:thin:@(DESCRIPTION="                   +
                         "(ADDRESS_LIST="              +
                             "(ADDRESS=(PROTOCOL=TCP)" +
                                      "(HOST=host)"    +
                                      "(PORT=port)"    +
                             ")"                       +
                         ")"                           +
                         "(CONNECT_DATA="              +
                             "(SERVICE_NAME=sid)"      +
                             "(SERVER=DEDICATED)"      +
                         ")"                           +
                       ")"

java.sql.SQLException: Io exception: The Network Adapter could not establish the connection Error Code: 17002

import java.sql.*;
public class Jdbctest
{
   public static void main (String args[])
   {
         try 
         {
                   /* Uncomment the next line for more connection information */
                   // DriverManager.setLogStream(System.out);          
                   /* Set the host, port, and sid below to match the entries in the listener.ora */         
                   String host = "myhost.oracle.com";
                   String port = "5221";
                   String sid  = "orcl";
                   // or pass on command line arguments for all three items         
                   if ( args.length >= 3 ) 
                   { 
                              host = args[0];
                              port = args[1];            
                              sid  = args[2];
                   }
                   
                   String s1 = "jdbc:oracle:thin:@" + host + ":" + port + ":" + sid ;
                   if ( args.length == 1 )
                   { 
                              s1 =  "jdbc:oracle:oci8:@" + args[0];
                   }
                   if ( args.length == 4 )
                   {
                               s1 = "jdbc:oracle:" + args[3] + ":@" + 
                                    "(description=(address=(host=" + host+ ")(protocol=tcp)(port=" + port+ "))(connect_data=(sid="+ sid + ")))";
                   } 
                   System.out.println( "Connecting with: " );
                   System.out.println( s1 );          
                   DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
                   Connection conn = DriverManager.getConnection( s1,"hr","hr");          
                   DatabaseMetaData dmd = conn.getMetaData();
                   System.out.println("DriverVersion:["+dmd.getDriverVersion()+"]");
                   System.out.println("DriverMajorVersion: ["+dmd.getDriverMajorVersion()+"]");
                   System.out.println("DriverMinorVersion: ["+dmd.getDriverMinorVersion()+"]");
                   System.out.println("DriverName:["+dmd.getDriverName()+"]");
                   if ( conn!=null ) 
                      conn.close();
                   System.out.println("Done.");
           } 
           catch ( SQLException e ) 
           { 
                   System.out.println ("\n*** Java Stack Trace ***\n");
                   e.printStackTrace();          
                   System.out.println ("\n*** SQLException caught ***\n");          
                   while ( e != null ) 
                   {
                               System.out.println ("SQLState: " + e.getSQLState ());             
                               System.out.println ("Message:  " + e.getMessage ());             
                               System.out.println ("Error Code:   " + e.getErrorCode ());             
                               e = e.getNextException ();             
                               System.out.println ("");          
                    }       
             }   
  }
}

Oracle Net Tracing to Trap Network Events

You can enable client and server Oracle-Net trace to trap the packets sent over Oracle Net. You can use client-side tracing only for the JDBC OCI driver; it is not supported for the JDBC Thin driver. You can find more information about tracing and reading trace files in the Oracle Net Services Administrator's Guide.

The trace facility produces a detailed sequence of statements that describe network events as they execute. "Tracing" an operation lets you obtain more information about the internal operations of the event. This information is printed to a readable file that identifies the events that led to the error. Several Oracle Net parameters in the SQLNET.ORA file control the gathering of trace information. After setting the parameters in SQLNET.ORA, you must make a new connection for tracing to be performed.

The higher the trace level, the more detail is captured in the trace file. Because the trace file can be hard to understand, start with a trace level of 4 when enabling tracing. The first part of the trace file contains connection handshake information, so look beyond this for the SQL statements and error messages related to your JDBC program.

TRACE_LEVEL_CLIENT=10

TRACE_LEVEL_SERVER=10

Third Party Debugging Tools

You can use tools such as JDBCSpy and JDBCTest from Intersolv to troubleshoot at the JDBC API level. These tools are similar to ODBC Spy and ODBC Test tools.