OracleStatistics

Gather statistics for a table or schema

Templates for gathering stats by hand. New statistics can change execution plans, so do this at a quiet time and know which plans matter.

Changes data

Not yet verified. How scripts are tested

This script changes the database. Check the names and values before you run it.

ora-gather-stats.sql

1-- One table and its indexes2EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'APP', tabname => 'ORDERS', cascade => TRUE, degree => 4);3 4-- Or only the stale objects in a schema5-- EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'APP', options => 'GATHER STALE');

Save it as ora-gather-stats.sql and run it with SQL> @ora-gather-stats.

Open in denrepo

More Oracle scripts: Statistics

Tables and partitions with stale statisticsApplication objects the optimizer considers stale. The flush at the top makes sure recent DML is counted before the check.
Tables that have never been analyzedApplication tables with no statistics at all, which forces dynamic sampling or guesswork. Locked statistics are shown so you know which ones are…
Automatic statistics job status and historyWhether the nightly optimizer stats job is enabled, and how each run in the last week went. A run that keeps hitting the window end isn't finishing…