oracledba.help

Statistics Gathering

<- SpecialTopics

TOC

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              ENABL
 ---------------- ------------------------- -----
 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';

Manually Run Job

 exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;

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

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;

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;
/
 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

Misc

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'
 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;