Improving the Performance of Loading or Refreshing a Large Number of Cache Instances

You can improve the performance of loading or refreshing a large number of cache instances into a cache group by specifying the operation to be multithreaded with the PARALLEL clause of the LOAD CACHE GROUP or REFRESH CACHE GROUP statement.

If you do not specify the PARALLEL clause, the load or refresh operation will be single-threaded. Specifying the PARALLEL clause to create multithreaded processes for a load or refresh provides a performance benefit if you have large data sets to be loaded or if the round trip time to Oracle is large. For example, if the data in the Oracle database is large, then an initial full load of the cache group can prove to be time consuming. Specifying multiple threads for the initial load can improve performance for that operation. However, note that multithreaded processes require more time to initiate than a single-threaded process and multithreaded processes use more system resources.

Specify the number of threads to use when processing the load or refresh operation. You can specify 2 to 10 threads. Do not specify more threads than the number of CPUs available on your system or you may encounter decreased performance than if you had not used the PARALLEL clause.

Note:

There is no default for the PARALLEL clause.

You cannot use the WITH ID clause with the PARALLEL clause. You can use the COMMIT EVERY n ROWS clause with the PARALLEL clause as long as n is greater than 0. In addition, you cannot use the PARALLEL clause for dynamic read-only cache groups or when database level locking is enabled. See REFRESH CACHE GROUP in the Oracle TimesTen In-Memory Database SQL Reference.

The following example refreshes cache instances in the TimesTen cache tables using a PARALLEL clause. This example refreshes the western_customers cache group from the cached Oracle Database tables using one thread to fetch rows from the cached Oracle Database tables and one thread to insert the rows into the cache tables:

LOAD CACHE GROUP western_customers COMMIT EVERY 256 ROWS PARALLEL 2;

The number of threads that you specify with the PARALLEL clause are assigned to readers and inserters. TimesTen recommends to have the number of readers >= number of inserters, because readers are slower than inserters. By default, only one thread is assigned to a reader to fetch rows from the cached Oracle Database tables. Since there is only one reader by default, then TimesTen assigns only one of the other threads as an inserter to insert the rows into the TimesTen cache tables. Using more inserters for a single reader offers no benefit.

If you want to specify more than 2 threads, use both the PARALLEL clause with the READERS clause to specify the number of readers to assign. The number of inserters assigned is the number of parallel threads minus the number of readers.

LOAD CACHE GROUP western_customers COMMIT EVERY 256 ROWS 
                          PARALLEL 6 READERS 4;

This specifies 6 threads with 4 of the threads assigned to readers and 2 threads assigned to inserters.