Oracle9i JDBC Developer's Guide and Reference Release 1 (9.0.1) Part Number A90211-01 |
|
This chapter describes how to optimize and troubleshoot a JDBC application or applet, including the following topics:
The Oracle JDBC drivers provide full support for programs that use Java multithreading. The following example creates a specified number of threads and lets you determine whether or not the threads will share a connection. If you choose to share the connection, then the same JDBC connection object will be used by all threads (each thread will have its own statement object, however).
Because all Oracle JDBC API methods are synchronized, if two threads try to use the connection object simultaneously, then one will be forced to wait until the other one finishes its use.
The program displays each thread ID and the employee name and employee ID associated with that thread.
Execute the program by entering:
java JdbcMTSample [number_of_threads
] [share]
Where number_of_threads
is the number of threads that you want to create, and share
specifies that you want the threads to share the connection. If you do not specify the number of threads, then the program creates 10 by default.
This example is repeated in "Multithreading--JdbcMTSample.java".
/* * This sample is a multi-threaded JDBC program. */ import java.sql.*; import oracle.jdbc.OracleStatement; public class JdbcMTSample extends Thread { // Default no of threads to 10 private static int NUM_OF_THREADS = 10; int m_myId; static int c_nextId = 1; static Connection s_conn = null; static boolean share_connection = false; synchronized static int getNextId() { return c_nextId++; } public static void main (String args []) { try { /* Load the JDBC driver */ DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); // If NoOfThreads is specified, then read it if ((args.length > 2) || ((args.length > 1) && !(args[1].equals("share")))) { System.out.println("Error: Invalid Syntax. "); System.out.println("java JdbcMTSample [NoOfThreads] [share]"); System.exit(0); } if (args.length > 1) { share_connection = true; System.out.println ("All threads will be sharing the same connection"); } // get the no of threads if given if (args.length > 0) NUM_OF_THREADS = Integer.parseInt (args[0]); // get a shared connection if (share_connection) s_conn = DriverManager.getConnection ("jdbc:oracle:" +args[1], "scott","tiger"); // Create the threads Thread[] threadList = new Thread[NUM_OF_THREADS]; // spawn threads for (int i = 0; i < NUM_OF_THREADS; i++) { threadList[i] = new JdbcMTSample(); threadList[i].start(); } // Start everyone at the same time setGreenLight (); // wait for all threads to end for (int i = 0; i < NUM_OF_THREADS; i++) { threadList[i].join(); } if (share_connection) { s_conn.close(); s_conn = null; } } catch (Exception e) { e.printStackTrace(); } } public JdbcMTSample() { super(); // Assign an Id to the thread m_myId = getNextId(); } public void run() { Connection conn = null; ResultSet rs = null; Statement stmt = null; try { // Get the connection if (share_connection) stmt = s_conn.createStatement (); // Create a Statement else { conn = DriverManager.getConnection("jdbc:oracle:oci8:@", "scott","tiger"); stmt = conn.createStatement (); // Create a Statement } while (!getGreenLight()) yield(); // Execute the Query rs = stmt.executeQuery ("select * from EMP"); // Loop through the results while (rs.next()) { System.out.println("Thread " + m_myId + " Employee Id : " + rs.getInt(1) + " Name : " + rs.getString(2)); yield(); // Yield To other threads } // Close all the resources rs.close(); rs = null; // Close the statement stmt.close(); stmt = null; // Close the local connection if ((!share_connection) && (conn != null)) { conn.close(); conn = null; } System.out.println("Thread " + m_myId + " is finished. "); } catch (Exception e) { System.out.println("Thread " + m_myId + " got Exception: " + e); e.printStackTrace(); return; } } static boolean greenLight = false; static synchronized void setGreenLight () { greenLight = true; } synchronized boolean getGreenLight () { return greenLight; } }
You can significantly enhance the performance of your JDBC programs by using any of these features:
Auto-commit mode indicates to the database whether to issue an automatic COMMIT
operation after every SQL operation. Being in auto-commit mode can be expensive in terms of time and processing effort if, for example, you are repeating the same statement with different bind variables.
By default, new connection objects are in auto-commit mode. However, you can disable auto-commit mode with the setAutoCommit()
method of the connection object (either java.sql.Conection
or oracle.jdbc.OracleConnection
).
In auto-commit mode, the COMMIT
operation occurs either when the statement completes or the next execute occurs, whichever comes first. In the case of statements returning a ResultSet
, the statement completes when the last row of the ResultSet
has been retrieved or when the ResultSet
has been closed. In more complex cases, a single statement can return multiple results as well as output parameter values. Here, the COMMIT
occurs when all results and output parameter values have been retrieved.
If you disable auto-commit mode with a setAutoCommit(false)
call, then you must manually commit or roll back groups of operations using the commit()
or rollback()
method of the connection object.
The following example illustrates loading the driver and connecting to the database. Because new connections are in auto-commit mode by default, this example shows how to disable auto-commit. In the example, conn
represents the Connection
object, and stmt
represents the Statement
object.
// Load the Oracle JDBC driver DriverManager.registerDriver(new oracle.jdbc.OracleDriver()); // Connect to the database // You can put a database hostname after the @ sign in the connection URL. Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger"); // It's faster when auto commit is off conn.setAutoCommit (false); // Create a Statement Statement stmt = conn.createStatement (); ...
Oracle JDBC connection and statement objects allow you to specify the number of rows to prefetch into the client with each trip to the database while a result set is being populated during a query. You can set a value in a connection object that affects each statement produced through that connection, and you can override that value in any particular statement object. The default value in a connection object is 10. Prefetching data into the client reduces the number of round trips to the server.
Similarly, and with more flexibility, JDBC 2.0 allows you to specify the number of rows to fetch with each trip, both for statement objects (affecting subsequent queries) and for result set objects (affecting row refetches). By default, a result set uses the value for the statement object that produced it. If you do not set the JDBC 2.0 fetch size, then the Oracle connection row-prefetch value is used by default.
For more information, see "Oracle Row Prefetching" and "Fetch Size".
The Oracle JDBC drivers allow you to accumulate INSERT
, DELETE
, and UPDATE
operations of prepared statements at the client and send them to the server in batches. This feature reduces round trips to the server. You can either use Oracle update batching, which typically executes a batch implicitly once a pre-set batch value is reached, or standard update batching, where the batch is executed explicitly.
For a description of the update batching models and how to use them, see "Update Batching".
This section describes some common problems that you might encounter while using the Oracle JDBC drivers. These problems include:
In PL/SQL, CHAR
columns defined as OUT
or IN
/OUT
variables are returned to a length of 32767 bytes, padded with spaces as needed. Note that VARCHAR2
columns do not exhibit this behavior.
To avoid this problem, use the setMaxFieldSize()
method on the Statement
object to set a maximum limit on the length of the data that can be returned for any column. The length of the data will be the value you specify for setMaxFieldSize()
, padded with spaces as needed. You must select the value for setMaxFieldSize()
carefully, because this method is statement-specific and affects the length of all CHAR
, RAW
, LONG
, LONG RAW
, and VARCHAR2
columns.
To be effective, you must invoke the setMaxFieldSize()
method before you register your OUT
variables.
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. The 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 result set or 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 objects on the server side.
Due to a restriction in the OCI layer, the JDBC drivers do not support the passing of BOOLEAN
parameters to PL/SQL stored procedures. If a PL/SQL procedure contains BOOLEAN
values, you can work around the restriction by wrapping the PL/SQL procedure with a second PL/SQL procedure that accepts the argument as an INT
and passes it to the first stored procedure. When the second procedure is called, the server performs the conversion from INT
to BOOLEAN
.
The following is an example of a stored procedure, BOOLPROC
, that attempts to pass a BOOLEAN
parameter, and a second procedure, BOOLWRAP
, that performs the substitution of an INT
value for the BOOLEAN
.
CREATE OR REPLACE PROCEDURE boolproc(x boolean) AS BEGIN [...] END; CREATE OR REPLACE PROCEDURE boolwrap(x int) AS BEGIN IF (x=1) THEN boolproc(TRUE); ELSE boolproc(FALSE); END IF; END; // Create the database connection Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@<...hoststring...>", "scott", "tiger"); CallableStatement cs = conn.prepareCall ("begin boolwrap(?); end;"); cs.setInt(1, 1); cs.execute ();
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.
This section describes strategies for debugging a JDBC program:
For information about processing SQL exceptions, including printing stack traces to aid in debugging, see "Processing SQL Exceptions".
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 on 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 on the internal operations of the event. This information is output 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.
Set the following parameters in the SQLNET.ORA
file on the client system.
Purpose: |
Turns tracing on/off to a certain specified level. |
Default Value: |
0 or OFF |
Available Values: |
|
Example: |
TRACE_LEVEL_CLIENT=10 |
Purpose: |
Specifies the name of the client trace file. |
Default Value: |
SQLNET.TRC |
Example: |
TRACE_FILE_CLIENT=cli_Connection1.trc |
Default Value: |
OFF |
Example: |
TRACE_UNIQUE_CLIENT = ON |
Set the following parameters in the SQLNET.ORA
file on the server system. Each connection will generate a separate file with a unique file name.
Purpose: |
Turns tracing on/off to a certain specified level. |
Default Value: |
0 or OFF |
Available Values: |
|
Example: |
TRACE_LEVEL_SERVER=10 |
Purpose: |
Specifies the destination directory of the trace file. |
Default Value: |
$ORACLE_HOME/network/trace |
Example: |
TRACE_DIRECTORY_SERVER=/oracle/traces |
Purpose: |
Specifies the name of the server trace file. |
Default Value: |
SERVER.TRC |
Example: |
TRACE_FILE_SERVER= svr_Connection1.trc |
You can use tools such as JDBCSpy and JDBCTest from Intersolv to troubleshoot at the JDBC API level. These tools are similar to ODBCSpy and ODBCTest.
Read-only connections are supported by the Oracle server, but not by the Oracle JDBC drivers.
For transactions, the Oracle server supports only the TRANSACTION_READ_COMMITTED
and TRANSACTION_SERIALIZABLE
transaction isolation levels. The default is TRANSACTION_READ_COMMITTED
. Use the following methods of the oracle.jdbc.OracleConnection
interface to get and set the level:
getTransactionIsolation()
: Gets this connection's current transaction isolation level.
setTransactionIsolation()
: Changes the transaction isolation level, using one of the TRANSACTION_*
values.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|