The following sections discusses how to monitor a grid and database in TimesTen Scaleout.
There are several ways to monitor a grid and database:
The ttStats utility - This utility enables you to monitor database metrics (statistics, states, and other information) or take and compare snapshots of metrics. See "Using the ttStats utility" for more information.
SQL Developer - SQL Developer enables you to create, manage, and explore a grid and its components. Additionally, you can also browse, create, edit and drop particular database objects; run SQL statements and scripts; manipulate and export data; view and create reports; and view database metrics. For more information, see "Using SQL Developer to work with TimesTen Scaleout".
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 the Oracle TimesTen In-Memory Database Reference. For details on the TT_STATS PL/SQL package, see "TT_STATS" in the Oracle TimesTen In-Memory Database PL/SQL Packages Reference.
The following sections describe how to use 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 the Oracle TimesTen In-Memory Database Reference.
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 the Oracle TimesTen In-Memory Database Reference and "SYS.V$STATS_CONFIG" in the 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.
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 18.01.0002.0001 Oracle TimesTen IMDB version 18.1.2.1.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 18.01.0002.0001 Oracle TimesTen IMDB version 18.1.2.1.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 18.01.0002.0001 Oracle TimesTen IMDB version 18.1.2.1.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 ...
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 18.01.0002.0001 Oracle TimesTen IMDB version 18.1.2.1.0. Snapshot ID was 88412
You can reference the snapshot that was created from the example with a snapshot ID of 1.
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 18.01.0002.0001 Oracle TimesTen IMDB version 18.1.2.1.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 18.01.0002.0001 Oracle TimesTen IMDB version 18.1.2.1.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 18.01.0002.0001 Oracle TimesTen IMDB version 18.1.2.1.0.
Report timereport.txt was created.
For more information about the tables of metrics that a ttStats report generates and the ttStats utility, see "Report examples" and "ttStats", respectively, in the Oracle TimesTen In-Memory Database Reference.
Oracle SQL Developer is a graphical user interface (GUI) tool that gives database developers a convenient way to create, manage, and explore a grid and its components. You can also browse, create, edit and drop particular database objects; run SQL statements and scripts; manipulate and export data; view and create reports; and view database metrics.
For more information, see Oracle SQL Developer Oracle TimesTen In-Memory Database Support User's Guide.
Management instances store metadata used to manage the grid. It is recommended that you use an active and a standby instance to have high availability for this metadata. If you use a single management instance and that management instance is down, the grid continues to operate but you cannot perform certain management operations for your grid.
A management instance can get full because it stores information about your grid, previous grid model versions, and logs of your grid. It is important for the management instances to have enough free space to function properly. If your management instance begins to get full, any command that you run with the ttGridAdmin utility outputs a warning.
You can perform these tasks to maintain the management instances:
When you create a grid, the grid sets a used-space warning threshold for the management instance. If the size of your management instance reaches this threshold, commands that you run with the ttGridAdmin utility output warnings that the management instance is getting full.
This example shows the output of a ttGridAdmin instanceCreate command for a grid where the management instance is almost full.
% ttGridAdmin instanceCreate host5 -location /grid Instance instance1 on Host host5 created in Model Warning: the TTGRIDADMIN database is 91% full; Temp space: 57%
Note:
When you use attGridAdmin command and you see a warning that your management instance is getting full, TimesTen Scaleout deletes old grid model versions and logs based on the retention days and retention versions parameters of your grid.Use the gridDisplay command of the ttGridAdmin utility on your management instance to see the current used-space warning threshold for the management instance, and retention days and quantity of previous versions of the grid model that TimesTen Scaleout stores.
This example shows the output of the ttGridAdmin gridDisplay command.
% ttGridAdmin gridDisplay Grid name: grid1 Grid GUID: 864C0CB2-AF40-4047-A711-7A9F9F0E7D6C Created: 2018-12-12 12:20:32.000000 Major Release: 18.1.4 Created Release: 18.1.4.1.0 K: 2 Admin Userid: instanceadmin Admin UID: 4133 Admin Group: admins Admin GID: 900 Retain Days: 30 Retain Versions: 10 Warn Threshold: 90 Perm In Use Pct: 91 Temp In Use Pct: 57
For more information about the ttGridAdmin gridDisplay command and the default values for the retention of previous grid models and warning threshold of the management instance, see "Display information about the grid (gridDisplay)" and "Modify grid settings (gridModify)", respectively, in the Oracle TimesTen In-Memory Database Reference.
See "Resize the management instance" for more information on resizing the grid administration database.
In some cases, you may want to increase or decrease the retention values of previous grid models or the current used-space warning threshold for the management instance.
This example sets the current used-space warning threshold for the management instance to 80%.
% ttGridAdmin gridModify -warnThresh 80 Grid Definition modified.
This example sets the retention days value to 60 and the retention versions value to 15. These values ensure that TimesTen Scaleout only deletes previous grid models that are older than 60 days and are at least 16 grid model versions old.
Note:
If you specify either the-retainDays or the -retainVersions parameter as 0, then only the other parameter is used. If you set both parameters values to 0, TimesTen Scaleout never automatically deletes previous grid model versions.% ttGridAdmin gridModify -retainDays 60 -retainVersions 15 Grid Definition modified.
For more information about the ttGridAdmin gridModify command, see "Modify grid settings (gridModify)" in the Oracle TimesTen In-Memory Database Reference.
In some cases, you may want to resize the management instance because it is getting full.
Depending on if your grid has a single management instance or an active and a standby management instances, follow one of these procedures:
To resize the management instance of a grid with one management instance, ensure that you are connected to the management instance.
Export the database definition.
% ttGridAdmin dbdefExport TTGRIDADMIN /tmp/ttgridadmin.dbdef
Example 9-1 shows the contents of the exported file.
Example 9-1 Exported database definition file
# DbDef GUID CF85D379-E776-41C6-A271-ACB6A2033BBB Exported 2018-03-24 14:31:52 [TTGRIDADMIN] AutoCreate=0 Connections=100 DBUUID=C12C4FAE-5732-4307-A08F-5F7FBF9BF1C0 DataStore=!!TIMESTEN_HOME!!/grid/admin/database/!!TTGRIDADMIN!! DatabaseCharacterSet=AL32UTF8 DurableCommits=1 LockWait=120 Overwrite=0 PLSQL=1 PLSQL_TIMEOUT=0 PermSize=200 TempSize=100
With a text editor, modify the value of the PermSize connection attribute to a larger value.
Example 9-2 shows the contents of the modified database definition file. In this example, the new value of the PermSize connection attribute is 400.
Example 9-2 Modified database definition file
# DbDef GUID CF85D379-E776-41C6-A271-ACB6A2033BBB Exported 2018-03-24 14:31:52 [TTGRIDADMIN] AutoCreate=0 Connections=100 DBUUID=C12C4FAE-5732-4307-A08F-5F7FBF9BF1C0 DataStore=!!TIMESTEN_HOME!!/grid/admin/database/!!TTGRIDADMIN!! DatabaseCharacterSet=AL32UTF8 DurableCommits=1 LockWait=120 Overwrite=0 PLSQL=1 PLSQL_TIMEOUT=0 PermSize=400 TempSize=100
Import the contents of the modified database definition file into the TTGRIDADMIN database definition.
% ttGridAdmin dbdefModify /tmp/ttgridadmin.dbdef Database Definition TTGRIDADMIN modified.
Apply the changes of the TTGRIDADMIN database definition file to the current version of the model.
% ttGridAdmin modelApply ... Pushing new configuration files to each Instance......................OK ... ttGridAdmin modelApply complete
Stop the management instance.
Note:
Stopping the management instance does not impact existing databases. However, you are unable to perform management operations until you start the management instance.% ttGridAdmin mgmtActiveStop Active management instance stopped
Start the management instance.
% ttGridAdmin mgmtActiveStart This management instance is now the active
You have successfully resized your management instance.
To resize the management instances of a grid with active and standby management instances, ensure that you are connected to the active management instance.
Export the database definition of the grid administration database.
% ttGridAdmin dbdefExport TTGRIDADMIN /tmp/ttgridadmin.dbdef
Example 9-3 shows the contents of the exported file.
Example 9-3 Exported database definition file
# DbDef GUID CF85D379-E776-41C6-A271-ACB6A2033BBB Exported 2018-03-24 14:31:52 [TTGRIDADMIN] AutoCreate=0 Connections=100 DBUUID=C12C4FAE-5732-4307-A08F-5F7FBF9BF1C0 DataStore=!!TIMESTEN_HOME!!/grid/admin/database/!!TTGRIDADMIN!! DatabaseCharacterSet=AL32UTF8 DurableCommits=1 LockWait=120 Overwrite=0 PLSQL=1 PLSQL_TIMEOUT=0 PermSize=200 TempSize=100
With a text editor, modify the value of the PermSize connection attribute to a larger value.
Example 9-4 shows the contents of the modified database definition file. In this example, the new value of the PermSize connection attribute is 400.
Example 9-4 Modified database definition file
# DbDef GUID CF85D379-E776-41C6-A271-ACB6A2033BBB Exported 2018-03-24 14:31:52 [TTGRIDADMIN] AutoCreate=0 Connections=100 DBUUID=C12C4FAE-5732-4307-A08F-5F7FBF9BF1C0 DataStore=!!TIMESTEN_HOME!!/grid/admin/database/!!TTGRIDADMIN!! DatabaseCharacterSet=AL32UTF8 DurableCommits=1 LockWait=120 Overwrite=0 PLSQL=1 PLSQL_TIMEOUT=0 PermSize=400 TempSize=100
Import the contents of the modified database definition file into the TTGRIDADMIN database definition.
% ttGridAdmin dbdefModify /tmp/ttgridadmin.dbdef Database Definition TTGRIDADMIN modified.
Apply the changes of the TTGRIDADMIN database definition file to the current version of the model.
% ttGridAdmin modelApply ... Pushing new configuration files to each Instance......................OK ... ttGridAdmin modelApply complete
From the standby management instance, stop the management instance.
Note:
This procedure does not impact existing databases or affect operations of the grid.% ttGridAdmin mgmtStandbyStop Standby management instance host2.instance1 stopped
From the standby management instance, start the management instance.
% ttGridAdmin mgmtStandbyStart Standby management instance started
From the standby management instance, promote the standby management instance to be the new active management instance and shut down the original active management instance.
% ttGridAdmin mgmtActiveSwitch This is now the active management instance
From the original active management instance, start the new standby management instance.
% ttGridAdmin mgmtStandbyStart Standby management instance started
You have successfully resized your management instances. Additionally, your original active management instance is now the standby management instance and the original standby management instance is now the active management instance. This does not affect operations of your grid.
TimesTen Scaleout enables you to collect various logs from every host that is part of your grid. These logs are useful for troubleshooting errors that you may encounter while using your grid or database. You can collect these logs with the ttGridAdmin gridLogCollect command:
ttGridAdmin.log
Shows support messages about the grid.
tterrors.log
Shows any error or warning messages that the TimesTen daemon encountered.
ttmesg.log
Shows support messages about the TimesTen daemon.
Configuration files
Configuration files that are stored in the timesten_home/conf/ directory of each instance.
Note:
The logs are stored in thetimesten_home/diag/ directory. This directory contains multiple tterrors.log and ttmesg.log files that are appended with numbers. The logs without the appended number are the most recent log files.Note:
Before collecting logs for your grid, ensure that you have configured a repository. See "Working with repositories" for more information on repositories.This example collects the logs for a grid and stores these logs in the repository repo1. By default, TimesTen Scaleout names your collection of logs with the current date and time, Lyyyymmddhhss. The prefix of the backup name, L, stands for logs.
Note:
You can add the-name parameter to specify a collection name. For example, ttGridAdmin gridLogCollect -repository repo1 -name mylogs creates a collection of logs named mylogs.% ttGridAdmin gridLogCollect -repository repo1 Logs copied to collection L20170331143740 in repository repo1
The ttGridAdmin gridLogCollect command creates a collection directory in the repository. The collection directory contains a directory for every host of your grid where each host directory contains logs for that specific host.
TimesTen Scaleout enables you to retrieve diagnostic information for a whole grid. This diagnostic information can be useful for the Oracle Support team to be able to diagnose any issue that might come up with your grid.
The following example retrieves diagnostic information for your whole grid by using the ttGridAdmin utility from the management instance. You can then provide this file to the Oracle Support team.
% ttGridAdmin gridDump /tmp/grid.status.txt
It is important to ensure that the system clocks of every host in your grid are roughly synchronized. Synchronized system clocks ensure that timestamps of transactions and logs are accurate on all hosts.
This example outputs the system date and time of every host in the grid.
% ttGridAdmin hostExec date Commands executed on: host1 rc 0 host2 rc 0 host3 rc 0 host4 rc 0 host5 rc 0 host6 rc 0 Return code from host1: 0 Output from host1: Fri Mar 31 18:16:51 PDT 2018 Return code from host2: 0 Output from host2: Fri Mar 31 18:16:49 PDT 2018 Return code from host3: 0 Output from host3: Fri Mar 31 18:16:51 PDT 2018 Return code from host4: 0 Output from host4: Fri Mar 31 18:16:51 PDT 2018 Return code from host5: 0 Output from host5: Fri Mar 31 18:16:50 PDT 2018 Return code from host6: 0 Output from host6: Fri Mar 31 18:16:52 PDT 2018
In case that the system clock of a host is not synchronized with the other hosts, adjust the system clock on that specific host. You can use the Network Time Protocol (NTP) to ensure that the system clock of your hosts are synchronized.