Best Practices for Monitoring

Monitoring is necessary for all databases, and TimesTen is no exception. Monitoring allows for the quick diagnosis of database performance / functionality issues, and allows you to take quick action in case any problem or performance degradation is detected. This page recommends the top methods for monitoring a TimesTen database.

• Monitor TimesTen using Oracle Enterprise Manager (OEM)
• Use the ttStats Utility
• Monitor Key TimesTen Database Statistics
• Measure SQL Execution Times
• Monitor TimesTen Replication
• Monitor TimesTen Application-Tier Database Cache

General Monitoring Tips

Monitor TimesTen Using Oracle Enterprise Manager (OEM)

Customers with OEM 11g Grid Control or OEM 12c Cloud Control may download the TimesTen plug-in for Enterprise Manager to monitor key configuration and performance metrics, as well as set up alerts based on user-defined statistic thresholds. Metrics monitored by Enterprise Manager can also be found in TimesTen system tables and outputs of TimesTen built-ins. OEM wraps the system performance information in an easily-understandable user interface. OEM allows you to view graphs of historical and real-time database performance as well as set up alert triggers when certain metric thresholds are surpassed.

If you are looking for a tool that can comprehensively monitor a TimesTen database and provide real-time information presented graphically, consider using the TimesTen plug-in for OEM Grid / Cloud control.

See the Enterprise Manager QuickStart page for interactive demos showing how to deploy, configure and undeploy the TimesTen plugin on EM 11g and 12c

See the Best Practices for Enterprise Manager 12c plug-in for plug-in configuration recommendations.

Use the ttStats Utility

The ttStats utility is a tool that captures real-time system statistics. It can be used to monitor and collect critical TimesTen metrics and display them in a readable format on the command line, or to generate comparisons (reports) of the conditions of a database at two periods of time (snapshots), rendered as an html file.

The primary intended usage for ttStats is for users to capture performance snapshots of their TimesTen database. ttStats can capture database performance statistics such as SQL statement execution counts, top SQL statements, checkpoint and transaction log operations, replication and cache group operations, locking information, as well as other database activities and configuration options. Performance statistics between two ttStats snapshots captured can then be compared and analyzed, which is useful for database tuning.

For more information and an example of how to use the utility, consult the TimesTen documentation for ttStats

If you would like to build your own monitoring capability that leverages the tt_stats PL/SQL package, you can view the TT_STATS procedures in the PL/SQL Packages Reference. Leveraging these stored procedures, you can create an in-house script that captures ttStats snapshots at fixed time intervals.

Monitor Key TimesTen Database Statistics

TimesTen collects a large set of statistics during runtime in its system tables. Monitoring TimesTen effectively means periodically querying the contents of system tables, either through direct SQL queries or TimesTen built-in procedures. If you would like to write your own custom script to constantly monitor key TimesTen statistics, follow these tips:

• Monitor the statistics in the SYS.SYSTEMSTATS table. These tables contain information about system performance and system wide monitoring statistics
• Use the ttIsql monitor command, which displays statistics on database space usage, number of connections, checkpoints, lock timeouts, commits, rollbacks and other information collected since the last time the database was loaded into memory. Running this command is the equivalent of doing a "select * " from the SYS.MONITOR table
• In times of transaction volume spikes, long running transactions, or any transactions that require TimesTen to hold bookmarks (replication, AWT, etc), the TimesTen in-memory transaction log buffer may fill up, and TimesTen performance may be partially bottlenecked by disk I/O, which could lead to decreased system performance. To guard against this, use the ttLogHolds and ttCkptHistory built-ins periodically to monitor excessive transaction log accumulation. See this documentation link for more information

Measure SQL Execution Times

New in TimesTen 11.2.2.5 is the ability to quickly, simply and efficiently measure execution time of SQL operations, to help determine which SQL statements are performing well / poorly. To minimize overhead and maximize query performance, new built-ins sample the execution times of commands as they are executed. If you want to gauge the performance of SQL queries with more granularity, use these built-ins:

1. ttStatsConfig -- controls the sampling configuration parameters for statistics collection
2. ttSQLExecutionTimeHistogram -- displays a histogram of SQL execution times for either a single SQL command or all SQL commands in the command cache, if sampling is enabled
3. ttSQLCmdCacheInfo -- returns all compiled statements in the TimesTen SQL command cache
4. ttSQLCmdCacheInfo2 -- the ttSQLCmdCacheInfo built-in modified with new output columns to reflect the number of SQL command fetches and their execution times

For more information about the utilities, consult the documentation for ttStatsConfig, ttSQLExecutionTimeHistogram, ttSQLCmdCacheInfo, and ttSQLCmdCacheInfo2. Note that all these built-ins require the ADMIN privilege to run.

Here are some recommendations when using these built-ins to measure execution times:

• Measuring SQL execution times introduces a slight performance overhead, as execution time capture happens within the critical path of a SQL query. Thus, you should plan to measure only the mission-critical database transactions.
• Take caution when setting the value of SQLCmdSampleFactor, as this determines how frequently execution time capture happens

TimesTen Replication Monitoring

This section lists high-level recommendations for monitoring TimesTen databases configured with replication. We recommend that you read the Best Practices for Replication and Configuring Replication Between TimesTen Databases sections first before diving into this section, as the topics here are more advanced. Detailed information about monitoring replication can be found in the TimesTen documentation.

This section lists high-level recommendations for monitoring TimesTen replication:

• The replication agent and its associated processes must be running for replication to be successful.
  • When accessing TimesTen via command line, show state of replication agents by running ttStatus, then looking for the processes of type "Replication"
  • When accessing TimesTen from a program, utilize TimesTen built-in procedures. Specifically, ttDataStoreStatus obtains the status of replication agents
• Database policy settings, which includes information about the replication agent can be viewed using the ttAdmin -query <DSN> utility
• Use the ttRepAdmin utility to display replication topologies and monitor replication status. ttRepAdmin provides a robust set of features for replication monitoring. Some frequently used ttRepAdmin arguments are:
  • ttRepAdmin -dsn <DSN> -self -list
  • ttRepAdmin -dsn <DSN> -receiver -list
  • ttRepAdmin -showconfig -dsn <DSN>
  • ttRepAdmin -dsn <DSN> -bookmark
  • ttRepAdmin -showstatus <DSN>
• Information about subscriber databases can be found by querying system replication tables such as TREP.REPPEERS, TTREP.TTSTORES, and SYS.MONITOR tables
• Database replication configuration can be seen using the repschemes command in ttIsql
• The status of one or more replicated databases can be shown using the ttReplicationStatus built-in procedure
• The location of bookmarks, which is the mechanism used for tracking successfully replicated transactions, can be viewed using the ttBookMark() built-in procedure
• The receipt status of database subscribers with respect to its database masters can be viewed using the ttRepSyncSubscriberStatus built-in procedure
• Statistics for the remaining transactions that need to be replicated can be viewed using ttXactLog, specifying the -logAnalyze argument. Shown below is an example:

  $ ttXactLog -v1 -logAnalyze rep1
  Summary:
  Total transactions left to replicate: 1
  Total rows left to replicate: 1
  Size of transactions left to replicate:    120 KB
  Size of rows left to replicate:     76 KB
  Largest transaction left to replicate:   1.26
  Total inserts remaining: 1
  StartLSN = 0. 7354632
  EndLSN = 1.9462762

More information can be found in the Best Practices for Replication section.

TimesTen Cache Monitoring

This section lists high-level recommendations for monitoring TimesTen Cache. In an environment where TimesTen is used as a cache for an Oracle Database, it is especially important to monitor both the TimesTen and Oracle Databases. This section only describes the monitoring of TimesTen database.

To cache a set of Oracle Database data into TimesTen, you define a SQL object known as a "cache group". There are two types of cache groups, readonly cache groups and asynchronous write-through (AWT) cache groups. For a basic understanding of concepts, we strongly recommend that you read through the Best Practices for TimesTen Cache and Configuring TimesTen Application-Tier Database Cache (TimesTen Cache) sections first before diving into this section, as the topics here are more advanced.

Monitoring Read-Only Cache Groups

A cache group is said to be "read-only" if all changes to the underlying table(s) occur in the Oracle Database, and are subsequently refreshed into TimesTen. A read-only cache group can be configured to be an "autorefresh" cache group if the delta changes to the underlying Oracle Database tables are refreshed periodically every predefined period of time. Here are some recommended ways to monitor read-only cache groups:

• Use the ttIsql utility's cachegroups command to obtain information about the cache groups defined in the database
• Use the ttCacheAutorefreshStatsGet built-in procedure to return information about the latest operations of an autorefresh cache group
• Run <TIMESTEN_INSTALL_DIR>/oraclescripts/cacheInfo.sql on the Oracle database to gather information for autorefresh cache groups
• Use SNMP traps to alert you when autorefresh problems occur

More information can be found in the Best Practices for TimesTen Cache section as well as the documentation.

Monitoring Asynchronous Write-Through Cache Groups

A cache group is said to be an "asynchronous write-through" (AWT) cache group if all changes to the underlying table(s) occur in the TimesTen database, and are subsequently flushed to the underlying Oracle Database. Here are some recommended ways to monitor AWT cache groups:

• Use the ttIsql utility's cachegroups command to obtain information about the cache groups defined in the database
• Return statistics for AWT cache groups using:
  ttRepAdmin -showstatus -awtmoninfo
  Note that before running this query, the replication agent must be running and you must set
  ttCacheAWTMonitorConfig("on",<sampling_frequency>)
  For accuracy and performance, a sampling factor of '16' is recommended
• Check whether asynchronous writethrough operations are keeping up with the rate of updates on cache tables in AWT cache groups by querying the LAST_LOG_FILE, REPHOLD_LOG_FILE and REPHOLD_LOG_OFF columns of the SYS.MONITOR table
• If you experience poor AWT cache group performance, see this section for possible causes.

More information can be found in the Best Practices for TimesTen Cache section as well as the documentation.