Oracle – How often should Oracle database statistics be run

databaseoracleperformancestatistics

In your experience, how often should Oracle database statistics be run? Our team of developers recently discovered that statistics hadn't been run our production box in over 2 1/2 months. That sounds like a long time to me, but I'm not a DBA.

Best Solution

Since Oracle 11g statistics are gathered automatically by default.

Two Scheduler windows are predefined upon installation of Oracle Database:

  • WEEKNIGHT_WINDOW starts at 10 p.m. and ends at 6 a.m. every Monday through Friday.
  • WEEKEND_WINDOW covers whole days Saturday and Sunday.

When statistics were last gathered?

SELECT owner, table_name, last_analyzed FROM all_tables ORDER BY last_analyzed DESC NULLS LAST; --Tables.
SELECT owner, index_name, last_analyzed FROM all_indexes ORDER BY last_analyzed DESC NULLS LAST; -- Indexes.

Status of automated statistics gathering?

SELECT * FROM dba_autotask_client WHERE client_name = 'auto optimizer stats collection';

Windows Groups?

SELECT window_group_name, window_name FROM dba_scheduler_wingroup_members;

Window Schedules?

SELECT window_name, start_time, duration FROM dba_autotask_schedule;

Manually gather Database Statistics in this Schema:

EXEC dbms_stats.gather_schema_stats(ownname=>NULL, cascade=>TRUE); -- cascade=>TRUE means include Table Indexes too.

Manually gather Database Statistics in all Schemas!

-- Probably need to CONNECT / AS SYSDBA
EXEC dbms_stats.gather_database_stats;