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 ObservableConnectionDS method 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 addBatch() and executeBatch() methods for Statement or PreparedStatement objects.
A batch can consist of a set of INSERT, UPDATE, DELETE, or 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 executeBatch() method.
For 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 setXXX() calls followed by the addBatch() call. The batch is executed by the executeBatch() method.
TimesTen recommends the following batch sizes for Release 11.2.1:
256 for INSERT statements
31 for UPDATE statements
31 for DELETE statements
31 for MERGE 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 Statement or 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 ResultSet method 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 byte or 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 getXXX() 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 setXXX() 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 setString().
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 MessageListener method 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 Session object 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 callback thread itself if they are using CLIENT_ACKNOWLEDGEMENT as acknowledgment mode. See "Receiving and processing updates".