|Oracle® TimesTen In-Memory Database Java Developer's Guide
Part Number E13068-05
This chapter provides tips on how to tune a Java application to run optimally on a TimesTen data store. See "TimesTen Database Performance Tuning" in Oracle TimesTen In-Memory Database Operations Guide for more general tuning tips.
This chapter is organized as follows:
This section describes general principles to consider when tuning Java applications for TimesTen. It includes the following topics:
Note:Also see "Working with TimesTen result sets: hints and restrictions" and the notes in "Binding parameters and executing statements".
TimesTen supports prepared statement pooling for pooled connections, as discussed in the JDBC 3.0 specification, through the TimesTen
ObservableConnectionDS class. This is the TimesTen implementation of
ConnectionPoolDataSource. Note that once enabled, statement pooling is transparent to an application. Use of the
PreparedStatement object, including preparing and closing the statement, is no different.
You can enable prepared statement pooling and specify the maximum number of statements in the pool by calling the
setMaxStatements(). A value of 0, the default, disables prepared statement pooling. Any integer value greater than 0 enables prepared statement pooling with the value taken as the maximum number of statements. Once set, this value should not be changed.
Prepared statements or callable statements will be pooled at the time of creation if the pool has not reached its capacity. In Java 6, you can remove a prepared statement or callable statement from the pool by calling
setPoolable(false) on the statement object. The statement will be removed from the pool once it is closed.
Important:With prepared statement pooling, JDBC considers two statements to be identical if their SQL (including comments) is identical, regardless of other considerations such as optimizer settings. Do not use prepared statement pooling in a scenario where different optimizer hints may be applied to statements that are otherwise identical. In this scenario, a statement execution may result in the use of an identical statement from the pool with an unanticipated optimizer setting.
You can improve performance by using groups, referred to as batches, of statement executions, calling the
executeBatch() methods for
Statement objects, a batch typically consists of a set of INSERT or UPDATE statements. Statements that return result sets are not allowed in a batch. A SQL statement is added to a batch by calling the
addBatch() method. The set of SQL statements associated with a batch are executed through the
PreparedStatement objects, a batch consists of repeated executions of a statement using different input parameter values. For each set of input values, create the batch by using appropriate
() calls followed by the
addBatch() call. The batch is executed by the
TimesTen recommends the following batch sizes for the Release 11.2.1:
256 for INSERT statements
31 for UPDATE statements
31 for DELETE statements
Example 5-1 Batching statements
// turn off autocommit conn.setAutoCommit(false); Statement stmt = conn.createStatement(); stmt.addBatch("INSERT INTO employees VALUES (1000, 'Joe Jones')"); stmt.addBatch("INSERT INTO departments VALUES (260, 'Shoe')"); stmt.addBatch("INSERT INTO emp_dept VALUES (1000, 260)"); // submit a batch of update commands for execution int updateCounts = stmt.executeBatch(); conn.commit ();
Example 5-2 Batching prepared statements
// turn off autocommit conn.setAutoCommit(false); // prepare the statement PreparedStatement stmt = conn.prepareStatement ("INSERT INTO employees VALUES (?, ?)"); // first set of parameters stmt.setInt(1, 2000); stmt.setString(2, "Kelly Kaufmann"); stmt.addBatch(); // second set of parameters stmt.setInt(1, 3000); stmt.setString(2, "Bill Barnes"); stmt.addBatch(); // submit the batch for execution. Check update counts int updateCounts = stmt.executeBatch(); conn.commit ();
For either a
PreparedStatement object, the
executeBatch() method returns an array of update counts (
updateCounts in Example 5-1 and Example 5-2), with one element in the array for each statement execution. The value of each element can be any of the following:
A number indicating how many rows in the database were affected by the corresponding statement execution.
SUCCESS_NO_INFO, indicating the corresponding statement execution was successful, but the number of affected rows is unknown.
EXECUTE_FAILED, indicating the corresponding statement execution failed.
Once there is a statement execution with EXECUTE_FAILED status, no further statement executions will be attempted.
For more information about using the JDBC batch update facility, see the Java Platform API specification for the
Statement interface, particularly information about the
executeBatch() method, at the following location:
TimesTen provides an extension that allows an application to fetch multiple rows of data. For applications that retrieve large amounts of TimesTen data, fetching multiple rows can increase performance greatly. However, when using read committed isolation, locks are held on all rows being retrieved until the application has received all the data, decreasing concurrency. For more information on this feature, see "Fetching multiple rows of data".
Because Java strings are immutable, the
getString() must allocate space for a new string, in addition to translating the underlying C-string to a Unicode string, each time it is called. This is one of the costliest calls in JDBC.
In addition, you should not call
getString() on primitive numeric types, like
int, unless it is absolutely necessary. It is much faster to call
getInt() on an integer column, for example.
TimesTen instruction paths are so short that even small delays due to data conversion can cause a relatively large percentage increase in transaction time.
Use the appropriate
() method on a
ResultSet object for the data type of the data in the underlying database. For example, if the data type of the data is DOUBLE, to avoid data conversion in the JDBC driver you should call
getDouble(). Similarly, use the appropriate
() method on the
PreparedStatement object for the input parameter in an SQL statement. For example, if you are inserting data into a CHAR column using a
PreparedStatement, you should use
This section contains specific performance tuning tips for applications that use the JMS/XLA API. JMS/XLA has some overhead that makes it slower than using the C XLA API. In the C API, records are returned to the user in a batch. In the JMS model an object is instantiated and each record is presented one at a time in a callback to the
onMessage(). High performance applications can use some tuning to overcome some of this overhead.
This section includes the following topics:
Note:See "Access control impact on XLA" for access control considerations relevant to JMS/XLA.
The code underlying the JMS layer that reads the transaction log is more efficient if it can fetch as many rows as possible before presenting the object/rows to the user. The amount of prefetching is controlled in the
jmsxla.xml configuration file with the
xlaPrefetch parameter. Set the prefetch count to a large value like 100 or 1000.
Calls to the C XLA function
ttXlaAcknowledge move the bookmark and involve updates to system tables, so one way to increase throughput is to wait until several transactions have been seen before issuing the call. This means that the reader application must have some tolerance for seeing the same set of records more than once. Moving the bookmark can be done manually using the
Session CLIENT_ACKNOWLEDGE mode when instantiating a session:
Session session = connection.createSession (false, Session.CLIENT_ACKNOWLEDGE);
For many applications, setting this value to 100 is a reasonable choice.
Refer to "ttXlaAcknowledge" in Oracle TimesTen In-Memory Database C Developer's Guide for information about this function.
The synchronous interface is suitable only for applications with low event rates and for which AUTO_ACKNOWLEDGE or DUPS_OK_ACKNOWLEDGE acknowledgment modes are acceptable. Applications that require CLIENT_ACKNOWLEDGE acknowledgment mode and applications with high event rates should use the asynchronous interface for receiving updates. They should acknowledge the messages on the call back thread itself if they are using CLIENT_ACKNOWLEDGEMENT as acknowledgment mode. See "Receiving and processing updates".