Optimize Performance for TimesTen Cache

The following recommendations may optimize performance for the TimesTen Cache:

  • Pin the TimesTen Cache meta tables and cache group base tables in the SGA. by executing the ALTER TABLE table_name CACHE statement to indicate to the Oracle database that these tables should be stored in the keep portion of the SGA buffer cache. Pinning TimesTen Cache tables in the SGA increases the probability that any given data block needed for a TimesTen Cache refresh operation will be available in the SGA when the refresh is performed and will not force a file system read. This minimizes physical file system reads executed during TimesTen cache refresh operations.

  • Pin TimesTen Cache triggers into the shared pool using the dbms_shared_pool.keep procedure. Pinning triggers into the shared pool for applications where updates to the cache group base tables are infrequent keeps the trigger from having to be reloaded and reparsed. This is not necessary for highly volatile tables where the trigger will be executed frequently and will remain in the shared pool under any circumstances.

  • Enable parallel query. For very large base tables with 10 million rows or more, consider using the Oracle database parallel query facility. The primary join query between the log table and the base table is the kind of query which the Oracle database parallel query is designed to handle. When parallel processing is enabled, the parallel query optimizer generates a query plan that enables the original query to be broken into sections to be worked concurrently by different parallel query worker processes. When using parallel query, users should assign a default degree of parallelism of (2*N) to the cache group base table, where "N" is the number of CPUs on the system. Then, experiment to understand what level of parallelism works best for their environment. Experiment with different table structures for base tables, as follows.

    • Use a standard heap table with default degree of parallelism assigned during table creation or by use of the ALTER TABLE PARALLEL command. Build an N-partition primary key index against the table.

    • Use an N-way partitioned table structure with partition range key based either on the table primary key or, in the case of a concatenated primary key, the high-order column of the primary key. The number of partitions should be set to the degree of parallelism. Use a local primary key index with the same number of partitions.

    • Use an N-way hashed partition structure using the primary key as the hash key, a local partitioned primary key index, and both index and table partitions equal to the degree of parallelism. The log table should not be partitioned, as the cardinalities of the log table should never be large enough that a partitioned log table would have any performance benefit. Further, given the continuously increasing value of the log table primary key column, range partitions cannot be used.

Note:

Each of these suggestions involve performance tradeoffs, which may not always be beneficial for optimal use. Consider and test each performance suggestion for your own configured environment.