# Why Cardinality Estimates Go Wrong: An Oracle Optimizer Lab with Skewed Data Every Oracle DBA has had this conversation with a developer: "the query ran fine yesterday, it's slow today, nothing changed." Nine times out of ten, when you pull the plan, the root cause is the same one-line diagnosis the optimizer's row estimate for some step is wildly off from what actually came back. E-Rows says 200,000. A-Rows says 4. Everything downstream of that step the join method, the join order, whether an index even gets used was built on a guess that was wrong from the start. The fix is usually "add a histogram," and that's often right. But "add a histogram" is a reflex, not an understanding, and it doesn't cover every way an estimate goes wrong. I wanted to actually *see* the failure modes side by side instead of pattern-matching from memory, so I built a small, disposable lab: one table, three different flavors of skew baked in on purpose, and a repeatable way to put the optimizer's estimate next to the truth. This post walks through what I built and what it showed. Everything here targets 12c/19c and runs fine on Standard Edition 2, nothing needs the Diagnostic or Tuning Pack. ## The one trick that makes this whole exercise possible Before the lab itself, the single most useful technique here is how to get a *real* actual-row-count next to the optimizer's estimate, in one shot, without touching AWR: ```sql SELECT /*+ gather_plan_statistics */ COUNT(*) FROM (SELECT * FROM my_table WHERE some_column = 'RARE_VALUE'); SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST')); ``` Two things make this work correctly, and both are easy to get wrong: **Wrap the query in** `COUNT(*)`**.** `DBMS_XPLAN.DISPLAY_CURSOR`'s `A-Rows` column only reflects rows the client actually *fetched*. If you run a plain `SELECT *` and your tool only displays the first page of results, `A-Rows` silently under-reports you'll think the estimate was closer than it was. Wrapping in `COUNT(*)` forces the aggregate to pull every row from its child row source before it can return anything, so `A-Rows` on that child line is always the true count. **Read the right line.** The top `SORT AGGREGATE` line will always say `A-Rows = 1` one row, the count itself. The number you actually want is on the `TABLE ACCESS FULL` or `INDEX RANGE SCAN` line underneath it. This trips people up constantly. With that out of the way, here's the lab. ## Building a dataset with skew you can actually reason about I generated one table, `LAB_ORDERS`, with 1,000,000 rows and three independent skew patterns, using a deterministic row generator (`MOD`/`DECODE` on the row number, no dependency on external data) so the exact percentages are reproducible run to run: ```sql CREATE TABLE lab_orders ( order_id NUMBER NOT NULL, order_status VARCHAR2(20) NOT NULL, -- frequency skew, 5 values region_code VARCHAR2(10) NOT NULL, -- looks uniform... channel VARCHAR2(10) NOT NULL, -- ...but is correlated with region_code order_amount NUMBER(10,2) NOT NULL, -- long-tail numeric skew order_date DATE NOT NULL, CONSTRAINT pk_lab_orders PRIMARY KEY (order_id) ); CREATE INDEX ix_lab_orders_status ON lab_orders(order_status); CREATE INDEX ix_lab_orders_amount ON lab_orders(order_amount); ``` `ORDER_STATUS` is split 90% / 5% / 3% / 1.5% / 0.5% across `COMPLETE`, `PENDING`, `CANCELLED`, `REFUNDED`, and `DISPUTED`. `REGION_CODE` and `CHANNEL` each look like a clean, evenly-split column on their own — but every `WEST` row is hard-wired to `ONLINE` in this dataset, so the two columns are secretly correlated. `ORDER_AMOUNT` is bimodal: 95% of orders are small-ticket ($10–$500), 5% are large B2B orders ($5,000–$50,000), with nothing in between. Three different assumptions the optimizer makes, three different ways to break each one. ## Failure #1: one column, five values, no histogram With `METHOD_OPT => 'FOR ALL COLUMNS SIZE 1'` (histograms explicitly off), `DBMS_STATS` has exactly one number to describe `ORDER_STATUS`'s selectivity roughly `1/NUM_DISTINCT`. That number gets applied uniformly, so every value from the 90%-of-the-table one to the 0.5%-of-the-table one gets estimated at *the same row count*. Querying the common value and the rare value produce nearly identical `E-Rows`, which is correct for maybe one of the five values and wrong for the rest. Before: ```plaintext -------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows | A-Rows | -------------------------------------------------------------------------------------- | 2 | TABLE ACCESS FULL | LAB_ORDERS | 200000 | 900000 | <- COMPLETE (90%) | 2 | TABLE ACCESS FULL | LAB_ORDERS | 200000 | 5000 | <- DISPUTED (0.5%) -------------------------------------------------------------------------------------- ``` The fix, once `ORDER_STATUS` has 254 or fewer distinct values (it has 5), is cheap: ```sql EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'LAB_ORDERS', - method_opt => 'FOR COLUMNS order_status SIZE 254', cascade => TRUE); ``` A frequency histogram doesn't approximate anything here it counts the exact number of rows for each of the five values and stores that directly. Re-run the same queries afterward and `E-Rows` tracks `A-Rows` closely across the board, and worth watching for the access path itself can flip from `TABLE ACCESS FULL` to `INDEX RANGE SCAN` for the rare values, now that the optimizer actually trusts the small estimate enough to think the index trip is worth it. This part is the textbook case, and if you've tuned Oracle for more than a week you've hit it. The next two are less reflexive. ## Failure #2: two columns that are each individually fine `REGION_CODE` has 4 evenly-split values. `CHANNEL` has 2 evenly-split values. Neither one, on its own, needs a histogram their individual stats are already accurate. The problem shows up only when you filter on both together: ```sql SELECT COUNT(*) FROM lab_orders WHERE region_code = 'WEST' AND channel = 'STORE'; -- actual rows: 0 (WEST is online-only in this dataset) ``` Oracle's default cardinality formula for an `AND` of two equality predicates multiplies their individual selectivities `sel(WEST) * sel(STORE)` ≈ `0.25 * 0.50` which is only valid if the columns are independent. They aren't, by construction, so the optimizer expects on the order of 100,000+ rows out of a million to match a combination that never actually occurs. No single-column histogram fixes this, because the defect isn't in either column's distribution it's in the independence assumption itself. The fix is extended statistics, telling `DBMS_STATS` to track the *joint* distribution of the two columns as its own virtual column: ```sql DECLARE v_ext_name VARCHAR2(30); BEGIN v_ext_name := DBMS_STATS.CREATE_EXTENDED_STATS( ownname => USER, tabname => 'LAB_ORDERS', extension => '(region_code, channel)'); END; / EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'LAB_ORDERS', - method_opt => 'FOR COLUMNS (region_code, channel) SIZE 254', cascade => TRUE); ``` After this, the estimate for the same predicate drops from six figures to single digits. It's worth being precise about what this does and doesn't guarantee: a frequency histogram on the column group records how many rows a combination *had* when sampled, not that the combination is *structurally impossible*; so don't expect `E-Rows` to land on exactly zero. Going from "off by 100,000+ rows" to "off by a handful" is the actual win, and it's usually enough to flip a join order or join method that was built on the bad number. The practical takeaway: when single-column stats look right but a combined predicate's estimate is still wildly off especially toward zero actual rows check `USER_STAT_EXTENSIONS` before reaching for another histogram. It's often a correlation problem in disguise. ## Failure #3: skew that a bucket-per-value histogram can't represent `ORDER_AMOUNT` isn't a handful of discrete values it's continuous, bimodal, and long-tailed. Without a histogram, range predicates are estimated with a linear-interpolation assumption between the column's recorded low and high value, which is a reasonable approximation for a uniform distribution and a bad one for a distribution with a big empty gap in the middle: ```sql SELECT COUNT(*) FROM lab_orders WHERE order_amount BETWEEN 600 AND 4900; -- actual rows: ~0 (this range sits entirely in the gap between the two clusters) SELECT COUNT(*) FROM lab_orders WHERE order_amount > 20000; -- actual rows: ~33,000 (true fraction ≈ 3.3%) -- uniform-assumption estimate: ~600,000 (≈ 60%) ``` That second number is the one worth sitting with — the optimizer's guess overshoots the truth by roughly 18x, because it has no way to know the bulk of the data sits near the bottom of the range. Since `ORDER_AMOUNT` has far more than 254 distinct values, asking for a histogram with `AUTO_SAMPLE_SIZE` gets you a **hybrid histogram** (the 12c+ default for high-NDV columns) rather than the older height-balanced type it picks representative endpoints the way height-balanced always did, but also records how many times each endpoint value repeats, which is what lets it distinguish "a genuinely popular value" from "one value in a sparse region." ```sql EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'LAB_ORDERS', - method_opt => 'FOR COLUMNS order_amount SIZE 254', cascade => TRUE); ``` One easy way to accidentally undo this: forcing a manual, non-`AUTO` sample percent on a column like this downgrades you back to the legacy height-balanced histogram, which has no per-endpoint repeat counts. If you've set a fixed low sample percent schema-wide for performance reasons, it's worth checking whether your skewed numeric columns are quietly paying for that. ## The gotcha that survives all three fixes: bind variables This is the one that tends to surprise people who've already done the histogram work correctly, and it matters a lot if your application layer binds almost everything by default which describes most ORDS/APEX-style apps. A histogram only informs the estimate at **hard parse** time, when the optimizer peeks at the bind variable's value. Every later execution that reuses the same cursor (a soft parse) keeps running whatever plan was optimal for *whichever value got peeked first* no matter how good the histogram is, and no matter how different the next bind value's selectivity is. ```sql VARIABLE b1 VARCHAR2(20) EXEC :b1 := 'COMPLETE'; -- hard parse, peeks 'COMPLETE' (~90%) SELECT /*+ gather_plan_statistics */ COUNT(*) FROM lab_orders WHERE order_status = :b1; -- TABLE ACCESS FULL, E-Rows ≈ A-Rows ≈ 900,000 — correct for this value EXEC :b1 := 'DISPUTED'; -- same cursor, soft parse, rare value (~0.5%) SELECT /*+ gather_plan_statistics */ COUNT(*) FROM lab_orders WHERE order_status = :b1; -- same FULL scan plan reused, E-Rows still ≈ 900,000, A-Rows ≈ 5,000 ``` That second execution has a histogram behind it and is still wrong, because the histogram never got consulted for this bind value the cursor was already parsed. This is exactly what Adaptive Cursor Sharing (11g+, on by default, no licensing restriction) exists to catch: once Oracle notices a cursor's actual row counts swing wildly across executions with different binds, it marks the cursor bind-sensitive, and a later execution whose peeked value falls in a different selectivity range triggers a fresh, bind-aware hard parse with its own plan. You can watch this happen (or not) directly: ```sql SELECT sql_id, child_number, plan_hash_value, executions, is_bind_sensitive, is_bind_aware FROM v$sql WHERE sql_text LIKE '%your_marker_here%' ORDER BY child_number; ``` Multiple child cursors with different `plan_hash_value` means ACS has already split the plan by bind value. If it hasn't, and you have a specific query you know will see wildly different selectivities on the same bind, the standard mitigations all fine on SE2 are: literal SQL for that one predicate if you're willing to pay the extra hard-parse cost, or a SQL Plan Baseline (`DBMS_SPM`, not a Tuning Pack feature) once you've confirmed the plan you actually want. ## The decision framework I actually use now Reaching for a histogram by reflex misses two of these three failure modes. Here's the shape of the decision I go through: * **A column with ≤254 distinct values, some far more or less common than the rest, filtered by literals or single binds** → frequency histogram on that column. * **A high-cardinality numeric/date column with a long tail or multiple clusters** → hybrid histogram under `AUTO_SAMPLE_SIZE` — don't force a manual sample percent on it. * **Two or more columns that are individually well-estimated but wrong when combined** → extended statistics (a column group), not another single-column histogram. * **A query behaves inconsistently and uses bind variables against a skewed column** → check `V$SQL.IS_BIND_SENSITIVE`/`IS_BIND_AWARE` before touching stats at all. * **Stats look right but the estimate is still off, or the predicate wraps a function/expression** → dynamic sampling as a stopgap, extended stats on the expression as the durable fix. And whatever you land on: `DBMS_STATS.SET_TABLE_PREFS` to lock in the `METHOD_OPT`, or your next scheduled stats job quietly reverts it. ## Try it yourself The whole thing is about 40 lines of DDL/DML to set up and reproducible on any 12c/19c instance, including a Standard Edition 2 one I ran mine against a docker `19c` container. Build the table, run the "before" queries, add the fix, run the same queries again, and put the `E-Rows`/`A-Rows` pair side by side instead of trusting your memory of what a histogram usually does. It's a fast way to turn "add a histogram" from a reflex into something you can actually explain the next time someone asks why a plan changed.