Gathering system, data dictionary and fixed object statistics – updated for Oracle 19c

The following script can be used for gathering data dictionary statistics, fixed object statistics and, when specifically required, system statistics.

Update for Oracle Database 19c: The recommendations regarding system statistics have changed somewhat since this post was originally written.

For Oracle 19c, Oracle recommends using the default system statistics in most cases. System statistics affect the costing of every SQL statement in the database, and manually gathered workload statistics can therefore result in execution plan changes across the database.

Consequently, I would not routinely gather workload system statistics on an Oracle 19c database. The system statistics part of the script below is kept as an example, but should only be used when there is a specific reason for doing so and the gathering interval represents the normal workload.

The data dictionary and fixed object statistics are separate from system statistics. Fixed object statistics are still important, but should be gathered when the database has a representative workload, particularly after an upgrade or significant database configuration changes.

alter session set nls_language='AMERICAN';

set timing on
set serveroutput on
set lines 200

col pname format a20
col sname format a20


PROMPT Current system statistics

SELECT sname, pname, pval1, pval2
FROM sys.aux_stats$;


PROMPT
PROMPT ------------------------------------------------------------
PROMPT System statistics
PROMPT ------------------------------------------------------------
PROMPT
PROMPT Oracle 19c: Do NOT gather workload system statistics routinely.
PROMPT Oracle recommends using the default system statistics in most cases.
PROMPT The following is therefore an example only.
PROMPT

/*
PROMPT Gather workload system stats, sample for 1 hour

BEGIN
   DBMS_STATS.GATHER_SYSTEM_STATS (
      gathering_mode => 'INTERVAL',
      interval       => 60,
      statid         => 'DAYTIME');
END;
/

SELECT pname, pval1
FROM sys.aux_stats$;
*/


PROMPT
PROMPT ------------------------------------------------------------
PROMPT Gather data dictionary statistics
PROMPT ------------------------------------------------------------

BEGIN
   DBMS_STATS.GATHER_DICTIONARY_STATS (
      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
      method_opt       => 'FOR ALL COLUMNS SIZE AUTO',
      degree           => NULL,
      cascade          => DBMS_STATS.AUTO_CASCADE,
      granularity      => 'AUTO',
      no_invalidate    => DBMS_STATS.AUTO_INVALIDATE);
END;
/


PROMPT
PROMPT ------------------------------------------------------------
PROMPT Gather fixed object statistics
PROMPT ------------------------------------------------------------
PROMPT Run this while the database has a representative workload.
PROMPT

BEGIN
   DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/


exit

Oracle 19c note: If SREADTIM, MREADTIM, CPUSPEED and MBRC are NULL in SYS.AUX_STATS$, while values such as CPUSPEEDNW, IOSEEKTIM and IOTFRSPEED are populated, the database is using the normal noworkload/default system statistics. There is normally no reason to replace these simply because they are old.

Workload system statistics gathered with GATHER_SYSTEM_STATS('INTERVAL') or GATHER_SYSTEM_STATS('START')/('STOP') should be treated as a deliberate optimizer tuning change rather than normal statistics maintenance.