DB2 Version 10.1 for Linux, UNIX, and Windows

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.

An example of graph output from thedb2exfmt command is as follows:

        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.

An example of operator details output from the db2exfmt command is as follows:

   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.

The following example shows how object statistics are displayed in the output of the db2exfmt command:

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