Introduction

For every SQL statement, the Oracle Optimizer considers multiple execution plans and selects the plan with the lowest estimated cost, where cost represents the expected consumption of system resources. Accurate cost estimation depends on the availability of detailed information about all referenced database objects, including tables and indexes, as well as system-level characteristics. This information is collectively known as optimizer statistics, and effective management of these statistics is critical to ensuring efficient SQL execution.

In Oracle Database 19c, Statistics Gathering is one of the most critical performance activities.
Even a perfectly written SQL can perform very badly if statistics are missing, stale, or incorrect.

In simple words:

Statistics tell Oracle “how much data exists and how it is distributed”.
Oracle’s optimizer uses this information to choose the fastest execution plan.

Without correct statistics, Oracle is guessing, and guessing in production is dangerous.

What Are Statistics in Oracle?

Statistics are metadata about database objects such as:

Tables
Indexes
Columns

They include information like:

Number of rows
Number of blocks
Data distribution
Distinct values
Index depth and clustering

Oracle’s Cost Based Optimizer (CBO) relies entirely on statistics.

Why Statistics Gathering Is Important (Simple Explanation)

Imagine a delivery app:

If it knows traffic conditions → fastest route
If it does not → random route

Similarly:

With Statistics	Without Statistics
Optimal plan	Wrong execution plan
Fast queries	Full table scans
Stable performance	Sudden slowness
Predictable behavior	Random spikes

Real Production Problem (Application Team Experience)

Problem Scenario

Application team complains:

Screen loads slowly after data load
Reports take minutes instead of seconds
Same query sometimes fast, sometimes slow

Root Cause (Very Common)

Large data load happened
Statistics were not refreshed
Optimizer thinks table is small
Chooses Nested Loop instead of Hash Join

Result

CPU spikes
High I/O
Application timeout

1. When to Decide Stats Gathering? (Important)

You should gather statistics when:

After bulk data load
- ETL jobs
- Data migration
- Monthly/Yearly load
After massive DELETE or PURGE
- Archival jobs
- Cleanup activities
After index rebuild
When execution plan suddenly changes
When SQL becomes slow without code change

Red Flag Symptoms

Sudden full table scans
Change in execution plan
High DB CPU after data load
AWR shows plan instability

2. Ideal Time to Perform Stats Gathering on Production

Best Practice Timing

Environment	Recommended Time
Production	Off-peak hours
Night batch window	Ideal
Weekends	Best for large schemas

Why Off-Peak?

Stats gathering consumes CPU & I/O
May invalidate execution plans
Can temporarily slow running queries

Golden Rule

Never run full schema stats during peak business hours

Automatic Statistics Gathering (Built-In)

Oracle Database uses a background scheduler job called Auto Optimizer Stats Collection.

Key Points

Runs automatically during maintenance windows
Gathers only stale or missing statistics
Uses DBMS_STATS
Enabled by default in Oracle 19c
Production-safe and recommended

Automatic Statistics Gathering in Oracle 19c

Oracle runs Auto Stats Job by default:

Uses DBMS_STATS
Runs during maintenance window
Gathers only stale statistics

Check auto stats job:

SELECT client_name, status
FROM dba_autotask_client;

3. Can a Normal User Perform Stats Gathering?

Short Answer: Yes

But with limited scope.

Privileges Required

Normal user can gather stats on:

Own tables
Own indexes

No DBA privileges needed.

Example (Normal User)

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'APPUSER',
  tabname => 'ORDERS'
);

For Other Schemas (DBA Required)

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'HR',
  tabname => 'EMPLOYEES'
);

4. Important Columns to Observe (Statistics Health)

Table Statistics Columns

SELECT table_name,
       num_rows,
       blocks,
       last_analyzed,
       stale_stats
FROM dba_tab_statistics
WHERE owner = 'APPUSER';

Key Columns:

NUM_ROWS → Total rows
BLOCKS → Storage usage
LAST_ANALYZED → Stats freshness
STALE_STATS → Needs regather

Column Statistics

SELECT column_name,
       num_distinct,
       density,
       histogram
FROM dba_tab_col_statistics
WHERE owner = 'APPUSER'
AND table_name = 'ORDERS';

Important:

NUM_DISTINCT → Cardinality
HISTOGRAM → Data skew handling

5. Important Views to Check (Must Know)

View	Purpose
`DBA_TAB_STATISTICS`	Table stats
`DBA_IND_STATISTICS`	Index stats
`DBA_TAB_COL_STATISTICS`	Column stats
`DBA_AUTOTASK_CLIENT`	Auto stats job
`DBA_TAB_MODIFICATIONS`	Data changes
`V$SQL_PLAN`	Execution plans

6. How to Gather Index Statistics

Index Stats Only

EXEC DBMS_STATS.GATHER_INDEX_STATS(
  ownname => 'APPUSER',
  indname => 'IDX_ORDERS_DATE'
);

Table + Index Together (Recommended)

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'APPUSER',
  tabname => 'ORDERS',
  cascade => TRUE
);

cascade => TRUE gathers index stats automatically.

Production-Level Best Practice Example

Schema Level Stats

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
  ownname => 'APPUSER',
  options => 'GATHER STALE',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
  method_opt => 'FOR ALL COLUMNS SIZE AUTO'
);

This is production safe and Oracle recommended.

Demo Example – Learn by Doing (Hands-On)

Step 1: Create Test Table

CREATE TABLE sales_data (
  order_id NUMBER,
  region   VARCHAR2(20),
  amount   NUMBER,
  order_date DATE
);

Step 2: Insert Data

INSERT INTO sales_data
SELECT level,
       CASE WHEN MOD(level,10)=0 THEN 'NORTH' ELSE 'SOUTH' END,
       DBMS_RANDOM.VALUE(100,10000),
       SYSDATE - MOD(level,30)
FROM dual CONNECT BY level <= 100000;

COMMIT;

Step 3: Check Stats (Before)

SELECT num_rows, last_analyzed
FROM user_tab_statistics
WHERE table_name = 'SALES_DATA';

Step 4: Run Query (Observe Plan)

EXPLAIN PLAN FOR
SELECT * FROM sales_data WHERE region = 'NORTH';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Step 5: Gather Stats

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => USER,
  tabname => 'SALES_DATA',
  cascade => TRUE
);

Step 6: Recheck Plan (Difference)

Run the same EXPLAIN PLAN again and compare.

Statistics gathering is not optional in Oracle 19c.
It directly controls query performance, application stability, and user experience.

Good statistics = predictable performance
Bad statistics = production fire-fighting

What Are Index Statistics?

Index statistics describe how an index is structured and how efficiently Oracle can use it.

In simple terms:

Table statistics tell Oracle how much data exists
Index statistics tell Oracle how fast data can be accessed

Oracle uses index statistics to decide:

Whether to use an index or not
Which index to use (when multiple exist)
Whether index access is cheaper than full table scan

Why Index Statistics Matter in Production

Even if table statistics are perfect, bad or missing index stats can still cause poor performance.

Common Production Issues Due to Bad Index Stats

Index exists but Oracle ignores it
Index scan becomes slower than full table scan
Wrong index chosen for query
High logical I/O

When Should You Gather Index Statistics?

You should gather index statistics when:

After index creation
After index rebuild
After large data load or purge
When index becomes bloated
When query execution plan changes unexpectedly

Real Production Example

Index rebuilt during maintenance window
Stats not gathered
Optimizer thinks index is empty
Index not used in production queries

How Oracle Handles Index Stats Automatically

When you gather table statistics with CASCADE = TRUE, Oracle automatically gathers:

All related index statistics
Column stats used by index

Example:

EXEC DBMS_STATS.GATHER_TABLE_STATS(
  ownname => 'APPUSER',
  tabname => 'ORDERS',
  cascade => TRUE
);

✅ Recommended for production
❌ Avoid gathering index stats separately unless required

How to Gather Index Statistics Manually

Syntax

EXEC DBMS_STATS.GATHER_INDEX_STATS(
  ownname => 'schema_name',
  indname => 'index_name'
);

Example (Production Safe)

EXEC DBMS_STATS.GATHER_INDEX_STATS(
  ownname => 'APPUSER',
  indname => 'IDX_ORDERS_ORDER_DATE'
);

Gather Index Stats with Advanced Options

EXEC DBMS_STATS.GATHER_INDEX_STATS(
  ownname => 'APPUSER',
  indname => 'IDX_ORDERS_ORDER_DATE',
  estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
);

AUTO_SAMPLE_SIZE lets Oracle decide optimal sample size.

Index Statistics – Important Columns to Monitor

SELECT index_name,
       num_rows,
       leaf_blocks,
       distinct_keys,
       clustering_factor,
       last_analyzed
FROM dba_ind_statistics
WHERE owner = 'APPUSER';

Key Columns Explained (Simple Language)

Column	Meaning
`NUM_ROWS`	Rows indexed
`LEAF_BLOCKS`	Index size
`DISTINCT_KEYS`	Unique values
`CLUSTERING_FACTOR`	Index efficiency
`LAST_ANALYZED`	Stats freshness

Understanding Clustering Factor (Very Important)

What is Clustering Factor?

It tells how well table data is ordered according to index.

Low value → Very good index
High value → Poor index performance

Rule of Thumb

Clustering Factor	Performance
Close to table blocks	Excellent
Close to table rows	Poor

Real-Time Production Decision Based on Index Stats

Example Decision

High clustering factor
Index used frequently
Performance still poor
→ Consider index rebuild or table reorganization

Check Stale Index Statistics

SELECT index_name, stale_stats
FROM dba_ind_statistics
WHERE owner = 'APPUSER';

If STALE_STATS = YES, stats must be refreshed.

Lock and Unlock Index Statistics (Advanced Production Control)

Lock Index Stats (Prevent Plan Change)

EXEC DBMS_STATS.LOCK_INDEX_STATS(
  ownname => 'APPUSER',
  indname => 'IDX_ORDERS_ORDER_DATE'
);

Unlock Index Stats

EXEC DBMS_STATS.UNLOCK_INDEX_STATS(
  ownname => 'APPUSER',
  indname => 'IDX_ORDERS_ORDER_DATE'
);

Used when execution plans must remain stable.

Hands-On Demo: Index Stats Impact

Step 1: Create Index

CREATE INDEX idx_sales_region ON sales_data(region);

Step 2: Check Index Stats Before

SELECT num_rows, last_analyzed
FROM user_ind_statistics
WHERE index_name = 'IDX_SALES_REGION';

Step 3: Run Query

SELECT * FROM sales_data WHERE region = 'NORTH';

Check plan:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

Step 4: Gather Index Stats

EXEC DBMS_STATS.GATHER_INDEX_STATS(
  ownname => USER,
  indname => 'IDX_SALES_REGION'
);

Step 5: Re-Execute Query & Compare Plan

Observe:

Index access path
Reduced cost
Lower I/O

Best Practices for Index Statistics (Production)

✔ Prefer CASCADE = TRUE with table stats
✔ Use AUTO_SAMPLE_SIZE
✔ Gather stats after index rebuild
✔ Monitor clustering factor
✔ Avoid frequent manual index stats during peak hours
✔ Lock stats for critical indexes if plan stability is required

Final Takeaway

Indexes without statistics are like a road without signboards.
Oracle may choose the wrong path even when the road exists.

Index statistics are as important as table statistics for consistent, predictable, and scalable performance in Oracle 19c.

Can We Schedule Jobs in Oracle Database to Gather Statistics?

Short Answer:
👉 Yes. Statistics gathering can be scheduled using Oracle Scheduler, and Oracle 19c already runs an automatic statistics gathering job by default.

1. Automatic Statistics Gathering (Built-In)

Oracle Database uses a background scheduler job called Auto Optimizer Stats Collection.

Key Points

Runs automatically during maintenance windows
Gathers only stale or missing statistics
Uses DBMS_STATS
Enabled by default in Oracle 19c
Production-safe and recommended

Check Auto Stats Job Status

SELECT client_name, status
FROM dba_autotask_client;

Expected output:

auto optimizer stats collection | ENABLED

Maintenance Window Timing

SELECT window_name, repeat_interval
FROM dba_scheduler_windows;

2. Why Schedule Stats Manually When Auto Job Exists?

Even though Oracle has auto stats, manual scheduling is still required in many production scenarios:

Common Use Cases

✔ Large ETL loads outside maintenance window
✔ Data warehouse nightly loads
✔ Monthly / quarterly batch processing
✔ Critical application schemas
✔ Controlled execution time

Auto stats may not run immediately after bulk loads

3. Scheduling Stats Gathering Using Oracle Scheduler

Example: Schedule Table Stats Daily at Night

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'gather_sales_stats_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN
                          DBMS_STATS.GATHER_TABLE_STATS(
                            ownname => ''APPUSER'',
                            tabname => ''SALES_DATA'',
                            cascade => TRUE
                          );
                        END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY;BYHOUR=23;BYMINUTE=0;BYSECOND=0',
    enabled         => TRUE
  );
END;
/

📌 This job runs every day at 11:00 PM.

4. Schedule Schema-Level Stats (Production Best Practice)

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'gather_appuser_schema_stats',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN
                          DBMS_STATS.GATHER_SCHEMA_STATS(
                            ownname => ''APPUSER'',
                            options => ''GATHER STALE'',
                            estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                            method_opt => ''FOR ALL COLUMNS SIZE AUTO''
                          );
                        END;',
    repeat_interval => 'FREQ=DAILY;BYHOUR=01',
    enabled         => TRUE
  );
END;
/

✔ Gathers only stale stats
✔ Safe for production
✔ Oracle recommended settings

5. Monitor Scheduled Stats Jobs

Check Job Status

SELECT job_name, state, last_start_date, last_run_duration
FROM dba_scheduler_jobs
WHERE job_name LIKE '%STATS%';

Check Job Run History

SELECT job_name, status, actual_start_date, run_duration
FROM dba_scheduler_job_run_details
WHERE job_name = 'GATHER_APPUSER_SCHEMA_STATS';

6. Can Normal Users Schedule Stats Jobs?

Yes, with Conditions

User Type	Can Schedule Job	Scope
Normal user	Yes	Own schema only
DBA	Yes	Any schema
SYS	Yes	Full control

Required Privilege

GRANT CREATE JOB TO appuser;

7. Should We Disable Auto Stats and Use Manual Jobs?

🚫 Not recommended

Best Practice

✔ Keep Auto Stats Job ENABLED
✔ Add custom scheduler jobs for special cases
✔ Avoid overlapping stats jobs

8. Production Best Practices for Scheduled Stats Jobs

✔ Schedule during off-peak hours
✔ Use GATHER STALE option
✔ Avoid full database stats
✔ Monitor execution time
✔ Do not schedule too frequently
✔ Lock stats for stable objects if needed

9. Common Production Mistakes

❌ Running full schema stats every hour
❌ Running stats during business hours
❌ Disabling auto stats without reason
❌ Ignoring job failures
❌ Gathering stats immediately after every DML

1. If Oracle has automatic statistics gathering, why do we still face performance issues?

👉 Because auto stats:

Run only during maintenance windows
Gather stats only when objects are marked stale
May not run immediately after bulk loads
May not capture complex data skew correctly

Auto stats help, but they are not a guarantee of perfect plans.

2. Can gathering statistics make performance worse instead of better?

👉 Yes.
New statistics can cause the optimizer to choose a different execution plan, which may perform worse for existing workloads.

This is why:

Stats should be gathered carefully
Plan stability must be monitored
Stats locking may be needed for critical objects

3. Why did the execution plan change even though only index stats were gathered?

👉 Because:

Index statistics influence access path selection
Changes in clustering factor or distinct keys can alter join methods
The optimizer recalculates costs using new index information

Even index-only stats can trigger plan changes.

4. Why is Oracle ignoring an index even though it exists and statistics are fresh?

Possible reasons:

High clustering factor
Index selectivity is poor
Optimizer estimates full table scan is cheaper
Predicate functions prevent index usage
Incorrect bind variable peeking

5. What happens if we don’t gather statistics at all?

Oracle may:

Use default or dynamic sampling
Make incorrect cardinality estimates
Choose inefficient plans
Cause unpredictable performance

In production, not gathering stats is not an option.

6. Is it safe to gather schema statistics every day?

👉 It depends.

Safe when:

GATHER STALE option is used
Scheduled during off-peak hours

Risky when:

Full schema stats are forced daily
System is OLTP-heavy during business hours

7. What is the difference between stale statistics and missing statistics?

Missing Stats	Stale Stats
Never gathered	Previously gathered
Optimizer guesses	Optimizer uses outdated data
High risk	Medium risk

Both must be addressed, but missing stats are worse.

8. Why does Oracle sometimes choose a full table scan even when an index exists?

Because Oracle decides based on cost, not availability.

Reasons include:

Large data fetch
Poor selectivity
Small table size
Index access cost higher than scan

9. How do histograms affect statistics gathering and execution plans?

Histograms help Oracle:

Understand skewed data
Estimate rows accurately
Choose better plans

But:

Incorrect histograms can cause plan instability
Too many histograms can increase parsing overhead

10. Can we lock statistics permanently to avoid plan changes?

👉 You can, but you shouldn’t blindly.

Lock stats when:

Table data is static
Execution plan is proven stable

Unlock stats when:

Data volume or distribution changes
Performance issues appear

11. Why did the query become slow even though statistics were just gathered?

Possible reasons:

Wrong execution plan chosen
Bind variable peeking issues
Stats do not reflect real workload
Histograms changed
Adaptive plans kicked in

Stats gathering is not a silver bullet.

12. Does gathering statistics lock tables or block users?

👉 No, stats gathering does not lock tables.

However:

It consumes CPU and I/O
Can impact performance indirectly during peak hours

13. Why do two identical environments behave differently after stats gathering?

Because:

Data distribution differs
System statistics differ
Hardware characteristics differ
Timing of stats gathering differs

Statistics are environment-specific.

14. Should we gather index stats separately or always use CASCADE?

👉 Best practice:

Use CASCADE => TRUE with table stats

Gather index stats separately only when:

Index rebuilt independently
Targeted troubleshooting is required

15. If stats are correct, can the optimizer still make a bad choice?

👉 Yes.

Reasons:

Complex SQL
Correlated predicates
Bind variables
Optimizer limitations

This is where hints, SQL profiles, or plan baselines may help.

16. How do we know stats gathering actually helped performance?

Check:

Execution plan before vs after
AWR reports
SQL elapsed time
Logical and physical reads

Stats success is measured by performance improvement, not completion status.

17. Is disabling automatic stats gathering ever recommended?

👉 Rarely.

Only consider when:

Custom stats strategy exists
Tight control is required
DBA team fully understands impact

Otherwise, leave auto stats enabled.

18. Why does Oracle mark stats as stale even with small data changes?

Oracle uses a change threshold (roughly 10%) to mark stats stale.

Frequent DML can quickly cross this threshold in active tables.

19. Can we predict execution plan changes after stats gathering?

👉 No, but we can:

Test in lower environments
Use SQL Plan Baselines
Lock stats temporarily
Monitor critical SQLs

20. What is the biggest misconception about optimizer statistics?

👉 “More stats = better performance”

Reality:

Correct stats matter
Poorly timed or excessive stats gathering can hurt performance

Oracle 19c Statistics Gathering – Knowledge Check Quiz

Use this quiz to test your understanding, revise key concepts, or prepare for interviews.

Section A – Basic Concepts

Q1. What is the primary purpose of statistics in Oracle Database?

A. To reduce storage usage
B. To help the optimizer choose the best execution plan
C. To lock database objects
D. To improve backup performance

Q2. Which Oracle component uses statistics to generate execution plans?

A. SQL Engine
B. Cost Based Optimizer (CBO)
C. Redo Log Writer
D. Database Writer

Q3. What happens if statistics are missing or stale?

A. Oracle throws an error
B. Queries fail to execute
C. Optimizer may choose a poor execution plan
D. Indexes are automatically rebuilt

Section B – Production & Real-Time Scenarios

Q4. An application becomes slow immediately after a bulk data load. The SQL code is unchanged. What is the most likely cause?

A. Network issue
B. Fragmented tablespace
C. Missing or stale statistics
D. Invalid indexes

Q5. Why is it recommended to gather statistics during off-peak hours in production?

A. Stats gathering locks tables
B. Stats gathering consumes CPU and I/O
C. Stats gathering deletes old data
D. Stats gathering stops user sessions

Q6. Which option ensures that both table and index statistics are gathered together?

A. estimate_percent
B. method_opt
C. cascade => TRUE
D. degree

Section C – Index Statistics

Q7. What does the clustering factor indicate?

A. Index size on disk
B. Number of leaf blocks
C. How well table data is ordered relative to the index
D. Number of distinct values

Q8. A clustering factor close to the number of table blocks indicates:

A. Poor index performance
B. Very efficient index access
C. Index corruption
D. Index not usable

Q9. After rebuilding an index, what is the correct next step?

A. Lock index statistics
B. Drop and recreate table stats
C. Gather index statistics
D. Disable auto stats job

Section D – Privileges & Views

Q10. Can a normal user gather statistics?

A. No, only SYS can
B. Yes, on own tables and indexes
C. Yes, on all schemas
D. Only through auto stats job

Q11. Which view helps identify whether statistics are stale?

A. V$SQL
B. DBA_TABLES
C. DBA_TAB_STATISTICS
D. DBA_SEGMENTS

Q12. Which column tells when statistics were last gathered?

A. CREATED
B. TIMESTAMP
C. LAST_ANALYZED
D. UPDATED

Section E – Hands-On & Commands

Q13. Which package is used for statistics gathering in Oracle 19c?

A. DBMS_SQL
B. DBMS_METADATA
C. DBMS_STATS
D. UTL_FILE

Q14. Which command is NOT recommended in Oracle 19c for statistics?

A. DBMS_STATS.GATHER_TABLE_STATS
B. DBMS_STATS.GATHER_SCHEMA_STATS
C. ANALYZE TABLE COMPUTE STATISTICS
D. Auto stats job

Q15. What does `STALE_STATS = YES` indicate?

A. Statistics are locked
B. Statistics are missing
C. Statistics are outdated and should be regathered
D. Statistics are corrupted

Answer Key

Section A

Section B
4. C
5. B
6. C

Section C
7. C
8. B
9. C

Section D
10. B
11. C
12. C

Section E
13. C
14. C
15. C

Disclaimer:
The commands, examples, and configurations shown in this article are for educational and demonstration purposes only. Always test statistics gathering operations in a non-production environment before applying them to live systems. Execution behavior and performance impact may vary depending on data volume, workload, and Oracle Database configuration.