How to gather statistics for a schema in Oracle

Gather optimizer statistics for every object in a schema.

This can run for a long time and changes plans across the schema. Prefer options => 'GATHER AUTO' to refresh only stale objects, and run in a maintenance window.

BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname          => '&&owner',
    estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
    method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
    degree           => DBMS_STATS.AUTO_DEGREE,
    cascade          => TRUE,
    options          => 'GATHER AUTO');
END;
/

-- Verify (read-only): most recently analyzed tables
SELECT table_name,
       TO_CHAR(last_analyzed,'YYYY-MM-DD HH24:MI') AS last_analyzed
FROM   dba_tables
WHERE  owner = UPPER('&owner')
ORDER  BY last_analyzed DESC NULLS LAST;