SpecialTopics

Statistics Gathering

Automatic Statistics Collection (Status, Enable|Disable...)
Global Settings: Change | Display

Gather Schema Stats
Gather Table Stats
Gather Dictionary Stats
Misc (Status of Running Stats Job, Last Time Analyzed, Stale Stats)
Stale Stats, Display

Manual Stats Update Session 🠈 (cut to the chase)

The content here is derived from Doc IDs: 1337116.1, 377152.1 and 1233203.1.

Overview

Statistics on tables, indexes and partitions are used by the Cost Based Optimizer (CBO) in determining the most optimal way to execute SQL. In 12c and later an automatic statistics gathering job is created for every newly created database.

Oracle strongly recommends using the automatic statistics gathering feature as it liberates DBAs from the arduous tasks of determining:

Which objects need statistics.
The correct sampling percentages for objects.
Histogram bucket sizes based on the workload profile.
Degree of parallelism for statistics gathering.

If you find the Auto Stats job is not optimal for your needs you can disable it and update stats as you see fit.

Automatic Statistics Collection

Status

 SELECT CLIENT_NAME, STATUS 
 FROM DBA_AUTOTASK_CLIENT 
 WHERE CLIENT_NAME='auto optimizer stats collection';

 CLIENT_NAME                      STATUS
 -------------------------------  -------
 auto optimizer stats collection  ENABLED

Other Status Values: DISABLED,RETRY SCHEDULED,SCHEDULED,RUNNING,COMPLETED,BROKEN,FAILED,REMOTE,SUCCEEDED,CHAIN_STALLED

 -- dba_scheduler_programs
 COL owner        FORMAT a25
 COL program_name FORMAT a25
 SELECT owner, program_name, enabled FROM dba_scheduler_programs;

 OWNER            PROGRAM_NAME              ENABLE
 ---------------- ------------------------- ------
 SYS              GATHER_STATS_PROG         TRUE

Disable

 exec DBMS_AUTO_TASK_ADMIN.DISABLE(
   client_name => 'auto optimizer stats collection', 
   operation => NULL, window_name => NULL);

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

 -- dba_scheduler_programs
 EXEC DBMS_SCHEDULER.disable (name => 'GATHER_STATS_PROG');

Enable

 exec DBMS_AUTO_TASK_ADMIN.ENABLE(
   client_name => 'auto optimizer stats collection',
   operation => NULL, window_name => NULL);

exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);

 -- dba_scheduler_programs
 EXEC DBMS_SCHEDULER.enable (name => 'GATHER_STATS_PROG');

History of Job Runs

 COL client_name       FORMAT a35
 COL window_name       FORMAT a25
 COL window_start_time FORMAT a35
 SELECT client_name, window_name, window_start_time
 FROM dba_autotask_job_history
 WHERE client_name='auto optimizer stats collection'
 ORDER BY  window_start_time;

Manually Run Job

 exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;

Put this in any script where you want to update the database stats.

Change Global Settings

Common change example:

 exec dbms_stats.set_global_prefs(pname=>'DEGREE',pvalue=>'10');
 exec dbms_stats.set_global_prefs(pname=>'STALE_PERCENT',pvalue=>'5');

 SELECT dbms_stats.get_prefs('DEGREE') FROM dual;
 10
 SELECT dbms_stats.get_prefs('STALE_PERCENT') FROM dual;
 5

Display Automatic Stats Gathering Job Parameters

SET ECHO OFF
SET TERMOUT ON
SET SERVEROUTPUT ON
SET TIMING OFF
DECLARE
   v1  varchar2(100);
   v2  varchar2(100);
   v3  varchar2(100);
   v4  varchar2(100);
   v5  varchar2(100);
   v6  varchar2(100);
   v7  varchar2(100);
   v8  varchar2(100);
   v9  varchar2(100);
   v10 varchar2(100);
BEGIN
   dbms_output.put_line('Automatic Stats Gathering Job - Parameters');
   dbms_output.put_line('==========================================');
   v1 := dbms_stats.get_prefs('AUTOSTATS_TARGET');
   dbms_output.put_line(' AUTOSTATS_TARGET:  ' || v1);
   v2 := dbms_stats.get_prefs('CASCADE');
   dbms_output.put_line(' CASCADE:           ' || v2);
   v3 := dbms_stats.get_prefs('DEGREE');
   dbms_output.put_line(' DEGREE:            ' || v3);
   v4 := dbms_stats.get_prefs('ESTIMATE_PERCENT');
   dbms_output.put_line(' ESTIMATE_PERCENT:  ' || v4);
   v5 := dbms_stats.get_prefs('METHOD_OPT');
   dbms_output.put_line(' METHOD_OPT:        ' || v5);
   v6 := dbms_stats.get_prefs('NO_INVALIDATE');
   dbms_output.put_line(' NO_INVALIDATE:     ' || v6);
   v7 := dbms_stats.get_prefs('GRANULARITY');
   dbms_output.put_line(' GRANULARITY:       ' || v7);
   v8 := dbms_stats.get_prefs('PUBLISH');
   dbms_output.put_line(' PUBLISH:           ' || v8);
   v9 := dbms_stats.get_prefs('INCREMENTAL');
   dbms_output.put_line(' INCREMENTAL:       ' || v9);
   v10:= dbms_stats.get_prefs('STALE_PERCENT');
   dbms_output.put_line(' STALE_PERCENT:     ' || v10);
END;
/

12c-19c default values.

 Automatic Stats Gathering Job - Parameters
 ==========================================
 AUTOSTATS_TARGET:  AUTO
 CASCADE:           DBMS_STATS.AUTO_CASCADE
 DEGREE:            NULL
 ESTIMATE_PERCENT:  DBMS_STATS.AUTO_SAMPLE_SIZE
 METHOD_OPT:        FOR ALL COLUMNS SIZE AUTO
 NO_INVALIDATE:     DBMS_STATS.AUTO_INVALIDATE
 GRANULARITY:       AUTO
 PUBLISH:           TRUE
 INCREMENTAL:       FALSE
 STALE_PERCENT:     10

Gather Schema Stats

 EXEC DBMS_STATS.GATHER_SCHEMA_STATS(
   ownname    => 'SCOTT', 
   degree     => DBMS_STATS.AUTO_DEGREE,
   cascade    => DBMS_STATS.AUTO_CASCADE,
   method_opt => 'FOR ALL COLUMNS SIZE AUTO',
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

Place on one line to execute.

degree: DBMS_STATS.AUTO_DEGREE or n (ex: 8).
estimate_percent : DBMS_STATS.AUTO_SAMPLE_SIZE or n (ex: 5).

Gather Table Stats

 EXEC DBMS_STATS.GATHER_TABLE_STATS(
   ownname    => 'SCOTT', 
   tabname    => 'MyTable', 
   degree     => DBMS_STATS.AUTO_DEGREE,
   cascade    => DBMS_STATS.AUTO_CASCADE,
   method_opt => 'FOR ALL COLUMNS SIZE AUTO',
   estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

Place on one line to execute.

degree: DBMS_STATS.AUTO_DEGREE or n (ex: 8).
estimate_percent : DBMS_STATS.AUTO_SAMPLE_SIZE or n (ex: 5).

Gather Dictionary Stats

According to Oracle, Dictionary stats should be updated every 6 months. Stats on system objects (via DBMS_STATS.GATHER_SYSTEM_STATS) are generally never needed (Doc ID 1501712.1).

 EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

Also see Doc ID 2100050.1 - How Does Dbms_Stats Gather_Dictionary_Stats and System Statistics?

Misc

Status of Running Stats Job

 SELECT to_char( max(last_analyzed), 'DD/MM/YYYY HH24:MI:SS') AS "last_analyzed" 
 FROM dba_tables;

 COL job_name  FORMAT a35
 COL log_date  FORMAT a35
 COL operation FORMAT a15
 COL status    FORMAT a15
 SELECT log_date,JOB_NAME,operation,status
 FROM dba_scheduler_job_log
 WHERE LOG_DATE > (sysdate-1/24)
 ORDER BY LOG_DATE;

Date Table Last Analyzed

 COL owner       FORMAT a15
 COL table_name  FORMAT a40
 COL STALE_STATS FORMAT a10
 SELECT owner,table_name,last_analyzed 
 FROM dba_tables 
 WHERE owner='SCOTT' 
 AND table_name='EMP';

Tables With Stale Statistics

 exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

 COL owner       FORMAT a15
 COL table_name  FORMAT a40
 COL STALE_STATS FORMAT a10
 SELECT OWNER,TABLE_NAME,to_char(LAST_ANALYZED,'yyyy.mm.dd hh24:mi'),STALE_STATS 
 FROM DBA_TAB_STATISTICS 
 WHERE STALE_STATS='YES'
 AND owner != 'SYS' 
 ORDER BY owner,table_name;

Running EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; should reduce the number of SYS objects if there are a lot.

Statistics on Fixed Objects

 COL database_creation FORMAT a18
 COL last_analyzed     FORMAT a18
 SELECT dbid,to_char(created,'yyyy.mm.dd hh24:mi') database_creation,
   version,
   (select to_char(max(last_analyzed),'yyyy.mm.dd hh24:mi') last_analyzed 
    from dba_tab_statistics where object_type='FIXED TABLE') last_analyzed 
 FROM v$database,v$instance;

Manual Stats Update Session

 -- Disable Auto Stats Gathering
 EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',
      operation => NULL,window_name => NULL);
 EXEC DBMS_SCHEDULER.disable (name => 'GATHER_STATS_PROG');

 -- Ensure All Stats Updated For Given Data
 EXEC dbms_stats.delete_schema_Stats(OWNNAME => 'SCOTT'); 🠈 May take a few minutes.
 EXEC DBMS_STATS.set_schema_prefs('SCOTT','STALE_PERCENT',1);
 EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

 -- Update Stats
 EXEC DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SCOTT', degree => 12,
      cascade => DBMS_STATS.AUTO_CASCADE, method_opt => 'FOR ALL COLUMNS SIZE AUTO',
      estimate_percent => 10);

 -- Reset to normal value locking in the stats (and available execution plans).
 EXEC dbms_stats.set_schema_prefs('SCOTT','STALE_PERCENT',10); 

 -- Re-enable Auto Stats Gathering (or not if you routinely manually update stats)
 EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',
      operation => NULL,window_name => NULL);
 EXEC DBMS_SCHEDULER.enable (name => 'GATHER_STATS_PROG');

All commands were executed on one line. Split here for ease of viewing.

Display Stale Stats

For Schema

SELECT RPAD(owner,15,' ') Owner, 
  RPAD(table_name,35,' ') Table_Name, 
  num_rows, 
  RPAD(TO_CHAR(last_analyzed,'DD-MON-YYYY HH24:MI:SS'),35,' ') last_analyzed 
FROM dba_tab_statistics
WHERE owner IN ('&Schema')
AND stale_stats='YES'
ORDER BY owner;

For Database

-- stats.stale.sql
-- Purpose: Locked stats in the database.

set verify off
COL owner       FORMAT a15
COL table_name  FORMAT a40
COL STALE_STATS FORMAT a10

prompt 
prompt +------------+
prompt |Stale Stats |
prompt +------------+
prompt 
--spool C:\oracle\scripts\spool\stale_stats.sql.spool

-- Process
SELECT OWNER,
      TABLE_NAME,
      to_char(LAST_ANALYZED,'yyyy.mm.dd hh24:mi') as LAST_ANALYZED,
      STALE_STATS 
FROM DBA_TAB_STATISTICS 
WHERE STALE_STATS='YES'
AND owner NOT IN('SYS','SYSTEM') 
ORDER BY owner,table_name;

-- End
prompt
--spool off