ttComputeTabSizes

The ttComputeTabSizes built-in procedure refreshes table size statistics stored in TimesTen system tables. After calling this built-in procedure, you can review the statistics updates by querying the DBA_TAB_SIZES, USER_TAB_SIZES or ALL_TAB_SIZES view.

This procedure computes the different types of storage allocated for the specified table, such as the amount of storage allocated for inline row storage, dictionary tables, out-of-line buffers and system usage. If no table is specified, the procedure computes the sizes for all tables on which the user has SELECT privileges. The implementation of this built-in behaves like a DDL statement: the transaction commits just before the procedure begins and commits again upon its successful termination.

Required Privilege

This procedure requires the SELECT privilege on the specified table.

Usage in TimesTen Scaleout and TimesTen Classic

This procedure is supported in TimesTen Classic.

TimesTen Scaleout applications can call this built-in procedure.

In TimesTen Scaleout, this procedure runs on all elements in the grid.

Related Views

This procedure has no related views.

Syntax

ttComputeTabSizes (['tblName'], [includeOutOfLine])

Parameters

ttComputeTabSizes has the parameters:

Parameter Type Description

Parameter	Type	Description
`tblName`	`TT_CHAR(61)`	Name of an application table. Can include the table owner. If a value of `NULL` or an empty string is provided, updates the statistics for all the current tables. The type of tables that can be estimated are: User tables, including cache group tables Materialized views System tables
`includeOutOfLine`	`TT_INTEGER`	`0` (no) or `1` (yes). Default is `1` (yes). If value is `0` (no), the procedure does not compute the size of out-of-line values for any table that has out-of-line columns. The out-of-line fields are displayed as `NULL`. Avoiding the computation of out-of-line values significantly decreases the latency of this procedure.

tblName

TT_CHAR(61)

Name of an application table. Can include the table owner. If a value of NULL or an empty string is provided, updates the statistics for all the current tables.

The type of tables that can be estimated are:

User tables, including cache group tables
Materialized views
System tables

includeOutOfLine

TT_INTEGER

0 (no) or 1 (yes). Default is 1 (yes).

If value is 0 (no), the procedure does not compute the size of out-of-line values for any table that has out-of-line columns. The out-of-line fields are displayed as NULL.

Avoiding the computation of out-of-line values significantly decreases the latency of this procedure.

Result Set

ttComputeTabSizes returns no results.

Examples

To compute the size of my_table without including out-of-line columns, use:

CALL ttComputeTabSizes ('my_table', 0);

Notes

The built-in procedure allows concurrent insertions while ttComputeTabSizes is running. For this reason, the size computed by ttComputeTabSizes for each table is any value between the minimum size of the table during the computation and the maximum size of the table during the computation. For example, if the size of a table is 250 MB when ttComputeTabSizes is performed, and a transaction running concurrently raises the size of the table to 300 MB, ttComputeTabSizes estimates a value between 250 and 300 MB.
The computed size of tables includes the size of dictionaries.