Client/Server Tuning

Diagnose Client/Server Performance

Performance impact: Variable

You can analyze your client/server performance with statistics that are tracked in the SYS.SYSTEMSTATS table.

These statistics can also be viewed with either the ttStats utility or the TT_STATS PL/SQL package.

Note:

For more details on the SYS.SYSTEMSTATS table, see SYS.SYSTEMSTATS in the Oracle TimesTen In-Memory Database System Tables and Views Reference. For details on the ttStats utility, see ttStats in the Oracle TimesTen In-Memory Database Reference. For more details on the TT_STATS PL/SQL package, see TT_STATS in the Oracle TimesTen In-Memory Database PL/SQL Packages Reference.

Total number of executions from a client/server application.
Total number of INSERT, UPDATE, DELETE, SELECT, MERGE, ALTER, CREATE, DROP statements run by the server.
Total number of transactions committed or rolled back by the server.
Total number of table rows inserted, updated, or deleted by the server.
Total number of client/server roundtrips.
Total number of bytes transmitted or received by the server.
Total number of client/server disconnects.

Work Locally When Possible

Performance impact: Large

Using a TimesTen client to access databases on a remote server system adds network overhead to your connections. Whenever possible, write your applications to access TimesTen locally using a direct driver, and link the application directly with TimesTen.

Choose Timeout Connection Attributes for Your Client

Performance impact: Variable

By default, connections wait 60 seconds for a TimesTen client and server to complete a network operation. The TTC_Timeout attribute determines the maximum number of seconds a client application waits for the result from the corresponding server process before timing out.

If you are using multiple servers for automatic client failover, then you can set the TTC_ConnectTimeout attribute to specify the maximum number of seconds the client waits for a SQLDriverConnect or SQLDisconnect request. It overrides the value of TTC_Timeout for those requests. Set the TTC_ConnectTimeout when you want connection requests to timeout with a different timeframe than the timeout provided for query requests. For example, you can set a longer timeout for connections if you know that it takes a longer time to connect, but set a shorter timeout for all other queries.

The overall elapsed time allotted for the SQLDriverConnect call depends on if automatic client failover is configured:

If there is just one TTC_SERVERn connection attribute defined, then there is no automatic client failover configured and the overall elapsed time allotted for the initial connection attempt is limited to 10 seconds.
If automatic client failover is defined and there is more than one TTC_SERVERn connection attribute configured, then the overall elapsed time is four times the value of the timeout (whether TTC_Timeout or TTC_ConnectTimeout). For example, if the user specifies TTC_TIMEOUT=10 and there are multiple TTC_SERVERn servers configured, then the SQLDriverConnect call could take up to 40 seconds.

However, within this overall elapsed timeout, each connection attempt can take the time specified by the TTC_Timeout or TTC_ConnectTimeout value, where the TTC_ConnectTimeout value overrides the TTC_Timeout value.

See Choose SQL and PL/SQL Timeout Values for information about how TTC_Timeout and TTC_ConnectTimeout relates to SQL and PL/SQL timeouts.

See TTC_Timeout and TTC_ConnectTimeout in the Oracle TimesTen In-Memory Database Reference.

Choose a Lock Wait Timeout interval

Performance impact: Variable

By default, connections wait 10 seconds to acquire a lock. To change the timeout interval for locks, set the LockWait conection attribute or use the ttLockWait built-in procedure.

When running a workload of high lock-contention potential, consider setting the LockWait connection attribute to a smaller value for faster return of control to the application, or setting LockWait to a larger value to increase the successful lock grant ratio (with a risk of decreased throughput).

See Setting Wait Time for Acquiring a Lock in this book or LockWait or ttLockWait in the Oracle TimesTen In-Memory Database Reference.

Choose the Best Method of Locking

Performance impact: Variable

When multiple connections access a database simultaneously, TimesTen uses locks to ensure that the various transactions operate in apparent isolation.

TimesTen supports the isolation levels described in Transaction Management.

It also supports the locking levels: database-level locking, table-level locking and row-level locking. You can use the LockLevel connection attribute to indicate whether database-level locking or row-level locking should be used. Use the ttOptSetFlag procedure to set optimizer hints that indicate whether table locks should be used. The default lock granularity is row-level locking.

Note:

See LockLevel and ttOptSetFlag in the Oracle TimesTen In-Memory Database Reference.

Choose an Appropriate Lock Level

If there is very little contention on the database, use table-level locking. It provides better performance and deadlocks are less likely. There is generally little contention on the database when transactions are short or there are few connections. In that case, transactions are not likely to overlap.

Table-level locking is also useful when a statement accesses nearly all the rows on a table. Such statements can be queries, updates, deletes or multiple inserts done in a single transaction.
Database-level locking completely restricts database access to a single transaction, and it is not recommended for ordinary operations. A long-running transaction using database-level locking blocks all other access to the database, affecting even the various background tasks needed to monitor and maintain the database.
Row-level locking is generally preferable when there are many concurrent transactions that are not likely to need access to the same row. On modern systems with a sufficient number of processors using high-concurrency, for example, multiple ttBulkCp processes, row-level locking generally outperforms database-level locking.

Note:

See ttBulkCp in the Oracle TimesTen In-Memory Database Reference.

Choose an Appropriate Isolation Level

When using row-level locking, applications can run transactions at the SERIALIZABLE or READ_COMMITTED isolation level.

The default isolation level is READ_COMMITTED. You can use the Isolation connection attribute to specify one of these isolation levels for new connections.

When running at SERIALIZABLE transaction isolation level, TimesTen holds all locks for the duration of the transaction.

Any transaction updating a row blocks writers until the transaction commits.
Any transaction reading a row blocks out writers until the transaction commits.

When running at READ_COMMITTED transaction isolation level, TimesTen only holds update locks for the duration of the transaction.

Any transaction updating a row blocks out writers to that row until the transaction commits. A reader of that row receives the previously committed version of the row.
Phantoms are possible. A phantom is a row that appears during one read but not during another read, or appears in modified form in two different reads, in the same transaction, due to early release of read locks during the transaction.

You can determine if there is an undue amount of contention on your system by checking for time-out and deadlock errors (errors 6001, 6002, and 6003). Information is also available in the lock.timeouts and lock.deadlocks columns of the SYS.SYSTEMSTATS table.

See Transaction Isolation Levels.

Enable Prefetch Close for Read-Only Transactions

Performance impact: Variable

A TimesTen extension enables applications to optimize read-only query performance in client/server applications. When you enable prefetch close, the server closes the cursor and commits the transaction after the server has fetched the entire result set for a read-only query. This enhances performance by decreasing the network round-trips between client and server.

ODBC: Use the SQLSetConnectOption to set the TT_PREFETCH_CLOSE ODBC connection option. See Optimizing Query Performance in the Oracle TimesTen In-Memory Database C Developer's Guide.
JDBC: Call the TimesTenConnection method setTtPrefetchClose() with a setting of true. See Optimizing Query Performance in the Oracle TimesTen In-Memory Database Java Developer's Guide.

Use a Connection Handle When Calling SQLTransact

Performance impact: Large

You should always call SQLTransact with a valid HDBC and a null environment handle.

SQLTransact (SQL_NULL_HENV, ValidHDBC, fType)

It is possible to call SQLTransact with a non-null environment handle and a null connection handle, but that usage is not recommended as it abruptly commits or rolls back every transaction for the process, which is slow and can cause unexpected application behavior.

Enable Multi-Threaded Mode to Handle Concurrent Connections

The MaxConnsPerServer connection attribute sets the maximum number of concurrent connections allowed for a TimesTen server process.

By default, MaxConnsPerServer equals to 1, meaning that each TimesTen server process can only handle one client connection.

Setting MaxConnsPerServer > 1 enables multi-threaded mode for the database so that it can use threads instead of processes to handle client connections. This reduces the time required for applications to establish new connections and increases overall efficiency in configurations that use a large number of concurrent client/server connections.

See Defining Server DSNs for TimesTen Server on a Linux or UNIX System.