ttSQLCmdCacheInfo

Required Privilege

This procedure requires the ADMIN privilege.

Usage in TimesTen Scaleout and TimesTen Classic

This procedure is supported in TimesTen Classic.

TimesTen Scaleout applications can call this built-in procedure.

TimesTen Scaleout, this procedure runs locally on the element from which it is called.

Related Views

This procedure has these related views.

SYS.GV$SQL_CMD_CACHE

SYS.V$SQL_CMD_CACHE

Syntax

ttSQLCmdCacheInfo([sqlCmdID])

Parameters

ttSQLCmdCacheInfo has the optional parameter:

Parameter	Type	Description
`sqlCmdID`	`TT_BIGINT`	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
`sqlCmdID`	`TT_BIGINT NOT NULL`	The unique identifier of a command.
`privateCommandConnectionID`	`TT_INTEGER`	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 `-1`.
`executions`	`TT_BIGINT` `NOT NULL`	Counts the number of executions of the command.
`prepares`	`TT_BIGINT NOT NULL`	Counts the number of prepares for the command.
`reprepares`	`TT_BIGINT` `NOT NULL`	Counts the number of reprepares for the command.
`freeable`	`TT_TINYINT NOT NULL`	Indicates whether this command can be garbage collected by the subdaemon. `1` - Indicates freeable. `0` - Indicates non-freeable.
`size`	`TT_INTEGER NOT NULL`	The total space (bytes) allocated for this command in the command cache.
`owner`	`TT_CHAR(31) NOT NULL`	The user who created the command.
`queryText`	`TT_VARCHAR (409600) NOT NULL`	The full SQL text for the current command.
`fetchCount`	`TT_BIGINT NOT NULL`	The total number of fetch executions done for this statement. The number of fetches depends on `TT_PREFETCH_COUNT`. The pre-fetch count has a default value of 5 in Read Committed isolation mode and a default of 128 in Serializable mode.
`startTime`	`TT_TIMESTAMP`	The time when the statement was last run. The value is in the form: `YYYY-MM-DD HH:MI:SS.FFF`
`maxExecuteTime`	`NUMBER`	The maximum wall clock execute time in seconds for this statement.
`lastExecuteTime`	`NUMBER`	Last measured execution time in seconds of the command.
`minExecuteTime`	`NUMBER`	If `SqlCmdSampleFactor > 0`, minimum execute time in seconds, otherwise `0.0`.
`execloc`	`TT_TINYINT NOT NULL`	Indicates the location of where the command was executed. `0` -For classic/local. `1` - For remote. `2` - Globally executed statement.
`gridCmdId`	`TT_VARCHAR (64)`	References a specific command in tools like ttXactAdmin and locking error messages. It is a unique identifier for a compiled command across the grid.
`tempSpaceUsage`	`TT_BIGINT`	The amount of temporary space in bytes used by the last execution of this command.
`maxTempSpaceUsage`	`TT_BIGINT`	Expresses the maximum amount of temporary space in bytes used by any previous run of this command.
`writeConcurrencyModeCompiled`	`TT_TINYINT`	The optimizer's write concurrency mode under which the command compiles.
`writeConcurrencyModeSensitive`	`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;
< 43428992, 2048, 5, 5, 0, 1, 2800, SYS, select sys.objectSequence.nextval from dual >
< 51629120, 2048, 12, 12, 0, 1, 3040, SYS, delete from sys.idl_char$ where obj#=:1 and part=:2 >
< 51641192, 2048, 2, 2, 0, 1, 2112, BWAF4EVR, create table c1 number not null, c2 number) >
< 43442488, 2048, 5, 5, 0, 1, 4616, SYS, insert intosys.obj$(owner#, name, namespace, obj#, type#,
ctime, mtime, stime, status, flags) values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10) >
< 51632072, 2048, 12, 12, 0, 1, 3040, SYS, delete from sys.idl_ub2$ where obj#=:1 and part=:2 >
< 49375216, 2048, 0, 1, 0, 0, 4232, SYS, select 1 from sys.sysauth$ s where s.grantee# = :userid or s.grantee# = 1)
and (s.privilege# = :priv or s.privilege# = 67) >
< 51626304, 2048, 12, 12, 0, 1, 3040, SYS, delete from sys.idl_ub1$ where obj#=:1 and part=:2 >
< 51645776, 2048, 1, 1, 0, 1, 2344, BWAF4EVR, create table (c1 number primary key not null, col2 number) >
< 51623232, 2048, 4, 4, 0, 1, 2704, SYS, delete from sys.source$ where obj#=:1 >
32 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.