Oracle JDBC Thin Connections
Version 19c

General Information
Note: 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.
 

Related Topics
DBMS_CHANGE_NOTIFICATION
SQL*Net
Tuning
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx