10 TT_STATS

Using TT_STATS

This section covers the following topics for the TT_STATS package:

• Overview

• Security model

• Operational notes

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.

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;

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.

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.

set serveroutput on;

Summary of TT_STATS subprograms

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

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.

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

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.

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

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".

Syntax

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

Parameters

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".

Syntax

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

Parameters

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

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

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