db2top Bottlenecks Screen (Part 2)

The db2top Bottlenecks screen lets us see who the biggest consumers of each resource are and how much of the resource they are consuming. These top consumers can often cause such resources to become bottlenecked, so the Bottlenecks screen gives you an easy way to find the cause of such bottlenecks so that you can take action. Today, we will examine each of the individual resources for which the bottleneck screen reports the biggest consumer and how much it consumes.

While the minimum size of the db2top screen is 24 rows in height, not all resources can be accommodated at this height. A screen of 31 rows or taller fits all resource types, as shown below:

The following table lists the resources and percentage and absolute usage along with the calculation that is performed to determine how much of the resource is being consumed:

Server Resource	Resource Usage	Resource Value	Calculation
Cpu	Delta mode: The percentage of total statement CPU consumption that is caused by the application whose statements are consuming the most CPU. Actual mode: The percentage of total session CPU consumption that is caused by the application that is consuming the most CPU.	Delta mode: The statement CPU consumption of the application whose statements are consuming the most CPU. Actual mode: The session CPU consumption of the application that is consuming the most CPU.	Delta mode: stmt_usr_cpu_time + stmt_sys_cpu_time Actual mode: agent_usr_cpu_time + agent_sys_cpu_time
SessionCpu	The percentage of total session CPU consumption that is caused by the application that is consuming the most CPU.	The session CPU consumption of the application that is consuming the most CPU.	agent_usr_cpu_time + agent_sys_cpu_time
IO r/w	Delta mode: The percentage of total I/O caused by the application that is consuming the most I/O. Actual mode: The percentage of total I/O caused by the application that is consuming the most I/O, as measured since the last snapshot reset or database activation.	Delta mode: The I/O consumption of the application that is consuming the most I/O, as measured during the interval. Actual mode: The I/O consumption of the application that is consuming the most I/O, as measured since the last snapshot reset or database activation.	pool_data_l_reads + pool_index_l_reads + pool_temp_data_l_reads + pool_temp_index_l_reads + pool_data_writes + pool_index_writes
Memory	The percentage of total memory caused by the application that is consuming the most memory.	The memory consumption of the application that is consuming the most memory.	pool_cur_size
Locks	The percentage of the total number of locks held by all applications that are held by the application that is holding the most locks.	The number of locks held by the application that is holding the most locks.	locks_held
Sorts	Currently reports incorrect data. APAR number forthcoming.	Currently reports incorrect data. APAR number forthcoming.
Sort Time	The percentage of the total time spent sorting by all applications that was spent by the application that is spending the most time sorting.	The time spent sorting by the application that is spending the most time sorting.	total_sort_time
Log Used	Of the total amount of log space used in the current units of work of all applications, the percentage used by the application that is using the most log space.	The amount of log space used in the current unit of work of the application that is using the most log space.	uow_log_space_used
Overflows	The percentage of the total number of sort and hash join overflows caused by the application that has had the most sort and hash join overflows.	The number of sort and hash join overflows of the application that has had the most sort and hash join overflows.	sort_overflows + hash_join_overflows
RowsRead	Delta mode: Of the total number of rows read by all applications, the percentage that were read by the application that read the most rows per second during the interval. Actual mode: Of the total number of rows read by all applications, the percentage that were read by the application that read the most rows, as measured since the last snapshot reset or database activation.	Delta mode: The number of rows read per second by the application that read the most rows per second during the interval. Actual mode: The number of rows read by the application that read the most rows, as measured since the last snapshot reset or database activation.	rows_read
RowsWritten	Delta mode: Of the total number of rows written (inserted, deleted, or updated) by all applications, the percentage that were written by the application that wrote the most rows during the interval. Actual mode: Of the total number of rows written (inserted, deleted, or updated) by all applications, the percentage that were written by the application that wrote the most rows, as measured since the last snapshot reset or database activation.	Delta mode: The number of rows written (inserted, deleted, or updated) per second by the application that wrote the most rows per second during the interval. Actual mode: The number of rows written (inserted, deleted, or updated) by the application that wrote the most rows, as measured since the last snapshot reset or database activation.	rows_written
TQ r/w	Delta mode: The percentage of the total amount of table queue I/O per second caused by the application that is performing table queue I/O at the highest rate during the interval. Actual mode: The percentage of the total amount of table queue I/O caused by the application that is performing the most table queue I/O, as measured since the last snapshot reset or database activation.	Delta mode: The amount of table queue I/O per second performed by the application that is performing table queue I/O at the highest rate during the interval. Actual mode: The amount of table queue I/O performed by the application that is performing the most table queue I/O, as measured since the last snapshot reset or database activation.	tq_rows_written + tq_rows_read
MaxQueryCost	Of the sum of all the maximum estimated query costs of all applications, the percentage that came from the maximum estimate query cost of the application that had the highest maximum estimated query cost.	The maximum estimated query cost of the application that had the highest maximum estimated query cost of all applications.	max(query_cost_estimate), where max() computes the maximum for the application
XDAPages	Delta mode: Of the total number of disk pages consumed by XML storage object (XDA) data across all applications, the percentage consumed by the application that consumed the most XML storage object data during the interval. Actual mode: Of the total number of disk pages consumed by XML storage object (XDA) data across all applications, the percentage consumed by the application that consumes the most XML storage object data, as measured since the last snapshot reset or database activation.	Delta mode: The number of disk pages consumed by the XML storage object (XDA) data of the application that consumes the most XML storage object data during the interval. Actual mode: The number of disk pages consumed by the XML storage object (XDA) data of the application that consumes the most XML storage object data, as measured since the last snapshot reset or database activation.	xda_object_pages

The Bottlenecks screen responds to the ‘k’ key to switch between deltas and actuals. It is not affected by the ‘i’ key to switch between showing active objects and all objects and it is not affected by the ‘X’ key to turn extended mode on and off.

For other articles on db2top at this site, see the main db2top page.