ttSQLCmdCacheInfo
This procedure returns information about all prepared SQL statements in the TimesTen SQL command cache.
Required Privilege
This procedure requires the ADMIN privilege.
Syntax
ttSQLCmdCacheInfo([sqlCmdID])Parameters
ttSQLCmdCacheInfo has the optional parameter:
| Parameter | Type | Description |
|---|---|---|
|
|
|
The unique identifier of a SQL command in the TimesTen command cache. If no value is supplied, information is displayed for all commands. |
Result Set
ttSQLCmdCacheInfo returns the result set:
| Column | Type | Description |
|---|---|---|
|
|
|
The unique identifier of a command. |
|
|
|
If the command is private, this is the connection ID of the connection where it was prepared. If not a private command, this value is |
|
|
|
Counts the number of executions of the command. |
|
|
|
Counts the number of prepares for the command. |
|
|
|
Counts the number of reprepares for the command. |
|
|
|
Indicates whether this command can be garbage collected by the subdaemon.
|
|
|
|
The total space (bytes) allocated for this command in the command cache. |
|
|
|
The user who created the command. |
|
|
|
The full SQL text for the current command. |
|
|
|
The total number of fetch executions done for this statement. The number of fetches depends on |
|
|
|
The time when the statement was last run. The value is in the form: |
|
|
|
The maximum wall clock execute time in seconds for this statement. |
|
|
|
Last measured execution time in seconds of the command. |
|
|
|
If |
|
|
|
The amount of temporary space in bytes used by the last execution of this command. |
|
|
|
Expresses the maximum amount of temporary space in bytes used by any previous run of this command. |
|
|
TT_TINYINT |
The optimizer's write concurrency mode under which the command compiles. |
|
|
TT_TINYINT |
The command compiles the same under all modes or compiles differently under different modes. |
Examples
To display command information in ttIsql for all the current valid commands, use:
Command> call ttsqlcmdcacheinfo;
< 45085320, -1, 0, 1, 0, 0, 4464, SYS , update sys.user$ u set u.lcount = u.lcount + 1 where u.user# = :userid, 0, <NULL>, 0, <NULL>, 0, 0, 0, 0, 0 >
< 43977560, -1, 0, 1, 0, 0, 4736, SYS , update sys.user$ u set u.lcount = 0, u.astatus = :astatus where u.user# = :userid, 0, <NULL>, 0, <NULL>, 0, 0, 0, 0, 0 >
< 45094152, -1, 0, 1, 0, 0, 4640, SYS , select u.password, u.password_date from sys.user_history$ u where u.user# = :userid order by u.password_date desc, 0, <NULL>, 0, <NULL>, 0, 0, 0, 0, 0 >
< 43968568, -1, 0, 1, 0, 0, 4080, SYS , select pn.profile# from sys.profname$ pn where pn.name = :profilename, 0, <NULL>, 0, <NULL>, 0, 0, 0, 0, 0 >
< 35824216, -1, 1, 1, 0, 1, 10504, SYS , select o.owner#,o.obj#,u.name,o.name,o.namespace from sys.user$ u, sys.obj$ o where u.user#=o.owner# and o.type#=:1 and not exists (select p_obj# from sys.dependency$ where p_obj# = o.obj#) order by o.obj# for update, 1, 2026-02-17 04:49:26.547000, 0, 0, 0, 5008, 5008, 0, 0 >
< 35017680, -1, 1, 1, 0, 0, 6849, JOHN , call ttsqlcmdcacheinfo, 6, 2026-02-17 04:49:39.179000, 0, 0, 0, 0, 0, 0, 0 >
< 43972488, -1, 0, 1, 0, 0, 5256, SYS , select p.resource#, p.limit# from sys.profile$ p where p.type# = 0 and p.profile# = :profileid, 0, <NULL>, 0, <NULL>, 0, 0, 0, 0, 0 >
< 35005120, -1, 6, 6, 0, 1, 7088, SYS , select owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,flags from sys.obj$ where owner#=:1 and name=:2 and namespace=:3, 6, 2026-02-17 04:49:39.179000, 0, 0, 0, 1110, 1110, 0, 0 >
< 43959784, -1, 0, 1, 0, 0, 8744, SYS , select 1 from sys.sysauth$ s where (s.grantee# = :userid or s.grantee# = 1) and (s.privilege# = :priv or s.privilege# = 67), 0, <NULL>, 0, <NULL>, 0, 0, 0, 0, 0 >
< 45098584, -1, 0, 1, 0, 0, 4472, SYS , delete from user_history$ where user# = :userid and password_date < :pdate, 0, <NULL>, 0, <NULL>, 0, 0, 0, 0, 0 >
< 45089352, -1, 0, 1, 0, 0, 5272, SYS , update sys.user$ u set u.lcount = u.lcount + 1, u.ltime = sysdate, u.astatus = :astatus where u.user# = :userid, 0, <NULL>, 0, <NULL>, 0, 0, 0, 0, 0 >
< 43953960, -1, 1, 1, 0, 0, 6328, SYS , select u.user#, u.password, u.identification, u.astatus, u.lcount, u.ltime, u.profile# from sys.user$ u where u.name = :name and u.type# = 1, 1, 2026-02-17 04:49:26.547000, 0, 0, 0, 1400, 1400, 0, 0 >
< 35012432, -1, 1, 1, 0, 1, 2385, JOHN , CALL ttOptSetFlag ('passthrough', 0), 0, 2026-02-17 04:49:26.547000, 0, 0, 0, 32, 32, 0, 0 >
< 35014848, -1, 1, 1, 0, 1, 2793, JOHN , CALL ttconfiguration ('TTGrid'), 2, 2026-02-17 04:49:26.547000, 0, 0, 0, 1148, 1148, 0, 0 >
< 35835080, -1, 0, 1, 0, 1, 8240, SYS , select null from sys.obj$ where obj#=:1 and type#=:2 and obj# not in (select p_obj# from dependency$ where p_obj# = sys.obj$.obj#), 0, <NULL>, 0, <NULL>, 0, 0, 0, 0, 0 >
15 rows found.To display the information formatted vertically in ttIsql, use:
Command> vertical call ttSQLCmdCacheInfo; ...
To display the information vertically in ttIsql for sqlCmdID 51623232, use:
Command> vertical call ttsqlcmdcacheinfo(51623232); SQLCMDID: 51623232 PRIVATE_COMMAND_CONNECTION_ID: 2048 EXECUTIONS: 4 PREPARES: 4 REPREPARES: 0 FREEABLE: 1 SIZE: 2704 OWNER: SYS QUERYTEXT: delete from sys.source$ where obj#=: FETCHCOUNT: STARTTIME: MAXEXECUTETIME: LASTEXECUTETIME: MINEXECUTETIME: 1 row found.
See Also