Oracle | JDBC Thin Connections

Oracle JDBC Thin Connections Version 19c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

The following advice is given not to teach scripting in PERL but rather to establish, in writing, some best practices for developers and DBAs using PERL to work with an Oracle database. Be sure you are installing the latest version of your JDBC driver, patching regularly, and monitoring that you are keeping current every time you update your database.

And, if you are not using DBMS_CHANGE_NOTIFICATION, you aren't trying very hard to get the best performance from your Java-based applications.

Array size

Optimize the array size with real-world tests using your database and your application. Do not accept the default value which is 10 unless you are looking for an opportunity to tune it later and claim immediate success.

You can optimize performance by altering the array size with the Statement, PreparedStatement, CallableStatement, and ResultSet methods. For example:

void setFetchSize(int rows) throws SQLException

      

      int getFetchSize() throws SQLException

Be sure that you set the fetch size on resultset instead of the callable statement in the program
Use the latest JDBC drivers
Set the SDU parameter on both the client and database server

java.sql.Statement.setFetchSize(100)

Auto Commit Oracle performance is optimized when commits are performed for the transaction, not the statement. By default an Oracle Database does not auto-commit.

If the application is written for Oracle and contains explicit commits stop the JDBC driver from doing its default performance-killing behavior.

At the statement level set with <connection_object>.setAutoCommit(False)

It is best to set the fetch size on resultSet rather than the callable statement in the program.

conn.setAutoCommit(false)

Connect Timeout set through SQL*Net property CONNECT_TIMEOUT See SQL*Net Link Below

Fetch Direction Oracle Databases only support the forward direction which should be explicitly specified.

ResultSet.FETCH_FORWARD

Read Timeout set through READ_TIMEOUT. Do not use as a query-timeout See SQL*Net Link Below

SDU

passed through the connect string, Be sure that you set the SDU on both the database server and on each and every client. jdbc:oracle:thin:@(DESCRIPTION ... (SDU=<value>)...)

jdbc:oracle:thin:@(DESCRIPTION ...(SDU=32767)...)

Timeout

Enable query timeout to better manage resource utilization Use statement.cancel for DML statements such as SELECT, UPDATE, DELETE and MERGE

Use the Connection.cancel method for SQL operations like COMMIT, ROLLBACK, and so on..or Statement.setQueryTimeout

Oracle recommends not using Thread.interrupt method.