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

In PL/SQL, when aCHAR or aVARCHAR2 column is defined as aOUT orIN/OUT variable, the driver allocates aCHAR array of 32512 chars. This can cause a memory consumption problem. JDBC Thin driver does not allocate memory when usingVARCHAR2 output type. But JDBC OCI driver allocates memory for bothCHAR andVARCHAR2 types. So, CPU load in OCI driver is higher than Thin driver.

At previous releases, the solution to the problem was to invoke theStatement.setMaxFieldSize method. A better solution is to useOracleCallableStatement.registerOutParameter. Oracle encourages you always to callregisterOutParameter (int paramIndex, int sqlType, int scale, int maxLength) on eachCHAR orVARCHAR2 column. This method is defined inoracle.jdbc.driver.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 receivemessages that you are running out of cursors or that you are running out of memory, make sure that all yourStatement andResultSet objects are explicitly closed. Oracle JDBC drivers do not have finalizer methods. They perform cleanup routines by using theclose method of theResultSet andStatement 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 closeConnection 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.

Boolean Parameters in PL/SQL Stored Procedures

The JDBC drivers do not support the passing ofBOOLEAN parameters to PL/SQL stored procedures. If a PL/SQL procedure containsBOOLEAN values, you can work around the restriction by wrapping the PL/SQL procedure with a second PL/SQL procedure that accepts the argument as anINT and passes it to the first stored procedure. When the second procedure is called, the server performs the conversion fromINT toBOOLEAN.

The following is an example of a stored procedure,BOOLPROC, that attempts to pass aBOOLEAN parameter, and a second procedure,BOOLWRAP, that performs the substitution of anINT value for theBOOLEAN.

CREATE OR REPLACE PROCEDURE boolproc(x boolean)ASBEGIN[...]END;CREATE OR REPLACE PROCEDURE boolwrap(x int)ASBEGINIF (x=1) THEN  boolproc(TRUE);ELSE  boolproc(FALSE);END IF;END;// Create the database connection from a DataSourceOracleDataSource ods = new OracleDataSource();ods.setURL("jdbc:oracle:oci:@<...hoststring...>");ods.setUser("scott");ods.setPassword("tiger");Connection conn = ods.getConnection();CallableStatement cs = conn.prepareCall ("begin boolwrap(?); end;");cs.setInt(1, 1);cs.execute ();

Opening More Than 16 OCI Connections for a Process

You might find that you are not able 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 methodStatement.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 invokedStatement.execute waits on the server, and continues execution only when it receives the error reply message invoked by the other thread's call toStatement.cancel.

As a result,Statement.cancel 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 anIOException that frees the thread that invokedStatement.execute. In some circumstances, the server is hung, but JDBC does not receive anIOException.Statement.cancel does not free the thread that initiated theStatement.execute.

When JDBC does not receive anIOException, Oracle Net may eventually time out and close the connection. This causes anIOException and frees the thread. This process can take many minutes. For information about how to control this time-out, see the description of thereadTimeout property forOracleDatasource.setConnectionProperties. You can also tune this time-out with certain Oracle Net settings. See theOracle Database Net Services Administrator's Guide for more information.

The JDBC standard methodStatement.setQueryTimeout relies onStatement.cancel. If execution continues longer than the specified time-out interval, then the monitor thread callsStatement.cancel. 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 invokedStatement.execute.

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.

Statement.cancel andStatement.setQueryTimeout are not supported in the server-side internal driver. The server-side internal driver runs in the single-threaded server process; the Oracle JVM implements Java threads within this single-threaded process. If the server-side internal driver is executing a SQL statement, then no Java thread can callStatement.cancel. This also applies to the Oracle JDBC monitor thread.

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.

• Passoracle.net.READ_TIMEOUT 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 theENABLE=BROKEN parameter in theDESCRIPTION clause in the connect descriptor. Also, set a lower value fortcp_keepalive_interval.

• Enable Oracle Net DCD by settingSQLNET.EXPIRE_TIME=1 in thesqlnet.ora file on the server-side.

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 theOracle Net Services Administrator's Guide.

Thetrace 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 theSQLNET.ORA file control the gathering of trace information. After setting the parameters inSQLNET.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.

Third Party Debugging Tools

You can use tools such asJDBCSpy andJDBCTest from Intersolv to troubleshoot at the JDBC API level. These tools are similar toODBCSpy andODBCTest.