Statistics Strategy for a Skewed 400-GB Table
STATS is a 400-GB, 1.5-billion-row table with columns region, status, created_date, and amount. One region accounts for 40% of rows; status has 12 values and is correlated with region. The optimizer currently estimates 1,200 rows for a query filtering region='WEST' AND status='OPEN', but the actual count is 18 million, so it chooses a nested-loop join and performs over a million probes. RUNSTATS TABLESPACE ... INDEX(ALL) takes seven hours and overruns the three-hour batch window. The team has tried SAMPLE 10 and saw the bad plan persist. A statistics profile does not exist. The application also runs an ad hoc query that filters only status='OPEN' and returns 30% of the table. The DBA must propose a statistics strategy, a short-term access-path fix, and a way to monitor whether the fix holds after the next stats refresh.
1. Why did SAMPLE 10 fail to fix the estimate, and what statistics would you collect?
Read the worked answer
SAMPLE 10 can produce a reasonable overall cardinality and still miss the combination that matters. The query has two correlated predicates, region and status, and the optimizer multiplies selectivities unless it has a column-group statistic that captures their joint distribution. A 10% sample can also under-represent the 40% region or the particular region/status pair, especially if the sample is not large enough for the frequency values the optimizer needs. Collect a frequency distribution on region, on status, and on the column group (region, status) with enough frequency values to capture the skew. Collect index statistics for the indexes used by the join and verify KEYCARDS and cluster ratios. If the batch window forces sampling, use a larger sample and validate the reported frequencies against targeted counts; do not assume that SAMPLE 10 is sufficient just because the utility finishes quickly. The ad hoc status-only query needs its own frequency distribution on status, because the column-group statistic will not help it.
2. What short-term access-path fix would you use without changing the SQL, and what are the risks?
Read the worked answer
For immediate containment, use a plan hint with OPTHINT to force a plan that does not drive a million nested-loop probes. Capture the desired plan in PLAN_TABLE using representative literal values or an OPTIMIZE FOR clause, bind with the hint, and verify with EXPLAIN before promotion. The risk is that OPTHINT pins a plan that is correct for the current data but wrong as the data changes, and it must be maintained across schema changes and rebinds. It also masks the statistics problem rather than solving it, so set an expiry and a review date. Do not use OPTHINT as a permanent substitute for the column-group statistics. If the SQL can be changed, an OPTIMIZE FOR value or a rewrite that gives the optimizer better cardinality may be preferable, but the question asks for a no-SQL-change fix. In either case, monitor getpages and prepare counts after the bind, and be ready to remove the hint once the statistics are corrected and the plan is stable.
3. How would you automate and monitor the statistics strategy so the next refresh does not reintroduce the bad plan?
Read the worked answer
Create a statistics profile or a controlled RUNSTATS job that always specifies the same COLGROUP, FREQVAL, and index options for STATS. The goal is to prevent a later generic RUNSTATS from replacing the column-group distribution with default statistics. Include the status-only column distribution needed by the ad hoc query, and decide whether that query should use a different access path or be routed away from the OLTP subsystem. After each refresh, check SYSIBM.SYSCOLDIST and the catalog cardinalities, then EXPLAIN the critical statements and compare the chosen join method and estimated row counts. Monitor actual executions with accounting or monitor traces for getpages, prepare counts, and elapsed time, and set a threshold that triggers review. Keep the previous statistics and plan information so a regression can be diagnosed quickly. The strategy is not just a one-time RUNSTATS; it is a repeatable profile plus a post-refresh verification step.