LEARN. PRACTICE. GROW.Distribution belongs in the diagnosis
In a fictional payments service, almost every transaction is settled and a small proportion needs review. The APS team observes a slow review-queue query. Start by identifying the SQL, searched values, PDB and affected window. A table with two states does not imply two equal populations. In the original lab, 10,000 rows split into 100 REVIEW and 9,900 SETTLED. Every amount is 10. The index on STATE exists from the start. This fixture isolates statistical information: no indexes are added and no amounts change between the two rare-query measurements. The same total row count can conceal very different distributions.
Estimating does not count returned rows
After gathering statistics with SIZE 1, the column has two distinct values and no histogram. For this simple equality, the plan estimates 5,000 rows, corresponding to 10,000 divided by two. Complete execution processes 100 rows at the table-access operation. The discrepancy is fiftyfold. The final SUM result is one row containing 1,000: that aggregate row is not the cardinality of the operation reading payments. Record the operation identifier when comparing estimates and observations. The uniform formula explains this controlled example; do not automatically apply it to joins, nulls, combined predicates or queries using additional statistical information.
The histogram represents a relevant difference
The second gathering uses the same table and requests a histogram only for STATE. Metadata shows FREQUENCY, two distinct values and two buckets. With data and index unchanged, a newly identifiable query estimates 100 rows for REVIEW and execution observes 100. For SETTLED, estimate and observation are both 9,900. The rare plan changed from FULL to index and ROWID access; the broad plan retained FULL. These are observed Oracle Free 26ai results, repeated in two synthetic schemas. A histogram improves representation of the relevant distribution but does not guarantee an index, a response time or the same plan in every environment.
Treat gathering as an operational change
Gathering statistics can change subsequent plans even without changing business rows. During a fund close, agree scope with the database team and service owner: table, columns, timing, representative queries, expected behavior and recovery of the previous configuration. The lab uses a 100% sample and no_invalidate=false to make the sequence explicit on a small disposable table. These parameters are not a recommendation for every banking table. Global gathering can increase cost and affect queries outside the incident. Keep intervention bounded to the hypothesis being investigated and retain enough evidence to compare before and after, including the distribution that motivated the decision.
Accept outcomes rather than a favorite plan
Acceptance should include rare and frequent values, equivalent business results and resource use within the agreed context. In the exercise, sums remain 1,000 and 99,000, and the total remains 100,000. This demonstrates preservation of synthetic data, not reconciliation of an actual financial service. Do not classify FULL as an error by definition: retrieving almost the entire table can justify that path. Nor should matching E-Rows and A-Rows alone establish success; the application can still be waiting elsewhere. Close the investigation with the effect on batch deadlines, affected queries, comparable metrics and limitations. Connect this topic to predicates, executed plans and change management.
-- Disposable lab only; full sampling and immediate invalidation are deliberate here.
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname=>USER,tabname=>'PAYMENTS',
estimate_percent=>100,method_opt=>'FOR ALL COLUMNS SIZE 1 FOR COLUMNS STATE SIZE 254',
cascade=>TRUE,no_invalidate=>FALSE);
END;
/
SELECT histogram,num_distinct,num_buckets FROM user_tab_col_statistics
WHERE table_name='PAYMENTS' AND column_name='STATE'Without a histogram: E-Rows 5,000, A-Rows 100. With a histogram: E-Rows 100, A-Rows 100, retaining the sum of 1,000.
Common pitfalls
Confusing NDV with equal frequencies; measuring the aggregate row instead of access; gathering the whole database; accepting only the rare case.
Related topics: Optimizer statistics · Performance diagnosis · Operational change acceptance
Use observed distribution to explain the estimate and validate changes with representative results and workload.
Reference: Histograms · 1Z0-183 public objectives inspected 2026-09-30; revision date not published