10 TT_STATS

The TT_STATS package enables you to collect snapshots of TimesTen database metrics (statistics, states, and other information) and generate reports comparing two specified snapshots.

This chapter contains the following topics:

Note:

There is also a ttStats utility program. In addition to acting as a convenient front-end for the TT_STATS package to collect snapshots and generate reports, the utility can monitor metrics in real-time. For information, see "ttStats" in Oracle TimesTen In-Memory Database Reference.

Using TT_STATS

This section covers the following topics for the TT_STATS package:


Overview

The TT_STATS package provides features for collecting and comparing snapshots of TimesTen system metrics, according to the capture level. Each snapshot can consist of what TimesTen considers to be basic metrics, typical metrics, or all available metrics.

For those familiar with Oracle Database performance analysis tools, these reports are similar in nature to Oracle Automatic Workload Repository (AWR) reports.

The package includes procedures and functions for the following:

  • Capture a snapshot of metrics according to the capture level.

  • Generate a report in HTML or plain text showing before and after values of metrics or the differences between those values.

  • Show the snapshot ID and timestamp of snapshots currently stored.

  • Delete snapshots based on a range of IDs or timestamps.

  • Get or set the value of a specified TT_STATS configuration parameter.

  • Show the values of all configuration parameters.

Note:

In this release, the only supported configuration parameters are for the maximum number of snapshots and the maximum total size of snapshots that can be stored.

Security model

By default, only the instance administrator has privilege to run functions or procedures of the TT_STATS PL/SQL package. Any other user, including an ADMIN user, must be granted EXECUTE privilege for the TT_STATS package by the instance administrator or by an ADMIN user, such as in the following example:

GRANT EXECUTE ON SYS.TT_STATS TO scott;

Note:

Although ADMIN users cannot execute the package by default, they can grant themselves privilege to execute it.

Operational notes

Each metric in the SYS.SYSTEMSTATS table has a designated level, and the capture level setting for a snapshot corresponds to those levels. Available levels are NONE, BASIC, TYPICAL (the default, appropriate for most purposes), and ALL. See "CAPTURE_SNAPSHOT procedure and function" for additional information.

Be aware that the capture level applies only to metrics in the SYS.SYSTEMSTATS table, however. For metrics outside of SYSTEMSTATS, the same set of data are gathered regardless of the capture level.

Note:

You can also use the ttStatsConfig built-in procedure to change the capture level. See "ttStatsConfig" in Oracle TimesTen In-Memory Database Reference.

Snapshots are stored in a number of TimesTen SYS.SNAPSHOT_xxxxx system tables. (For reference, these tables are listed in "Tables and views reserved for internal or future use" in Oracle TimesTen In-Memory Database System Tables and Views Reference.) To assist you in minimizing the risk of running out of permanent space, the TT_STATS package has configuration parameters to specify the maximum number of snapshots that can be stored and the total size of snapshots stored. In this release, an error is issued if either limit is exceeded, and the snapshot capture would fail. TimesTen provides default limits, but you can alter them through the SET_CONFIG procedure. (See "SET_CONFIG procedure".)

Be aware that execution of this package may involve numerous reads and insertions, which may impact database performance during package operations.

Note:

To view output that goes to the standard output (such as from versions of the SHOW_SNAPSHOTS and GET_CONFIG procedures), run the following command from ttIsql:
set serveroutput on;

Summary of TT_STATS subprograms

This section summarizes and then documents the procedures and functions of the TT_STATS package.

Table 10-1 TT_STATS Package Subprograms

Subprogram Description

CAPTURE_SNAPSHOT procedure and function

Takes a snapshot of TimesTen metrics. The function also returns the snapshot ID.

DROP_SNAPSHOTS_RANGE procedures

Deletes snapshots according to a specified range of snapshot IDs or timestamps.

GENERATE_REPORT_HTML procedure

Produces a report in HTML format based on the data from two specified snapshots.

GENERATE_REPORT_TEXT procedure

Produces a report in plain text format based on the data from two specified snapshots.

GET_CONFIG procedures

Retrieves the value of a specified TT_STATS configuration parameter or the values of all configuration parameters.

SET_CONFIG procedure

Sets a specified value for a specified TT_STATS configuration parameter.

SHOW_SNAPSHOTS procedures

Shows the snapshot IDs and timestamps of all snapshots currently stored in the database.


Note:

In this release, the only supported TT_STATS configuration parameters are for limits of the number of snapshots and total size of snapshots that can be stored in the permanent memory segment.

CAPTURE_SNAPSHOT procedure and function

The procedure captures a snapshot of TimesTen metrics according to the specified capture level, or by default uses what is considered a typical level. The snapshots are stored in TimesTen SYS.SNAPSHOT_xxxx system tables.

The function does the same and also returns the ID number of the snapshot.

Notes:

  • The capture level applies only to metrics from SYS.SYSTEMSTATS, as discussed below.

  • There are defined limits for the maximum number of snapshots that can be stored and the maximum total size of all stored snapshots. See "SET_CONFIG procedure" for additional information, including how to set new values.

Syntax

TT_STATS.CAPTURE_SNAPSHOT (
   capture_level     IN VARCHAR2 DEFAULTED,
   description       IN VARCHAR2 DEFAULTED);

TT_STATS.CAPTURE_SNAPSHOT (
   capture_level     IN VARCHAR2 DEFAULTED,
   description       IN VARCHAR2 DEFAULTED)
  RETURN BINARY_INTEGER;

Parameters

Table 10-2 CAPTURE_SNAPSHOT procedure parameters

Parameter Description

capture_level

The desired level of metrics to capture

The following choices are available:

  • NONE: For metrics outside of SYS.SYSTEMSTATS only.

  • BASIC: For a minimal basic set of metrics.

  • TYPICAL (default): For a typical set of metrics. This level is appropriate for most purposes. The basic set is a subset of the typical set.

  • ALL: For all available metrics.

Use the same level for any two snapshots to be used in a report.

Note: For metrics outside of SYS.SYSTEMSTATS, the same data are gathered regardless of the capture level.

description

An optional description of the snapshot

Use this if you want to provide any description or notes for the snapshot, such as to distinguish it from other snapshots.


Usage notes

  • As mentioned above, the capture level applies only to metrics in the SYS.SYSTEMSTATS table. For metrics outside of SYSTEMSTATS, the same data are gathered regardless of the capture level.

  • For SYSTEMSTATS metrics, only those within the specified capture level have meaningful accumulated values. SYSTEMSTATS metrics outside of the specified level have a value of 0 (zero).

  • You can call the procedure or function without specifying the capture_level parameter. This results in capture of what is considered a typical level of metrics.

Return value

The function returns a BINARY_INTEGER value for the ID of the snapshot.

Examples

Capture just the basic metrics:

call tt_stats.capture_snapshop('BASIC');

Capture the default typical level of metrics:

call tt_stats.capture_snapshot;

This example uses the function to capture the default typical level of metrics and displays the snapshot ID:

declare
   id   number;
begin
   id := tt_stats.capture_snapshot();
   dbms_output.put_line('Snapshot with ID (' || id || ') was captured.');
end;

DROP_SNAPSHOTS_RANGE procedures

This procedure deletes previously captured snapshots of TimesTen metrics according to a specified range of snapshot IDs or timestamps.

Note:

You can use the SHOW_SNAPSHOTS procedures to display the IDs and timestamps of all currently stored snapshots.

Syntax

TT_STATS.DROP_SNAPSHOTS_RANGE (
   snapshot_low      IN BINARY_INTEGER,
   snapshot_high     IN BINARY_INTEGER);

TT_STATS.DROP_SNAPSHOTS_RANGE (
   ts_old            IN TIMESTAMP(6),
   ts_new            IN TIMESTAMP(6));

Parameters

Table 10-3 DROP_SNAPSHOTS_RANGE procedure parameters

Parameter Description

snapshot_low

Snapshot ID for the start of the range of snapshots to delete

snapshot_high

Snapshot ID for the end of the range of snapshots to delete

ts_old

Timestamp for the start of the range of snapshots to delete

ts_new

Timestamp for the end of the range of snapshots to delete


Usage notes

  • Specify 0 (zero) for both input parameters to drop all snapshots.

  • It is permissible for snapshot_low to be greater than snapshot_high. The range of snapshots from the lower value through the higher value are still deleted.

  • Similarly, it is permissible for ts_new to be an older timestamp than ts_old.

Examples

This example specifies snapshot IDs, dropping the snapshots with IDs of 1, 2, and 3.

call tt_stats.drop_snapshots_range(1,3);

GENERATE_REPORT_HTML procedure

This procedure uses the data from two specified snapshots of TimesTen metrics to produce a report in HTML format with information for each metric, such as rate of change or start and end values.

Reports include a summary of memory usage, connections, and load profile, followed by metrics (as applicable) for SQL statements, transactions, PL/SQL memory, replication, logs and log holds, checkpoints, cache groups, cache grid, latches, locks, XLA, and TimesTen connection attributes.

For a detailed example of the HTML reports that are produced, see "ttStats" in Oracle TimesTen In-Memory Database Reference.

Also see "GENERATE_REPORT_TEXT procedure".

Notes:

  • You can use the SHOW_SNAPSHOTS procedures to display the IDs and timestamps of all currently stored snapshots.

  • Use snapshots taken at the same capture level. See "CAPTURE_SNAPSHOT procedure and function".

  • The reports are similar in nature to Oracle Automatic Workload Repository (AWR) reports.

  • For SYSTEMSTATS metrics, only those within the specified capture level have meaningful accumulated values. SYSTEMSTATS metrics outside of the specified level have a value of 0 (zero).

Syntax

TT_STATS.GENERATE_REPORT_HTML (
   snapshot_id1     IN  BINARY_INTEGER,
   snapshot_id2     IN  BINARY_INTEGER,
   report           OUT TT_STATS.REPORT_TABLE);

Parameters

Table 10-4 GENERATE_REPORT_HTML procedure parameters

Parameter Description

snapshot_id1

ID of the first snapshot to analyze

snapshot_id2

ID of the second snapshot to analyze

report

An associative array (index-by table) containing the HTML-formatted report

Each row is of type VARCHAR2(32767).

The application can output the report contents line-by-line as desired.


Usage notes

  • You can enter the snapshot IDs in either order. The procedure determines which is the earlier.


GENERATE_REPORT_TEXT procedure

This procedure analyzes and compares two specified snapshots of TimesTen metrics and produces a report in plain text format with information for each metric, such as rate of change or start and end values.

Reports include a summary of memory usage, connections, and load profile, followed by metrics (as applicable) for SQL statements, transactions, PL/SQL memory, replication, logs and log holds, checkpoints, cache groups, cache grid, latches, locks, XLA, and TimesTen connection attributes.

Also see "GENERATE_REPORT_HTML procedure".

Notes:

  • You can use the SHOW_SNAPSHOTS procedures to display the IDs (and timestamps) of all currently stored snapshots.

  • Use snapshots taken at the same capture level. See "CAPTURE_SNAPSHOT procedure and function".

  • The reports are similar in nature to Oracle Automatic Workload Repository (AWR) reports.

  • For SYSTEMSTATS metrics, only those within the specified capture level have meaningful accumulated values. SYSTEMSTATS metrics outside of the specified level have a value of 0 (zero).

Syntax

TT_STATS.GENERATE_REPORT_TEXT (
   snapshot_id1     IN  BINARY_INTEGER,
   snapshot_id2     IN  BINARY_INTEGER,
   report           OUT TT_STATS.REPORT_TABLE);

Parameters

Table 10-5 GENERATE_REPORT_TEXT procedure parameters

Parameter Description

snapshot_id1

ID of the first snapshot to analyze

snapshot_id2

ID of the second snapshot to analyze

report

An associative array (index-by table) containing the plain-text-formatted report

Each row is of type VARCHAR2(32767).

The application can output the report contents line-by-line as desired.


Usage notes

  • You can enter the snapshot IDs in either order. The procedure determines which is the earlier.


GET_CONFIG procedures

Either procedure retrieves the value of a specified TT_STATS configuration parameter or the values of all configuration parameters. The version without the OUT parameter sends the information to the standard output.

Syntax

TT_STATS.GET_CONFIG (
   name      IN   VARCHAR2 DEFAULTED);

TT_STATS.GET_CONFIG (
   name      IN   VARCHAR2 DEFAULTED,
   params    OUT  TT_STATS.REPORT_TABLE);

Parameters

Table 10-6 GET_CONFIG procedure parameters

Parameter Description

name

Name of a TT_STATS configuration parameter whose value you want to retrieve

In this release, the following TT_STATS parameters are supported.

  • MAX_SNAPSHOT_COUNT: This is the maximum number of snapshots that can be stored.

  • MAX_SNAPSHOT_RETENTION_SIZE: This is the maximum total size of all stored snapshots, in MB.

If no parameter name is specified (name is empty), the values of all configuration parameters are displayed.

Also see "SET_CONFIG procedure".

params

An associative array (index-by table) containing the value of the specified TT_STATS parameter or values of all parameters

Each row is of type VARCHAR2(32767).



SET_CONFIG procedure

This procedure sets a specified value for a specified TT_STATS configuration parameter.

Syntax

TT_STATS.SET_CONFIG (
   name      IN  VARCHAR2,
   value     IN  BINARY_INTEGER);

Parameters

Table 10-7 SET_CONFIG procedure parameters

Parameter Description

name

Name of the TT_STATS configuration parameter to set

In this release, the following TT_STATS parameters are supported:

  • MAX_SNAPSHOT_COUNT: This is the maximum number of snapshots that can be stored. The default value is 256.

  • MAX_SNAPSHOT_RETENTION_SIZE: This is the maximum total size of all stored snapshots, in MB. The default value is 256 MB.

An error is issued if either limit is exceeded, and the snapshot capture fails.

Also see "GET_CONFIG procedures".

value

Value to set for the specified parameter


Usage notes

  • The scope of these settings is global, affecting all connections to the database.

Examples

Specify a limit of 500 stored snapshots:

call tt_stats.set_config('MAX_SNAPSHOT_COUNT', 500);

SHOW_SNAPSHOTS procedures

This procedure shows the IDs and timestamps of all snapshots of TimesTen metrics currently stored in the database.

The version without the OUT parameter sends the information to the standard output.

Syntax

TT_STATS.SHOW_SNAPSHOTS;

TT_STATS.SHOW_SNAPSHOTS (
   resultset    OUT TT_STATS.REPORT_TABLE);

Parameters

Table 10-8 SHOW_SNAPSHOTS procedure parameters

Parameter Description

resultset

An associative array (index-by table) with pairs of data showing the ID and timestamp of each currently stored snapshot

Each row is of type VARCHAR2(32767).