10 TT_STATS
The TT_STATS
package enables you to collect snapshots of TimesTen Classic database metrics (statistics, states, and other information) and generate reports comparing two specified snapshots.
This chapter contains the following topics:
-
-
Overview
-
Security model
-
Operational notes
-
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. 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:
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.
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. 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.
TT_STATS Subprograms
Table 10-1 summarizes the TT_STATS
subprograms, followed by a full description of each subprogram.
Table 10-1 TT_STATS Package Subprograms
Subprogram | Description |
---|---|
Takes a snapshot of TimesTen metrics. The function also returns the snapshot ID. |
|
Deletes snapshots according to a specified range of snapshot IDs or timestamps. |
|
Produces a report in HTML format based on the data from two specified snapshots. |
|
Produces a report in plain text format based on the data from two specified snapshots. |
|
Retrieves the value of a specified |
|
Sets a specified value for a specified |
|
Shows the snapshot IDs and timestamps of all snapshots currently stored in the database. |
Note:
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.
Note:
-
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.
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 |
---|---|
|
The desired level of metrics to capture The following choices are available:
Use the same level for any two snapshots to be used in a report. Note: For metrics outside of |
|
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 ofSYSTEMSTATS
, 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 ID for the start of the range of snapshots to delete |
|
Snapshot ID for the end of the range of snapshots to delete |
|
Timestamp for the start of the range of snapshots to delete |
|
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 thansnapshot_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 thants_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, 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.
Note:
-
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 |
---|---|
|
ID of the first snapshot to analyze |
|
ID of the second snapshot to analyze |
|
An associative array (index-by table) containing the HTML-formatted report Each row is of type 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, latches, locks, XLA, and TimesTen connection attributes.
Also see GENERATE_REPORT_HTML Procedure.
Note:
-
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 |
---|---|
|
ID of the first snapshot to analyze |
|
ID of the second snapshot to analyze |
|
An associative array (index-by table) containing the plain-text-formatted report Each row is of type 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 of a In this release, the following
If no parameter name is specified ( Also see SET_CONFIG Procedure. |
|
An associative array (index-by table) containing the value of the specified Each row is of type |
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 of the In this release, the following
An error is issued if either limit is exceeded, and the snapshot capture fails. Also see GET_CONFIG Procedures. |
|
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 |
---|---|
|
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 |