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.

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:

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:

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:

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

Oracle IMDB Cache Monitoring

This section lists high-level recommendations for monitoring Oracle In-Memory Database 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 IMDB Cache and Configuring In-Memory Database 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:

More information can be found in the Best Practices for IMDB 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:

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