Oracle® TimesTen In-Memory Database PL/SQL Packages Reference 11g Release 2 (11.2.2) E21645-06 |
|
|
View PDF |
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:
Overview
Security model
Operational notes
Note:
There is also attStats
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.This section covers the following topics for the TT_STATS
package:
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.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:
AlthoughADMIN
users cannot execute the package by default, they can grant themselves privilege to execute it.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 thettStatsConfig
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 theSHOW_SNAPSHOTS
and GET_CONFIG
procedures), run the following command from ttIsql
:
set serveroutput on;
This section summarizes and then documents the procedures and functions of the TT_STATS
package.
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:
In this release, the only supportedTT_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.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.
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;
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. |
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.
The function returns a BINARY_INTEGER
value for the ID of the snapshot.
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;
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.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));
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 |
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
.
This example specifies snapshot IDs, dropping the snapshots with IDs of 1, 2, and 3.
call tt_stats.drop_snapshots_range(1,3);
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).
TT_STATS.GENERATE_REPORT_HTML ( snapshot_id1 IN BINARY_INTEGER, snapshot_id2 IN BINARY_INTEGER, report OUT TT_STATS.REPORT_TABLE);
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. |
You can enter the snapshot IDs in either order. The procedure determines which is the earlier.
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).
TT_STATS.GENERATE_REPORT_TEXT ( snapshot_id1 IN BINARY_INTEGER, snapshot_id2 IN BINARY_INTEGER, report OUT TT_STATS.REPORT_TABLE);
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. |
You can enter the snapshot IDs in either order. The procedure determines which is the earlier.
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.
TT_STATS.GET_CONFIG ( name IN VARCHAR2 DEFAULTED); TT_STATS.GET_CONFIG ( name IN VARCHAR2 DEFAULTED, params OUT TT_STATS.REPORT_TABLE);
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 |
This procedure sets a specified value for a specified TT_STATS
configuration parameter.
TT_STATS.SET_CONFIG ( name IN VARCHAR2, value IN BINARY_INTEGER);
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 |
The scope of these settings is global, affecting all connections to the database.
Specify a limit of 500 stored snapshots:
call tt_stats.set_config('MAX_SNAPSHOT_COUNT', 500);
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.
TT_STATS.SHOW_SNAPSHOTS;
TT_STATS.SHOW_SNAPSHOTS (
resultset OUT TT_STATS.REPORT_TABLE);