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 the ttStats 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 the ttStats 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.