Client/Server Tuning
You can tune your client/server.
The following sections include tips for client/server tuning:
Diagnose Client/Server Performance
Performance impact: Variable
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
Choose Timeout Connection Attributes for Your Client
Performance impact: Variable
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_SERVER
n
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_SERVER
n
connection attribute configured, then the overall elapsed time is four times the value of the timeout (whetherTTC_Timeout
orTTC_ConnectTimeout
). For example, if the user specifiesTTC_TIMEOUT
=10 and there are multipleTTC_SERVER
n
servers configured, then theSQLDriverConnect
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
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
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.
Enable Prefetch Close for Read-Only Transactions
Performance impact: Variable
-
ODBC: Use the
SQLSetConnectOption
to set theTT_PREFETCH_CLOSE
ODBC connection option. See Optimizing Query Performance in the Oracle TimesTen In-Memory Database C Developer's Guide. -
JDBC: Call the
TimesTenConnection
methodsetTtPrefetchClose()
with a setting oftrue
. 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
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.