LLI Wiki — My Digital Knowledge Base

Gathering Oracle optimizer statistics with DBMS_STATS

Common DBMS_STATS calls to gather schema, database, dictionary, fixed object and system statistics, and flush the shared pool afterwards.

Optimizer statistics describe the data volume and value distribution of tables, indexes and columns. The cost-based optimizer relies on them to choose execution plans. This article lists the DBMS_STATS calls most often used to gather them.

Gather statistics for a schema

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS ( -
        ownname          => 'APP_SCHEMA', -
        options          => 'GATHER', -
        estimate_percent => 100, -
        method_opt       => 'FOR ALL COLUMNS SIZE AUTO', -
        cascade          => TRUE, -
        degree           => 5 -
      );
Parameter Description
ownname Owner of the schema to analyze
estimate_percent Percentage of rows sampled; 100 means a full compute
method_opt Column and histogram options; SIZE AUTO lets Oracle decide
cascade Also gathers index statistics
degree Degree of parallelism

To gather histogram statistics for several schemas, repeat the call for each owner.

Gather statistics for the database

BEGIN
    DBMS_STATS.GATHER_DATABASE_STATS(cascade => TRUE);
END;
/

Gather dictionary and fixed object statistics

BEGIN
    DBMS_STATS.GATHER_DICTIONARY_STATS;
END;
/

BEGIN
    DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/

System statistics

EXECUTE DBMS_STATS.GATHER_SYSTEM_STATS (gathering_mode => 'NOWORKLOAD');

-- On Exadata
EXEC DBMS_STATS.GATHER_SYSTEM_STATS('EXADATA');

Gather table by table

This loop gathers statistics for every table whose name ends with 300 in a given schema:

SET SERVEROUTPUT ON
BEGIN
    FOR cur IN (SELECT table_name FROM all_tables
                WHERE owner = 'APP_SCHEMA' AND table_name LIKE '%300')
    LOOP
        DBMS_OUTPUT.PUT_LINE('Compute stats for APP_SCHEMA.' || cur.table_name);
        DBMS_STATS.GATHER_TABLE_STATS('APP_SCHEMA', cur.table_name);
    END LOOP;
END;
/

Flush the shared pool

After recalculating statistics, cursors already in memory may keep using the old plans. To force them to be parsed again:

ALTER SYSTEM FLUSH SHARED_POOL;

Notes

Mastodon LLIMastodon SocialFosstodon