Purpose
Collects the session information for the current user on the current host.
Definition
REPLACE FUNCTION SYSLIB.MonitorMySessions()
RETURNS TABLE
(HostId SMALLINT,
SessionNo INTEGER,
LogonPENo SMALLINT,
RunVprocNo SMALLINT,
PartName VARCHAR(16)CHARACTER SET LATIN,
PEstate VARCHAR(18)CHARACTER SET LATIN,
LogonTime VARCHAR(22)CHARACTER SET LATIN,
UserId INTEGER,
LSN INTEGER,
UserName VARCHAR(128),
UserAccount VARCHAR(128),
PECPUsec FLOAT,
XActCount FLOAT,
ReqCount FLOAT,
ReqCacheHits FLOAT,
AMPState VARCHAR(18)CHARACTER SET LATIN,
AMPCPUSec FLOAT,
AMPIO FLOAT,
ReqSpool FLOAT,
Blk1HostId SMALLINT,
Blk1SessNo INTEGER,
Blk1UserId INTEGER,
Blk1Lmode CHAR(1) CHARACTER SET LATIN,
Blk1Otype CHAR (2) CHARACTER SET LATIN,
Blk1ObjDBID INTEGER,
Blk1ObjTID INTEGER,
Blk1Status CHAR (1) CHARACTER SET LATIN,
Blk2HostId SMALLINT,
Blk2SessNo INTEGER,
Blk2UserId INTEGER,
Blk2Lmode CHAR(1) CHARACTER SET LATIN,
Blk2Otype CHAR(2) CHARACTER SET LATIN,
Blk2ObjDBID INTEGER,
Blk2ObjTID INTEGER,
Blk2Status CHAR(1) CHARACTER SET LATIN,
Blk3HostId SMALLINT,
Blk3SessNo INTEGER,
Blk3UserId INTEGER,
Blk3Lmode CHAR(1) CHARACTER SET LATIN,
Blk3Otype CHAR(2) CHARACTER SET LATIN,
Blk3ObjDBID INTEGER,
Blk3ObjTID INTEGER,
Blk3Status CHAR(1) CHARACTER SET LATIN,
MoreBlockers CHAR(1) CHARACTER SET LATIN,
LogonSource VARCHAR(128) CHARACTER SET UNICODE,
HotAmp1CPU FLOAT,
HotAmp2CPU FLOAT,
HotAmp3CPU FLOAT,
HotAmp1CPUId FLOAT,
HotAmp2CPUId FLOAT,
HotAmp3CPUId FLOAT,
HotAmp1IO FLOAT,
HotAmp2IO FLOAT,
HotAmp3IO FLOAT,
HotAmp1IOId INTEGER,
HotAmp2IOId INTEGER,
HotAmp3IOId INTEGER,
LowAmp1CPU FLOAT,
LowAmp2CPU FLOAT,
LowAmp3CPU FLOAT,
LowAmp1CPUId INTEGER,
LowAmp2CPUId INTEGER,
LowAmp3CPUId INTEGER,
LowAmp1IO FLOAT,
LowAmp2IO FLOAT,
LowAmp3IO FLOAT,
LowAmp1IOId INTEGER,
LowAmp2IOId INTEGER,
LowAmp3IOId INTEGER,
AvgAmpCPUSec FLOAT,
AvgAmpIOCnt FLOAT,
AmpCount SMALLINT,
TempSpaceUsg FLOAT,
ReqStartTime VARCHAR(22) CHARACTER SET LATIN,
ReqCPU FLOAT,
ReqIO FLOAT,
ReqNo INTEGER,
WlcId INTEGER,
DontReclassifyFlag SMALLINT,
ProxyUser VARCHAR (128) CHARACTER SET UNICODE,
CPUDecayLevel SMALLINT,
IODecayLevel SMALLINT,
TacticalCPUException INTEGER,
TacticalIOException INTEGER,
ReqIOKB FLOAT,
ReqPhysIO FLOAT,
ReqPhysIOKB FLOAT,
ReqStepsCompletedCnt INTEGER,
RedriveProtection CHAR (2),
CurrentRedriveParticipation CHAR (1),
ReqRedriveSpoolSpace FLOAT,
BlockerSessionCnt SMALLINT,
ReqTblOpBytesIn FLOAT,
ReqTblOpBytesOut FLOAT,
ProxyUserId INTEGER,
ZoneId INTEGER,
ReqHotAmpCPU FLOAT,
ReqHotAmpCPUId SMALLINT,
ReqHotAmpIO FLOAT,
ReqHotAmpIOId SMALLINT,
ReqInvolvedAMPCnt SMALLINT,
ReqFirstRespTime VARCHAR(22) CHARACTER SET LATIN,
ReqLocalQueryStatus SMALLINT,
ReqRemoteHostId SMALLINT,
ReqRemoteSessionId INTEGER,
ReqRemoteRequestId INTEGER,
ReqRemoteQueryId FLOAT
ReqHotAmpSpool FLOAT,
ReqHotAmpSpoolId SMALLINT,
ReqMapNo SMALLINT,
ReqMaxNumMapAMPs INTEGER,
ReqMinNumMapAMPs INTEGER,
ReqSysDefNumMapAMPs INTEGER,
ReqRemoteHostIp VARCHAR(128) CHARACTER SET UNICODE,
ReqServerName VARCHAR(128) CHARACTER SET UNICODE,
ReqFlexReleased SMALLINT
)
.
.
.
;
Usage Notes - MonitorMySessions
The following CPU fields in the MonitorMySessions response are affected by the MonSesCPUNormalization field:
|
|
|
The MonSesCPUNormalization field, a DBS Control General Record field, controls whether normalized or non-normalized statistical CPU data is reported by the functions: MonitorMySessions and MonitorSession, and by the MONITOR SESSION request. For more information about the MonSesCPUNormalization field and CPU normalization, see the DBS Control utility in Teradata Vantage™ - Database Utilities , B035-1102 .
For a complete description of these fields, see the topic that follows.
You can also refer to MonitorSession and MONITOR SESSION for a list of these CPU fields.
A Trusted Session enables a middle-tier application to switch the user on an already active database session to another user (proxy user). Once the user is switched, all subsequent requests uses the privileges and session attributes of the proxy user. MonitorMySessions returns the logon user name and ID in the UserName and UserId fields and the proxy user name and ID in the ProxyUserName and ProxyUserId fields.
When connected as a permanent proxy user, MonitorMySessions returns all information on sessions directly logged on as the permanent proxy user. It does not return the current session or any other proxy user sessions for the permanent proxy user.
Result Rows
| Column Name | Description |
|---|---|
| HostId | Logical Host ID associated with a PE or session. For a PE, HostId identifies one of the hosts or LANs associated with the described PE. For a session, the combination of a host ID and a session number uniquely identifies a user session on the system. This value is NULL for AMPs. A value of zero represents the Supervisor window.
|
| SessionNo | Number of the current session. Together with a given host ID, a session number uniquely identifies a session on the database system. This value is assigned by the host (or client) at logon time. |
| LogonPENo | Vproc number of the PE the session is currently logged on to; it identifies the PE that has control responsibility for the session. Normally, this is the PE that processed the logon request; but if that PE goes offline, it will be the PE to which the session was switched. |
| RunVprocNo | Vproc number of the AMP or PE currently assigned to process the session requests. For sessions in Teradata SQL partitions, this value is identical to the LogonPENo. For sessions in FastLoad or MultiLoad partitions, this is the AMP that initially processes the data being loaded. For HUTPARSE or HUTCTL (archive/recovery) sessions, this value is NULL. If a RunVprocNo value of -1 in record mode or NULL in indicator mode is returned by MonitorMySessions for FastLoad, MultiLoad or FastExport sessions, this indicates that the session is in the process of starting up. |
| PartName | Name of the session partition associated with this session. Following a successful logon request by a session or as part of a connect request, the session identifies the partition with which the user wants the session to be associated. FASTLOAD, Teradata SQL, MONITOR, INTERNAL are examples of valid partition names. |
| PEstate | Current state of the session within the PE. See the MONITOR SESSION PEState field for a list of the PEState values. |
| LogonTime | Date and time portion of the information recorded by Session Control when a session successfully logs on. It is usually formatted for display as yyyy/mm/dd 99:99:99.99, which represents the year, month, day, hours: minutes: seconds. |
| UserId | User or internal ID of a user for this session. Within the database, UserId is equivalent to Database ID. Typically, UserId is used when the associated record is known to be a user name, and Database ID is used when the associated record is known to be a database. However, UserId can identify either a given user or database name. |
| LSN | Logon Sequence Number (LSN) that is associated with a session when the session logs on. It identifies a collection of sessions performing a related activity. For example, in a FastLoad job, a user is logged on as a Teradata SQL session, as well as n FastLoad sessions with the same user name. Therefore, n+1 sessions (1 Teradata SQL and n FastLoad) with the same LSN are all associated with the given FastLoad job. To see how the FastLoad job is doing, the user can pick out all sessions reported with the same LSN number. This information supplies the parent-child relationship for sessions involved with FastLoad, MultiLoad, and Archive/Recovery jobs.
|
| UserName | User name of the session. |
| UserAccount | Current account for the session. |
| PECPUsec | CPU time (in seconds) used in a PE by the associated session for parsing and dispatching requests. It is accurate to the second. This value is valid only when associated with Teradata SQL and MONITOR partition sessions. This value is NULL when it is returned for all other sessions. |
| XActCount | Number of explicit and implicit transactions executed by the session. This value is valid only when returned for Teradata SQL sessions, and is NULL for all other partition sessions. For this value, you must make a request for data before completion of the first collection period that follows either a system outage or a change in the ResMonitor rate. |
| ReqCount | Number of requests (Tequel Start Request [TSR] messages) initiated by the session. This value is NULL when a request for data is made before completion of the first collection period following either a system outage or change in the ResMonitor rate. |
| ReqCacheHits | Number of times that this session processed a request using information from the Teradata SQL Parser request cache, specifically, how many times there was a request cache hit. This value is valid only for Teradata SQL sessions, and is NULL for all other partition sessions. This value is also NULL when a request for data is made before completion of the first collection period following either a system outage or a change in the ResMonitor rate. |
| AMPState | Current state of the associated session in AMP vprocs in decreasing priority:
This value is NULL when a request for data is made before completion of the first collection period following either a system outage or a change in the SesMonitorLoc or SesMonitorSys rate. |
| AMPCPUSec | Current elapsed CPU time, in seconds, used on all AMPs by the associated session for executing requests. For example, for Teradata SQL requests, this is the time spent by the database actively working or rolling back an aborted transaction. This does not include any PDE CPU time spent handling database requests. This value is NULL when a request for data is made before completion of the first collection period following either a system outage or a change in the ResMonitor rate. |
| AMPIO | Current number of logical Reads and Writes issued across all AMPs by the associated session. This value is NULL when a request for data is made before completion of the first collection period following either a system outage or a change in the ResMonitor rate. |
| ReqSpool | Current spool used by current request across all AMPs, expressed as a number of bytes. This value is NULL when a request for data is made before completion of the first collection period following either a system outage or a change in the ResMonitor rate. |
| Blk1HostId, Blk2HostId, Blk3HostId |
Logical host ID of a session causing a block. This value is derived from equating the transactions causing a database lock conflict to the sessions that issued those transactions. The Blk_x_HostId in combination with Blk_x_SessNo uniquely identifies the session that is causing a block. A valid value is not returned if an inactive Archive/Recovery job is causing the lock conflict, because no session is logged in. This value is NULL if:
If the Blk_x_HostId, Blk_x_SessNo, and Blk_x_UserID values all return as NULLs and AMPState is BLOCKED, a Host Utility (HUT) lock left over after the session holding the lock was aborted or logged off. The lock was never released, and no blocking information is available because the session no longer exists. Use the Show Locks utility to obtain the user name that placed the HUT lock. For more information, see Teradata Vantage™ - Database Utilities , B035-1102 . |
| Blk1SessNo, Blk2SessNo, Blk3SessNo |
Number of the session causing a block. This value is derived from associating the transactions causing a lock conflict to the sessions that issued those transactions. The Blk_x_SessNo in combination with Blk_x_HostId uniquely identifies the session causing a block. This information is unavailable if an inactive Archive/Recovery job is causing the lock conflict. This value is NULL if:
If the Blk_x_HostId, Blk_x_SessNo, and Blk_x_UserID values all return as NULLs and AMPState is BLOCKED, a host utility lock left over after the session holding the lock was aborted or logged off. The lock was never released, and no blocking information is available because the session no longer exists. Use the Show Locks utility to obtain the user name that placed the HUT lock. For more information, see Teradata Vantage™ - Database Utilities , B035-1102 . |
| Blk1UserId, Blk2UserId, Blk3UserId |
ID of the user or host utility job preventing the session from being granted a lock. This information is especially important when an Archive/Recovery job is holding the lock, because the user ID is the only information available about who placed the lock. This value is NULL if:
If the Blk_x_HostId, Blk_x_SessNo, and Blk_x_UserID values all return as NULLs and AMPState is BLOCKED, a host utility lock left over after the session holding the lock was aborted or logged off. The lock was never released, and no blocking information is available because the session no longer exists. Use the Show Locks utility to obtain the user name that placed the HUT lock. For more information, see Teradata Vantage™ - Database Utilities , B035-1102 . |
| Blk1Lmode, Blk2Lmode, Blk3Lmode |
Mode (severity) of the lock involved in causing a block:
Locks are reported in decreasing order of severity because removing the most severe lock conflict may eliminate the source of the lock conflict. This value is NULL if:
A session may be blocked by either a granted lock or an ungranted lock that precedes the blocked lock in the queue and is in conflict with the lock requested by this blocked session. For information on whether the lock is granted, see the MONITOR SESSSION fields: Blk1Status, Blk2Status, and Blk3Status.
|
| Blk1Otype, Blk2Otype, Blk3Otype |
Type of object whose lock is causing the session described by the associated row to be blocked:
However, this object is not necessarily the type of object the blocked session is trying to access. For example, if the session is requesting a row hash lock, the blocking object could be a database, table, or row hash. This value is NULL if:
For a Table T, it is possible for User A to block User B with a table level lock on Table T on AMP_1 and with a Row Hash Level lock on that same Table T on AMP_2. When that occurs, the only lock conflict reported is that User B is blocked by User A on a table. |
| Blk1ObjDBID, Blk2ObjDBID, Blk3ObjDBID |
Unique ID of the database object over which a lock conflict is preventing the session from being granted a lock. Within the database system, Database ID is equivalent to User ID. Typically, User ID is used when the associated record is known to be a user name, and Database ID is used when the associated record is known to be a database. However, Database ID can identify either a user or database name. This value is NULL if:
|
| Blk1ObjTID, Blk2ObjTID, Blk3ObjTID |
Unique ID of the table object over which a lock conflict is preventing the session from being granted a lock. This value is NULL if:
|
| Blk1Status, Blk2Status, Blk3Status |
Status of lock causing a block:
This value is NULL if:
A lock request may be blocked by either a granted lock or an ungranted lock that precedes the blocked lock request in the queue and is in conflict with it.
A status of Waiting has a higher priority than that of Granted when there is more than one lock involved. For example, for a given object and a given session, a session that is blocked by a Waiting lock on one AMP and a Granted lock on another AMP has Waiting reported as its status. |
| MoreBlockers | Indicator of more lock conflicts:
This value is NULL if:
|
| LogonSource | Logon source information. At logon time, this information is optionally supplied by the Teradata Director Program or the Gateway to further identify the physical or logical location of the session, the logon user name, and the interface under which the session was initiated. (For example, the data string may include DBC as the user ID and BTEQ as the interface.) A two-byte value precedes the LogonSource data to indicate the length of the string. The length value is zero if LogonSource is NULL.
For a list of the commonly seen LogonSource string application names, see Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 . |
| HotAmp1CPU | CPU time of the highest CPU utilized AMP during the collection interval. This value is NULL if the request is made before the collection period expires. |
| HotAmp2CPU | CPU time of the second highest CPU utilized AMP during the collection interval. This value is NULL if the request is made before the collection period expires. |
| HotAmp3CPU | CPU time of the third highest CPU utilized AMP during the collection interval. This value is NULL if the request is made before the collection period expires, and if there are only two AMPs on the system. |
| HotAmp1CPUId | Vproc ID of the highest CPU utilized AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires. |
| HotAmp2CPUId | Vproc ID of the second highest CPU utilized AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires. |
| HotAmp3CPUId | Vproc ID of the third highest CPU utilized AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires, and if there are only two AMPs in the system. |
| HotAmp1IO | I/O count of the highest I/O utilized AMP during the collection interval. This value is NULL if the request is made before the collection period expires. |
| HotAmp2IO | I/O count of the second highest I/O utilized AMP during the collection interval. This value is NULL if the request is made before the collection period expires. |
| HotAmp3IO | I/O count of the third highest I/O utilized AMP for the last collection interval. This value is NULL if the request is made before the collection period expires, and if there are only two AMPs in the system. |
| HotAmp1IOId | Vproc ID of the highest I/O utilized AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires. |
| HotAmp2IOId | Vproc ID of the second highest I/O utilized AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires. |
| HotAmp3IOId | Vproc ID of the third highest I/O utilized AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires, and if there are only two AMPs in the system. |
| LowAmp1CPU | CPU time of the lowest CPU utilized AMP during the collection interval. This value is NULL if the request is made before the collection period expires. |
| LowAmp2CPU | CPU time of the second lowest CPU utilized AMP during the collection interval. This value is NULL if the request is made before the collection period expires. |
| LowAmp3CPU | CPU time of the third lowest CPU utilized AMP during the collection interval. This value is NULL if the request is made before the collection period expires, and if there are only two AMPs on the system. |
| LowAmp1CPUId | Vproc ID of the lowest CPU utilized AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires. |
| LowAmp2CPUId | Vproc ID of the second lowest CPU utilized AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires. |
| LowAmp3CPUId | Vproc ID of the third lowest CPU utilized AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires, and if there are only two AMPs on the system. |
| LowAmp1IO | I/O count of the lowest I/O utilized AMP during the collection interval. This value is NULL if the request is made before the collection period expires. |
| LowAmp2IO | I/O count of the second lowest I/O utilized AMP during the collection interval. This value is NULL if the request is made before the collection period expires. |
| LowAmp3IO | I/O count of the third lowest I/O utilized AMP during the collection interval. This value is NULL if the request is made before the collection period expires, and if there are only two AMPs on the system. |
| LowAmp1IOId | Vproc ID of the lowest I/O utilized AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires. |
| LowAmp2IOId | Vproc ID of the second lowest I/O utilized AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires. |
| LowAmp3IOId | Vproc ID of the third lowest I/O utilized AMP for the last session collection interval. This value is NULL if the request is made before the collection period expires, and if there are only two AMPs on the system. |
| AvgAmpCPUSec | Average AMP CPU utilization for the last session collection interval. The average is calculated as the sum of CPU utilization for all amps participating divided by the number of online AMPs. This value is NULL if the request is made before the collection period expires. |
| AvgAmpIOCnt | Average AMP I/O utilization for the last session collection interval. The average is calculated as the sum of I/O utilization for all AMPs participating divided by the number of online AMPs. This value is NULL if the request is made before the collection period expires. |
| AmpCount | Current number of AMPs currently executing on the associated node. |
| TempSpaceUsg | Total amount, in bytes, of temporary space used by the session. This value is NULL if the session did not materialize any temporary tables. |
| ReqStartTime | Date and time of the current request on the session started. It is usually formatted for display as yyyy/mm/dd 99:99:99.99, which represents the year, month, day hours: minutes: seconds. |
| ReqCPU | Total CPU usage by the current SQL request on the session on all AMPs. This value contains proper request-level statistics for DBC/SQL sessions running SQL requests only. Ignore the value returned in this field for other types of sessions, such as DBC/SQL sessions linked to a utility job. This field is equivalent to the MONITOR SESSION RequestAmpCPU field. |
| ReqIO | Total number of accesses by the current SQL request for the session on all AMPs. This value contains proper request-level statistics for DBC/SQL sessions running SQL requests only. Ignore the value returned in this field for other types of sessions, such as DBC/SQL sessions linked to a utility job. This field is equivalent to the MONITOR SESSION RequestAmpI/O field. |
| ReqNo | Active request number. If no request is running, a value of zero or NULL is displayed in indicator mode. In some rare cases, in the very early phase of a request in parsing state, when PEState = PARSING, the request number may not be available and returned as zero or NULL. The active request number will be available on the next collection. |
| WlcId | Workload ID associated with the specified request. |
| DontReclassifyFlag | Flag indicating that the next request on the session will not be classified but will use the workload ID (WlcId) already assigned to the session. This will occur if this is a utility session or a WlcId was assigned to the session using the TDWMAssignWD function or the TDWM WD ASSIGNMENT request. For more information on these APIs, see Teradata Dynamic Workload Management APIs: PM/APIs. |
| ProxyUser | Name of the proxy user in a trusted session. |
| CPUDecayLevel | Current most severe decay level as reached due to CPU usage. Nodes can be at different levels of decay (for example, 0, 1, or 2).
|
| IODecayLevel | Current most severe decay level as reached due to I/O usage. Nodes can be at different levels of decay (for example, 0, 1, or 2).
|
| TacticalCPUException | Number of nodes that encountered a CPU exception. |
| TacticalIOException | Number of nodes that encountered an I/O exception. |
| ReqIOKB | Total logical I/O usage in KB. |
| ReqPhysIO | Number of physical I/Os. |
| ReqPhysIOKB | Physical I/O usage in KB. |
| ReqStepsCompletedCnt | Count of completed steps for the current request. No change in ReqStepsCompletedCnt from the previous Monitor Session collection indicates no new steps completed. |
| RedriveProtection | Redrive protection type:
|
| CurrentRedriveParticipation |
Indicates if the session is participating in Redrive. Sessions that use Redrive can enable or disable the functionality using the REDRIVE reserved query band. Possible values:
|
| ReqRedriveSpoolSpace | Persistent spool space for the current request. |
| BlockerSessionCnt | Total number of blocker sessions for the session. Unlike the MONITOR SESSION request, this field returns only the first three blocker sessions in the record parcel. There are no additional blocker sessions if there are more than three blocker sessions. |
| ReqTblOpBytesIn | The total number of bytes transferred into the database from a foreign server for the current request through one or more table operators. The request may involve one or multiple table operator executions. The ReqTblOpBytesIn output parameter shows bytes transferred across all invocations within the request.
|
| ReqTblOpBytesOut | The total number of bytes transferred out of the database and into a foreign server for the current request through one or more table operators. The request may involve one or multiple table operator executions. The ReqTblOpBytesOut output parameter shows bytes transferred across all invocations within the request.
|
| ProxyUserId | The user ID charged for SPOOL and TEMP space if being charged to the proxy user. |
| ZoneId | The unique identifier of the zone. |
| ReqHotAmpCPU | The CPU time of the highest CPU utilized AMP during the life of the current request on the session. |
| ReqHotAmpCPUId | Vproc ID of the highest CPU utilized AMP for the current request. |
| ReqHotAmpIO | I/O count of the highest I/O utilized AMP during the life of the current request on the session. |
| ReqHotAmpIOId | Vproc ID of the highest I/O utilized AMP for the current request. |
| ReqInvolvedAMPCnt | The number of AMPs involved in processing the current request. |
| ReqFirstRespTime | Date and time that the first response of the current request on the session is ready. The response may be held to meet the TASM Minimum Response Time. |
| ReqLocalQueryStatus | The current state of the Unified Data Architecture (UDA) query. |
| ReqRemoteHostId | Host ID of the remote system. |
| ReqRemoteSessionId | Session ID of the executing remote query. |
| ReqRemoteRequestId | Request ID of the executing remote query. |
| ReqRemoteQueryId | Query ID of the executing remote query. |
| ReqHotAmpSpool | Spool value of the highest spool utilized AMP during the life of the current request on the session. This value is NULL if no request is running on the session. |
| ReqHotAmpSpoolId | Vproc ID of the highest spool utilized AMP for the current request. This value is NULL if no request is running on the session. |
| ReqMapNo | Map number for the largest map the request is using. |
| ReqMaxNumMapAMPs | Number of AMPs in the largest contiguous map used by the request. |
| ReqMinNumMapAMPs | Number of AMPs in the smallest contiguous map used by the request. |
| ReqSysDefNumMapAMPs | Number of AMPs in the system-default map used by the request. |
| ReqRemoteHostIp | Host IP address of the remote system. |
| ReqServerName | Name of the foreign server. |
| ReqFlexReleased | The TDWM Flex Throttle feature detects available system resources, overrides existing workload throttle limits and automatically releases queries from the delay queue. This minimizes the DBA manually managing the TASM delay queue. Note, only Workload throttles are overridden; all System level throttles are still honored. Return values:
|
Example: Using MonitorMySessions
select * from table (monitormysessions()) as t1;
*** Query completed. 2 rows found. 116 columns returned.
*** Total elapsed time was 1 second.
HostId 1
SessionNo 1006
LogonPENo 30718
RunVprocNo 30718
PartName DBC/SQL
PEstate DISPATCHING
LogonTime 2016/04/29 11:26:46.00
UserId 1028
LSN 0
UserName JCK
UserAccount DBC
PECPUsec 2.40000000000000E-002
XActCount 1.00000000000000E 000
ReqCount 2.00000000000000E 000
ReqCacheHits 0.00000000000000E 000
AMPState ACTIVE
AMPCPUSec 1.12050000000000E 003
AMPIO 3.49605000000000E 005
ReqSpool 4.39090831360000E 010
Blk1HostId 0
Blk1SessNo 0
Blk1UserId 0
Blk1Lmode
Blk1Otype
Blk1ObjDBID 0
Blk1ObjTID 0
Blk1Status
Blk2HostId 0
Blk2SessNo 0
Blk2UserId 0
Blk2Lmode
Blk2Otype
Blk2ObjDBID 0
Blk2ObjTID 0
Blk2Status
Blk3HostId 0
Blk3SessNo 0
Blk3UserId 0
Blk3Lmode
Blk3Otype
Blk3ObjDBID 0
Blk3ObjTID 0
Blk3Status
MoreBlockers
LogonSource (TCP/IP) f1a3 153.64.183.39 MYSYSTEM 9964
HotAmp1CPU 3.33200000000000E 001
HotAmp2CPU 3.32880000000000E 001
HotAmp3CPU 3.32880000000000E 001
HotAmp1CPUId 2
HotAmp2CPUId 1
HotAmp3CPUId 0
HotAmp1IO 1.03620000000000E 004
HotAmp2IO 1.03460000000000E 004
HotAmp3IO 1.03270000000000E 004
HotAmp1IOId 2
HotAmp2IOId 3
HotAmp3IOId 1
LowAmp1CPU 3.32760000000000E 001
LowAmp2CPU 3.32880000000000E 001
LowAmp3CPU 3.32880000000000E 001
LowAmp1CPUId 3
LowAmp2CPUId 1
LowAmp3CPUId 0
LowAmp1IO 1.03160000000000E 004
LowAmp2IO 1.03270000000000E 004
LowAmp3IO 1.03460000000000E 004
LowAmp1IOId 0
LowAmp2IOId 1
LowAmp3IOId 3
AvgAmpCPUSec 3.32930000000000E 001
AvgAmpIOCnt 1.03377500000000E 004
AmpCount 4
TempSpaceUsg 0.00000000000000E 000
ReqStartTime 2016/04/29 11:26:46.00
ReqCPU 1.12050000000000E 003
ReqIO 3.49605000000000E 005
ReqNo 3
WlcId 0
DontReclassifyFlag 255
ProxyUser
CPUDecayLevel 0
IODecayLevel 0
TacticalCPUException 0
TacticalIOException 0
ReqIOKB 4.34172850000000E 007
ReqPhysIO 9.09660000000000E 004
ReqPhysIOKB 3.97004920000000E 007
ReqStepsCompletedCnt 9
RedriveProtection MN
CurrentRedriveParticipation F
ReqRedriveSpoolSpace 0.00000000000000E 000
BlockerSessionCnt 0
ReqTblOpBytesIn 0.00000000000000E 000
ReqTblOpBytesOut 0.00000000000000E 000
ProxyUserId 0
ZoneId 0
ReqHotAmpCPU 2.80292000000000E 002
ReqHotAmpCPUId 3
ReqHotAmpIO 8.75320000000000E 004
ReqHotAmpIOId 3
ReqInvolvedAMPCnt 4
ReqFirstRespTime 0000/00/00 00:00:00.00
ReqLocalQueryStatus 0
ReqRemoteHostId 0
ReqRemoteSessionId 0
ReqRemoteRequestId 0
ReqRemoteQueryId 0.00000000000000E 000
ReqHotAmpSpool 1.09939000320000E 010
ReqHotAmpSpoolId 3
ReqMapNo 0
ReqMaxNumMapAMPs 0
ReqMinNumMapAMPs 0
ReqSysDefNumMapAMPs 0
ReqRemoteHostIp
ReqServerName
ReqFlexReleased 0
HostId 1
SessionNo 1010
LogonPENo 30719
RunVprocNo 30719
PartName DBC/SQL
PEstate DISPATCHING
LogonTime 2016/04/29 11:45:40.00
UserId 1028
LSN 0
UserName JCK
UserAccount DBC
PECPUsec 3.00000000000000E-001
XActCount 1.00000000000000E 000
ReqCount 3.00000000000000E 000
ReqCacheHits 0.00000000000000E 000
AMPState ACTIVE
AMPCPUSec 4.00000000000000E-003
AMPIO 0.00000000000000E 000
ReqSpool 0.00000000000000E 000
Blk1HostId 0
Blk1SessNo 0
Blk1UserId 0
Blk1Lmode
Blk1Otype
Blk1ObjDBID 0
Blk1ObjTID 0
Blk1Status
Blk2HostId 0
Blk2SessNo 0
Blk2UserId 0
Blk2Lmode
Blk2Otype
Blk2ObjDBID 0
Blk2ObjTID 0
Blk2Status
Blk3HostId 0
Blk3SessNo 0
Blk3UserId 0
Blk3Lmode
Blk3Otype
Blk3ObjDBID 0
Blk3ObjTID 0
Blk3Status
MoreBlockers
LogonSource (TCP/IP) f1f0 153.64.183.39 MYSYS 8348 JK
HotAmp1CPU 4.00000000000000E-003
HotAmp2CPU 0.00000000000000E 000
HotAmp3CPU 0.00000000000000E 000
HotAmp1CPUId 2
HotAmp2CPUId 0
HotAmp3CPUId 0
HotAmp1IO 0.00000000000000E 000
HotAmp2IO 0.00000000000000E 000
HotAmp3IO 0.00000000000000E 000
HotAmp1IOId 0
HotAmp2IOId 0
HotAmp3IOId 0
LowAmp1CPU 0.00000000000000E 000
LowAmp2CPU 0.00000000000000E 000
LowAmp3CPU 0.00000000000000E 000
LowAmp1CPUId 3
LowAmp2CPUId 1
LowAmp3CPUId 0
LowAmp1IO 0.00000000000000E 000
LowAmp2IO 0.00000000000000E 000
LowAmp3IO 0.00000000000000E 000
LowAmp1IOId 2
LowAmp2IOId 3
LowAmp3IOId 1
AvgAmpCPUSec 1.00000000000000E-003
AvgAmpIOCnt 0.00000000000000E 000
AmpCount 4
TempSpaceUsg 0.00000000000000E 000
ReqStartTime 2016/04/29 11:45:44.00
ReqCPU 4.00000000000000E-003
ReqIO 0.00000000000000E 000
ReqNo 3
WlcId 0
DontReclassifyFlag 255
ProxyUser
CPUDecayLevel 0
IODecayLevel 0
TacticalCPUException 0
TacticalIOException 0
ReqIOKB 0.00000000000000E 000
ReqPhysIO 0.00000000000000E 000
ReqPhysIOKB 0.00000000000000E 000
ReqStepsCompletedCnt 0
RedriveProtection MN
CurrentRedriveParticipation F
ReqRedriveSpoolSpace 0.00000000000000E 000
BlockerSessionCnt 0
ReqTblOpBytesIn 0.00000000000000E 000
ReqTblOpBytesOut 0.00000000000000E 000
ProxyUserId 0
ZoneId 0
ReqHotAmpCPU 4.00000000000000E-003
ReqHotAmpCPUId 2
ReqHotAmpIO 0.00000000000000E 000
ReqHotAmpIOId 0
ReqInvolvedAMPCnt 4
ReqFirstRespTime 0000/00/00 00:00:00.00
ReqLocalQueryStatus 0
ReqRemoteHostId 0
ReqRemoteSessionId 0
ReqRemoteRequestId 0
ReqRemoteQueryId 0.00000000000000E 000
ReqHotAmpSpool 0.00000000000000E 000
ReqHotAmpSpoolId 0
ReqMapNo 0
ReqMaxNumMapAMPs 0
ReqMinNumMapAMPs 0
ReqSysDefNumMapAMPs 0
ReqRemoteHostIp
ReqServerName
ReqFlexReleased 0