This chapter provides tips on how to tune a Java application to run optimally on a TimesTen database. 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 statement pooling is transparent to an application. Use of the
PreparedStatement object, including preparing and closing the statement, is no different.
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. After the statement is closed, it will be removed from the pool.
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
A batch can consist of a set of
MERGE statements. Statements that return result sets, such as
SELECT statements, are not allowed in a batch. A SQL statement is added to a batch by calling
addBatch() on the statement object. 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 Release 11.2.1:
// 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 ();
// 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 above), 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, refer to the Javadoc 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 level, 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, making it a costly call.
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 detected before issuing the call. 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
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
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 callback thread itself if they are using
CLIENT_ACKNOWLEDGEMENT as acknowledgment mode. See "Receiving and processing updates".