Chapter 23 Quiz: Reading and Interpreting EXPLAIN

Test your understanding of EXPLAIN concepts across both z/OS and LUW platforms. Choose the best answer for each question.

Question 1

What does the EXPLAIN facility do?

A) Executes the query and returns timing information B) Captures the optimizer's chosen access plan without executing the query (when using EXPLAIN mode) C) Rewrites the query for better performance D) Displays the SQL text in a formatted view

Question 2

On z/OS, which PLAN_TABLE column indicates whether a query can be satisfied entirely from an index?

A) ACCESSTYPE B) MATCHCOLS C) INDEXONLY D) PREFETCH

Question 3

On LUW, what unit does the optimizer use to express cost?

A) Milliseconds B) Processor service units (PROCSU) C) Timerons D) I/O operations

Question 4

In a z/OS PLAN_TABLE row, ACCESSTYPE = 'R' means:

A) Row-level access via a unique index B) RID scan C) Tablespace scan (reading all rows) D) Reverse index scan

Question 5

On LUW, index-only access is indicated by:

A) A FETCH operator with an INDEXONLY argument B) An IXSCAN operator with no FETCH operator above it C) A TBSCAN operator on the index D) A RIDSCN operator

Question 6

What does MATCHCOLS = 3 mean on a z/OS PLAN_TABLE row?

A) The index has 3 columns B) 3 index key columns matched equality predicates C) 3 tables are being accessed D) The query returns 3 rows

Question 7

Which join method reads the smaller table into a hash table and probes it with rows from the larger table?

A) Nested loop join (NLJOIN) B) Merge scan join (MSJOIN) C) Hash join (HSJOIN) D) Cross join

Question 8

On z/OS, METHOD = 1 in PLAN_TABLE indicates:

A) First table access (no join) B) Nested loop join C) Merge scan join D) Hash join

Question 9

Which z/OS PLAN_TABLE column shows whether list prefetch, sequential prefetch, or no prefetch is used?

A) ACCESSTYPE B) PREFETCH C) MATCHCOLS D) COLUMN_FN_EVAL

Question 10

The "worst" EXPLAIN pattern described in this chapter is:

A) Table scan on a small lookup table B) Hash join on two large tables C) Nested loop join with inner table scan on a large table D) Index scan with MATCHCOLS = 1

Question 11

On LUW, which operator sorts input rows?

A) GRPBY B) FILTER C) SORT D) TEMP

Question 12

What is the primary purpose of the DSN_PREDICAT_TABLE on z/OS?

A) Store the SQL text of the query B) Record how each predicate was classified (indexable, stage 1, stage 2, residual) C) Store the index definitions D) Record lock contention events

Question 13

On LUW, which EXPLAIN table shows the parent-child relationships between operators?

A) EXPLAIN_OPERATOR B) EXPLAIN_ARGUMENT C) EXPLAIN_STREAM D) EXPLAIN_PREDICATE

Question 14

A predicate like WHERE UPPER(CUST_NAME) = 'SMITH' typically prevents index usage because:

A) The index does not contain the CUST_NAME column B) The function applied to the column makes the predicate non-sargable C) Uppercase comparisons are not supported D) The optimizer cannot estimate the cost

Question 15

What does SORTC_ORDERBY = 'Y' in PLAN_TABLE indicate?

A) The new table is sorted for ORDER BY B) The composite (accumulated join result) is sorted for ORDER BY C) The ORDER BY is redundant and removed D) No sort is needed because an index provides the order

Question 16

When comparing two EXPLAIN plans (before and after optimization), the most reliable comparison metric is:

A) The number of PLAN_TABLE rows B) The ACCESSTYPE values C) The estimated total cost (PROCSU on z/OS, TOTAL_COST on LUW) D) The number of indexes used

Question 17

A cardinality estimate that is wildly inaccurate (e.g., estimated 10 rows, actual 1,000,000) is most commonly caused by:

A) A bug in the optimizer B) Stale or missing statistics C) Too many indexes on the table D) Using dynamic SQL instead of static SQL

Question 18

On z/OS, what SET CURRENT EXPLAIN MODE value explains a query without executing it?

A) YES B) NO C) EXPLAIN D) ANALYZE

Question 19

On LUW, the RETURN operator at the top of the operator tree shows:

A) The first row cost only B) The total cost for the entire query C) The number of indexes used D) The lock mode

Question 20

Multiple index access (ACCESSTYPE = 'M' or 'MX' on z/OS, IXAND/IXOR on LUW) often suggests:

A) The query is perfectly optimized B) A better composite index could replace the multiple index access C) The table has too few indexes D) The optimizer is broken

Question 21

You see PREFETCH = 'L' on a z/OS PLAN_TABLE row. This means:

A) Sequential prefetch — reading pages ahead sequentially B) List prefetch — collecting RIDs, sorting by page, then fetching in page order C) Lookahead prefetch — predicting which pages will be needed D) No prefetch is used

Question 22

In the EXPLAIN-driven optimization workflow, what should you do BEFORE making any changes?

A) Create a new index B) Update RUNSTATS C) Capture the current EXPLAIN output as a baseline D) Rewrite the query

Question 23

On LUW, a FETCH operator below a NLJOIN indicates:

A) The data is being fetched from a remote database B) Data rows are being retrieved from the table using RIDs from an index scan C) The result is being materialized into a temporary table D) The query is fetching the first row only

Question 24

Which of the following queries is most likely to achieve index-only access on an index defined as (BRANCH_ID, ACCT_STATUS)?

A) SELECT * FROM ACCOUNTS WHERE BRANCH_ID = 'BR-0042' B) SELECT BRANCH_ID, ACCT_STATUS FROM ACCOUNTS WHERE BRANCH_ID = 'BR-0042' C) SELECT BALANCE FROM ACCOUNTS WHERE BRANCH_ID = 'BR-0042' D) SELECT COUNT(BALANCE) FROM ACCOUNTS WHERE BRANCH_ID = 'BR-0042'

Question 25

An access path regression is:

A) A planned rollback of an index creation B) When a previously efficient access plan becomes inefficient after a change C) The process of simplifying a complex query D) When the optimizer improves a plan automatically

Answer Key

  1. B — EXPLAIN captures the access plan chosen by the optimizer. When EXPLAIN MODE = EXPLAIN, it does so without executing the query.

  2. C — INDEXONLY = 'Y' means the query can be answered entirely from the index without accessing data pages.

  3. C — Timerons are the abstract cost unit used by the DB2 LUW optimizer. They are proportional to, but not equal to, actual elapsed time.

  4. C — ACCESSTYPE = 'R' means a tablespace scan (table scan), which reads all rows sequentially.

  5. B — On LUW, index-only access is indicated when an IXSCAN operator feeds directly to its parent without an intervening FETCH operator.

  6. B — MATCHCOLS = 3 means three consecutive leading index key columns have matching equality predicates.

  7. C — HSJOIN (hash join) builds a hash table from the smaller input and probes it with rows from the larger input.

  8. B — METHOD = 1 indicates a nested loop join. METHOD = 0 is the first table, METHOD = 2 is merge scan join, and METHOD = 4 is hash join.

  9. B — The PREFETCH column shows 'S' for sequential prefetch, 'L' for list prefetch, or blank for no prefetch.

  10. C — A nested loop join with an inner table scan on a large table means the entire large table is scanned once for every row from the outer table, which is catastrophically expensive.

  11. C — The SORT operator explicitly sorts input rows. GRPBY performs grouping, FILTER applies predicates, and TEMP materializes results.

  12. B — DSN_PREDICAT_TABLE records how the optimizer classified each predicate (indexable, stage 1, stage 2, or residual) and its filter factor.

  13. C — EXPLAIN_STREAM shows the data flow connections between operators (which operator feeds into which).

  14. B — Applying a function to a column in a predicate makes it non-sargable because the optimizer cannot use the index to look up the transformed value.

  15. B — The "C" prefix means "composite" — the accumulated result of all joins so far. SORTC_ORDERBY = 'Y' means this composite result is being sorted for the ORDER BY clause.

  16. C — The estimated total cost is the most comprehensive comparison metric because it reflects the optimizer's overall assessment of query expense.

  17. B — Stale or missing statistics are by far the most common cause of wildly inaccurate cardinality estimates.

  18. C — SET CURRENT EXPLAIN MODE = EXPLAIN captures the plan without executing the query. YES would both explain and execute.

  19. B — The RETURN operator shows the cumulative total cost for the entire query.

  20. B — Multiple index access usually means no single index covers all the predicates well. A composite index on the relevant columns would typically be more efficient.

  21. B — List prefetch (PREFETCH = 'L') collects RIDs from an index scan, sorts them by page number, and fetches the data pages in page order to minimize random I/O.

  22. C — Always capture the current EXPLAIN output as your baseline before making any changes. You need this to verify whether your changes actually improved the plan.

  23. B — A FETCH operator retrieves data rows from the table using the RIDs provided by its child operator (typically an IXSCAN).

  24. B — This query selects only BRANCH_ID and ACCT_STATUS, which are both in the index. No data page access is needed.

  25. B — An access path regression occurs when a change (REBIND, statistics update, DB2 upgrade, etc.) causes the optimizer to choose a worse access plan than before.