ttStats in TimesTen Scaleout

In TimesTen Scaleout, 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.

The ttStats utility gathers metrics from TimesTen system tables, views, and built-in procedures. In reports, this includes information such as 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, and TimesTen connection attributes. Monitoring displays a smaller set of key data, as shown later in this section.

For TimesTen Client DSNs, use the ttStatsCS version of the utility.

Note:

Although cross-release compatibility over client/server protocol is supported in TimesTen, the tool ttStatsCS is not backward and forward release compatible; hence it can be used only for the same version client/server connections.

There are three modes of operation:

Monitor mode (default mode): Tracks database performance in real-time by monitoring a pre-determined set of metrics, displays those metrics (primarily those whose values have changed since the last display), and calculates rates of change in the values where appropriate. Information is output to the standard output for display to the user and is not stored to disk.

If the duration or number of iterations is not specified, the monitoring runs until interrupted with Ctrl-C.

Note:

The set of metrics displayed in monitor mode is subject to change, depending on changes to the system tables and built-in procedures from which metrics are gathered.
Snapshot mode: In TimesTen Scaleout, the ttStats daemon automatically takes snapshots of the TimesTen Scaleout database based on the parameters of the ttStatsConfig built-in procedure.

If you use the -snapshot option of the ttStats utility, the -description option is mandatory. When you run ttStats -snapshot -description description, you can associate a description to the latest system generated snapshot. Provide any description or notes for the snapshot, for example to distinguish it from other snapshots.
Report mode: Generates a report from two specified snapshots or two specified timestamps of metrics. Reports are only available in plain text format. You must specify an output file with the -outputFile option. For those familiar with Oracle Database performance analysis tools, the ttStats reports are similar in nature to Oracle Automatic Workload Repository (AWR) reports.

In monitor mode, the overhead of reading from the database is avoided. In snapshot mode and report mode, the ttStats utility is a convenient front end to the TT_STATS PL/SQL package provided by TimesTen. Refer to TT_STATS in Oracle TimesTen In-Memory Database PL/SQL Packages Reference for details on that package.

Note:

The ttStats utility has the following dependencies and limitations:

The utility cannot be used if you are connecting to TimesTen through a driver manager.

For information about built-in procedures mentioned, and the data they gather, see Built-In Procedures.

Required Privilege

Monitor mode: No special privilege is required to run monitor mode, but ADMIN privilege is required for the monitoring information to include data from the ttSQLCmdCacheInfo built-in procedure.
Snapshot and report mode: By default, only the instance administrator has privilege to create snapshots, get snapshot information and run in report mode, due to security restrictions 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;
```

Syntax

ttStats [-h | -help]
ttStats [-V | -version]
ttStats [-monitor] [-interval seconds]
        [-duration seconds] [-iterations count]
           {DSN | -connStr connection_string}
ttStats  -snapshot -description snap_desc
           {DSN | -connStr connection_string}
ttStats  -report [-snap1 snapid1 -snap2 snapid2 | 
         -timestamp1 'timestamp1' -timestamp2 'timestamp2']
         -outputFile filename
ttStats  -snapshotInfo {DSN | -connStr connection_string}

Note:

Specify only one of -monitor, -snapshot, or -report.

Options

These are the supported options for the ttStats utility in TimesTen Scaleout:

Option	Description
`-h` `-help`	Prints the list of options and exits. Note: This is also the result if nothing is entered on the `ttStats` command line, or if options are entered without a DSN or connection string.
`-V` `-version`	Prints the TimesTen release number and exits.
`-monitor`	Run in real-time monitor mode. Monitors a pre-determined set of metrics and repeatedly displays the metrics and rates of change. Unlike in snapshot mode, nothing is stored to the database. Note: This is the default mode if neither `-monitor`, `-snapshot`, nor `-report` is specified.
`-interval` `seconds`	For monitor mode, this is the time interval between sets of metrics that are displayed, in seconds. The default is 10 seconds. Shorter intervals may negatively impact system performance.
`-duration` `seconds`	For monitor mode, this is the duration of how long `ttStats` runs, in seconds. After this duration, the utility exits. Also see information for the `-iterations` option.
`-iterations` `count`	For monitor mode, this is the number of iterations `ttStats` performs in gathering and displaying metrics. After these iterations, the utility exits. Note: If you specify both `-duration` and `-iterations`, monitoring stops when the first of the two limits is reached. If you specify neither, monitoring continues until interrupted by `Ctrl-C`
`-snapshot -description` `snap_desc`	If you use the `-snapshot` option, the `-description` option is mandatory. When you run `ttStats -snapshot -description` `description`, you can associate a description to the latest system generated snapshot. Provide any description or notes for the snapshot, for example to distinguish it from other snapshots.
`-report`	Generate a report from two specified snapshots or two specified timestamps. Use the `-snapshotInfo` option to see available snapshots for your database.
`-snap1` `snapid1`	For report mode, this is the snapshot ID of the first snapshot.
`-snap2` `snapid2`	For report mode, this is the snapshot ID of the second snapshot. The report period must span at least four existing snapshot ID values. Therefore, you must have at least three snapshots between `-snap1` and `-snap2`.
`-timestamp1` `'timestamp1'`	For report mode, this specifies the timestamp of the first snapshot. The timestamp must use the `YYYY-MM-DD HH:MM:SS` format and be wrapped in straight single quotes.
`-timestamp2` `'timestamp2'`	For report mode, this specifies the timestamp of the second snapshot. The timestamp must use the `YYYY-MM-DD HH:MM:SS` format and be wrapped in straight single quotes.
`-outputFile` `filename`	For report mode, optionally specify a file path and name where the report is to be written. If no file is specified, TimesTen writes the to the standard output.
`-snapshotInfo`	Prints the snapshot ID, date, time, and the description of all snapshots.
`-connStr` `connection_string` or `DSN`	To specify and connect to the database from which to gather metrics, do one of the following: Specify an ODBC connection string, preceded by `-connStr`. Specify a DSN (data source name), without `-connStr`, at the end of the command line. See Specifying Data Source Names to Identify TimesTen Databases in Oracle TimesTen In-Memory Database Operations Guide for information about TimesTen DSNs.

Examples

This section provides examples of ttStats monitoring and report output.

Note:

Examples are for illustrative purposes only. Details are subject to change.

The rest of this section shows excerpts from tables of metrics for a ttStats report for a TimesTen Scaleout database and a ttStats report for an element of a TimesTen Scaleout database. This output was produced using the default plain text output format.

Note:

To include latch metrics, you must enable them for the database, using the ttXactAdmin utility as follows:

% ttXactAdmin -latchstats on DSN

Monitor Example

This section shows sample output from monitor mode.

% ttStats database1

Connected to TimesTen Version 22.01.0001.0021 Oracle TimesTen IMDB version 22.1.1.21.0.

Waiting for 10 seconds for the next snapshot

Description                       Current  Rate/Sec  Notes
date.2021-Mar-16 15:29:23      1458167363         1  sample #, not rate
connections.count                      20            
lock.locks_granted.immediate       124817         1  
log.log_bytes_per_transaction           0            
loghold.bookmark.log_force_lsn 0/21102592            
loghold.bookmark.log_write_lsn 0/21102856            
loghold.checkpoint_hold_lsn    0/21100544            database1.ds0
loghold.checkpoint_hold_lsn    0/21078016            database1.ds1
plsql.GetHitRatio                   0.640     0.000  
plsql.GetHits                     258.000     0.200  
plsql.Gets                        403.000     0.200  
plsql.PinHitRatio                   0.557     0.000  
plsql.PinHits                     424.000     0.200  
plsql.Pins                        761.000     0.200  
stmt.executes.count                 24407         1  
stmt.executes.selects                 620         1

Note:

The number following the date and time is a numeric representation of the time of the snapshot and can be ignored.

The following command line example specifies that monitoring should stop after two iterations and uses a connection string to set a connection attribute value.

% ttStats -iterations 2 -connStr "DSN=database1"

Report Examples

The following example creates a report from the snapshots generated in the previous section.

Note:

The report period must span at least four existing snapshot ID values. Therefore, you must have at least three snapshots between -snap1 and -snap2.

% ttStats -report -outputFile testreport.txt -snap1 1 -snap2 5 database1

Connected to TimesTen Version 22.01.0001.0021 Oracle TimesTen IMDB version 22.1.1.21.0.
Report testreport.txt was created.

TimesTen Scaleout ttStats Report

The following sections show excerpts from tables of metrics for a ttStats report for a TimesTen Scaleout database.

TimesTen Scaleout Snapshot Summary

Displays a grid snapshot summary. The TimesTen Scaleout snapshot summary shows information regarding the snapshots that you specified for the ttStats report.

TimesTen Scaleout Elements

Displays information about each database element. This section of the report shows the host name and current number of connections for each database element. See "TimesTen Scaleout Element ttStats Report" for more information on the contents of an elements ttStats report.

TimesTen Scaleout Summary

Displays a summary of critical TimesTen Scaleout statistics. The summary includes statistics of your TimesTen Scaleout about transaction rates, SQL statements, database connections, checkpoint rates, transaction log rates, and other critical statistics.

TimesTen Scaleout Load Profile

Displays various database metric rates. This gives you an idea of the workload, showing the rate of checkpoints, log buffer waits (delays when the log buffer fills and flushes to disk), inserts, updates, deletes, parses (such as for prepares), commits, and s. Consider whether there may be too many parses or too many durable commits (which are more expensive than non-durable commits).

Operating System Metrics Summary

Displays various operating system metrics for the TimesTen Scaleout database. These metrics show the used disk space, CPU, I/O rate, and RAM.

TimesTen Scaleout Efficiency Metrics

Displays various metrics that determine the efficiency of the TimesTen Scaleout. This section of the report includes the following sections:

Target 100% - bigger is better: This shows you recommendations to improve the efficiency of the TimesTen Scaleout. It includes the following metrics:
- Prepare exec efficiency: This shows if your SQL statements are prepared and then executed many times. If you prepare a SQL statement once for every execution, this metric goes down.
  
  Try to minimize the number of times your SQL statements are prepared because preparing SQL statements is CPU intensive. In your applications, consider using bind variables. You can then prepare your SQL statements once and then run your SQL statements multiple times.
Target 0% - smaller is better: This shows you recommendations to improve the efficiency of the TimesTen Scaleout. It includes the following metrics:
- Log buffer waits: This shows the number of log buffer waits which helps you determine how operations that use the log files are doing. It is optimal to maintain the log buffer wait low because it indicates that transactions do not need to wait before writing to the log buffer.
  
  If this number is large, try to checkpoint more frequently, increase the Log Buffer Size and/or increase the log buffer parallelism
- Table data skew deviation: This shows the percentage of table data skew deviations between the elements of the TimesTen Scaleout. Ideally the rows in tables are distributed evenly across all elements. If elements have too many rows compared to other elements, the elements with more rows use more permSize which can cause disk size and data distribution problems.
- Direct mode connection distribution deviation: This shows the percentage of the direct mode connection deviation between the elements of the TimesTen Scaleout. Evenly spread the direct mode connections between the elements to achieve optimal throughput and latency.
- Client server connection distribution deviation: This shows the percentage of the client/server connection deviation between the elements of the TimesTen Scaleout. Evenly spread the client/server connections between the elements to achieve optimal throughput and latency.
- SQL statement distribution deviation: This shows the percentage of the SQL statement distribution deviation between the elements of the TimesTen Scaleout. It is not optimal to run all SQL statements on a single attempt. Evenly run the SQL statements on the elements to achieve optimal throughput and latency.
- Grid channel invalidation: This shows the number of channel invalidations between the elements of the TimesTen Scaleout. Applications should cleanly disconnect and release resources to minimize channel invalidations. The cleanup process that TimesTen Scaleout performs after a channel invalidation takes time, which affects the latency and scalability of operations that want to use that channel.

TimesTen Scaleout Transactions

Displays various metrics that show information about transactions on the TimesTen Scaleout. This section of the report includes the following sections:

TimesTen Scaleout transactions per second

This table shows various transaction metrics for each element such as the rate of transactions that: only involve the local element, multiple elements, and require 2PC.
TimesTen Scaleout 2PC transactions

This table shows various 2PC transaction metrics for each element such as the percentage of transactions that: started on this element, were involved in a 2PC transaction but did not initiate it, and used durable 2PC prepares.
TimesTen Scaleout Durable Commits

This table shows various transaction metrics for each element such as the percentage of transactions that are committed durably on this element.

SQL Statements: SQL Statement Protocol

Displays an excerpt of SQL statement protocol statistics from the SQL Statements section of a report. These statistics show you the percentage of SQL statements: run for that element, run on their local element, that required implementation on a remote element, and that required a broadcast to all elements to run.

SQL Statements: SQL Statements Type

Displays an excerpt of SQL statement type statistics from the SQL Statements section of a report. These statistics show you various statistics of SQL statements run for that element.

DB Connections

Displays various connection statistics for each element of the TimesTen Scaleout. These statistics show you the type of connections, connections and disconnections per minute, and client server failover for every element of the TimesTen Scaleout.

TimesTen Scaleout Data Distribution: Table Data Skew - Worst Three Tables

Displays the three tables with the highest data skew percentage of the TimesTen Scaleout. For more information on the row distribution table, see TimesTen Scaleout Data Distribution: Row Distribution for Table. These statistics show you the percentage of deviation, the table distribution type, and the distribution keys for the three tables with the highest data skew percentage.

TimesTen Scaleout Data Distribution: TimesTen Scaleout PermSize Usage

Displays statistics related to the PermSize attribute for each element of the TimesTen Scaleout. These statistics show you the proportional percentage of used PermSize for each element in the TimesTen Scaleout, percentage of used PermSize, the percentage of used high water of PermSize, and the size of the PermSize for each element of the TimesTen Scaleout. For more information about the PermSize attribute, see "PermSize."

TimesTen Scaleout Data Distribution: TimesTen Scaleout TempSize Usage

Displays statistics related to the TempSize attribute for each element of the TimesTen Scaleout. These statistics show you the percentage of used TempSize, the percentage of used high water of TempSize, and the size of the TempSize for each element of the TimesTen Scaleout. For more information about the TempSize attribute, see TempSize.

TimesTen Scaleout Data Distribution: Row Distribution for Table

There are three of these tables in your ttStats report, which show row distribution statistics for the tables with the highest data skew percentage of the TimesTen Scaleout. These statistics show you the number of rows that are stored on each element for that specific table. For more information about the three tables with the highest data skew percentage of the TimesTen Scaleout, see TimesTen Scaleout Data Distribution: Table Data Skew - Worst Three Tables.

TimesTen Scaleout Channel: TimesTen Scaleout Messages per Second

Displays statistics related to message rates over TimesTen Scaleout channels. These messages can be requests for data or data result sets. These statistics show you the number of sent, received, and invalidated messages for each element of the TimesTen Scaleout.

TimesTen Scaleout Channel: TimesTen Scaleout Channel Data Rate

Displays statistics related to channel data rates for each element of the TimesTen Scaleout. These data rates are based on the size of messages that TimesTen Scaleout sends over the channels. The messages can be requests for data or data result sets. Larger messages tend to have better throughput than smaller messages. These statistics show you the data rates of sent and received messages for each element of the TimesTen Scaleout.

Checkpoint: Checkpoint Data Rate

Displays statistics related to checkpoint data rates for each element of the TimesTen Scaleout database.

Transaction Log: Transaction Log Data Rate

Displays statistics related to transaction log data rates for each element of the TimesTen Scaleout database.

Top SQL: Top SQL Attributes

Displays statistics related to the attributes of the most run SQL statements on the TimesTen Scaleout database.

Top SQL: Top SQL Text

Displays information related to the SQL text of the most run SQL statements on the TimesTen Scaleout database.

OS Disk Space

Displays the statistics related to the disk space of every element that is part of the grid.

CPU Utilization

Displays the statistics related to the CPU of every element that is part of the grid.

Operating System IO Rates: Disk IO Rates

Displays the statistics related to the disk I/O rates of every element that is part of the grid. This table enables you to determine if a host has a lot of disk I/O operations that are unrelated to TimesTen Scaleout.

Operating System IO Rates: Network IO Rates

Displays the statistics related to the network I/O rates of every element that is part of the grid. This table enables you to determine if a host has a lot of network I/O operations that are unrelated to TimesTen Scaleout.

OS Memory

Displays the statistics related to the memory usage of every element that is part of the grid. This table enables you to determine if a host is using swap space or is having performance issues due to a lack of available RAM.

TimesTen Scaleout Element ttStats Report

The following sections show excerpts from tables of metrics for a ttStats report for an element of a TimesTen Scaleout database.

Snapshot Summary

Displays the statistics related to the snapshots that ttStats uses to create the ttStats report for the element.

Element Summary

Displays a summary of critical statistics for the element. The summary includes statistics of your element related to transaction rates, SQL statements, database connections, checkpoint rates, transaction log rates, and other critical statistics.

Load Profile

Displays various database metric rates. This gives you an idea of the workload, showing the rate of checkpoints, log buffer waits (delays when the log buffer fills and flushes to disk), inserts, updates, deletes, parses (such as for prepares), commits, and rollbacks. Consider whether there may be too many parses or too many durable commits (which are more expensive than non-durable commits).

Operating System Metrics Summary

Displays various operating system metrics for the element. These metrics show the used disk space, CPU, I/O rate, and RAM.

Efficiency Metrics

Displays various metrics that determine the efficiency of the element. This section of the report includes the following sections:

Target 100% - bigger is better: This shows you recommendations to improve the efficiency of the element. It includes the following metrics:
- Prepare exec efficiency: This shows if your SQL statements are prepared and then executed many times. If you prepare a SQL statement once for every execution, this metric goes down.
  
  Try to minimize the number of times your SQL statements are prepared because preparing SQL statements is CPU intensive. In your applications, consider using bind variables. You can then prepare your SQL statements once and then execute your SQL statements multiple times.
Target 0% - smaller is better: This shows you recommendations to improve the efficiency of the element. It includes the following metrics:
- Log buffer waits: This shows the percentage of log buffer waits which helps you determine how operations that use the log files are doing. It is optimal to maintain the log buffer wait low because it indicates that transactions do not need to wait before writing to the log buffer.
  
  If this percentage is high, try to checkpoint more frequently, increase the Log Buffer Size and/or increase the log buffer parallelism
- Table data skew deviation: This shows the percentage of table data skew deviations between the elements of the TimesTen Scaleout. Ideally the rows in tables are distributed evenly across all elements. If elements have too many rows compared to other elements, the elements with more rows use more permSize which can cause disk size and data distribution problems.
- Direct mode connection distribution deviation: This shows the percentage of the direct mode connection deviation between the elements of the TimesTen Scaleout. Evenly spread the direct mode connections between the elements to achieve optimal throughput and latency.
- Client server connection distribution deviation: This shows the percentage of the client/server connection deviation between the elements of the TimesTen Scaleout. Evenly spread the client/server connections between the elements to achieve optimal throughput and latency.
- SQL statement distribution deviation: This shows the percentage of the SQL statement distribution deviation between the elements of the TimesTen Scaleout. It is not optimal to run all SQL statements on a single attempt. Evenly run the SQL statements on the elements to achieve optimal throughput and latency.
- Grid channel invalidation: This shows the percentage of channel invalidations between the elements of the TimesTen Scaleout. Applications should cleanly disconnect and release resources to minimize channel invalidations. The cleanup process that the TimesTen Scaleout performs after a channel invalidation takes time, which affects the latency and scalability of operations that want to use that channel.

Transactions

Displays various metrics that show information about transactions on the element. This section of the report includes the following sections:

Transaction Type

This table shows various transaction metrics for your element such as the percentage of transactions that: only involve the local element, use remote transactions, and require 2PC.
2PC transactions

This table shows various 2PC transaction metrics for each element such as the percentage of transactions that: started on this element, were involved in a 2PC transaction but did not initiate it, and used durable 2PC prepares.

SQL statements: SQL Statement Protocol

Displays an excerpt of SQL statement protocol statistics for the element from the SQL Statements section of a report. These statistics show you the percentage of SQL statements: run, run locally, that required implementation on a remote element, and that required a broadcast to all elements to run.

SQL Statements: SQL Statement Type

Displays an excerpt of SQL statement type statistics for the element from the SQL Statements section of a report. These statistics show you various statistics for SQL statements run on your element.

Database Connections

Displays various connection statistics for the element. These statistics show you the type of connections, connections and disconnections per minute, and client server failover for your element.

Table Data Skew

Displays the three tables with the highest data skew percentage of the element. For more information on the row distribution table, see TimesTen Scaleout Data Distribution: Row Distribution for Table. These statistics show you the percentage of deviation, the table distribution type, and the distribution keys for the three tables with the highest data skew percentage.

Grid Channel Usage

Displays message statistics over grid channels. These messages can be requests for data or data result sets. These statistics show you the number of sent, received, and invalidated messages for the element.

Log Holds

Displays log hold information from a report. It shows bookmark positions for checkpoint log holds for each checkpoint file. This report may also show log hold information for backup, XLA, and long-running transactions. Where the begin and end values are the same, there have been no movements.

Ideally there will be evidence of a smooth progression through the log file. (The ttStats monitor information may be more useful in tracking this.)

Checkpoint Usage

Displays checkpoint usage metrics from a report.

Transaction Log Usage

Displays transaction log usage statistics for the element. This provides information about the rate of I/O operations for the transaction log, log buffer waits, log file reads, and log reads for commits.

Top SQL: Top SQL Attributes

Displays statistics related to the attributes of the most run SQL statements on the element.

Top SQL: Top SQL Text

Displays information related to the SQL text of the most run SQL statements on the element.