The optimizer plans every query from the statistics stored for its tables: row counts, distinct values, and the lowest and highest value of each column. When a table changes a lot after its statistics were gathered, those numbers no longer describe the data, and the plans built on them can be badly wrong. Oracle tracks the changes and marks such statistics as stale. This guide shows how stale statistics mislead the optimizer, how to find stale and missing statistics, and how to fix them.
Sample Table
100,000 orders from 2025, with an index on ORDER_DATE and fresh statistics:
Example:
create table demo_orders ( order_id number constraint demo_orders_pk primary key, order_date date not null, amount number(10,2) not null); insert into demo_orders select level, date '2025-01-01' + trunc((level - 1) / 274), mod(level * 13, 500) + 10 from dual connect by level <= 100000; commit; create index demo_orders_date_ix on demo_orders (order_date); exec dbms_stats.gather_table_stats(user, 'DEMO_ORDERS') select num_rows, stale_stats, to_char(last_analyzed, 'YYYY-MM-DD HH24:MI') as last_analyzed from user_tab_statistics where table_name = 'DEMO_ORDERS';
Output:
Table DEMO_ORDERS created.
100,000 rows inserted.
Commit complete.
Index DEMO_ORDERS_DATE_IX created.
PL/SQL procedure successfully completed.
NUM_ROWS STALE_STATS LAST_ANALYZED
___________ ______________ ___________________
100000 NO 2026-10-12 04:11How Statistics Become Stale
A busy quarter adds 60,000 orders for 2026:
Example:
-- a busy period: 60,000 orders for 2026
insert into demo_orders
select 100000 + level, date '2026-01-01' + trunc((level - 1) / 667),
mod(level * 13, 500) + 10
from dual connect by level <= 60000;
commit;
select inserts, updates, deletes
from user_tab_modifications where table_name = 'DEMO_ORDERS';
select num_rows, stale_stats from user_tab_statistics where table_name = 'DEMO_ORDERS';Output:
60,000 rows inserted.
Commit complete.
INSERTS UPDATES DELETES
__________ __________ __________
60000 0 0
NUM_ROWS STALE_STATS
___________ ______________
100000 YESUSER_TAB_MODIFICATIONS counts the inserts, updates, and deletes since the last gathering. Once they exceed 10% of the rows, STALE_STATS turns to YES. The statistics still say 100,000 rows.
What Stale Statistics Do to a Plan
Example:
select count(*) as orders_2026 from demo_orders where order_date >= date '2026-01-01'; explain plan for select * from demo_orders where order_date >= date '2026-01-01'; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE +NOTE');
Output:
ORDERS_2026
______________
60000
Explained.
PLAN_TABLE_OUTPUT
___________________________________________________________________________________________
Plan hash value: 1176654212
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 273 | 3 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO_ORDERS | 273 | 3 (0)|
|* 2 | INDEX RANGE SCAN | DEMO_ORDERS_DATE_IX | 273 | 2 (0)|
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ORDER_DATE">=TO_DATE(' 2026-01-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
15 rows selected.As far as the statistics know, the latest order date is in December 2025. A query for 2026 asks for values beyond that highest known value, so the optimizer expects almost nothing, 273 rows, and reads them through the index. In fact 60,000 rows match, and they are fetched one index entry at a time. Queries on recent dates, the most common kind, are hit hardest by stale statistics, because new data always lies beyond the old maximum.
Find Stale and Missing Statistics
Example:
-- tables of the schema whose statistics are stale or missing
select table_name, num_rows, stale_stats,
to_char(last_analyzed, 'YYYY-MM-DD HH24:MI') as last_analyzed
from user_tab_statistics
where table_name like 'DEMO%'
and (stale_stats = 'YES' or last_analyzed is null)
order by table_name;
select dbms_stats.get_prefs('STALE_PERCENT', user, 'DEMO_ORDERS') as stale_percent
from dual;Output:
TABLE_NAME NUM_ROWS STALE_STATS LAST_ANALYZED ______________ ___________ ______________ ___________________ DEMO_ORDERS 100000 YES 2026-10-12 04:11 STALE_PERCENT ________________ 10
- STALE_STATS = 'YES' marks tables changed by more than STALE_PERCENT since the last gathering.
- A table whose LAST_ANALYZED is empty has never had statistics.
- STALE_PERCENT is 10 by default; it can be changed per table with DBMS_STATS.SET_TABLE_PREFS.
Fix: Gather the Statistics Again
Example:
exec dbms_stats.gather_table_stats(user, 'DEMO_ORDERS') select num_rows, stale_stats from user_tab_statistics where table_name = 'DEMO_ORDERS'; explain plan for select * from demo_orders where order_date >= date '2026-01-01'; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE +NOTE');
Output:
PL/SQL procedure successfully completed.
NUM_ROWS STALE_STATS
___________ ______________
160000 NO
Explained.
PLAN_TABLE_OUTPUT
_________________________________________________________________
Plan hash value: 1815706504
--------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------
| 0 | SELECT STATEMENT | | 31717 | 137 (1)|
|* 1 | TABLE ACCESS FULL| DEMO_ORDERS | 31717 | 137 (1)|
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ORDER_DATE">=TO_DATE(' 2026-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss'))
14 rows selected.NUM_ROWS is now 160,000, the table is no longer stale, and the query gets a full scan. The estimate, 31,717 rows, is closer but still below the real 60,000: the statistics now cover dates from January 2025 to March 2026, and Oracle assumes the rows are spread evenly over those 455 days, while 2026 has many more orders per day. A histogram on ORDER_DATE would capture that.
Missing Statistics and Dynamic Sampling
A table created and filled without gathering has no statistics at all:
Example:
-- a table that never had statistics create table demo_new_orders (order_id number, amount number); insert into demo_new_orders select level, mod(level, 500) from dual connect by level <= 5000; commit; select num_rows, stale_stats, last_analyzed from user_tab_statistics where table_name = 'DEMO_NEW_ORDERS'; explain plan for select * from demo_new_orders where amount = 42; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE +NOTE');
Output:
Table DEMO_NEW_ORDERS created.
5,000 rows inserted.
Commit complete.
NUM_ROWS STALE_STATS LAST_ANALYZED
___________ ______________ ________________
Explained.
PLAN_TABLE_OUTPUT
_____________________________________________________________________
Plan hash value: 2997614690
------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 5 (0)|
|* 1 | TABLE ACCESS FULL| DEMO_NEW_ORDERS | 10 | 5 (0)|
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("AMOUNT"=42)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
17 rows selected.Without statistics, the optimizer reads a few blocks of the table while it parses the query, and estimates from that sample. The Note section reports it as dynamic sampling. It keeps plans reasonable for new tables, but it adds work to every hard parse and is less reliable than real statistics.
Things to Know
- The automatic statistics job gathers stale and missing statistics in the maintenance window, every night by default. Tables that change a lot during the day, such as staging tables, need a gathering right after they are loaded.
- Gather statistics after large loads and deletes inside your batch jobs, so the next queries see the new data at once.
- Locked statistics are not regathered, even when stale; check STATTYPE_LOCKED in USER_TAB_STATISTICS.
- Direct-path loads into an empty table, such as CREATE TABLE AS SELECT, gather basic statistics automatically.
Related Guides
Conclusion
Statistics turn stale when more than 10% of a table changes, and the optimizer then plans from old row counts and old high values, which hurts queries on new data the most. Find stale and missing statistics in USER_TAB_STATISTICS, gather them with DBMS_STATS, and gather right after big loads instead of waiting for the nightly job.
