Monitoring Cache Groups

Using the ttIsql Utility cachegroups Command

You can obtain information about cache groups in a TimesTen database using the ttIsql utility cachegroups command.

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> cachegroups;

Cache Group CACHEADMIN.RECENT_SHIPPED_ORDERS:

  Cache Group Type: Read Only
  Autorefresh: Yes
  Autorefresh Mode: Incremental
  Autorefresh State: On
  Autorefresh Interval: 1440 Minutes
  Autorefresh Status: ok
  Aging: Timestamp based uses column WHEN_SHIPPED lifetime 30 days cycle 24 hours on

  Root Table: SALES.ORDERS
  Table Type: Read Only


Cache Group CACHEADMIN.SUBSCRIBER_ACCOUNTS:

  Cache Group Type: Asynchronous Writethrough (Dynamic)
  Autorefresh: No
  Aging: LRU on

  Root Table: SALES.SUBSCRIBER
  Table Type: Propagate

Cache Group CACHEADMIN.WESTERN_CUSTOMERS:

  Cache Group Type: User Managed
  Autorefresh: No
  Aging: No aging defined

  Root Table: SALES.ACTIVE_CUSTOMER
  Where Clause: (sales.active_customer.region = 'West')
  Table Type: Propagate

  Child Table: SALES.ORDERTAB
  Table Type: Propagate

  Child Table: SALES.ORDERDETAILS
  Where Clause: (sales.orderdetails.quantity >= 5)
  Table Type: Not Propagate

  Child Table: SALES.CUST_INTERESTS
  Table Type: Read Only

3 cache groups found.

The information displayed by the ttIsql utility's cachegroups command include:

Cache group type, including whether the cache group is dynamic
Autorefresh attributes (mode, state, interval) and status, if applicable
Aging policy, if applicable
Name of root table and, if applicable, name of child tables
Cache table WHERE clause, if applicable
Cache table attributes (read-only, propagate, not propagate)

See ttIsql in Oracle TimesTen In-Memory Database Reference.

Monitoring Autorefresh Operations on Cache Groups

TimesTen offers several mechanisms to obtain information and statistics about autorefresh operations on cache groups.

See Monitoring Autorefresh Cache Groups in Oracle TimesTen In-Memory Database Monitoring and Troubleshooting Guide.

Monitoring AWT Cache Groups

TimesTen Classic offers several mechanisms to obtain information and statistics about operations in AWT cache groups.

See AWT Performance Monitoring in Oracle TimesTen In-Memory Database Monitoring and Troubleshooting Guide.

Configuring a Transaction Log File Threshold for AWT Cache Groups

In TimesTen Classic, the replication agent uses the transaction log to determine which updates on cache tables in AWT cache groups have been propagated to the cached Oracle Database tables and which updates have not. If updates are not being automatically propagated to the Oracle database because of a failure, transaction log files accumulate on the file system.

Examples of a failure that prevents propagation are that the replication agent is not running or the Oracle database server is unavailable. See Monitoring Accumulation of Transaction Log Files in Oracle TimesTen In-Memory Database Operations Guide.

You can call the ttCacheAWTThresholdSet built-in procedure as the TimesTen cache administration user to set a threshold for the number of transaction log files that can accumulate before TimesTen Classic stops tracking updates on cache tables in AWT cache groups. The default threshold is 0. This built-in procedure can only be called if the TimesTen database contains AWT cache groups.

After the threshold has been exceeded, you need to manually synchronize the cache tables with the cached Oracle Database tables using an UNLOAD CACHE GROUP statement followed by a LOAD CACHE GROUP statement. TimesTen may purge transaction log files even if they contain updates that have not been propagated to the cached Oracle Database tables.

The following example sets a transaction log file threshold for AWT cache groups. In this example, if the number of transaction log files that contain updates on cache tables in AWT cache groups exceeds 5, TimesTen stops tracking updates and can then purge transaction log files that may contain unpropagated updates:

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> CALL ttCacheAWTThresholdSet(5);

You can call the ttCacheAWTThresholdGet built-in procedure to determine the current transaction log file threshold setting:

Command> CALL ttCacheAWTThresholdGet;
< 5 >
Command> exit

Tracking DDL Statements Issued on Cached Oracle Database Tables

When a DDL statement is issued on a cached Oracle Database table, this statement can be tracked in the Oracle Database TT_version_DDL_L table when the Oracle Database TT_version_schema-ID_DDL_T trigger is fired to insert a row into the table. The version is an internal TimesTen version number and schema-ID is the ID of user that owns the cached Oracle Database table.

A trigger is created for each Oracle Database user that owns cached Oracle Database tables. One DDL tracking table is created to store DDL statements issued on any cached Oracle Database table. The Oracle cache administration user owns the TT_version_DDL_L table and the TT_version_schema-ID_DDL_T trigger.

By default, DDL statements are not tracked. On TimesTen, you can enable tracking of DDL statements issued on cached Oracle Database tables, call the ttCacheDDLTrackingConfig built-in procedure as the TimesTen cache administration user. The following example enables tracking of DDL statements issued on cached Oracle Database tables:

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> CALL ttCacheDDLTrackingConfig('enable');

The TT_version_DDL_L table and TT_version_schema-ID_DDL_T trigger are automatically created if the Oracle cache administration user has been granted the set of required privileges including CREATE TRIGGER, CREATE SEQUENCE, CREATE TYPE, CREATE PROCEDURE, CREATE TABLE and CREATE ANY TRIGGER. These Oracle Database objects are created when you create a cache group after tracking of DDL statements has been enabled.

On TimesTen Classic, if you manually created the Oracle Database objects used to manage the caching of Oracle Database data, you need to run the ttIsql utility cachesqlget command with the ORACLE_DDL_TRACKING option and the INSTALL flag as the TimesTen cache administration user. This command should be run for each Oracle Database user that owns cached Oracle Database tables that you want to track DDL statements on. Running this command generates a SQL*Plus script used to create the TT_version_DDL_L table and TT_version_schema-ID_DDL_T trigger in the Oracle database.

After generating the script, use SQL*Plus to run the script as the sys user.

The following example creates DDL tracking table and trigger when Oracle Database objects are manually created. In this example, the SQL*Plus script generated by the ttIsql utility cachesqlget command is saved to the /tmp/trackddl.sql file. The owner of the cached Oracle Database table sales is passed as an argument to the command.

% ttIsql "DSN=cache1;UID=cacheadmin;PwdWallet=/wallets/cacheadminwallet"
Command> cachesqlget ORACLE_DDL_TRACKING sales INSTALL /tmp/trackddl.sql;
Command> exit

% sqlplus sys as sysdba
Enter password: password
SQL> @/tmp/trackddl
SQL> exit

You can run the ttCacheInfo utility or the timesten_home/install/oraclescripts/cacheInfo.sql SQL*Plus script as the Oracle cache administration user to display information about the Oracle Database objects used to track DDL statements issued on cached Oracle Database tables. The following example runs the cacheInfo.sql SQL*Plus script.

% cd timesten_home/install/oraclescripts
% sqlplus cacheadmin/orapwd
SQL> @cacheInfo.sql
***************** Database Information	*********************
Database name: DATABASE1
Unique database name: database1
Primary database name:
Database Role: PRIMARY
Database Open Mode: READ WRITE
Database Protection Mode: MAXIMUM PERFORMANCE
Database Protection Level: UNPROTECTED
Database Flashback On: NO
Database Current SCN: 21512609
*************************************************************
*************Autorefresh Objects Information  ***************
Grid name: grid1 (7D03C680-BD93-4233-A4CF-B0EDB0064F3F)
Timesten database name: database1
Cache table name: SALES.CUSTOMERS
Change log table name: tt_07_96977_L
Number of rows in change log table: 4
Maximum logseq on the change log table: 1
Timesten has autorefreshed updates upto logseq: 1
Number of updates waiting to be autorefreshed: 0
Number of updates that has not been marked with a valid logseq: 0
*************DDL Tracking Object Information  ***************
Common DDL Log Table Name: TT_07_DDL_L
DDL Trigger Name: TT_07_315_DDL_T
Schema for which DDL Trigger is tracking: SALES
Number of cache groups using the DDL Trigger: 10
****************************
 
PL/SQL procedure successfully completed.

The information returned for each Oracle Database user that owns cached Oracle Database tables includes the name of the DDL tracking table, the name of its corresponding DDL trigger, the name of the user that the DDL trigger is associated with, and the number of cache groups that cache a table owned by the user associated with the DDL trigger.

If a cache group contains more than one cache table, each cache table owned by the user associated with the DDL trigger contributes to the cache group count.

See SQL*Plus Scripts for Cache in this book and ttCacheDDLTrackingConfig and ttCacheInfo in Oracle TimesTen In-Memory Database Reference.