Check Large Batch Jobs on Oracle Database Tables

Customers sometimes run large batch jobs at month-end or year-end on the Oracle database tables that are cached in read-only cache groups with incremental autorefresh. This can cause performance and memory problems for autorefresh operations and replication unless preventative steps are taken.

The tasks described in this section are supported for these components in an TimesTen Cache configuration:

• TimesTen Scaleout

• Active standby pair replication

• Active standby pair with a return service specified

• Active standby pair with a disaster recovery subscriber

• Physical, synchronous Oracle Data Guard

• Oracle RAC

Perform these tasks when large batch jobs must be run on the cached Oracle database tables:

1. Set the autorefresh state to PAUSED for cache groups with the AUTOREFRESH attribute that are affected by the batch job.
2. Set the autorefresh state to PAUSED for cache groups with the AUTOREFRESH attribute that are not directly affected by the batch job. This ensures that there is a consistent view of the data during batch processing.
3. Run the batch job on the cached Oracle database tables.
4. Make sure all autorefresh change log records have been assigned a valid log sequence number (logseq). Call the cacheInfo.sql script:

   % cd timesten_home/install/oraclescripts
   % sqlplus cacheuser/oracle
   SQL> @cacheInfo
   

   Look for the number of updates that have not been marked with a valid log sequence number. Ideally, the number should be zero or small (less than 100) for all tables in cache groups for which the autorefresh state was paused. Consider the following example.

   *************Autorefresh Objects Information  ***************
   Host name: host1
   Timesten datastore name: /scratch/ttuser/ds/mydsn
   Cache table name: TTUSER.NOTAFFECTED
   Change log table name: tt_05_460491_L
   Number of rows in change log table: 1
   Maximum logseq on the change log table: 1
   Timesten has autorefreshed updates up to logseq: 1
   Number of updates waiting to be autorefreshed: 0
   Number of updates that has not been marked with a valid logseq: 0
   ****************************
   Host name: host2
   Timesten datastore name: /scratch/ttuser/ds/mydsn
   Cache table name: TTUSER.AFFECTED
   Change log table name: tt_05_460489_L
   Number of rows in change log table: 100
   Maximum logseq on the change log table: 213
   Timesten has autorefreshed updates up to logseq: 213
   Number of updates waiting to be autorefreshed: 10000
   Number of updates that has not been marked with a valid logseq: 0
   ****************************
   

5. For each cache group that was altered in step 1, manually refresh the cache group in parallel mode. Select appropriate values for the transaction size (number of rows committed at a time) and degree of parallelism. For example:

   REFRESH CACHE GROUP samplecg
    COMMIT EVERY n ROWS PARALLEL m;
   COMMIT;
   

   Note that this operation automatically resets the autorefresh state to ON.

6. For each cache group that was altered in step 2, set the autorefresh state to ON. For example:

   ALTER CACHE GROUP sampecg2 SET AUTOREFRESH ON;
   COMMIT;