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
- Flushing the shared pool removes all cursors from memory. Schedule it in a maintenance window when possible.
- After an upgrade, it is strongly advisable to gather the dictionary statistics and the table statistics again.
- For the role of
METHOD_OPTand histograms, see the Oracle optimizer blog: https://blogs.oracle.com/optimizer/how-does-the-methodopt-parameter-work