Collect statistics

Collect statistics using dbms_stats package.

Enable Monitoring

ALTER TABLE MYTABLE MONITORING;

Schema

-- gather stale statistics (use with MONITORING option)
exec dbms_stats.gather_schema_stats( -
      ownname => 'SYSADM', -
      estimate_percent => 0.2, -
      method_opt => 'FOR ALL COLUMNS SIZE AUTO', -
      degree => 2, -
      granularity => 'ALL', -
      options => 'GATHER STALE', -
      cascade => TRUE -
);

or

-- gather empty statistics (use with MONITORING option)
exec dbms_stats.gather_schema_stats( -
      ownname => 'SYSADM', -
      estimate_percent => 0.2, -
      method_opt => 'FOR ALL COLUMNS SIZE AUTO', -
      degree => 2, -
      granularity => 'ALL', -
      options => 'GATHER EMPTY', -
      cascade => TRUE -
);

Table

-- gather statistics for table and indexes
exec dbms_stats.gather_table_stats( -
      ownname => 'SYSADM', -
      tabname =>'MYTABLE', -
      method_opt => 'FOR COLUMNS SIZE AUTO', -
      degree => 2, -
      cascade => TRUE -
);