Using the ttStats Utility
The ttStats
utility enables you to monitor database metrics
(statistics, states, and other information), automatically captures system
snapshots, and take and compare snapshots of metrics. The
ttStats
utility can perform the following
functions.
-
Monitor and display database performance metrics in real-time, calculating rates of change during each preceding interval.
Monitoring and analyzing reports of the database helps you determine the overall performance of your grid. By knowing the overall performance of your database, you can take preventive measures that ensure that your database is running with optimal conditions.
There are several differences in how ttStats
works in TimesTen
Classic and TimesTen Scaleout. For more information, see ttStats in Oracle TimesTen In-Memory Database
Reference. For details on the TT_STATS
PL/SQL package, see TT_STATS
in Oracle TimesTen In-Memory Database PL/SQL Packages
Reference.
The following topics describe how to use the ttStats
utility:
View the Configuration of the ttStats Utility
The ttStatsConfigGet
built-in procedure enables you to view the configuration settings of the ttStats
utility. This built-in shows the values of the pollSec
, retentionDays
, and retainMinutes
parameters which set the collection settings of the ttStats
utility.
This following example shows the collection settings of the ttStats
utility:
Command> call ttStatsConfigGet();
< POLLSEC, 30 >
< RETENTIONDAYS, 62 >
< RETAINMINUTES, 120 >
3 rows found.
The pollSec
, retentionDays
, and retainMinutes
parameters, which are only supported in TimesTen Scaleout, enable you to set the polling interval, purging time for aggregated data, and purging time for raw data for TimesTen Scaleout statistics, respectively. The polling interval parameter, pollsec
, determines the interval, in seconds, at which the ttStats
daemon collects metrics of the database.
The value of the polling interval does not affect the performance of the database. However, a polling interval of 10 seconds tends to use six times less space than a polling interval of 60 seconds. Most metrics get aggregated and use around 6 MB (even up to 10 years worth of metrics) of PermSize
space on each element. However, some metrics such as log holds, top SQL commands, and checkpoint history cannot be aggregated. You can use the ttStats -snapshotInfo
utility to determine how much PermSize
is being used for your metrics.
For more information of the ttStatsConfigGet
built-in procedure and
the ttStats
utility, see ttStatsConfigGet and ttStats, respectively, in Oracle TimesTen In-Memory Database
Reference.
Configure the ttStats Utility
The ttStatsConfig
built-in procedure controls the configuration
settings of the ttStats
utility and when ttStats
automatically takes system snapshots. Call the ttStatsConfig
built-in procedure to modify statistics collection parameters that affect the
ttStats
utility. For more information on the parameters of the
ttStatsConfig
built-in procedure and the
SYS.V$STATS_CONFIG
system view, see ttStatsConfig in Oracle TimesTen In-Memory Database
Reference and SYS.V$STATS_CONFIG in Oracle TimesTen In-Memory Database System Tables
and Views Reference, respectively.
The polling interval parameter, pollsec
, determines the interval, in seconds, at which the ttStats
daemon collects metrics of the database.
The following example returns the current value of the polling interval for TimesTen Scaleout statistics:
SQL> SELECT VALUE FROM SYS.V$STATS_CONFIG WHERE PARAM='POLLSEC';
< 30 >
1 row found.
The following example sets the polling interval of TimesTen Scaleout statistics to 45 seconds:
Command> call ttStatsConfig('pollsec', 45);
< POLLSEC, 45 >
1 row found.
The retention time interval, retentionDays
, determines the interval, in days, at which the ttStats
daemon drops metrics of the database. 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.
The following example returns the current value of the retention time interval for TimesTen Scaleout statistics:
SQL> SELECT VALUE FROM SYS.V$STATS_CONFIG WHERE PARAM='RETENTIONDAYS';
< 62 >
1 row found.
The following example sets the retention time interval for TimesTen Scaleout statistics to 30 days:
Command> call ttStatsConfig('retentionDays', 30);
< RETENTIONDAYS, 30 >
1 row found.
The purging time interval, retainMinutes
, determines the interval, in minutes, in which the ttStats
daemon purges raw metrics of the database. For example, if the retention time interval is 120 minutes, the ttStats
daemon purges the raw metrics every 120 minutes.
The following example returns the current value of the purging time interval for TimesTen Scaleout statistics:
SELECT VALUE FROM SYS.V$STATS_CONFIG WHERE PARAM='RETAINMINUTES';
< 120 >
1 row found.
The following example sets the purging time interval for TimesTen Scaleout statistics to 60 minutes:
Command> call ttStatsConfig('retainMinutes', 60);
< RETAINMINUTES, 60 >
1 row found.
Monitor a Database with the ttStats Utility
Use the ttStats -monitor
utility to monitor your database workload on a local instance in real-time. You can specify the -duration
or -iterations
option to set the length of time that the ttStats
utility monitors the TimesTen Scaleout. Monitoring continues until the limit of the -duration
or -iterations
options is reached or when you use Ctrl-C
. You can also specify an interval time, -interval
, which sets the time interval between sets of metrics that are displayed, in seconds. These options can be specified together. You can specify the following options:
-
-duration
: This option sets the duration of how long thettStats
utility runs, in seconds. After this duration, the utility exits.The following example monitors a database for 60 seconds:
% ttStats -monitor -duration 60 database1 Connected to TimesTen Version 22.01.0001.0018 Oracle TimesTen IMDB version 22.1.1.18.0. Waiting for 10 seconds for the next snapshot Description Current Rate/Sec Notes date.2017-Feb-22 11:33:41 1456169621 1 sample #, not rate cmdcache.id:278352904.preps 142072 1 COMMIT cmdcache.id:283596680.execs 135242 1 SELECT COUNT(*) FROM SYS.TTSTATS cmdcache.id:283613080.execs 340200 3 SELECT COUNT(*) FROM SYS.TTSTATS cmdcache.id:283619720.execs 135242 1 INSERT INTO SYS.TTSTATS_SQL_COMM connections.count 15 db.joins.nested_loop 22874 1 db.table.full_scans 136618 2 lock.locks_granted.immediate 24138575 291 log.buffer.bytes_inserted 4887634664 52988 log.buffer.insertions 41123321 447 log.file.writes 247855 2 log.forces 183285 1 log.log_bytes_per_transaction 0 loghold.bookmark.log_force_lsn 88/46899200 loghold.bookmark.log_write_lsn 88/46899464 loghold.checkpoint_hold_lsn 88/41543680 database1.ds0 loghold.checkpoint_hold_lsn 88/33990656 database1.ds1 plsql.GetHitRatio 0.714 0.000 plsql.GetHits 380.000 0.200 plsql.Gets 532.000 0.200 plsql.PinHitRatio 0.989 0.000 plsql.PinHits 34556.000 0.500 plsql.Pins 34933.000 0.500 stmt.executes.count 1103839 12 stmt.executes.inserts 280246 2 stmt.executes.selects 777408 9 stmt.prepares.count 173038 1 txn.commits.count 233082 2 txn.commits.durable 182275 1 ...
-
-iterations
: This option sets the number of iterations that thettStats
utility performs when gathering and displaying metrics. After these iterations, the utility exits.The following example sets the number of iterations to 3:
% ttStats -monitor -iterations 3 database1 Connected to TimesTen Version 22.01.0001.0018 Oracle TimesTen IMDB version 22.1.1.18.0. Waiting for 10 seconds for the next snapshot Description Current Rate/Sec Notes date.2017-Feb-22 11:54:34 1456170874 1 sample #, not rate connections.count 15 lock.locks_granted.immediate 24195281 1 log.log_bytes_per_transaction 0 loghold.bookmark.log_force_lsn 88/61253632 loghold.bookmark.log_write_lsn 88/61253896 loghold.checkpoint_hold_lsn 88/55470080 database1.ds0 loghold.checkpoint_hold_lsn 88/48414720 database1.ds1 plsql.GetHitRatio 0.730 0.000 plsql.GetHits 410.000 0.200 plsql.Gets 562.000 0.200 plsql.PinHitRatio 0.989 0.000 plsql.PinHits 34667.000 0.200 plsql.Pins 35044.000 0.200 stmt.executes.count 1106494 1 stmt.executes.selects 779348 1 ...
-
-interval
: This option sets the time interval between sets of metrics that are displayed, in seconds.The following example sets the interval time to 30 seconds:
% ttStats -interval 30 -monitor database1 Connected to TimesTen Version 22.01.0001.0018 Oracle TimesTen IMDB version 22.1.1.18.0. Waiting for 30 seconds for the next snapshot Description Current Rate/Sec Notes date.2017-Feb-19 15:18:38 1455923918 1 sample #, not rate connections.count 15 lock.locks_granted.immediate 12536701 1 log.log_bytes_per_transaction 0 loghold.bookmark.log_force_lsn 45/13309952 loghold.bookmark.log_write_lsn 45/13310216 loghold.checkpoint_hold_lsn 45/4683776 database1.ds0 loghold.checkpoint_hold_lsn 45/11804672 database1.ds1 plsql.GetHitRatio 0.700 0.000 plsql.GetHits 355.000 0.067 plsql.Gets 507.000 0.067 plsql.PinHitRatio 0.980 0.000 plsql.PinHits 18201.000 0.067 plsql.Pins 18578.000 0.067 ...
Create a Snapshot with the ttStats Utility
Use the ttStats -snapshot
utility to associate a snapshot ID with the latest system generated snapshot of your database. Snapshots are used to create reports that show you database metrics. When a system generated snapshot gets automatically purged, the associated user snapshots will also be purged.
The following example uses the ttStats -snapshot
utility to create a snapshot. The -description
command is required when you use the -snapshot
command. The -description
command lets you provide any description or notes for the snapshot, for example to distinguish it from other snapshots.
% ttStats -snapshot -description 1 database1
Connected to TimesTen Version 22.01.0001.0018 Oracle TimesTen IMDB version 22.1.1.18.0.
Snapshot ID was 88412
You can reference the snapshot that was created from the example with a snapshot ID of 1
.
Create a Report Between Two Snapshots with the ttStats Utility
Use the ttStats -report
utility to create a report between two snapshots of your TimesTen Scaleout database. ttStats
reports show the change in statistics between two snapshots of your database. The -outputFile
option enables you to specify a file path and name where the report is to be written. Use one of the following set of options to define the start and end points of the report:
-
The
-snap1
and-snap2
options to specify snapshot IDs. The report period must span at least four existing snapshot ID values. Therefore, you must have at least three snapshots between-snap1
and-snap2
. -
The
-timestamp1
and-timestamp2
options to specify timestamps.
You can use the ttStats -snapshotInfo
command to view available snapshots for your database.
The following example uses the ttStats -snapshotInfo
utility to return the IDs and timestamps of available snapshots. This command also returns information about aggregated snapshots as well as the values of the ttStatsConfig
built-in procedure.
% ttStats -snapshotInfo database1
Connected to TimesTen Version 22.01.0001.0018 Oracle TimesTen IMDB version 22.1.1.18.0.
There are 2 user snapshots:
Snapshot ID User comment When snapshot occurred
============ ================ ======================
88412 1 2018-02-09 13:28:50
88412 2 2018-02-10 11:13:55
88412 3 2018-02-10 18:39:50
88412 4 2018-02-11 08:10:12
88412 5 2018-02-12 17:23:46
There are 151 AGGREGATED snapshots:
Oldest snapshot 2880, 2018-01-04 15:37:29
Newest snapshot 88412, 2018-02-03 10:00:26
There are 240 NON AGGREGATED snapshots:
Oldest snapshot 88173, 2018-02-03 08:00:42
Newest snapshot 88412, 2018-02-03 10:00:26
There are about 16.3 MB of metrics stored in ttStats SYS tables
The PollSec was 30
The RetentionDays was 62
The RetainMinutes was 120
The following example creates a report, snapreport.txt
, between the snapshots with ID 1
and ID 5
:
% ttStats -report -snap1 1 -snap2 5 -outputFile snapreport.txt database1
Connected to TimesTen Version 22.01.0001.0018 Oracle TimesTen IMDB version 22.1.1.18.0.
Report snapreport.txt was created.
The following example creates a report, timereport.txt
, between two timestamps:
% ttStats -report -timestamp1 2018-02-22 12:50:31 -timestamp2 2018-02-23 09:15:23 -outputFile snapreport.txt database1
Connected to TimesTen Version 22.01.0001.0018 Oracle TimesTen IMDB version 22.1.1.18.0.
Report timereport.txt was created.
For more information about the tables of metrics that a ttStats
report generates and the ttStats
utility, see ttStats in TimesTen Scaleout in Oracle TimesTen In-Memory Database
Reference.