Viewing SQL Statements Stored in the SQL Command Cache

All commands after they run—SQL statements, built-in procedures, and so on—are stored in the SQL command cache, which uses temporary memory.

The commands are stored up until the limit of the SQL command cache is reached, then the new commands are stored after the last used commands are removed. You can retrieve one or more of these commands that are stored in the SQL command cache.

Note:

This section describes viewing the commands stored in the SQL command cache. For details on how to view the query plans associated with these commands, see Viewing Query Plans Associated with Commands Stored in the SQL Command Cache.

The following sections describe how to view commands cached in the SQL command cache:

Managing Performance and Troubleshooting Commands

You can view all one or more commands or details of their query plans with the ttSQLCmdCacheInfo and ttSQLCmdQueryPlan built-in procedures. Use the query plan information to monitor and troubleshoot your queries.

Viewing commands and query plans can help you perform the following:

Detect updates or deletes that are not using an index scan.
Monitor query plans of running queries to ensure all plans are optimized.
Detect applications that do not prepare SQL statements or that re-prepare the same statement multiple times.
Detect the percentage of space used in the command cache for performance evaluation.

Displaying Commands Stored in the SQL Command Cache

The commands run against the TimesTen database are cached in the SQL command cache. The ttSQLCmdCacheInfo built-in procedure displays a specific or all cached commands in the TimesTen SQL command cache.

By default, all commands are displayed; if you specify a command id, then only this command is retrieved for display.

The command data is saved in the following format:

Command identifier, which is used to retrieve a specific command or its associated query plan.
Private connection identifier.
Counter for the number of executions.
Counter for the number of times the user prepares this statement.
Counter for the number of times the user re-prepares this SQL statement.
Freeable status, where if the value is one, then the subdaemon can free the space with the garbage collector. A value of zero determines that the space is not able to be freed.
Total size in bytes allocated for this command in the cache.
User who created the command.
Query text up to 1024 characters.
Number of fetch executions performed internally for this statement.
The timestamp when the statement started.
The maximum runtime in seconds for the statement.
Last measured runtime in seconds for the statement.
The minimum runtime in seconds for the statement.
The unique identifier of the statement compiled across the TimesTen.
Total temporary size in bytes used for this TimesTen statement the last time it ran.
The maximum temporary size in bytes ever used to run this TimesTen statement.

At the end of the list of all commands, a status is printed of how many commands were in the cache.

The following examples show how to display all or a single command from the SQL command cache using the ttSQLCmdCacheInfo built-in utility:

Example: Displaying all commands in the SQL command cache

This example runs within ttIsql the ttSQLCmdCacheInfo built-in procedure without arguments to show all cached commands. The commands are displayed in terse format. To display the information where each column is prepended with the column name, run vertical on before calling the ttSQLCmdCacheInfo procedure.

Command> vertical 1;
Command> call ttSQLCmdCacheInfo;

  SQLCMDID:                        43402480
  PRIVATE_COMMAND_CONNECTION_ID:   -1
  EXECUTIONS:                      1
  PREPARES:                        1
  REPREPARES:                      0
  FREEABLE:                        1
  SIZE:                            4344
  OWNER:                           HR
  QUERYTEXT:                       INSERT INTO employees VALUES
        ( 191
        , 'Randall'
        , 'Perkins'
        , 'RPERKINS'
        , '650.505.4876'
        , TO_DATE('19-DEC-1999', 'dd-MON-yyyy')
        , 'SH_CLERK'
        , 2500
        , NULL
        , 122
        , 50
        )
  FETCHCOUNT:                      0
  STARTTIME:                       2015-04-09 15:22:22.139000
  MAXEXECUTETIME:                  0
  LASTEXECUTETIME:                 0
  MINEXECUTETIME:                  0
  GRIDCMDID:                       <NULL>
  TEMPSPACEUSAGE:                  0
  MAXTEMPSPACEUSAGE:               0
 
 
  SQLCMDID:                        43311000
  PRIVATE_COMMAND_CONNECTION_ID:   -1
  EXECUTIONS:                      1
  PREPARES:                        1
  REPREPARES:                      0
  FREEABLE:                        1
  SIZE:                            4328
  OWNER:                           HR
  QUERYTEXT:                       INSERT INTO employees VALUES
        ( 171
        , 'William'
        , 'Smith'
        , 'WSMITH'
        , '011.44.1343.629268'
        , TO_DATE('23-FEB-1999', 'dd-MON-yyyy')
        , 'SA_REP'
        , 7400
        , .15
        , 148
        , 80
        )
  FETCHCOUNT:                      0
  STARTTIME:                       2015-04-09 15:22:22.139000
  MAXEXECUTETIME:                  0
  LASTEXECUTETIME:                 0
  MINEXECUTETIME:                  0
  GRIDCMDID:                       <NULL>
  TEMPSPACEUSAGE:                  0
  MAXTEMPSPACEUSAGE:               0
 
 ...
102 rows found.

Example: Displaying a single SQL command

If you provide a command id as the input for the ttSQLCmdCacheInfo, the single command is displayed from within the SQL command cache. If no command id is provided to the ttSQLCmdCacheInfo built-in procedure, then it displays information about all current commands, where the command id is the first column of the output.

The following example displays the command identified by Command ID of 527973892. It is displayed in terse format; to view with the column headings prepended, run vertical on before calling the ttSQLCmdCacheInfo built-in.

Command> call ttSQLCmdCacheInfo(43311000);
< 43311000, -1, 1, 1, 0, 1, 4328, HR, INSERT INTO employees VALUES
( 171, 'William', 'Smith', 'WSMITH', '011.44.1343.629268', TO_DATE('23-FEB-1999',
 'dd-MON-yyyy'), 'SA_REP', 7400, .15, 148, 80), 0, 2015-04-09 15:22:22.139000, 0,
 0, 0, <NULL>, 0, 0 > 
1 row found.