Basic column statistics tell the optimizer how many distinct values a column has, and it assumes those values are spread evenly. For skewed data, where one value fills most of the table and another appears a hundred times, that assumption gives wrong row estimates, and wrong estimates give wrong plans. A histogram records how often each value, or range of values, occurs, so the optimizer can tell a rare value from a common one. This guide shows the estimates and plans for skewed columns before and after histograms, how Oracle decides to create them, and the histogram types you will see.
Syntax
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'TABLE_NAME',
method_opt => 'FOR ALL COLUMNS SIZE AUTO');
end;METHOD_OPT controls histograms. FOR ALL COLUMNS SIZE AUTO, the default, lets Oracle decide per column; SIZE 1 means no histograms; FOR COLUMNS column_name SIZE n asks for a histogram with up to n buckets on one column, at most 2048.
Sample Table
100,000 orders with two skewed columns: STATUS, where almost every order is DELIVERED, and CUSTOMER_ID, where customer 1 has a quarter of all orders. The statistics are gathered without histograms:
Example:
create table demo_orders (
order_id number constraint demo_orders_pk primary key,
customer_id number not null,
status varchar2(10) not null,
amount number(10,2) not null);
insert into demo_orders
select level,
case when mod(level, 4) = 0 then 1 else mod(level, 5000) + 1 end,
case when mod(level, 1000) = 0 then 'NEW'
when mod(level, 50) = 0 then 'SHIPPED'
else 'DELIVERED' end,
mod(level * 13, 500) + 10
from dual connect by level <= 100000;
commit;
create index demo_ord_status_ix on demo_orders (status);
create index demo_ord_cust_ix on demo_orders (customer_id);
-- statistics without any histogram
exec dbms_stats.gather_table_stats(user, 'DEMO_ORDERS', -
method_opt => 'FOR ALL COLUMNS SIZE 1')Output:
Table DEMO_ORDERS created. 100,000 rows inserted. Commit complete. Index DEMO_ORD_STATUS_IX created. Index DEMO_ORD_CUST_IX created. PL/SQL procedure successfully completed.
The Data and What the Statistics Know
Example:
-- the real distribution
select status, count(*) as orders from demo_orders group by status order by orders desc;
-- what the statistics know
select column_name, num_distinct, histogram
from user_tab_col_statistics
where table_name = 'DEMO_ORDERS' and column_name in ('STATUS', 'CUSTOMER_ID');Output:
STATUS ORDERS ____________ _________ DELIVERED 98000 SHIPPED 1900 NEW 100 COLUMN_NAME NUM_DISTINCT HISTOGRAM ______________ _______________ ____________ CUSTOMER_ID 3751 NONE STATUS 3 NONE
The statistics know that STATUS has 3 distinct values, but not that 98,000 of the 100,000 rows are DELIVERED and only 100 are NEW.
Estimates Without a Histogram
Example:
explain plan for select * from demo_orders where status = 'NEW'; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE'); explain plan for select * from demo_orders where customer_id = 1; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');
Output:
Explained.
PLAN_TABLE_OUTPUT
_________________________________________________________________
Plan hash value: 1815706504
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 33333 | 136 (0)|
|* 1 | TABLE ACCESS FULL| DEMO_ORDERS | 33333 | 136 (0)|
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"='NEW')
13 rows selected.
Explained.
PLAN_TABLE_OUTPUT
________________________________________________________________________________________
Plan hash value: 2203928416
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 22 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO_ORDERS | 27 | 22 (0)|
|* 2 | INDEX RANGE SCAN | DEMO_ORD_CUST_IX | 27 | 1 (0)|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUSTOMER_ID"=1)
14 rows selected.- For STATUS = 'NEW' the optimizer divides the rows by the 3 distinct values and expects 33,333 rows, so it scans the whole table to find 100.
- For CUSTOMER_ID = 1 it divides by 3,751 customers and expects 27 rows, so it reads them through the index. Customer 1 really has 25,000 orders, which means 25,000 single-row table reads.
Both plans are wrong in opposite directions, for the same reason: the optimizer assumes every value is equally common.
Gather Statistics with SIZE AUTO
Example:
-- the default method_opt is FOR ALL COLUMNS SIZE AUTO
exec dbms_stats.gather_table_stats(user, 'DEMO_ORDERS')
select column_name, num_distinct, histogram, num_buckets
from user_tab_col_statistics
where table_name = 'DEMO_ORDERS' and column_name in ('STATUS', 'CUSTOMER_ID');Output:
PL/SQL procedure successfully completed. COLUMN_NAME NUM_DISTINCT HISTOGRAM NUM_BUCKETS ______________ _______________ ____________ ______________ CUSTOMER_ID 3751 HYBRID 254 STATUS 3 FREQUENCY 3
With SIZE AUTO, Oracle creates a histogram on a column when queries have used the column in their predicates and its data is skewed. Both conditions hold here: the EXPLAIN PLAN statements above used STATUS and CUSTOMER_ID in WHERE clauses, and Oracle records such column usage. That is why the first gather of a new table often creates no histograms; they appear after the application has run its queries.
A Frequency Histogram
STATUS got a FREQUENCY histogram, with one bucket for each of its three values. USER_TAB_HISTOGRAMS shows it; ENDPOINT_NUMBER is a running total of rows:
Example:
select endpoint_actual_value as value, endpoint_number as cumulative_rows,
endpoint_number
- nvl(lag(endpoint_number) over (order by endpoint_number), 0) as rows_for_value
from user_tab_histograms
where table_name = 'DEMO_ORDERS' and column_name = 'STATUS'
order by endpoint_number;Output:
VALUE CUMULATIVE_ROWS ROWS_FOR_VALUE ____________ __________________ _________________ DELIVERED 98000 98000 NEW 98100 100 SHIPPED 100000 1900
The differences between the running totals are the exact counts: 98,000, 100, and 1,900. The plans now follow the data:
Example:
explain plan for select * from demo_orders where status = 'NEW'; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE'); explain plan for select * from demo_orders where status = 'DELIVERED'; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');
Output:
Explained.
PLAN_TABLE_OUTPUT
__________________________________________________________________________________________
Plan hash value: 2854783816
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 2 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO_ORDERS | 100 | 2 (0)|
|* 2 | INDEX RANGE SCAN | DEMO_ORD_STATUS_IX | 100 | 1 (0)|
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("STATUS"='NEW')
14 rows selected.
Explained.
PLAN_TABLE_OUTPUT
_________________________________________________________________
Plan hash value: 1815706504
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 98000 | 136 (0)|
|* 1 | TABLE ACCESS FULL| DEMO_ORDERS | 98000 | 136 (0)|
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("STATUS"='DELIVERED')
13 rows selected.NEW is estimated at exactly 100 rows and found through the index, at a cost of 2. DELIVERED, 98,000 rows, still gets the full scan, which is right for it.
A Hybrid Histogram
CUSTOMER_ID has 3,751 distinct values, more than the 254 buckets, so Oracle built a HYBRID histogram. It stores bucket boundaries like a height-balanced histogram, plus how often each boundary value occurs, which captures the most frequent values precisely:
Example:
select customer_id, count(*) as orders from demo_orders where customer_id in (1, 78) group by customer_id order by customer_id; explain plan for select * from demo_orders where customer_id = 1; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE'); explain plan for select * from demo_orders where customer_id = 78; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');
Output:
CUSTOMER_ID ORDERS
______________ _________
1 25000
78 20
Explained.
PLAN_TABLE_OUTPUT
_________________________________________________________________
Plan hash value: 1815706504
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25214 | 136 (0)|
|* 1 | TABLE ACCESS FULL| DEMO_ORDERS | 25214 | 136 (0)|
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CUSTOMER_ID"=1)
13 rows selected.
Explained.
PLAN_TABLE_OUTPUT
________________________________________________________________________________________
Plan hash value: 2203928416
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 17 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO_ORDERS | 20 | 17 (0)|
|* 2 | INDEX RANGE SCAN | DEMO_ORD_CUST_IX | 20 | 1 (0)|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUSTOMER_ID"=78)
14 rows selected.Customer 1 is estimated at 25,214 rows, close to the real 25,000, and gets the full scan. Customer 78 is estimated at 20 rows, exactly right, and gets the index. The same column now gets the plan that fits each value.
Histogram Types
- FREQUENCY: one bucket per distinct value, when the column has no more distinct values than buckets. Exact counts.
- TOP-FREQUENCY: when a few values hold nearly all rows, the most frequent values get exact buckets and the rare rest is ignored.
- HYBRID: for columns with more distinct values than buckets; bucket boundaries plus the frequency of each boundary value.
- HEIGHT BALANCED: the older form for many values, still found on statistics gathered with an explicit sampling percentage.
Things to Know
- Histograms matter for columns used in WHERE clauses with skewed data. On evenly spread columns they add gathering time without changing any estimate.
- With bind variables, the plan is built for the value peeked at the first parse. Oracle's adaptive cursor sharing can later create separate plans for rare and common values.
- SIZE 1 removes histograms on the next gather; use it for columns where a histogram does harm.
- Keep the default SIZE AUTO in general, and set preferences per table or column only for known cases.
Related Guides
Conclusion
Without a histogram, Oracle assumes every value of a column is equally common, which misjudges both rare and frequent values of skewed data. Gather statistics with SIZE AUTO after the application has run its queries, check the HISTOGRAM column of USER_TAB_COL_STATISTICS, and compare the plan estimates with the real counts for the values that matter.
