Compute Exact or Estimated Statistics

Performance impact: Large

If statistics are available on the data in the database, the TimesTen optimizer uses them when preparing a command to determine the optimal path to the data. If there are no statistics, the optimizer uses generic guesses about the data distribution.

Note:

See The TimesTen Query Optimizer.

You should compute statistics before preparing your statements, since the information is likely to result in a more efficient query optimizer plan. When gathering statistics, you need to determine when and how often to gather new statistics as performance is affected by the statistics collection process. The frequency of collection should balance the task of providing accurate statistics for the optimizer against the processing overhead incurred by the statistics collection process.

Since computing statistics is a time-consuming operation, you should compute statistics with the following guidelines:

  • Update statistics after loading your database or after major application upgrades.

  • Do not update statistics during a heavy transaction load.

  • Update statistics when there is substantial creation or alteration on tables, columns, or PL/SQL objects.

    If you have created or altered a substantial number of tables, columns, or PL/SQL objects in your database, you should update the data dictionary optimizer statistics for the following system tables: SYS.TABLES, SYS.COLUMNS, and SYS.OBJ$.

  • When you substantially modify tables in batch operations, such as a bulk load or bulk delete, you can gather statistics on these tables as part of the batch operation.

  • Update statistics infrequently, such as once a week or once a month, when tables are only incrementally modified.

  • Update statistics as part of a regularly run script or batch job during low transaction load times.

  • When updating the statistics for multiple large tables, see Update Table Statistics for Large Tables in Parallel.

Note:

For performance reasons, TimesTen does not hold a lock on tables or rows when computing statistics.

Use the following for computing statistics: ttIsql statsupdate command, ttOptUpdateStats, or ttOptEstimateStats. Providing an empty string as the table name updates statistics for all tables in the current user's schema.

  • The statsupdate command within ttIsql evaluates every row of the table(s) in question and computes exact statistics.

  • The ttOptUpdateStats built-in procedure evaluates every row of the table(s) in question and computes exact statistics.

  • The ttOptEstimateStats procedure evaluates only a sampling of the rows of the table(s) in question and produces estimated statistics. This can be faster, but may result in less accurate statistics. Computing statistics with a sample of 10 percent is about ten times faster than computing exact statistics and generally results in the same execution plans.

Note:

See ttIsql and Built-In Procedures in the Oracle TimesTen In-Memory Database Reference.