ttSQLCmdCacheInfo
This procedure returns information about all prepared SQL statements in the TimesTen SQL command cache.
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.
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 |
|
|
Indicates the location of where the command was executed.
|
|
|
References a specific command in tools like ttXactAdmin and locking error messages. It is a unique identifier for a compiled command across the grid. |
|
|
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; < 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.
See Also