Monitor Flow Control

Monitor flow control state:

Refer table DBC.ResUsageSAWT to monitor flow control state also consider snapshot interval (10 mins default)


Column Definition of ResUsageSAWT

TheDate: Returns the calendar date on which the event was logged.

NodeID: Returns Node ID.

TheTime: Returns the time of the event in theform hh:mm:ss.

VprId: Identifies the AMP vproc.

Active: The Active controls whether or not the rows will be logged to the resource usage tables if Active Row Filter Mode is enabled.

MailBoxDepth: Current depth of the AMP work mailbox at the end of the period.
High AWT usage does not necessarily mean that the system is in flow control. Only if all AMP worker tasks are in use and the message queues are filled, flow control state is initiated.
While often MailBoxDepth is > 0 if the AMP is in flow control state, it’s not always like this because this measure presents only the size of the message queue at the end of each snapshot interval. It could be, that the AMP was in flow control state during the snapshot interval, but the queue is empty at the end of the interval. Keep this in mind.

FlowControlled: Specifies if an AMP is in flow control. If the value is non-zero, then the AMP is in flow control.

FlowCtlCnt: Returns the number of times this log period that the node entered the flow control state from a non-flow controlled state.

FlowCtlTime: Returns total time, in milliseconds, that an AMP is in flow control.

InuseMax: Returns maximum number of AMP WorkerTasks in use at any one time during the log period.By checking the the InUseMax measure, you will see how the total system workload is consuming AMP worker tasks, which means: how loaded your system is

Available: Returns the number of unreserved AMP Worker Tasks from the pool that are not being used at the end of the interval.
Available shows the number of unused AMP worker tasks at the end of the snapshot interval. A consecutive number of snapshots having Available = 0 means that your system ran out of AMP worker tasks over a longer time frame.

AvailableMin: Returns the minimum number of unreserved AMP Worker Tasks available in the pool for each AMP for the logged period. In other words, It is measuring the minimum number of AMP worker tasks having been used at one point in time during the snapshot interval

AwtLimit: Returns the current setting for AMP Worker Task (for example, 80, 100, or so on) in the DBS Control MaxLoadAWT field.

WorkTypeInuse00 - 15: Returns current number of AMP WorkerTasks in use for each work type.

WorkTypeMax00 - 15: Returns maximum number of AMP WorkerTasks in use at one time during the log period for each work type for the VprId vproc.

SELECT
TheDate
,TheTime
,Secs
,VPRID as TheAMP
,MailBoxDepth
,FlowCOntrolled
,FlowCtlCnt
,InUseMax
,Available
,AvailableMin
,AWTLimit
,WorkTypeMax00 AS DispatcherStep
,WorkTypeMax01 AS Spawned_Level1
,WorkTypeMax02 AS Spawned_Level2
,WorkTypeMax03 AS InternalWork
,WorkTypeMax04 AS Recovery
,WorkTypeMax08 AS ExpeditedDispatcherStep
,WorkTypeMax09 AS ExpeditedSpawned_Level1
,WorkTypeMax10 AS ExpeditedSpawned_Level2
,WorkTypeMax12 AS AbortStep
,WorkTypeMax13 AS SpawnedWorkAbort
,WorkTypeMax14 AS UrgentInternalWork
,WorkTypeMax15 AS MostUrgentInterbalWork
FROM DBC.ResUsageSAWT
WHERE thedate = date -2
and FlowCtlTime > 0 ;