Analysis of section actuals information in explain output
Section actuals, when available, are displayed in different parts of the explain output. Where to find section actuals information, operator details, and object statistics in explain output is described here.
Section actuals in db2exfmt command graph output
If explain actuals are available, they are displayed in the graph under the estimated rows. Graph output includes actuals only for operators, not for objects. NA (not applicable) is displayed for objects in the graph.
Rows
Rows Actual
RETURN
( 1)
Cost
I/O
|
3.21948 << The estimated rows that are used by the optimizer
301 << The actuals rows that are collected in run time
DTQ
( 2)
75.3961
NA
|
3.21948
130
HSJOIN
( 3)
72.5927
NA
/--+---\
674 260
220 130
TBSCAN TBSCAN
( 4) ( 5)
40.7052 26.447
NA NA
| |
337 130
NA NA << Graph output does not include actuals for objects
TABLE: FF TABLE: FF
T1 T2
In a partitioned database environment, the cardinality that is displayed in the graph is the average cardinality for the database partitions where the actuals are collected. The average is displayed because that is the value that is estimated by the optimizer. The actual average is a meaningful value to compare against the estimated average. In addition, a breakdown of section actuals per database partition is provided in the operator details output. You can examine these details to determine other information, such as total (across all partitions), minimum, and maximum.
Operator details in db2exfmt command output
The actual cardinality for an operator is displayed in the stream section following the line containing Estimated number of rows (Actual number of rows in the explain output). In a partitioned database environment, if the operator is running on more than one database member, the actual cardinality that is displayed is the average cardinality for the environment. The values per database partition are displayed under a separate section, Explain Actuals. This section is shown only for a partitioned database environment, but not in the serial mode. If the actuals are not available for a particular database partition, NA is displayed in the list of values per database partition next to the partition number. Actual number of rows in the section Output Streams is also shown as NA.
9) UNION : (Union)
Cumulative Total Cost: 10.6858
Cumulative First Row Cost: 9.6526
Arguments:
---------
UNIONALL: (UnionAll Parameterized Base Table)
DISJOINT
Input Streams:
-------------
5) From Operator #10
Estimated number of rows: 30
Actual number of rows: 63
Partition Map ID: 3
7) From Operator #11
Estimated number of rows: 16
Actual number of rows: 99
Partition Map ID: 3
Output Streams:
--------------
8) To Operator #8
Estimated number of rows: 30
Actual number of rows: 162
Partition Map ID: 3
Explain Actuals: << This section is shown only in a partitioned database environment
----------------
DB Partition number Cardinality
------------------- -----------
1 193
2 131
Object statistics in db2exfmt command output
The explain output includes statistics for each object that is used in the access plan. For partitioned tables and indexes, the statistics are per data partition. In a partitioned database environment or Db2® pureScale® environment, the statistics are per member. If the statistics are not available for a particular member, NA is displayed in the values list for that member next to the member number.
Runtime statistics for objects Used in Access Plan:
-----------------------------------------------------
Schema: GOSALES
Name: ORDER_DETAILS
Type: Table
Member 0
---------
Metrics
-----------------
lock_wait_time:85899
lock_wait_time_global:25769
lock_waits_local:21474
lock_waits_global:85899
lock_escals_local:17179
lock_escals_global:2
direct_writes:12884
direct_read_reqs:1
pool_data_gbp_invalid_pages:446
pool_data_lbp_pages_found:445
pool_xda_l_reads:446
pool_xda_p_reads:15