Identifying Subsection Bottlenecks in DB2 DPF Queries
imaione 2700007WSP Visits (10918)
Queries in partitioned (DPF) environments generally have multiple subsections executing concurrently, with each subsection executing on one or more database partitions. As a result when a DPF query is performing poorly it can be important to identify which subsection or subsections may be holding up the progress of the query. For long running DPF queries, explain (db2exfmt) information and global application snapshots provide very useful information which can be used for this purpose. These specific techniques do not necessarily apply to SMP parallelized or BLU queries.
To use this method, it is important to take global application snapshots, as this is necessary in order to get complete information on the status of the subsections that are executing on each partition. To use the snapshot and db2exfmt information, it is necessary to identify which subsections in the snapshot output are incurring significant cost, and then associate those subsections with parts of the explain output.
1) Identifying subsections:
In the plan graph shown in db2exfmt output, TQ operators (BTQ, DTQ, MDTQ, etc) are the operators which separate different subsections. For example in the following plan graph,
NLJOIN 17 is part of one subsection
HSJOIN 19, HSJOIN 20, HSJOIN 21, TBSCAN 22, TBSCAN 23, TBSCAN 24, HSJOIN 25, TBSCAN 26, SORT 27, HSJOIN 28, FETCH 33, RIDSCN 34, SORT 35, IXSCAN 36 are all part of the same subsection
TBSCAN 30 is part of its own subsection