Oracle Checkpoint: Understanding GATHER_TABLE_STATS with GRANULARITY in Oracle Database

Optimizer statistics are one of the most important components of Oracle Database performance tuning.

Oracle’s Cost-Based Optimizer (CBO) depends heavily on accurate statistics to generate efficient execution plans.

In this blog, we will explore:

What table statistics are
Why statistics matter
What GRANULARITY means in DBMS_STATS
How GATHER_TABLE_STATS works
Understanding DEGREE parameter
Reading DBA_TAB_STATISTICS
Analyzing real output from Oracle Autonomous Database

Gathering Table Statistics

The following command was executed:

SQL> exec dbms_stats.gather_table_stats(
    'TEST_USER',
    'TAB1',
    degree=>4,
    granularity=>'ALL'
)

Output:

PL/SQL procedure successfully completed.

What Does GATHER_TABLE_STATS Do?

The procedure:

DBMS_STATS.GATHER_TABLE_STATS

collects optimizer statistics for a table.

These statistics help Oracle understand:

How many rows exist
How much storage is used
Average row size
Data distribution
Whether statistics are stale
How expensive a query may be

Oracle’s optimizer uses this information to choose:

Index scans
Full table scans
Hash joins
Nested loops
Parallel execution plans

Understanding the Parameters

1. Schema Name

'TEST_USER'

This is the schema owner.

2. Table Name

'TAB1'

The target table whose statistics will be gathered.

3. DEGREE => 4

degree=>4

This enables parallelism during statistics gathering.

Oracle can use:

4 parallel execution workers

to scan the table faster.

This is especially useful for:

Large tables
Data warehouse systems
Partitioned tables
High-volume analytics

4. GRANULARITY => 'ALL'

granularity=>'ALL'

This is one of the most important parts of the command.

GRANULARITY determines:

At which levels Oracle gathers statistics.

What Does GRANULARITY Mean?

For partitioned tables, Oracle can gather statistics at multiple levels:

Global level
Partition level
Subpartition level

The GRANULARITY parameter controls which levels are analyzed.

Common GRANULARITY Options

Granularity	Meaning
AUTO	Oracle decides automatically
GLOBAL	Only global table statistics
PARTITION	Only partition statistics
SUBPARTITION	Only subpartition statistics
ALL	Global + Partition + Subpartition statistics

What Happens with GRANULARITY='ALL'?

When using:

granularity=>'ALL'

Oracle gathers:

Table-level statistics
Partition-level statistics
Subpartition-level statistics

if the table is partitioned.

For non-partitioned tables, Oracle simply gathers normal table statistics.

Checking Row Count

The table contains:

SQL> select count(*) from test_user.tab1;

COUNT(*)
--------
93743

This confirms:

93,743 rows exist in the table

Understanding DBA_TAB_STATISTICS

Oracle stores optimizer statistics metadata in:

DBA_TAB_STATISTICS

This view contains:

Row counts
Block counts
Sampling information
Last analyzed time
Stale statistics status

Checking Gathered Statistics

The following query was executed:

SQL> select
       TABLE_NAME,
       AVG_ROW_LEN,
       SAMPLE_SIZE,
       LAST_ANALYZED,
       STALE_STATS,
       NUM_ROWS,
       BLOCKS,
       EMPTY_BLOCKS
     from dba_tab_statistics
     where table_name='TAB1';

Output

TABLE_NAME       AVG_ROW_LEN    SAMPLE_SIZE LAST_ANALYZED    STALE_STATS       NUM_ROWS    BLOCKS    EMPTY_BLOCKS
_____________ ______________ ______________ ________________ ______________ ___________ _________ _______________

TAB1                     158          93743 24-05-26         NO                   93743       108               0

Analyzing the Statistics Output

1. AVG_ROW_LEN

This means:

Average row length is 158 bytes

Oracle uses this to estimate:

I/O costs
Memory usage
Join costs
Hash area requirements

2. SAMPLE_SIZE

Oracle sampled:

93,743 rows

which equals the entire table.

This indicates:

Oracle performed a full statistics collection rather than partial sampling.

3. LAST_ANALYZED

24-05-26

This shows when statistics were last gathered.

The optimizer uses this timestamp to determine statistics freshness.

4. STALE_STATS

NO

This means:

Statistics are current
Oracle considers them fresh

If significant table modifications occur later:

INSERTS
UPDATES
DELETES

Oracle may eventually mark statistics as:

YES

5. NUM_ROWS

Oracle estimates the table contains:

93,743 rows

This matches the COUNT(*) query exactly.

6. BLOCKS

The table occupies:

108 Oracle blocks

Oracle uses this for:

Cost calculations
Full scan estimates
I/O predictions

7. EMPTY_BLOCKS

This indicates:

No completely empty blocks exist

which is usually a healthy sign for storage utilization.

Why Statistics Matter So Much

Without statistics, Oracle’s optimizer may generate poor execution plans.

Examples:

Choosing full table scans unnecessarily
Ignoring indexes
Using incorrect join methods
Underestimating cardinality

Good statistics are critical for:

Performance tuning
SQL optimization
Parallel query execution
Data warehouse workloads

Statistics in Autonomous Database

Oracle Autonomous Database automatically gathers optimizer statistics in most situations.

However, manual gathering is still useful when:

Large data loads occur
Bulk inserts happen
Partition maintenance is performed
Testing optimizer behavior
Benchmarking workloads

Why GRANULARITY is Important for Partitioned Tables

Partitioned tables can have:

Millions or billions of rows
Different data distributions per partition

Global statistics alone may not be sufficient.

Using:

granularity=>'ALL'

allows Oracle to maintain:

Detailed partition-level statistics
Accurate pruning estimates
Better optimizer decisions

Best Practices

Gather statistics after large data loads
Use parallel DEGREE carefully
Use GRANULARITY='ALL' for partitioned tables
Monitor STALE_STATS regularly
Avoid unnecessary repeated statistics gathering

Key Takeaways

DBMS_STATS.GATHER_TABLE_STATS collects optimizer statistics
GRANULARITY controls which levels of statistics are gathered
ALL gathers global, partition, and subpartition statistics
DEGREE enables parallel statistics gathering
DBA_TAB_STATISTICS stores optimizer metadata
Fresh statistics improve query performance significantly

Conclusion

Optimizer statistics are the foundation of Oracle SQL performance tuning.

Understanding parameters like:

degree=>4
granularity=>'ALL'

helps DBAs and developers control how Oracle analyzes tables and generates execution plans.

Even in Autonomous Database, understanding statistics remains extremely important for:

Performance optimization
Partition tuning
Large-scale analytics
SQL troubleshooting

The output shown above demonstrates how Oracle successfully gathered accurate statistics for:

93,743 rows
108 blocks
Fresh optimizer metadata

allowing the Cost-Based Optimizer to make smarter execution decisions.

Oracle Checkpoint

Sunday, 24 May 2026

Understanding GATHER_TABLE_STATS with GRANULARITY in Oracle Database