Timesten - (SQL) Command Cache

> Database > (TimesTen|TT) (In-Memory Database|IMDB)

1 - About

All commands executed:

  • SQL statements,
  • built-in procedures,

are stored in the Command Cache, which uses temporary memory.

The commands are stored up until the limit of the Command Cache is reached, then the new commands are stored after the last used commands are removed.

3 - How to retrieve informations

The commands can be retrieve with the cmdcache command.

3.1 - cmdcache

Displays the contents of the TimesTen SQL Command Cache.

Optionally specify the column to search (cmdid, querytext or owner) and substring to filter the SQL statements that are returned.

The default field to search is the querytext.

The syntax is:

cmdcache [ [ by {cmdid | querytext | owner} ] <query substring>]

where:

  • Arguments in <> are required.
  • Arguments in [] are optional.

Example: cmdcache; -or- cmdcache mytable -or- cmdcache by owner SYS

To retrieve all SQL in the cache which contain the table all_users:

Command> cmdcache all_users;
< 62958925456, 1, 1, 1, 0, 1, 3488, ORACLE                         , select * from all_users >
< 62958908272, 1, 3, 3, 0, 1, 3488, ORACLE                         , select * from all_users >
2 rows found.
Advertising

3.2 - ttsqlCmdCacheInfo

call ttsqlCmdCacheInfo;

with the SQLCMDID:

call ttsqlCmdCacheInfo(262145825784);
SQLCMDID      PRIVATE_COMMAND_CONNECTION_ID  EXECUTIONS  PREPARES  REPREPARES  FREEABLE  SIZE  OWNER   QUERYTEXT
------------  -----------------------------  ----------  --------  ----------  --------  ---   ------  ---------
262145825784  6                              1           1         0           1         3640  TH_DWH  SELECT STEP, LEVEL