3 Troubleshooting Oracle In-Memory Database Cache 

Check status of the cache agent

Check the status of the cache agent by using the ttStatus utility as described in "Using the ttStatus utility" to check the status of the cache agent.

If the cache agent is not running, start it as described in "Starting the cache agent" in the Oracle In-Memory Database Cache User's Guide. If attempts to start the cache agent fail, then investigate the possible causes and reboot the machine before attempting to start the cache agent.

Check ORACLE_HOME environment variable

On UNIX or Linux platforms, check that the ORACLE_HOME environment variable is set correctly for the shell from which you are starting the cache agent and the TimesTen daemon. Use the ttmodinstall utility if you need to change the setting for ORACLE_HOME.

See "Environment variables" in Oracle TimesTen In-Memory Database Installation Guide.

Check NLS environment variables

NLS environment variables are set in the environment where the TimesTen application is running, even though TimesTen is not using the NLS environment variables. Unset the NLS environment variables and restart the TimesTen daemon, the cache agent, and the replication agent.

A portion of the cache grid nodes are still running

When the server shuts down, some of the cache grid notes exited unexpectedly, but others are still active. In this case, you must detach the dead nodes first by executing ttGridDetachList from an attached node, as follows:

1. Connect to a surviving grid node and execute ttGridDetachList to force a detach of all dead nodes from the grid.

2. Connect to the databases on the rebooted server. Start the replication agent by executing ttRepStart.

3. Attach the cache grid nodes by executing ttGridAttach.

4. Resume normal database operations.

All cache grid nodes exited unexpectedly

If all cache grid nodes exited unexpectedly when the server shut down, perform the following tasks to recover the cache grid:

1. Log on to each grid node by connecting to the databases on the rebooted server. Start the replication agent by executing ttRepStart. The replication agent will flush the existing log, even if the log is current.

2. Call ttGridAttach on each node, which will fail with a communication error because it cannot communicate with other members. The failed attach cleans up the node information.

3. The last node on which you execute the ttGridAttach should succeed. At this point you have cleaned up all nodes, so execute ttGridAttach on all nodes again to attach each node to the grid.

4. Resume normal database operations.

Check library path environment variable

Check the library path environment variable on your platform.

The library path environment variable must include the following information:

Check status of TNS listener and Oracle Server

Try to connect to the Oracle database by using SQL*Plus or use Oracle Enterprise Manager to verify the status.

Check Oracle privileges

From an Oracle SQL*Plus command prompt, list the current Oracle privileges granted to you by entering:

SELECT * FROM SESSION_ROLES;
SELECT * FROM SESSION_PRIVS;

Compare the privileges listed against the required privileges for the various IMDB Cache operations that are specified in "Grant privileges to Oracle users" in the Oracle In-Memory Database Cache User's Guide. Contact your Oracle Administrator if you require additional privileges.

Check DSN definition

• Confirm you have correctly set the DSN attributes as described in "DSN for a TimesTen database that caches data from an Oracle database" in the Oracle In-Memory Database Cache User's Guide.

• Confirm that the DSN definition for IMDB Cache is a system DSN.

• Confirm that the DSN for IMDB Cache is defined only once.

• Confirm Oracle user name and password. Use SQLPlus and connect to Oracle using the same OracleID and OraclePWD used in your DSN definition to confirm they are correct.

Reboot TimesTen machine

If the Oracle client was installed and the machine has not been restarted, then the TimesTen daemon is still running under the "old" environment before the Oracle client install. Reboot your machine so the TimesTen can start under the "new" environment.

Set the cache administration user id and password

From a ttIsql session, connect to the database and enter the following:

Command> call ttCacheUidPwdSet('scott','tiger');

If it returns an error, then check the Oracle ID, the cache administration user ID and cache administration password. Also check whether the Oracle instance is running.

Check user and system environment

Test to see if the problem is due to differences in user and system environment. This procedure requires two session windows (Command Prompt windows in Windows or shell windows in UNIX).

1. Stop the TimesTen daemon.

2. In one session window, start the Timesten daemon as a regular user.

   On Windows:

   % install_dir/srv/ttsrv1121.exe -d -verbose
   

   On UNIX:

   % install_dir/srv/timestend -d verbose
   

   Some messages will flash by, and then it goes into a wait state.

3. In another session window, try to restart the cache agent.

4. If Step 3 succeeds, then use Ctrl-C on Windows or the kill command on UNIX to stop the TimesTen daemon you started for the other session in Step 2.

5. Compare the user environment and system environment. For example, do both user and system see the same copy of oci.dll? Are there any differences in the path name to the oci.dll library between the user and system environments?

6. If you detect differences, make the necessary modifications.

7. Reboot the system and restart the TimesTen daemon.

Verify the loaded dynamic libraries

If you are running on a Windows system with Visual C++ installed, verify the loaded dynamic libraries. This works only if you can start the cache agent without autorefresh:

1. Make sure TimesTen is started.

2. Start the cache agent without autorefresh.

   Command> call ttCacheStart;
   Command> create cache group cg1 from t1(c1 int not null primary key);
   

3. Open the Windows Task Manager, find process ttora1121.exe and highlight it. Right-click on it and select Debug. This brings you into Visual C++ and you should see the loaded DLL in the debug window, as described in "Unable to resolve Oracle Service Name".

4. Load the cache group to force an cache connection from the cache agent:

   Command> load cache group cg1 commit every 100 rows;
   

5. Compare the loaded DLL in your debug window with the partial list shown in Example 3-1.

Loaded 'E:\TimesTen\tt1121_32\bin\timestenorad1121.exe', no matching symbolic information found.
Loaded 'C:\WINDOWS\SYSTEM32\ntdll.dll', no matching symbolic information found.
Loaded 'C:\WINDOWS\SYSTEM32\kernel32.dll', no matching symbolic information found.
Loaded 'E:\TimesTen\tt1121_32\bin\tten1121.dll', no matching symbolic information found.
Loaded 'E:\TimesTen\tt1121_32\bin\ttcommon1121.dll', no matching symbolic information found.
Loaded 'C:\WINDOWS\SYSTEM32\wsock32.dll', no matching symbolic information found.
Loaded 'C:\WINDOWS\SYSTEM32\ws2_32.dll', no matching symbolic information found.
Loaded 'C:\WINDOWS\SYSTEM32\msvcrt.dll', no matching symbolic information found.
Loaded 'C:\WINDOWS\SYSTEM32\ws2help.dll', no matching symbolic information found.
Loaded 'C:\WINDOWS\SYSTEM32\advapi32.dll', no matching symbolic information found.
Loaded 'C:\WINDOWS\SYSTEM32\rpcrt4.dll', no matching symbolic information found.
...

Using the ttCacheAutorefreshStatsGet procedure

The ttCacheAutorefreshStatsGet procedure returns information about the last ten autorefresh operations on a specified cache group.

The ttCacheAutorefreshStatsGet procedure returns information only when the cache agent is running and the autorefresh state is ON or PAUSED. All of the return fields are set to 0 when the cache agent is restarted or the autorefresh state is changed to OFF.

Command> call ttcacheautorefreshstatsget('user1','testcache');

< 1164260, 2007-07-23 15:43:52.000000, 850280, 44, 0, 75464, 528255, 75464, 310, 110, 6800, 
1890912, 12439795, 1890912, 160020, InProgress >
< 1164260, 2007-07-23 15:43:33.000000, 831700, 43, 13550, 108544, 759808, 108544, 1030, 230, 
12290, 1815448, 11911540, 1815448, 160020, Complete >
< 1164260, 2007-07-23 15:43:12.000000, 810230, 42, 17040, 115712, 809984, 115712, 610, 330, 
16090, 1706904, 11151732, 1706904, 146470, Complete >
< 1164260, 2007-07-23 15:42:52.000000, 790190, 41, 14300, 94208, 659456, 94208,560, 320, 
13410, 1591192, 10341748, 1591192, 129430, Complete >
< 1164260, 2007-07-23 15:42:32.000000, 770180, 40, 12080, 99328, 695296, 99328,450, 290, 
11340, 1496984, 9682292, 1496984, 115130, Complete >
< 1164260, 2007-07-23 15:42:12.000000, 750130, 39, 10380, 86016, 598368, 86016,430, 230, 
9720, 1397656, 8986996, 1397656, 103050, Complete >
< 1164260, 2007-07-23 15:41:52.000000, 730130, 38, 13530, 112640, 700768, 112640, 530, 220, 
12780, 1311640, 8388628, 1311640, 92670, Complete >
< 1164260, 2007-07-23 15:41:32.000000, 710120, 37, 9370, 56320, 326810, 56320, 310, 160, 
8900, 1199000, 7687860, 1199000, 79140, Complete >
< 1164260, 2007-07-23 15:41:22.000000, 700120, 36, 2120, 10240, 50330, 10240, 50, 200, 1870, 
1142680, 7361050, 1142680, 69770, Complete >
< 1164260, 2007-07-23 15:41:12.000000, 690110, 35, 0, 0, 0, 0, 0, 0, 0, 1132440, 7310720, 
1132440, 67650, Complete >
10 rows found.

1164260

2007-07-23 15:43:52.000000

850280

44

0

75464

528255

75464

310

110

6800

1890912

12439795

1890912

160020

InProgress

Note that the total number of autorefreshed rows (1890912) is the same as the total number of autorefreshed root table rows in this example because there are no child tables.

The number of autorefreshed rows in TimesTen does not necessarily reflect the number of rows updated on Oracle. The Oracle updates may be applied in TimesTen more than once, or multiple Oracle updates on the same row may be applied as one update in TimesTen.

Displaying information from the change log tables

TimesTen provides a SQL script that gathers information from the change log tables that exist on the Oracle database for autorefresh cache groups. See "Oracle objects used to manage a caching environment" in the Oracle In-Memory Database Cache User's Guide for more information about change log tables.

The script displays the following information for each cached table:

****************************
 * Host name: my-pc
 * Timesten datastore name: c:\data\tt1121
 * Cache table name: USER1.TESTCACHE
 * Change log table name: tt_03_55555_L
 * number of rows in change log table: 100000
 * Maximum logseq on the change log table: 38
 * Timesten has autorefreshed updates up to logseq: 38
 * Number of updates waiting to be autorefreshed: 0
 * Number of updates that has not been marked with a valid logseq: 0
****************************

The log sequence number (logseq) acts as a marker for the autorefresh operation.

Run the script as the cache administration user on the Oracle database using SQL*Plus. If you run the script as a different user, it reports that the change log tables do not exist.

The script is in the following location:

install_dir/oraclescripts/cacheInfo.sql

Understanding messages about autorefresh in the support log

The support log contains messages that show the progress of autorefresh. For example, testcache is a READONLY cache group with an autorefresh interval of 10 seconds (10,000 milliseconds).

The support log shows when autorefresh starts:

15:43:33.96 Info: ORA:  5264: ora-5264-5676-refresh03918: Starting autorefresh 
number 43 for interval 10000ms

The message includes the following information:

• Timestamp (15:43:33.96)

• Cache agent process ID (5264)

• Thread ID (5676)

The thread ID is important because autorefresh numbers are unique only for a specific interval. Always check both the thread ID and the autorefresh number when you are tracking a specific autorefresh operation.

The support log also contains a longer message that reports information similar to the ttCacheAutorefreshStatsGet procedure. 108544 rows were updated in this autorefresh interval, and 1815448 rows have been updated since the cache agent was started. Note that the total number of rows and the total number of root table rows are the same in this message because there is only one table in the cache group. Number refers to the autorefresh number. All times are expressed in milliseconds.

15:43:51.81 Info: ORA:  5264: ora-5264-5676-refresh04387: Cache agent refreshed 
cache group USER1.TESTCACHE: Number - 43, Duration - 13550, NumRows - 108544, 
NumRootTblRows - 108544, NumOracleBytes - 759808, queryExecDuration - 230, 
queryFetchDuration - 1030, ttApplyDuration - 12290, totalNumRows - 1815448, 
totalNumRootTblRows - 1815448, totalNumOracleBytes - 11911540, totalDuration - 
160020

Additional messages show that the autorefresh operation completes successfully:

15:43:51.81 Info: ORA:  5264: ora-5264-5676-refresh04449: Autorefresh number 43 
finished for interval 10000ms successfully
15:43:51.81 Info: ORA:  5264: ora-5264-5676-fresher01619: Autorefresh number 43 
succeeded for interval 10000 milliseconds

Inspect the timestamps to determine whether autorefresh is progressing as expected.

See "Managing TimesTen daemon options" in the Oracle TimesTen In-Memory Database Operations Guide for information about setting the support log location.

Diagnosing autorefresh failure

If ttCacheAutorefreshStatsGet shows that the status of an autorefresh operation is Failed, check the support log for messages related to the autorefresh operation with number the number shown in the ttCacheAutorefreshStatsGet output. Look for errors that occurred after the autorefresh operation started.

< 1164260, 2007-08-01 14:56:36.000000, 959350, 9, 0, 0, 0, 0, 0, 0, 0, 1, 7, 1, 50, Failed >

14:56:36.10 Info: ORA:  5988: ora-5988-4724-refresh03926: Starting autorefresh number 9 for interval 15000ms

14:56:36.10 Info: ORA:  5988: ora-5988-4724-refresh03953: Autorefresh thread for 
interval 15000ms is connected to instance inst1 on host host1. Server handle 
231976252
14:56:36.12 Err : ORA:  5988: ora-5988-4724-refresh07567: TimesTen error 
code:5901, msg The Oracle refresh log table, "USER2"."TT_03_81799_L", for base 
table, USER2.READTAB2, cannot be found.
14:56:36.12 Info: ORA:  5988: ora-5988-4724-refresh05559: Autorefresh rolled 
back.
14:56:36.12 Info: ORA:  5988: ora-5988-4724-refresh04458: Autorefresh number 9 
finished for interval 15000ms with error.
14:56:36.12 Err : ORA:  5988: ora-5988-4724-fresher01606: Autorefresh number 9 
failed for cache groups with interval 15000 ms after 10 retries.

Diagnosing autorefresh performance problems

You can use the ttTraceMon utility to diagnose autorefresh performance problems. See "AUTOREFRESH tracing".

TimesTen tracing severely impacts application performance and consumes a great deal of disk space if trace output is directed to a file. When you are finished, reset tracing to the default values.

Using SNMP traps for alerts about autorefresh problems

Enable SNMP traps to alert you when autorefresh problems occur.The SNMP traps related to autorefresh include:

• ttCacheAutoRefQueFullTrap

• ttCacheIncAutoRefFailedTrap

• ttCacheValidationErrorTrap

• ttCacheValidationWarnTrap

• ttCacheValidationAbortedTrap

See "Diagnostics through SNMP Traps" in the Oracle TimesTen In-Memory Database Error Messages and SNMP Traps.

Reset autorefresh state

Incremental autorefresh does not work if the TRUNCATE statement is used on an Oracle base table. If TRUNCATE is used on an Oracle base table, then you must reset autorefresh by using the ALTER CACHE GROUP statement to set the autorefresh state to OFF followed by another ALTER CACHE GROUP to reset the autorefresh state to ON.

Recover and reset autorefresh Oracle objects

If you know or suspect the Oracle objects used by autorefresh are the cause of the problem, use the following procedure to re-create the Oracle objects.

1. Use ALTER CACHE GROUP to reset the autorefresh state to OFF on all cache groups on all databases that have the affected cached table:

   ALTER CACHE GROUP cache_group_name SET AUTOREFRESH STATE OFF;
   

2. Shut down all cache agents on all affected databases.

3. Check if the user count is zero for each table in the cache group.

   On the Oracle database, execute the following statement:

   SELECT usercount FROM autorefresh_id.tt_version_user_count
       WHERE tablename ='owner.tablename';
   

   If the count is not zero, set the count to zero:

   UPDATE autorefresh_id.tt_version_user_count SET usercount = 0
       WHERE tablename ='owner.tablename';
   

4. Start one of the cache agents. The cache agent performs a clean up operation. It displays the following message to the support log after it has completed the cleanup:

   Cleanup of the Oracle objects completed
   

5. After the cache agent has completed the clean up, use ALTER CACHE GROUP to reset the autorefresh state back to ON:

   ALTER CACHE GROUP cache_group_name SET AUTOREFRESH STATE ON;
   

6. Start all other cache agents.

7. Use ALTER CACHE GROUP to reset the autorefresh state back to ON for all of the affected cache groups on all databases.

Validate autorefresh Oracle objects

The cache agent automatically verifies that Oracle objects exist and that they are valid so that autorefresh can progress. In normal operation, you should not see object validation errors or warnings in the user error log. If you see object validation errors, contact Technical support.unless one of the following conditions has occurred:

• The TimesTen database has been destroyed without using the DROP CACHE GROUP statement.

• A customer application inadvertently modifies the objects directly in the Oracle database.

• A DDL operation occurs on the base table on the Oracle database. This disables the trigger that controls autorefresh operations.

The cache group needs to be re-created if one of the preceding conditions has occurred.

Detecting when incremental autorefresh becomes full

You can detect when incremental autorefresh becomes full refresh by several methods:

• Check for messages in the support log that indicate full autorefresh operations are occurring. For example:

  2007-08-08 08:06:51.35 Warn: ORA: 22119: ora-22119-0015-refresh05652: A full 
  autorefresh will be performed for Incremental autorefresh table USER1.READTAB 
  because change log table T_03_55555_L on Oracle has been truncated.
  

• Use the ttCacheAutorefreshStatsGet procedure.

  • If autorefresh is InProgress for longer than usual, full autorefresh may be occurring.

  • If a much larger number of rows (autoRefNumRows) was autorefreshed than usual, full autorefresh may have occurred.

  Check the support log for messages about full autorefresh.

• If SNMP traps are enabled, the ttCacheRecoveryAutorefreshTrap SNMP trap indicates a full autorefresh.

Understanding the cache administration user tablespace

TimesTen strongly recommends creating a separate tablespace for the cache administration user. This tablespace is used as the cache administration user's default tablespace. The tablespace contains autorefresh triggers for each Oracle table, change log tables for each Oracle table, and other objects that TimesTen needs for each cache administration user. If you do not specify a separate tablespace, then these objects are placed in the Oracle system tablespace.

Specify the tablespace when you create the cache administration user on Oracle. You can also specify the tablespace after user creation with the DEFAULT TABLESPACE clause of the Oracle ALTER USER statement.

Change log tables for each of the cached Oracle tables reside in the cache administration user tablespace. For each update on an Oracle table, one row (a change log record) is inserted into the change log table for that Oracle table. The size of a change log record in bytes is as follows:

size of change log record = size of primary key on Oracle table + 250

The number of records in a change log table depends on the update rate on the Oracle table and on the autorefresh interval on TimesTen. Every 20 seconds, TimesTen removes change log records that have been applied to all databases that cache the associated Oracle table.

When change logs are removed, a message similar to the following is displayed in the support log:

16:32:26.73 Info: ORA: 5652: ora-5652-4756-ogTblGC01036: Garbage collector 
deleted 1 rows from TT_03_383270_L where logseq < 1

There are options on how to manage what happens when the cache administration user tablespace is filled. See "Considerations when the cache administration user's tablespace is full" for more information.

Diagnosing a full cache administration user tablespace

Check for the following conditions if the cache administration user tablespace is full:

• Is the autorefresh state set to PAUSED? Change log records accumulate when the state is PAUSED.

• Has the cache group been created but not loaded? The default autorefresh state for cache group creation is PAUSED.

• Is a cache group being created or is a database being duplicated? Both of these operations temporarily stop clean-up operations on the change log table.

• Are the cache agents on all TimesTen databases running? If a cache agent is not running, change log records accumulate.

• Has a database been abandoned without dropping autorefresh cache groups in the database? Abandoned databases result from scenarios such as the following:

  • The database is destroyed by ttDestroy -force.

  • The application connected to the database with the Overwrite connection attribute set to 1, but the cache groups that were in the old database are not re-created.

  If the database still exists, connect to the abandoned database and drop the cache group.

Use the cacheInfo.sql script to find out how large the change log tables are for each cached Oracle table. Use the output to verify that the databases are still in use. See "Displaying information from the change log tables".

If the databases are still in use, verify that the cache agents are running.

Compare the autorefresh progress on TimesTen to the maximum log sequence number on the change log table. If TimesTen is behind, then call the ttCacheAutorefreshStatsGet procedure to see whether the autorefresh operations are successful. See "Using the ttCacheAutorefreshStatsGet procedure".

If the status is InProgress longer than seems reasonable, see "Poor autorefresh performance".

You may need to decrease the autorefresh interval or increase the size of the cache administration user tablespace.

There are options on how to manage what happens when the cache administration user tablespace is filled. See "Considerations when the cache administration user's tablespace is full" for more information.

Monitoring the usage of the cache administration user's tablespace

To monitor the cache administration user tablespace, you can use either Oracle Enterprise Manager alerts or set the TimesTen tablespace threshold parameter.

The cache agent can be configured to periodically monitor the tablespace usage and issue a warning when it exceeds a specified threshold. Set the tablespace threshold percentage with the TblspaceThreshold parameter of the ttCacheConfig built-in procedure. For example, if you set the TblspaceThreshold parameter to 80, then a warning is issued when more than 80% of the tablespace is used.

• If the threshold is set to zero, then no warning is issued. This is the default.

• If the threshold is set between 1 and 99, a warning is issued when the tablespace threshold exceeds that number.

• If the threshold is set to 100, then a warning is issued when the tablespace is full.

For example, to configure for a warning to be issued if the tablespace exceeds 80%, execute the following:

call ttCacheConfig('TblspaceThreshold',,,'80');

For full details of the ttCacheConfig built-in procedure, see the "ttCacheConfig" section in the Oracle TimesTen In-Memory Database Reference.

Considerations when the cache administration user's tablespace is full

With Oracle tables that are cached in a TimesTen database, you can configure them to use incremental automatic refresh. For these tables, you can specify which one of the following is to occur when the cache administration user's tablespace is full:

• The application performing the DML is to fail. This is the default.

  The tablespace full recovery is set to none. The application receives an "Out of Tablespace" error from Oracle when the tablespace is full. At that point, the application will need to rollback the transaction.

  Setting the tablespace full recovery to none is configured when you set the Param parameter to TblSpaceFullRecovery and the Value parameter to None with the ttCacheConfig built-in procedure. For example, the following configures Param to TblSpaceFullRecovery and Value to None for the employees table that is owned by terry:

  call ttCacheConfig('TblSpaceFullRecovery','terry', 'employees','None');
  

• Truncate the change log table to free up space and cause a full autorefresh.

  When the cache administration user's tablespace is full, any application that is executing DML statements on the autorefresh cached Oracle tables continues to execute. A trigger executes to free up space for new change log records by deleting existing change log records. This can result in a full automatic refresh on cache groups that have the incremental automatic refresh mode configured. However, if the Oracle table is not configured for incremental automatic refresh, then no trigger executes.

  To set the operation to allow the application to continue and cause an autorefresh, set the Param parameter to TblSpaceFullRecovery and the Value parameter to Reload with the ttCacheConfig procedure. The user will see stale data until the full autorefresh is complete.

  However, even if the user sets the cache configuration parameter TblSpaceFullRecovery with the value of Reload, the tablespace may not be able to be emptied enough to handle the case of a growing index. Deleting rows from the change log table may not free up enough space for the index that is on the change log table. If the index is growing so fast that it uses all the tablespace to the point where purging the change log tables does not help, then the user's application may receive the following error:

  ORA-01654: unable to extend index <index> by 128 in tablespace <tblspace>
  

For full details of the ttCacheConfig built-in procedure, see the "ttCacheConfig" section in the Oracle TimesTen In-Memory Database Reference.

Unresponsive or dead TimesTen database degrades autorefresh performance

If any TimesTen databases containing autorefresh cache groups are destroyed or no longer in use, TimesTen continues to track autorefresh changes to the Oracle tables for the TimesTen database for which the cache agent is not running. This causes automatic refresh to cache groups in active TimesTen databases to slow down.

The cache agent is responsible for detecting if a database is unresponsive or no longer in use. You can specify if and how a dead TimesTen database is to be recovered. However, you cannot recover a TimesTen database if all of the Oracle objects have been removed.

The following sections describe how you can avoid a degraded autorefresh performance for inactive TimesTen databases:

• Setting cached TimesTen database timeout

• Configuring recovery method for certain cache groups

ttIsql> call ttCacheConfig('AgentTimeOut',,,'6000');

ttIsql> call ttCacheConfig('DeadDbRecovery',,,'Normal');

Excessive deadlocks, buffer busy and row lock waits during autorefresh cache group refresh

During an autorefresh cache group refresh, there can be excessive buffer busy waits, row lock waits, and deadlocks on updates in the Oracle database, which can negatively affect the throughput performance. When there are multiple deadlocks on updates in the Oracle database involving the autorefresh log tables, the following may appear in the support log:

Oracle native error code = 60, msg = ORA-00060: deadlock detected while waiting
for resource
An error occurred while preparing or executing the following Oracle sql 
statement: <some statement involving <cache admin user>.TT_##_#######_L  where 
the # is some number>

You can improve your performance by modifying the INITRANS and FREELISTS settings, which can affect the concurrent inserts into the autorefresh log table as well as internal maintenance of these tables. The application updating the base table that is being autorefreshed encounters a throughput performance hit when these settings are not appropriately configured.

You can automatically or manually manage these settings as follows:

• Use ASSM tablespace, which automatically manages FREELISTS.

• Manually adjust FREELISTS and INITRANS for the autorefresh log table on the Oracle database.

The following details how to manually modify INITRANS and FREELISTS for the autorefresh log table on the Oracle database:

1. Retrieve the name of the autorefresh log table that is on the Oracle database.

   Under the cache administration user login, execute the SQL*Plus script cacheInfo.sql that lists the autorefresh change log table name, along with other items. The following example executes the cacheInfo.sql script that lists the autorefresh change log table name as tt_05_1216726_L, as shown in bold:

   SQL> @cacheInfo.sql
   *************Autorefresh Objects Information  ***************
   Host name: gordon-tt
   Timesten datastore name: /scratch/ds/myDB
   Cache table name: SCOTT.ALOBN
   Change log table name: tt_05_1216726_L 
   Number of rows in change log table: 1
   Maximum logseq on the change log table: 2
   Timesten has autorefreshed updates upto logseq: 1
   Number of updates waiting to be autorefreshed: 1
   Number of updates that has not been marked with a valid logseq: 0
   ****************************
   Host name: conobar-tt
   Timesten datastore name: /scratch/ds/myDB
   Cache table name: SCOTT.A
   Change log table name: tt_05_1279699_L
   Number of rows in change log table: 7
   Maximum logseq on the change log table: 0
   Timesten has autorefreshed updates upto logseq: 0
   Number of updates waiting to be autorefreshed: 5
   Number of updates that has not been marked with a valid logseq: 5
   ****************************
    
   

2. Manually alter the table on the Oracle database. The following example uses the table from the previous example. This example alters the INITRANS and FREELISTS settings for the bar.tt_05_1279699_L table.

   Note:

   See "INITRANS integer" and "FREELISTS" in the Oracle Database SQL Language Reference for details on what are the correct values for configuring these settings.

   ALTER TABLE BAR.TT_05_1279699_L INITRANS 10;
   ALTER TABLE BAR.TT_05_1279699_L STORAGE(FREELISTS 5);
   or
   ALTER TABLE BAR.TT_05_1279699_L MOVE STORAGE(FREELISTS 5);
   

3. Alter the INITRANS and FREELISTS settings for the index for this table, which have the same name as the autorefresh change log table with an additional "L" at the end of it. For example, the index for table bar.tt_05_1279699_L is bar.tt_05_1279699_LL

   These settings should be the same as what you set for the autorefresh change log table.

   ALTER INDEX BAR.TT_05_1279699_LL INITRANS 10;
   ALTER INDEX BAR.TT_05_1279699_LL STORAGE(FREELISTS 5);
   

Abnormally large log and base tables degrade autorefresh performance

The cache thread SQL refresh joins the log table and the base table, which identifies rows needed to be refreshed into TimesTen. The larger the cardinalities of the base table and the log table, the longer the time necessary to perform this join. Performance degradation may occur if either the log table or the base table is abnormally large.

The following describe scenarios where the log table can become abnormally large:

• If the log table is never purged in configurations where cache groups from multiple DSNs all reference the same base table, it increases in size indefinitely. If one or more of the cache agents for these groups are turned off, those DSNs will not properly refresh their cache groups and the log tables will not be purged. If the autorefresh state is turned to paused on one of multiple nodes, the other nodes may slow down.

• The log table can grow abnormally large if some of the cache agents have been shutdown. Resolve this issue by restarting the cache, which will purge all of the backlogged log rows to be purged and all of the cache groups to be synchronized after the completion of the refresh cycle for all cache groups.

• The log table can be abnormally large if rows inserted into the log table are never purged and can never be purged by normal processing. This occurs when one or more DSNs are destroyed or rebuilt without first removing the cache groups. The cache group tables on the Oracle database have no information that the cache groups have been destroyed, which corrupts the entire cache group. Rebuild and reinitialize all of the cache groups associated with this base table. Alternatively, never destroy a DSN with cache groups. Instead, always drop the cache groups before destroying a DSN.

Performance degrades when autorefresh interval is small

When a relatively short refresh interval, such as a few hundred milliseconds, is combined with a large number of entries in the log table or in the base table, a cache refresh operation does not complete before the next refresh operation is scheduled to begin. In this case, the entries in the log table can be un-marked when the current autorefresh cycle finishes.

Thus, the same rows can be refreshed from the base table to the cache group in the next autorefresh cycle, by which time the rows will be marked. Make sure that the time it for the refresh is greater than the refresh interval. Set the refresh interval to a value where redundant refreshes will not occur.