In the Oracle Database, Statistics are the eyes of the Query Optimizer.

Without statistics, the Optimizer is like a pilot trying to land a plane in thick fog without any instruments. It doesn't know if a table has 10 rows or 10 billion, or if a column contains mostly unique IDs or just the same "Yes/No" value repeated millions of times.

In short: Statistics provide the metadata that allows the Cost-Based Optimizer (CBO) to make intelligent decisions.

1. What Information is Captured?

When you "gather stats," Oracle collects specific data points about your objects:

• Table Statistics: * Number of rows: How big is the table?

  • Blocks: How much physical space does it take up?

  • Average row length: How much data is fetched in one "read"?

• Column Statistics: * Number of distinct values (NDV): How unique is the data?

  • Number of Nulls: How many rows are empty?

  • Histograms: How is the data distributed? (Are 90% of your orders from the "USA"?)

• Index Statistics:

  • Leaf blocks: How wide is the index?

  • B-tree level: How "tall" is the index?

  • Clustering Factor: How well does the index order match the table order?

2. Why Statistics Matter for Your SQL

The Optimizer uses these numbers to calculate the "Cost" of an execution plan.

Scenario: The Small vs. Large Table

• The Query: SELECT * FROM orders WHERE status = 'PENDING';

• If Stats say 100 rows: The Optimizer picks a Full Table Scan. It’s faster to just read the whole tiny table into memory.

• If Stats say 100 million rows: The Optimizer picks an Index Range Scan. It would be insane to read 100 million rows if only a few are "Pending."

The Danger: If your stats are stale (e.g., they say 100 rows but the table actually has 100 million), Oracle will pick the Full Table Scan, and your query will "hang" indefinitely.

3. Histograms: Dealing with "Skewed" Data

By default, Oracle assumes data is evenly distributed. But real-world data is rarely "fair."

• Imagine a CITIZENS table where the COUNTRY column is 95% "India" and 5% "Other."

• Without a Histogram: Oracle assumes 1/200th of the rows are "India."

• With a Histogram: Oracle knows "India" is a massive portion of the data and will correctly choose a Full Table Scan for "India" but an Index for "Vatican City."

4. How to Gather Statistics

Modern Oracle databases have an automated maintenance task that gathers stats during "maintenance windows" (usually at night). However, after a massive data load, you should manually refresh them:

-- For a specific table
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');

-- For an entire schema
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SALES_APP');

5. Summary: The Impact of Statistics

6. How to Know if Your Stats are Stale

You can check when your tables were last analyzed by querying USER_TAB_STATISTICS:

SELECT table_name, last_analyzed, stale_stats 
FROM user_tab_statistics 
WHERE table_name = 'MY_LARGE_TABLE';

• Stale = YES: The data has changed by more than 10% since the last check.

• Last_Analyzed: If this date is months ago, the Optimizer is working with a very old map.

Pro-Tip: Dynamic Sampling

If you are working with Global Temporary Tables or tables where the data is deleted and re-inserted constantly, standard statistics won't work. In these cases, Oracle uses Dynamic Statistics (or Dynamic Sampling), where it does a "quick count" of the rows at the moment the query starts to make a better guess.