DBAtlas · Database operations atlas

Gather statistics (DBMS_STATS)

OracleMaintenance

Command

-- Single table (including cascaded indexes):
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'CUSTOMER_APP', tabname => 'CUSTOMER', cascade => TRUE, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 4);

-- Whole schema:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'CUSTOMER_APP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE, degree => 4);

-- Whole database (run only in a maintenance window / off-peak hours):
EXEC DBMS_STATS.GATHER_DATABASE_STATS(estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, degree => 4);

What it does

Gathers table/index statistics for the optimizer; ensures correct execution plans are produced.

When to use it

After large DML/loads, when plans regress, after a new index/table.

Caution

Statistics changes can change execution plans; on critical systems test/compare with AWR first. DEGREE parallelism uses CPU. GATHER_DATABASE_STATS is heavy during busy hours.

Open in DBAtlas →

Related