Update Table Statistics for Large Tables in Parallel

Performance impact: Large

It is important to keep table statistics up to date for all TimesTen tables. However, this process can be time-consuming and performance intensive when used on large tables. Consider calling the ttOptUpdateStats built-in procedure in parallel when updating the statistics for multiple large tables.

Note:

A TimesTen table is considered a small table when it contains less than 1 million rows. A TimesTen table is considered a large table when it contains over 100 million rows.

Call the ttOptUpdateStats built-in procedure for all of the large tables where you want to update table statistics. Make sure to call each ttOptUpdateStats built-in procedure in parallel. See ttOptUpdateStats in the Oracle TimesTen In-Memory Database Reference.

Command> call ttOptUpdateStats('table1',0,0);
Command> call ttOptUpdateStats('table2',0,0);
...
...
Command> call ttOptUpdateStats('finaltable',0,0);

Once the ttOptUpdateStats built-in procedure calls have completed, determine how many transactions are accessing the large TimesTen tables for which you updated table statistics. During low transaction load times run the ttOptCmdCacheInvalidate('',1) built-in procedure. See ttOptCmdCacheInvalidate in the Oracle TimesTen In-Memory Database Reference. During high transaction load times run the following built-in procedures and make sure to call each ttOptCmdCacheInvalidate built-in procedure in parallel:

Command> call ttOptCmdCacheInvalidate('table1',1);
Command> call ttOptCmdCacheInvalidate('table2',1);
...
...
Command> call ttOptCmdCacheInvalidate('finaltable',1);

The table statistics of your tables are now up to date and compiled commands in the SQL command cache are invalidated.