x_cons <database_name> show sleep
Shows statistics that indicate how long the individual user kernel threads were idle. The values can be seen if the time measurement is enabled.
Measured interval time: 01:13 (dd:hh:mm:ss) ------------------------------------------------------------------------------- Task Idle Time Idle Time I/O Wait I/O Wait RunQueue Group Intervals dd:hh:mm:ss Intervals dd:hh:mm:ss Length ------------------------------------------------------------------------------- UKT2 25925 58 80.04% 0 00 0.00% 0 UKT4 12 18 50.15% 0 00 0.00% 0 UKT6 30148 58 82.87% 0 00 0.00% 0 UKT7 196 01:03 94.15% 0 00 0.00% 0 UKT8 197 08 12.69% 0 00 0.00% 0 UKT9 151 08 11.90% 0 00 0.00% 0 ------------------------------------------------------------------------------- Task CSwitch CSwitch Scheduling Scheduling Group Intervals hh:mm:ss:mmm Intervals hh:mm:ss:mmm State ------------------------------------------------------------------------------- UKT2 0 00:000 51849 00:075 Sleeping UKT4 0 00:000 23 00:000 Sleeping UKT6 396923 00:080 431057 00:155 Sleeping UKT7 281 00:000 490 00:000 Sleeping UKT8 241818 00:128 242248 00:119 Sleeping UKT9 237934 00:124 238328 00:118 Sleeping
- Task Group: UKT Id that the task belongs to.
- Idle Time Intervals: Number of Idle times of the current task inside the dispatcher
- I/O Wait Intervals: Number of waits for I/O completion
- I/O Time dd:hh:mm:ss : Wait duration
- RunQueue Length: Length of the run queue in the dispatcher
- CSwitch Intervals: Number of context switches
- CSwitch hh:mm:ss:mmm : Duration of context switches, this value can be seen if the time measurement is enabled.
- Scheduling Intervals: Number of scheduling inside the dispatcher including the time to scan the queues ( run queue, external queue, com queue)
- Scheduling hh:mm:ss:mmm: Duration spent in scheduling inside the dispatcher.
Analysis Tips
Particular attention should be paid to the columns, "Idle Time Intervals" and "RunQueue Length". The growth of the numbers in "RunQueue Length" indicates that within a UKT database tasks are waiting for CPU allocation, but another task within the same UKT holds the CPU. These waiting tasks can be displayed with "x_cons show runnable" command. If "Idle Time Intervals" does not change then the UKT is always working, it indicates a long running database procedure which does not give control back to the database kernel and cannot be dispatched. This can cause performance problems, since all other tasks that run in the same UKT cannot work. Time measurement has to be enabled to see the values for "CSwitch" and "CSwitch hh:mm:ss:mmm".