Access Path Regression and REOPT Choice for a High-Volume OLTP Package
An OLTP package PKG_ORDER runs 5,000 times per minute and uses host variables for order_status and order_date. Last week it averaged 3 ms; after a RUNSTATS on ORDERS, it averages 45 ms and the accounting trace shows more class 1 CPU but little class 3 wait. EXPLAIN before the refresh showed a matching index scan on (order_status, order_date). The current access path is a tablespace scan with a nested-loop join to ORDER_LINE. The package is bound with REOPT(ALWAYS), ISOLATION(RR), and has 12 statements. A developer wants to force the old plan with OPTHINT and set REOPT(NONE). A second proposal is to run RUNSTATS with a histogram on order_status and set REOPT(AUTO). The table has 300 million rows; order_status has 40 distinct values and is heavily skewed (one value is 65% of rows). The batch window cannot support frequent REOPTs. The team asks how to separate statistics from bind-option effects and what to change first.
1. How would you prove whether the 45 ms is caused by the new access path, by REOPT(ALWAYS) prepare CPU, or by lock waits?
Read the worked answer
Start with the accounting trace and separate class 1, class 2, and class 3 time. Little class 3 wait argues against lock contention, but confirm with lock wait and timeout counts rather than elapsed time alone. Class 1 CPU rising while class 3 stays flat points to execution or prepare CPU. Next, count prepares. With REOPT(ALWAYS), static statements are reoptimized at execution, so a package running 5,000 times per minute can generate 5,000 prepares per statement per minute. Compare prepare CPU and prepare counts before and after the RUNSTATS. Then reproduce the access path with EXPLAIN using representative host-variable values, including the 65% status value. If EXPLAIN shows the tablespace scan while the old plan used the index, the path changed. If the path is the same but prepare counts exploded, the bind option is the dominant cost. This distinguishes a statistics problem from a bind-option problem before changing either.
2. Evaluate the two proposals: OPTHINT + REOPT(NONE) versus histogram + REOPT(AUTO). Which would you choose and why?
Read the worked answer
Treat OPTHINT + REOPT(NONE) as containment, not the fix. OPTHINT can force the old plan and stop the immediate regression, but it pins a plan captured under old statistics, must be maintained as schema and data change, and removes the optimizer's ability to adapt to the 65% skew. REOPT(NONE) makes that rigidity permanent. Histogram plus REOPT(AUTO) addresses the cause: the frequency distribution on order_status gives the optimizer the skewed value, and REOPT(AUTO) reoptimizes when the input host variables differ enough from the previous execution, rather than on every execution. At 5,000 executions per minute, monitor prepare counts because AUTO can still reoptimize frequently if values vary widely. My order is: collect the histogram and a column group for correlated status/date predicates, rebind with REOPT(AUTO), and use OPTHINT only as a short-term emergency measure with an expiry date. If the workload has one dominant status value, consider whether a separate package or SQL path is more appropriate than forcing one plan for all values.
3. What specific statistics would you collect, and what is the risk of a full RUNSTATS on 300 million rows?
Read the worked answer
Collect frequency distribution on order_status, because the 65% value is exactly what a uniform distribution hides. Add a column group for (order_status, order_date) if the predicates are correlated, and collect index statistics for the matching index. A full RUNSTATS on 300 million rows may exceed the batch window and consume CPU and I/O that the OLTP workload needs. Sampling can reduce that cost, but a small sample can miss the skewed value; if you sample, verify the reported frequency against a targeted query and be prepared to run a full or larger sample on the key columns. Use a statistics profile or scheduled utility so the same COLGROUP and FREQVAL options are applied consistently; otherwise the next routine RUNSTATS can silently remove the distribution and reintroduce the bad plan. After collection, check SYSIBM.SYSCOLDIST and EXPLAIN the representative statements before rebinding.