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 restart the system 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. Check to see if TimesTen is using the NLS environment variables.

A portion of the cache grid nodes are still running

When the server shuts down, some cache grid nodes 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 restarted 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 restarted 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 Database Server

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

Check the Oracle database privileges

From an Oracle SQL*Plus command prompt, list the current Oracle database 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 the Oracle database users" in the Oracle In-Memory Database Cache User's Guide. Contact your Oracle Database 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 the Oracle database user name and password. Use SQLPlus and connect to the Oracle database using the same OracleNetServiceName and OraclePWD used in your DSN definition to confirm they are correct.

Reboot TimesTen machine

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

Set the cache administration user name and password

The cache administration user name and password must be set only once in a TimesTen database. However, they must be changed if the TimesTen database is destroyed and recreated or if the cache administration user name is dropped and recreated in the Oracle database.

The cache administration user name and password cannot be changed if the cache agent is running on the TimesTen database or there are cache groups in the database. The cache groups must be dropped before you can change the cache administration user name and password. You must also stop the cache agent before you change the cache administration user name and password, and then restart the cache agent after the user name and password have been changed.

From a ttIsql session, connect to the database as the cache manager user and call the ttCacheUidPwdSet built-in procedure to set the Oracle database cache administration user name and password, as follows:

Command> call ttCacheUidPwdSet('cacheuser','oracle');

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

You can also set the user name and password by executing the ttAdmin -cacheUidPwdSet utility command as a TimesTen external user with the CACHE_MANAGER privilege:

% ttAdmin -cacheUidPwdSet -cacheUid cacheuser -cachePwd oracle cachealone1

If you do not specify the -cachePwd option, the ttAdmin utility prompts for the cache administration user's password. For more information about the utility, see "ttAdmin" in Oracle TimesTen In-Memory Database Reference.

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/ttsrv1122.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. Restart the system and restart the TimesTen daemon.

Verify the loaded dynamic libraries

If you run 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 ttora1122.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 the Oracle database. The Oracle database updates may be applied in TimesTen more than once, or multiple Oracle database updates on the same row may be applied as one update in TimesTen.

Displaying information from the change log tables

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

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

The cacheInfo script displays the following information for each cached table:

% cd TimesTen_install_dir/oraclescripts
% sqlplus cacheuser/oracle
SQL> @cacheInfo
*************Autorefresh Objects Information ***************
Host name: sys1
Timesten datastore name: /users/OracleCache/alone1
Cache table name: ORATT.ORDERS
Change log table name: tt_06_69245_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 information returned for each change log table includes the name of the change log table, the name of its corresponding TimesTen cache table, the number of rows in the change log table, and the number of updates in the change log table that have not been automatically refreshed into the cache table.

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

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: CAC: 5264: TT47118-5264-5676-refresh03918: Starting autorefresh
number 43 for interval 10000ms

The message includes the following information:

• Message number: TT47118

• Timestamp (15:43:33.96)

• Cache agent process ID (5264)

• Thread ID (5676)

You can look up the message number in the Oracle TimesTen In-Memory Database Error Messages and SNMP Traps. For example, in the log message above, TT47118 is error message number 47118.

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: CAC: 5264: TT47087-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: CAC: 5264: TT47119-5264-5676-refresh04449: Autorefresh 
number 43 finished for interval 10000ms successfully
15:43:51.81 Info: CAC: 5264: TT47119-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: CAC: 5988: TT47118-5988-4724-refresh03926: Starting 
autorefresh number 9 for interval 15000ms

14:56:36.10 Info: CAC: 5988: TT47117-5988-4724-refresh03953: Autorefresh thread 
for interval 15000ms is connected to instance inst1 on host host1. Server handle 
231976252
14:56:36.12 Err : CAC: 5988: TT40018-5988-4724-refresh07567: TimesTen error 
code:5901, msg The Oracle refresh log table, "USER2"."TT_06_81799_L", for base 
table, USER2.READTAB2, cannot be found.
14:56:36.12 Info: CAC: 5988: TT47055-5988-4724-refresh05559: Autorefresh rolled 
back.
14:56:36.12 Info: CAC: 5988: TT47119-5988-4724-refresh04458: Autorefresh number 
9 finished for interval 15000ms with error.
14:56:36.12 Err : CAC: 5988: TT40035-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 the following.

• 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 database base table. If TRUNCATE is used on an Oracle database 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 for the Oracle database objects

If you know or suspect the Oracle database objects used by autorefresh are the cause of the problem, use the following procedure to recreate the Oracle database 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 for the Oracle database objects

The cache agent automatically verifies that the Oracle database 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 TimesTen 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 must be recreated 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: CAC: 11384: TT47166-11384-1087179104-lMarker01403:
  A full autorefresh will be performed for Incremental autorefresh table 
  USER1.READTAB because change log table TT_06_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 database table, change log tables for each Oracle database 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 Database system tablespace.

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

Change log tables for each of the cached Oracle database tables reside in the cache administration user tablespace. For each update on an Oracle database table, one row (a change log record) is inserted into the change log table for that Oracle database 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 database 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 database table.

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

16:32:26.73 Info: CAC: 5652: TT47112-5652-4756-ogTblGC01036: Garbage collector 
deleted 1 rows from TT_06_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 tablespace of the cache administration 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 recreated.

  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 database 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 tablespace of the cache administration is full", following shortly, 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 tablespace of the cache administration is full

With Oracle database 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 Database 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 database 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 database table is not configured for incremental automatic refresh, then no trigger executes.

  To set the operation to enable 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 database 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 database 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');

Autorefresh cache group refresh with excessive waiting on resources

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 and 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_06_1216726_L, as shown in bold:

   SQL> @cacheInfo.sql
   *************Autorefresh Objects Information  ***************
   Host name: syst
   Timesten datastore name: /users/OracleCache/alone1
   Cache table name: ORATT.ORDERS
   Change log table name: tt_06_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: consyst
   Timesten datastore name: /users/OracleCache/alone1
   Cache table name: ORATT.ITEMS
   Change log table name: tt_06_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_06_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_06_1279699_L INITRANS 10;
   ALTER TABLE BAR.TT_06_1279699_L STORAGE(FREELISTS 5);
   or
   ALTER TABLE BAR.TT_06_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_06_1279699_L is bar.tt_06_1279699_LL.

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

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

Abnormally large change log and base tables degrade autorefresh performance

The cache thread SQL refresh joins the change 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 change log table, the longer the time necessary to perform this join. Performance degradation may occur if either the change log table or the base table is abnormally large.

The following describes scenarios where the change log table can become abnormally large.

• If the change 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 change 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 change log table can grow abnormally large if some cache agents have been shut down. 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 change log table can be abnormally large if rows inserted into the change 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.

Fragmented autorefresh change log table space

Change log tables can become fragmented when a high water mark occurs as the result of change logs building up when TimesTen is shut down, for example. If change log tables have become fragmented, you can:

• Coalesce their indexes. This can be done without preventing DML changes to the base tables.

• Perform an online segment shrink. This can be done without preventing DML changes to the base tables. See "Understanding Reclaimable Unused Space" in Oracle Database Administrator's Guide.

• Rebuild the change log tables.

Check to see whether space is being wasted:

1. Determine the name of the change log table by running the cacheInfo.sql script on the Oracle database.

2. Calculate the size of the change log table. Call the result A. Adapt the name of the change log table in this example.

   SELECT table_name, ROUND((BLOCKS*8),2)||'KB' "size" 
    FROM user_tables
    WHERE table_name LIKE 'TT_05_%_L";
   

3. Calculate the size of the data in the change log table. Call the result B. Adapt the name of the change log table in this example.

   SELECT table_name, ROUND((num_rows*avg_row_length/1024),2)|| 'KB' "size"
    FROM user_tables
    WHERE table_name LIKE 'TT_05_%_L';
   

4. If (B/A)*100 is greater than 50 percent, then there is at least 40 percent space wasted (assuming a PCTFREE storage parameter set to 10). If there is at least 40 percent space wasted, defragmenting the change log table is recommended.

Perform these steps to defragment the change log table:

1. Alter the cache group to set the autorefresh state to PAUSED.

2. Copy the rows in the change log table to a temporary table.

3. Truncate the change log table.

4. Insert the rows from the temporary table to the change log table.

5. Alter the cache group to set the autorefresh state to ON.

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.