ttStatsConfig

Required Privilege

This procedure requires the ADMIN privilege.

Usage in TimesTen Scaleout and TimesTen Classic

This procedure is supported in TimesTen Classic.

This procedure is supported in TimesTen Scaleout, but supports different parameter/value pairs than in TimesTen Classic.

In TimesTen Scaleout, this procedure broadcasts changes to all elements.

Related Views

This procedure has no related views.

Syntax

ttStatsConfig('param', [value], [option])

Parameters

ttStatsConfig has the parameters:

Parameter	Type	Description
`param`	`VARCHAR2(50)` `NOT NULL`	The name of the parameter to configure.
`value`	`VARCHAR2(200)`	The value of the specified parameter. If no value is supplied, the built-in procedure displays the current value for the specified parameter.
`option`	`VARCHAR2 (200)`	The value of a second optional parameter, for example `con_id` for `LatchStats`.

Parameter / Value Pairs

The supported parameter/value pairs in TimesTen Classic and TimesTen Scaleout are different. These are the supported parameter/value pairs:

TimesTen Classic

These parameter/value pairs can be set with TimesTen Classic:

Parameter	Value	Description
`ConnSampleFactor`	`C,S` `0<=C<=Connections` `0<=S<=60000`	Specifies the frequency at which a specific connection is sampled. `C` – Stands for the connection ID to be sampled. For connection ID, see ttXactIdGet. `Connections` – Represents the total number of connections to the database. `S` – Is the sampling factor. The default value is 0 and indicates that sampling is turned off. A value greater than 0 indicates that a sample is taken at that interval of statements for the connection specified.
`LatchStats`	`scope,level` `scope=conn\|db\|con_id` `level=NONE\|TYPICAL\|` `ALL\|BASIC`	Specifies the scope, `scope`, and the level, `level`, for collection for latch statistics. The scope value determines at what level TimesTen should collect latch statistics: `conn` - Collects latch statistics for your current connection. `db` - Collects latch statistics for your database. `con_id` - Collects latch statistics for the connection id that you specify. The level value determines the level at which TimesTen collects statistics: `NONE` - Disables the collection of latch statistics. `TYPICAL` - Ensures the collection of major useful latch statistics. `ALL` - Additional statistics are added to the set of statistics collected with the `TYPICAL` setting. The additional statistics include internal and debugging statistics. `BASIC` - Disables the collection of many of the important latch statistics. If latch statistics are enabled, TimesTen allocates around 100KB from temporary memory to store these statistics. Once you have configured the `LatchStats` parameter, you can use the `ttLatchStatsGet` built-in procedure to view latch statistics. See ttLatchStatsGet for more information.
`SQLCmdHistogramReset`	`0` or not	The existing SQL run time statistics are reset if the specified value is nonzero.
`SQLCmdSampleFactor`	`0` <= `value` <= `60000`	The frequency at which a SQL command sample is taken. The default is `0`. A value of `0` indicates that sampling is turned off. A value greater than `0` indicates that a sample is taken at that interval of SQL statements. For example, a value of `10` indicates that for every 10th SQL statement run, the wall clock time of that run is captured.
`StatsLevel`	`NONE` `TYPICAL` `ALL` `BASIC`	Specifies the level of collection for database statistics. TimesTen stores theses statistics in system tables. Setting the `StatsLevel` parameter to `NONE` disables the collection of system statistics. The default setting of `TYPICAL` ensures collection of major useful statistics and should be adequate for most environments. When the `StatsLevel` parameter is set to `ALL`, additional statistics are added to the set of statistics collected with the `TYPICAL` setting. The additional statistics include internal and debugging statistics. Setting the `StatsLevel` parameter to `BASIC` disables the collection of many of the important statistics required by many TimesTen features.

Note:

There are two ttStatsConfig calls that configure the collection of histogram data for SQL commands.

ttStatsConfig('SQLCmdSampleFactor', S) enables SQL histogram collection for all connections and the frequency at which a SQL command sample is taken. The value of the sampling factor S should be greater than 0.
ttStatsConfig(;ConnSampleFactor', C, S) enables SQL histogram collection for a connection to the database (C ) and the frequency at which a specific connection is sampled. The value of the sampling factor S should be greater than 0.

If the sampling factor value is greater than 0 for both calls, then the value for ttStatsConfig('ConnSampleFactor', C, S) takes precedence. If the sampling factor value is set to 0 for both parameters, then the sampling is turned off.

TimesTen Scaleout

These parameter/value pairs can be set with TimesTen Scaleout:

Parameter Value Description

Parameter	Value	Description
`pollSec`	`0` `10` <= `value` <= `60`	The polling interval, in seconds, at which the `ttStats` daemon captures snapshots of the TimesTen Scaleout. A value of `0` disables the `ttStats` daemon from capturing metrics. The value of the polling interval does not affect the performance of the TimesTen Scaleout. However, a polling interval of 60 seconds tends to use six times more space than a polling interval of 10 seconds. Ensure that you have sufficient `PermSize` to support the desired polling interval. The default value is `30` seconds.
`retainMinutes`	15<= `value` <= `1440`	The time, in minutes, that the `ttStats` daemon waits before aggregating and purging raw metrics. If you use a larger value for `retainMinutes`, the `ttStats` daemon stores more metrics in the system tables. The default value is `120` minutes.
`retentionDays`	`1` < `value` < `730`	The retention time interval, in days, at which the `ttStats` daemon drops `ttStats` snapshots of the TimesTen Scaleout. For example, if the retention time interval is 62 days, the `ttStats` daemon drops the 1st day's snapshot on the 63rd day. Ensure that you have sufficient `PermSize` to support the desired retention time interval. In most cases, a day's worth of data takes up around 20 MB of space. These metrics are stored in `SYS` tables and endure database bounces. The default value is `62` days.

pollSec

0

10 <= value <= 60

The polling interval, in seconds, at which the ttStats daemon captures snapshots of the TimesTen Scaleout. A value of 0 disables the ttStats daemon from capturing metrics.

The value of the polling interval does not affect the performance of the TimesTen Scaleout. However, a polling interval of 60 seconds tends to use six times more space than a polling interval of 10 seconds. Ensure that you have sufficient PermSize to support the desired polling interval.

The default value is 30 seconds.

retainMinutes

15<= value <= 1440

The time, in minutes, that the ttStats daemon waits before aggregating and purging raw metrics. If you use a larger value for retainMinutes, the ttStats daemon stores more metrics in the system tables.

The default value is 120 minutes.

retentionDays

1 < value < 730

The retention time interval, in days, at which the ttStats daemon drops ttStats snapshots of the TimesTen Scaleout. For example, if the retention time interval is 62 days, the ttStats daemon drops the 1st day's snapshot on the 63rd day.

Ensure that you have sufficient PermSize to support the desired retention time interval. In most cases, a day's worth of data takes up around 20 MB of space. These metrics are stored in SYS tables and endure database bounces.

The default value is 62 days.

Result Set

ttStatsConfig returns the result set:

Column	Type	Description
`param`	`VARCHAR2(50)` `NOT NULL`	The name of the parameter that was configured.
`value`	`VARCHAR2(200)`	The value of the specified parameter. If no value is supplied, the built-in procedure displays the current value for the specified parameter.

Examples

Since TimesTen and TimesTen Scaleout support different parameter/value pairs, there are also different examples. These are supported examples:

TimesTen Classic

Sample every command:

Command> call ttStatsConfig('SqlCmdSampleFactor',1);
< SQLCMDSAMPLEFACTOR, 1 >
1 row found.

Check sampling:

Command> call ttStatsConfig('SqlCmdSampleFactor');
< SQLCMDSAMPLEFACTOR, 1 >
1 row found.

Sample every fifth statement on connection 1.

Command> call ttStatsConfig('ConnSampleFactor', '1,5');
< CONNSAMPLEFACTOR, 1,5 >
1 row found.

Turn off sampling on connection 1.

Command> call ttStatsConfig('ConnSampleFactor', '1,0');
< CONNSAMPLEFACTOR, 1,0 >
1 row found.

Check data store statistics collection level.

Command> call ttstatsconfig('StatsLevel');
< STATSLEVEL, TYPICAL >
1 row found.

Turn off data store statistics collection.

Command> call ttstatsconfig('StatsLevel','None');
< STATSLEVEL, NONE >
1 row found.

TimesTen Scaleout

Sets the polling interval of statistics to 45 seconds. Therefore, the ttStats daemon aggregates statics every 45 seconds:

Command> call ttStatsConfig('pollsec', 45);
< POLLSEC, 45 >
1 row found.

Sets the time interval when the ttStats daemon purges raw metrics to 60 minutes:

Command> call ttStatsConfig('retainMinutes', 60);
< RETAINMINUTES, 60 >
1 row found.

Sets the retention time interval for statistics to 30 days:

Command> call ttStatsConfig('retentionDays', 30);
< RETENTIONDAYS, 30 >
1 row found.