11 Working with TimesTen Performance Metrics

TimesTen collects performance metrics from TimesTen system tables, system views, and built-in procedures. SQL Developer allows you to capture snapshots and generate reports that compare two snapshots. A snapshot is a collection of performance metrics.

Note:

SQL Developer uses functions from the TT_STATS PL/SQL package to capture snapshots and generate reports that compare two snapshots. For more information about the TT_STATS PL/SQL package, see "TT_STATS" in the Oracle TimesTen In-Memory Database PL/SQL Packages Reference.

Before attempting to use the TimesTen Performance Metrics:

  • Make sure you are using Oracle TimesTen In-Memory Database Release 11.2.2.5 (or later).

  • Make sure that the TimesTen connection user has execution privileges on the TT_STATS PL/SQL package.

This chapter provides information about working with TimesTen performance metrics.

Topics include:

Creating snapshots

A snapshot is a collection of performance metrics from a database. You can create a report comparing two snapshots to analyze the performance of your database.

To create a new snapshot of a database, make sure you are on the main SQL Developer page and that you have expanded the TimesTen connection in the connections navigator.

  1. Right-click the name of the database and select TimesTen Performance Metrics, then select Create Snapshot.

    Figure 11-1 Creating a snapshot

    Description of Figure 11-1 follows
    Description of "Figure 11-1 Creating a snapshot"

    The New TimesTen Metrics Snapshot dialog displays.

  2. In the Prompts tab of the New TimesTen Metrics Snapshot dialog, select the desired capture level from the Capture Level drop-down list:

    • TYPICAL: For a typical set of metrics. This level is appropriate for most purposes.

    • NONE: For metrics outside of SYS.SYSTEMSTATS only.

    • BASIC: For a minimal basic set of metrics.

    • ALL: For all available metrics.

    Make sure to use the same capture level for any two snapshots to be used in a report. An error dialog displays if you attempt to generate a report that compares two snapshots with different capture levels.

    Figure 11-2 New TimesTen Metrics Snapshot dialog

    Description of Figure 11-2 follows
    Description of "Figure 11-2 New TimesTen Metrics Snapshot dialog"

    You are now ready to create a new snapshot.

  3. Click Apply to create the snapshot.

    Figure 11-3 Creating new snapshot

    Description of Figure 11-3 follows
    Description of "Figure 11-3 Creating new snapshot"

    A confirmation dialog shows the ID number of the snapshot. TimesTen generates the snapshot ID.

  4. Click OK.

    Figure 11-4 Snapshot ID confirmation

    Description of Figure 11-4 follows
    Description of "Figure 11-4 Snapshot ID confirmation"

    TimesTen creates the snapshot with the specified snapshot ID. The snapshot ID is useful for creating TimesTen metrics reports.

Dropping snapshots

Consider dropping unneeded snapshots to free up permanent space. If you need to save more snapshots of your database, consider editing the TimesTen performance metrics configuration. See "Editing the TimesTen performance metrics configuration".

To drop a new snapshot from a database, make sure you are on the main SQL Developer page.

  1. Right-click the name of the database and select TimesTen Performance Metrics, then select Drop Snapshot.

    Figure 11-5 Dropping a snapshot

    Description of Figure 11-5 follows
    Description of "Figure 11-5 Dropping a snapshot"

  2. By default, the Prompts tab of the Drop TimesTen Metrics Snapshot(s) dialog is selected. In the Selector column, select the snapshots you want to drop.

    You can also click on the column header to sort the table based on the column. A description of each column follows:

    • Selector

      The check box you select to determine the snapshots that you want to drop.

    • Snapshot

      The snapshot ID of the snapshot.

    • Timestamp

      The timestamp of when the snapshot was captured.

    • Capture Level

      The capture level of the snapshot. This value can be TYPICAL, NONE, BASIC, or ALL.

    To select all snapshots, click the Select all check box.

  3. Once you have selected the snapshots that you want to drop, click the Apply button.

    Figure 11-6 Selecting snapshots that you want to drop

    Description of Figure 11-6 follows
    Description of "Figure 11-6 Selecting snapshots that you want to drop"

    A Confirm dialog displays asking you if you want to proceed with dropping the selected snapshots.

  4. Click Yes.

    Figure 11-7 Confirmation dialog for dropping snapshots

    Description of Figure 11-7 follows
    Description of "Figure 11-7 Confirmation dialog for dropping snapshots"

    A confirmation dialog displays a message indicating that one or more TimesTen Metrics snapshots were dropped.

  5. Click OK.

    Figure 11-8 Dropped snapshots

    Description of Figure 11-8 follows
    Description of "Figure 11-8 Dropped snapshots"

    You successfully dropped the TimesTen metrics snapshots.

Editing the TimesTen performance metrics configuration

TimesTen performance metrics configuration allows you to modify the maximum snapshot count and the maximum snapshot retention size. These configuration settings are useful in preventing your database from running out of permanent space and in allowing you to save more snapshots.

Once the maximum snapshot count or the maximum snapshot retention size is reached, TimesTen removes the oldest snapshots to free up permanent space. By default, the maximum snapshot count is 256 and the maximum snapshot retention size is 256 MB.

To edit the TimesTen performance metrics configuration for a database, make sure you are on the main SQL Developer page.

  1. Right-click the name of the database and select TimesTen Performance Metrics, then select Edit Configuration.

    Figure 11-9 Editing the TimesTen performance metrics configuration

    Description of Figure 11-9 follows
    Description of "Figure 11-9 Editing the TimesTen performance metrics configuration"

    The TimesTen Metrics Configuration dialog displays and you see the current configuration for the TimesTen metrics.

  2. The Prompts tab of the TimesTen Metrics Configuration dialog is selected. In the Maximum Snapshot Count field, specify a numeric value to indicate the maximum number of snapshots. By default, the maximum number of snapshots is 256.

  3. In the Maximum Snapshot Retention Size (MB) field, specify the maximum total size of all stored snapshots, in megabytes. By default, the maximum snapshot retention size is 256 MB.

  4. Click Apply.

    Figure 11-10 TimesTen metrics configuration dialog

    Description of Figure 11-10 follows
    Description of "Figure 11-10 TimesTen metrics configuration dialog"

    You successfully applied the new TimesTen Metrics configuration settings.

Creating a TimesTen performance metrics report

TimesTen performance metrics reports use the data from two specified snapshots of TimesTen metrics to produce a report in HTML format. TimesTen metrics reports include information for each metric. For more information about the various metrics displayed in the report, see "Report examples" in the Oracle TimesTen In-Memory Database Reference.

To generate a new report for a database, make sure you are on the main SQL Developer page.

  1. Right-click the name of the database and select TimesTen Performance Metrics, then select Report.

    Figure 11-11 Creating a report

    Description of Figure 11-11 follows
    Description of "Figure 11-11 Creating a report"

    The Create TimesTen Metrics Report dialog displays.

  2. By default, the Report generator tab of the Create TimesTen Metrics Report dialog is selected. In the Selector column, select two snapshots to compare.

    You can also click on the column header to sort the table based on the column. A description of each column follows:

    • Selector

      The check box that determines the snapshots that are compared in the metrics report. Make sure to only select two snapshots with the same capture level. An error dialog displays if you attempt to generate a report that compares more than two snapshots.

    • Snapshot

      The snapshot ID of the snapshot.

    • Timestamp

      The timestamp of when the snapshot was captured.

    • Capture Level

      The capture level of the snapshot. This value can be TYPICAL, NONE, BASIC, or ALL.

  3. Once you have selected two snapshots with the same capture level, click the Generate report button. An error dialog displays if you attempt to generate a report that compares snapshots with different capture levels.

    Figure 11-12 Create TimesTen metrics report

    Description of Figure 11-12 follows
    Description of "Figure 11-12 Create TimesTen metrics report"

    TimesTen generates your TimesTen metrics report. You can view the generated TimesTen metrics report in the pane located below the Generate report button.

    For more information about the various metrics displayed in the report, see "Report examples" in the Oracle TimesTen In-Memory Database Reference.

  4. If you do not want to save the report, click Close. To save the report, see "Saving a report".

Saving a report

To save the report for future reference, follow these steps:

  1. Click Save.

    The Save dialog displays.

  2. Select the directory where you want to save the TimesTen metrics report.

  3. In the File Name field, define the name of your TimesTen metrics report.

    The default filename of the TimesTen metrics report is ttstats-snapshot_id_1-snapshot_id_2-YYYYMMDDHHMISS.html, where snapshot_id_1 and snapshot_id_2 are the snapshot IDs of the two snapshots you are comparing in your report. YYYYMMDDHHMISS is a timestamp of when the report was generated, where YYYY is the year, MM the month, DD the day, HH the hour, MI the minutes, and SS the seconds.

  4. Click Save.

    Figure 11-13 Save TimesTen metrics report

    Description of Figure 11-13 follows
    Description of "Figure 11-13 Save TimesTen metrics report"

    SQL Developer saves your report.