TimesTen Case Study: Performance Problems Resulting From A Very Large Command Cache (Doc ID 1442292.1)

Last updated on SEPTEMBER 23, 2016

Applies to:

TimesTen Data Server - Version 7.0.0.0.0 to 11.2.2.2.0 [Release 7.0 to 11.2]
Information in this document applies to any platform.
This Case Study will be of interest to all TimesTen application developers, especially those involved in building very large OLTP systems using TimesTen.

***Checked for relevance on 28-OCT-2013***

Symptoms

Customer operated a very large OLTP system. Customer reported "poor performance" occurring at peak usage. Such performance problems had not been encountered during system acceptance testing. Customer further reported that problems seemed to go away temporarily if the data store(s) were stopped.

The application appeared to be well designed:
  • All SQL statements used bind variables.
  • Output from the ttisql performance utility monitor  showed no deadlocks, no  lock wait timeouts or other lock contention;
  • Monitor output showed very few temporary indexes being built, indicating that efficient index access paths existed
  • Regeneration of optimizer statistics did not have any impact on peak-usage problems.
  • There were additionally very few transaction rollbacks
  • There were no reads on the transaction logs, indicating that there were no long-running transactions.
  • No permanent or temporary space problems were observed
  • Examination of OS performance statistics indicated that there was sufficient hardware resources (RAM, CPU power, I/O and network bandwidth) to handle the application workload.
             Performance statistics taken from monitor utility output at customer's site (selected statistics only )
  DS_CONNECTS:                6177
  DS_DISCONNECTS:             6063


  CMD_PREPARES:               2197103
  CMD_REPREPARES:             0
  CMD_TEMP_INDEXES:           8


  XACT_BEGINS:                118723175
  XACT_COMMITS:               118726566
  XACT_D_COMMITS:             0
  XACT_ROLLBACKS:             13522


  DEADLOCKS:                  0
  LOCK_TIMEOUTS:              0
  LOCK_GRANTS_IMMED:          1019080968
  LOCK_GRANTS_WAIT:           3105

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms