8 Cache Performance

Using ttCacheAutorefreshXactLimit

For the month end processing, there can be a large number updates in a single transaction for the Oracle tables that are cached in autorefresh cache groups. In order to ensure that the large transaction does not fill up permanent memory, you can enable autorefresh to commit after every 256 (or any other user specified number) operations with the ttCacheAutorefreshXactLimit built-in procedure.

Turn on an autorefresh transaction limit for incremental autorefresh read-only cache groups before a large transaction with the ttCacheAutorefreshXactLimit built-in procedure where the value is set to ON or to a specific number of operations. Then, when autorefresh finishes updating the cached tables in TimesTen, turn off the autorefresh transaction limit for incremental autorefresh read-only cache groups with the ttCacheAutorefreshXactLimit built-in procedure.

The following example sets up the transaction limit to commit after every 256 operations for all incremental autorefresh read-only cache groups that are defined with an interval value of 10 seconds.

call ttCacheAutorefreshXactLimit('10000', 'ON');

After the month end process has completed and the incremental autorefresh read-only cache groups are refreshed, disable the transaction limit for incremental autorefresh read-only cache groups that are defined with the interval value of 10 seconds.

call ttCacheAutorefreshXactLimit('10000', 'OFF');

To enable the transaction limit for incremental autorefresh read-only cache groups to commit after every 1024 operations, provide 1024 as the value as follows:

call ttCacheAutorefreshXactLimit('10000', '1024');

Example of potential transactional inconsistency

The following example uses the employee and departments table, where the department id of the department table is a foreign key that points to the department id of the employee table.

The following example creates two incremental autorefresh read-only cache groups, where each is in its own cache group. The autorefresh transaction limit is enabled with ttCacheAutorefreshXactLimit before a large transaction and is disabled after it completes.

1. Before you initiate the large transaction, invoke ttCacheAutorefreshXactLimit to set the interval value and the number of operations after which to automatically commit. The following sets the number of operations to three (which is intentionally low to show a brief example) for all incremental autorefresh read-only cache groups with a two second interval.

   CALL ttCacheAutorefreshXactLimit('2000', '3');
   < 2000, 3 >
   1 row found.
   

2. Create the incremental autorefresh read-only cache groups with interval of two seconds. This example creates two static (non-dynamic) read-only cache groups, where each contains a single table.

   CREATE READONLY CACHE GROUP cgDepts AUTOREFRESH MODE INCREMENTAL 
    INTERVAL 2 SECONDS 
   FROM departments
       ( department_id    NUMBER(4) PRIMARY KEY
       , department_name  VARCHAR2(30) NOT NULL
       , manager_id       NUMBER(6)
       , location_id      NUMBER(4)
       );
    
   CREATE READONLY CACHE GROUP cgEmpls AUTOREFRESH MODE INCREMENTAL 
    INTERVAL 2 SECONDS 
   FROM employees
       ( employee_id    NUMBER(6) PRIMARY KEY
       , first_name     VARCHAR2(20)
       , last_name      VARCHAR2(25) NOT NULL
       , email          VARCHAR2(25) NOT NULL UNIQUE
       , phone_number   VARCHAR2(20)
       , hire_date      DATE NOT NULL
       , job_id         VARCHAR2(10) NOT NULL
       , salary         NUMBER(8,2)
       , commission_pct NUMBER(2,2)
       , manager_id     NUMBER(6)
       , department_id  NUMBER(4)
       );
   

3. Perform a manual LOAD CACHE GROUP for both autorefresh cache groups.

   LOAD CACHE GROUP cgDepts COMMIT EVERY 256 ROWS;
   27 cache instances affected.
    
   LOAD CACHE GROUP cgEmpls COMMIT EVERY 256 ROWS;
   107 cache instances affected.
   

You can have inconsistency within the table during an autorefresh as shown with the employees table.

1. On TimesTen, select the minimum and maximum salary of all employees.

   SELECT MIN(salary), MAX(salary) FROM employees;
   < 2100, 24000 >
   1 row found.
   

2. On the Oracle database, add 100,000 to everyone's salary.

   UPDATE employees SET salary = salary + 100000;
   107 rows updated.
   

3. On TimesTen, when you perform the SELECT again (while the autorefresh transactions are commmitted after every 3 records), it shows that while the maximum salary has updated, the minimum salary is still the old value.

   SELECT MIN(salary), MAX(salary) FROM employees;
   < 2100, 124000 >
   1 row found.
   

4. However, once the autorefresh completes, transactional consistency is maintained. For this example, once the autorefresh process completes, all salaries have increased by 100,000.

   SELECT MIN(salary), MAX(salary) FROM employees;
   < 102100, 124000 >
   1 row found.
   

5. The large transaction is complete, so disable the transaction limit for autorefresh cache groups with a 2 second interval.

   call ttCacheAutorefreshXactLimit('2000', 'OFF');
   

You can have transactional inconsistency between cache groups if you perform a SQL statement while the autorefresh process is progressing. The following SELECT statement example executes against the employees and department table in the cgDepts autorefresh cache group. With this example, since the foreign key is not enforced on TimesTen and the autorefresh process applies several transactions, the employee table updates may be inserted before the department updates.

In addition, all of the updates for both tables in the cache group are not applied until the autorefresh cycle has completed. In the following example, the SELECT statement is executed before the autorefresh process is complete. Thus, the results do not show all of the expected data, such as the department name and several employees (some of the lawyers in the legal department 1000) are missing.

SELECT e.department_id, d.DEPARTMENT_NAME, e.FIRST_NAME, e.LAST_NAME  
       FROM employees e, departments d         
       WHERE e.DEPARTMENT_ID  = d.DEPARTMENT_ID (+) 
       AND e.department_id >= 1000 ORDER BY 1,2,3,4;
< 1000, <NULL>, Alan, Dershowitz >
< 1000, <NULL>, F. Lee, Bailey >
< 1000, <NULL>, Johnnie, Cochran >
3 rows found.

However, after the autorefresh process completes, transactional consistency is maintained. The following shows the same SELECT statement executed after the autorefresh is complete. All expected data, the department information and all of the new lawyers, are updated.

SELECT e.department_id, d.DEPARTMENT_NAME, e.FIRST_NAME, e.LAST_NAME  
       FROM employees e, departments d         
       WHERE e.DEPARTMENT_ID  = d.DEPARTMENT_ID (+) 
       AND e.department_id >= 1000 ORDER BY 1,2,3,4;
< 1000, Legal, Alan, Dershowitz >
< 1000, Legal, Barry, Scheck >
< 1000, Legal, F. Lee, Bailey >
< 1000, Legal, Johnnie, Cochran >
< 1000, Legal, Robert, Kardashian >
< 1000, Legal, Robert, Shapiro >
6 rows found.

For autorefresh cache groups that have more than one table, you can also experience transactional inconsistency if you execute SQL statements while the autorefresh process is in progress.

1. Initiate the transaction limit for incremental autorefresh cache groups of 2 seconds with the ttCacheAutorefreshXactLimit built-in procedure and create a single autorefresh cache group with two tables: the employees and departments tables.

   CALL ttCacheAutorefreshXactLimit('2000', '3');
   < 2000, 3 >
   1 row found.
    
   CREATE READONLY CACHE GROUP cgDeptEmpls AUTOREFRESH MODE INCREMENTAL
    INTERVAL 2 SECONDS 
   FROM departments
        ( department_id    NUMBER(4) PRIMARY KEY
        , department_name  VARCHAR2(30) NOT NULL
        , manager_id       NUMBER(6)
        , location_id      NUMBER(4)
        )
      , employees
        ( employee_id    NUMBER(6) PRIMARY KEY
        , first_name     VARCHAR2(20)
        , last_name      VARCHAR2(25) NOT NULL
        , email          VARCHAR2(25) NOT NULL UNIQUE
        , phone_number   VARCHAR2(20)
        , hire_date      DATE NOT NULL
        , job_id         VARCHAR2(10) NOT NULL
        , salary         NUMBER(8,2)
        , commission_pct NUMBER(2,2)
        , manager_id     NUMBER(6)
        , department_id  NUMBER(4)
        , foreign key(department_id) references departments(department_id)
        );
   

2. Manually load the cache group.

   LOAD CACHE GROUP cgDeptEmpls COMMIT EVERY 256 ROWS;
   27 cache instances affected.
   

3. Perform a SELECT statement on TimesTen that uploads all of the legal department data.

   SELECT e.department_id, d.department_name, count(*)
          FROM employees e, departments d         
          WHERE e.department_id  = d.department_id (+) 
          GROUP BY e.department_id, d.department_name
          ORDER BY 1 desc;
   < 110, Accounting, 2 >
   < 100, Finance, 6 >
   < 90, Executive, 3 >
   < 80, Sales, 34 >
   < 70, Public Relations, 1 >
   < 60, IT, 5 >
   < 50, Shipping, 45 >
   < 40, Human Resources, 1 >
   < 30, Purchasing, 6 >
   < 20, Marketing, 2 >
   < 10, Administration, 1 >
   11 rows found.
   

4. On Oracle, insert a new legal department, numbered 1000, with 6 new lawyers in both the employee and department tables.

5. When performing a SELECT statement on TimesTen during the autorefresh process, only data on two of the lawyers in department 1000 have been uploaded into TimesTen.

   SELECT e.department_id, d.department_name, count(*)
          FROM employees e, departments d         
          WHERE e.department_id  = d.department_id (+) 
          GROUP BY e.department_id, d.department_name
          ORDER BY 1 desc;
   < 1000, Legal, 2 >
   < 110, Accounting, 2 >
   < 100, Finance, 6 >
   < 90, Executive, 3 >
   < 80, Sales, 34 >
   < 70, Public Relations, 1 >
   < 60, IT, 5 >
   < 50, Shipping, 45 >
   < 40, Human Resources, 1 >
   < 30, Purchasing, 6 >
   < 20, Marketing, 2 >
   < 10, Administration, 1 >
   12 rows found.
   

6. However, after the autorefresh process completes, all 6 employees (lawyers) in the legal department have been uploaded to TimesTen. Now, it is transactionally consistent.

   SELECT e.department_id, d.department_name, COUNT(*)
          FROM employees e, departments d         
          WHERE e.department_id  = d.department_id (+) 
          GROUP BY e.department_id, d.department_name
          ORDER BY 1 desc;
   < 1000, Legal, 6 >
   < 110, Accounting, 2 >
   < 100, Finance, 6 >
   < 90, Executive, 3 >
   < 80, Sales, 34 >
   < 70, Public Relations, 1 >
   < 60, IT, 5 >
   < 50, Shipping, 45 >
   < 40, Human Resources, 1 >
   < 30, Purchasing, 6 >
   < 20, Marketing, 2 >
   < 10, Administration, 1 >
   12 rows found.
   

7. The large transaction is complete, so disable the transaction limit for autorefresh cache groups with a 2 second interval.

   call ttCacheAutorefreshXactLimit('2000', 'OFF');
   

Retrieving statistics to evaluate performance when a transaction limit is set

To see how a autorefresh transaction limit for a particular autorefresh interval is performing, you can retrieve statistics for the last 10 incremental autorefresh transactions for this autorefresh interval with the ttCacheAutorefIntervalStatsGet built-in procedure. See "Retrieving statistics on autorefresh transactions" for more information.

How to determine the cache group name for a particular select limit

To determine the interval for a cache group, use ttIsql and run the cachegroups command:

> cachegroups cgowner.cgname;

This returns all attributes for the cgowner.cgname cache group including the interval.

To determine which intervals have a select limit, you can run the following query on the Oracle database where <cacheAdminUser> is the cache administrator, <hostName> is the host name of the machine where the TimesTen database is located, <databaseFileName> is the database path taken from the DataStore attribute, and substitute the version number (such as 06) for the xx.

SELECT * FROM <cacheAdminUser>.tt_xx_arinterval_params
 WHERE param='AutorefreshSelectEveryN'
   AND host='<hostName>'
   AND database like '%<databaseFileName>%'
 ORDER BY arinterval;
 

For example, if the cache administrator user name is pat, the host name is myhost, the database file name is myTtDb, and 06 is substituted for xx that is the TimesTen minor release number then:

SELECT * FROM pat.tt_06_arinterval_params
 WHERE param='AutorefreshSelectEveryN'
   AND host='myhost'
   AND database like '%myTtDb%'
 ORDER BY arinterval;

The interval is stored in milliseconds.

Retrieving statistics to evaluate performance when using a select limit

To see how a select limit for a particular autorefresh interval is performing, you can retrieve statistics for incremental autorefresh transactions for this autorefresh interval with the ttCacheAutorefIntervalStatsGet built-in procedure. See "Retrieving statistics on autorefresh transactions" for more information.