Gather stats in Oracle - most commonly used DBMS_STATS.GATHER_TABLE_STATS parameters

Below is a comprehensive explanation of the most commonly used DBMS_STATS.GATHER_TABLE_STATS parameters and when you should use them.

Syntax

DBMS_STATS.GATHER_TABLE_STATS(

ownname => 'SCOTT',

tabname => 'EMP',

partname => NULL,

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

block_sample => FALSE,

method_opt => 'FOR ALL COLUMNS SIZE AUTO',

degree => DBMS_STATS.AUTO_DEGREE,

granularity => 'AUTO',

cascade => TRUE,

no_invalidate => DBMS_STATS.AUTO_INVALIDATE,

options => 'GATHER',

force => FALSE

);

---

Parameter Explanation Table

Parameter	Purpose	Typical Value	Why Needed	Recommendation
OWNNAME	Schema owner	'HR'	Identifies schema containing table	Mandatory
TABNAME	Table name	'EMPLOYEES'	Table for stats collection	Mandatory
PARTNAME	Specific partition	'P202501'	Gather stats for only one partition	Use only for partitioned tables
ESTIMATE_PERCENT	Sample size percentage	AUTO_SAMPLE_SIZE	Determines how much data Oracle samples	Use AUTO_SAMPLE_SIZE
BLOCK_SAMPLE	Block sampling method	TRUE/FALSE	Samples blocks instead of rows	Usually FALSE
METHOD_OPT	Histogram collection method	'FOR ALL COLUMNS SIZE AUTO'	Controls histogram creation	Most important parameter
DEGREE	Parallelism	AUTO_DEGREE	Speeds up large-table stats collection	Use AUTO
GRANULARITY	Partition/global stats level	'AUTO'	Determines partition-level stats collection	Use AUTO
CASCADE	Gather index stats also	TRUE	Updates index statistics	Always TRUE
NO_INVALIDATE	Cursor invalidation control	AUTO_INVALIDATE	Determines when execution plans become obsolete	Use AUTO
OPTIONS	Collection mode	GATHER AUTO, GATHER STALE	Controls which objects get analyzed	Usually GATHER STALE
FORCE	Ignore lock status	TRUE/FALSE	Gather stats on locked objects	Usually FALSE

---

Important Parameters Deep Dive

1. ESTIMATE_PERCENT

AUTO_SAMPLE_SIZE

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE

Why Use It?

Oracle decides the optimal sample size.

Benefits:

• More accurate cardinality estimates
• Reduced runtime
• Recommended by Oracle

Avoid

estimate_percent => 100

unless doing optimizer troubleshooting.

---

2. METHOD_OPT

This parameter has the most impact on optimizer plans.

Recommended

method_opt => 'FOR ALL COLUMNS SIZE AUTO'

What It Does

Oracle creates histograms only when useful.

Example:

Column:

STATUS

-------

ACTIVE 99%

INACTIVE 1%

Without histogram:

Optimizer assumes even distribution.

With histogram:

Optimizer understands skew.

Result:

• Better index selection
• Better join ordering
• Better cardinality estimates

---

Other Options

Value	Purpose
FOR ALL COLUMNS SIZE AUTO	Oracle chooses histograms
FOR ALL COLUMNS SIZE 1	No histograms
FOR ALL INDEXED COLUMNS SIZE AUTO	Histograms only on indexed columns
FOR COLUMNS SIZE 254 col1,col2	Maximum histogram detail

---

3. CASCADE

Example

cascade => TRUE

What It Does

Gathers statistics on:

• Table
• Indexes

Without it:

Table stats updated

Index stats stale

This can lead to poor plans.

Recommended:

cascade => TRUE

---

4. NO_INVALIDATE

AUTO_INVALIDATE

no_invalidate => DBMS_STATS.AUTO_INVALIDATE

Purpose

When statistics change Oracle may invalidate cached SQL execution plans.

Options:

Value	Effect
TRUE	Keep existing plans
FALSE	Immediately invalidate plans
AUTO_INVALIDATE	Oracle decides

Recommended

AUTO_INVALIDATE

for production systems.

---

5. DEGREE

Controls parallel workers.

Examples

degree => 8

degree => DBMS_STATS.AUTO_DEGREE

Usage

Table Size	Recommendation
< 10 GB	AUTO
10-100 GB	AUTO
> 100 GB	4-16

---

6. GRANULARITY

Used for partitioned tables.

Example

granularity => 'AUTO'

Options:

Value	Description
AUTO	Oracle decides
GLOBAL	Global stats only
PARTITION	Partition stats only
ALL	Global + Partition
SUBPARTITION	Subpartition stats

Recommendation

AUTO

---

7. OPTIONS

Very important for maintenance windows.

GATHER

options => 'GATHER'

Gather regardless of staleness.

---

GATHER STALE

options => 'GATHER STALE'

Only tables with stale statistics.

Recommended for production.

---

GATHER AUTO

options => 'GATHER AUTO'

Gathers:

• Missing stats
• Stale stats
• Oracle-selected objects

Recommended when running manually.

---

LIST STALE

options => 'LIST STALE'

Report only.

No gathering performed.

---

8. FORCE

Example

force => TRUE

Normally locked statistics are skipped.

force => TRUE

forces collection anyway.

Mostly used by DBAs during troubleshooting.

---

Best Production Script

For Oracle 12c/19c/21c:

BEGIN

DBMS_STATS.GATHER_TABLE_STATS(

ownname => 'SCHEMA_NAME',

tabname => 'TABLE_NAME',

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt => 'FOR ALL COLUMNS SIZE AUTO',

cascade => TRUE,

degree => DBMS_STATS.AUTO_DEGREE,

granularity => 'AUTO',

no_invalidate => DBMS_STATS.AUTO_INVALIDATE

);

END;

/

Best Production Schema Script

BEGIN

DBMS_STATS.GATHER_SCHEMA_STATS(

ownname => 'SCHEMA_NAME',

options => 'GATHER STALE',

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt => 'FOR ALL COLUMNS SIZE AUTO',

cascade => TRUE,

degree => DBMS_STATS.AUTO_DEGREE

);

END;

/

DBA Recommendation

For Oracle 19c/21c production systems, the combination below provides the best balance between optimizer accuracy, maintenance window duration, and plan stability:

Parameter	Recommended Value
ESTIMATE_PERCENT	AUTO_SAMPLE_SIZE
METHOD_OPT	FOR ALL COLUMNS SIZE AUTO
CASCADE	TRUE
DEGREE	AUTO_DEGREE
GRANULARITY	AUTO
NO_INVALIDATE	AUTO_INVALIDATE
OPTIONS	GATHER STALE (schema) / default GATHER (table)
FORCE	FALSE

=> Dynamic SQL query that generates DBMS_STATS.GATHER_TABLE_STATS statements for all tables in a schema, use one of the following.

Generate Gather Stats Commands for All Tables in a Schema

SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS('||

'ownname=>'''||owner||''','||

'tabname=>'''||table_name||''','||

'estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,'||

'method_opt=>''FOR ALL COLUMNS SIZE AUTO'','||

'cascade=>TRUE,'||

'degree=>DBMS_STATS.AUTO_DEGREE);'

FROM dba_tables

WHERE owner = UPPER('&SCHEMA_NAME')

ORDER BY table_name;

Generate Gather Stats Only for Stale Tables

SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS('||

'ownname=>'''||owner||''','||

'tabname=>'''||table_name||''','||

'estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,'||

'method_opt=>''FOR ALL COLUMNS SIZE AUTO'','||

'cascade=>TRUE,'||

'degree=>DBMS_STATS.AUTO_DEGREE);'

FROM dba_tab_statistics

WHERE owner = UPPER('&SCHEMA_NAME')

AND stale_stats = 'YES'

ORDER BY table_name;

Generate Gather Stats for Tables with Missing Statistics

SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS('||

'ownname=>'''||owner||''','||

'tabname=>'''||table_name||''','||

'estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,'||

'method_opt=>''FOR ALL COLUMNS SIZE AUTO'','||

'cascade=>TRUE);'

FROM dba_tables

WHERE owner = UPPER('&SCHEMA_NAME')

AND last_analyzed IS NULL;

Execute Automatically Using Dynamic PL/SQL

BEGIN

FOR r IN (

SELECT owner, table_name

FROM dba_tab_statistics

WHERE owner = 'SCHEMA_NAME'

AND stale_stats = 'YES'

)

LOOP

DBMS_STATS.GATHER_TABLE_STATS(

ownname => r.owner,

tabname => r.table_name,

estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,

method_opt => 'FOR ALL COLUMNS SIZE AUTO',

cascade => TRUE,

degree => DBMS_STATS.AUTO_DEGREE

);

END LOOP;

END;

/

Parallel Execution Script Generator (Useful for Large Schemas)

SELECT 'EXEC DBMS_STATS.GATHER_TABLE_STATS('||

'ownname=>'''||owner||''','||

'tabname=>'''||table_name||''','||

'estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,'||

'method_opt=>''FOR ALL COLUMNS SIZE AUTO'','||

'cascade=>TRUE,'||

'degree=>8);'

FROM dba_tables

WHERE owner='SCHEMA_NAME';

For Oracle 19c/21c production environments, generally recommend gathering only stale statistics using AUTO_SAMPLE_SIZE, FOR ALL COLUMNS SIZE AUTO, and CASCADE=>TRUE rather than forcing stats collection on every table. This reduces maintenance time while keeping optimizer plans accurate.