5 Statistics Collection for Schema (Recommended Method)

This topic explains in the statistics collection for schema.

Oracle 19c provides a default scheduled job to collect statistics for the entire database and is default scheduled to run every night. Given that the batch as well runs in the night. It is critical that the statistics gathering is not run during the batch.

It is recommended to use the default database scheduled job that is shipped with Oracle Database to collect statistics for Schema.

Statistics collection recommendation is specific to the schema. Other available statistics like System statistics, Fixed table statistics, dictionary statistics, etc., are not part of this recommendation and are required to be executed on a need basis.

Note:

This document assumes that there is no other tool or program is scheduled to collect statistics for the Database.

Customize Default Statistics Collection Schedule

The Default Scheduler is to be customized for the following:

• Ensure that the default statistics gathering program is configured and Running.

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

  Should return - ENABLED

• Ensure that the default statistics gathering program is configured to run only on weekends.

  /* Start of Script – Script to be executed as SYS*/

  BEGIN
  DBMS_AUTO_TASK_ADMIN.ENABLE
  (CLIENT_NAME => 'auto optimizer stats collection', 
  OPERATION => NULL,
  WINDOW_NAME => 'SATURDAY_WINDOW');
  DBMS_AUTO_TASK_ADMIN.ENABLE
  (CLIENT_NAME => 'auto optimizer stats collection',
  OPERATION => NULL,
  WINDOW_NAME => SUNDAY_WINDOW');
  END;
  / 

  /* End of Script */

• The default schedule is daily. So, disable the daily schedules for optimizer statistics.

  /* Start of Script – Script to be executed as SYS*/

  BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE
  (CLIENT_NAME => 'auto optimizer stats collection',
  OPERATION => NULL,
  WINDOW_NAME => 'MONDAY_WINDOW');
  DBMS_AUTO_TASK_ADMIN.DISABLE
  (CLIENT_NAME =>'auto optimizer stats collection',
  OPERATION => NULL,
  WINDOW_NAME => 'TUESDAY_WINDOW');
  DBMS_AUTO_TASK_ADMIN.DISABLE
  (CLIENT_NAME =>'auto optimizer stats collection',
  OPERATION => NULL,
  WINDOW_NAME => 'WEDNESDAY_WINDOW');
  DBMS_AUTO_TASK_ADMIN.DISABLE
  (CLIENT_NAME => 'auto optimizer stats collection',
  OPERATION => NULL,
  WINDOW_NAME => 'THURSDAY_WINDOW');
  DBMS_AUTO_TASK_ADMIN.DISABLE
  (CLIENT_NAME => 'auto optimizer stats collection',
  OPERATION => NULL,
  WINDOW_NAME => 'FRIDAY_WINDOW');
  END;
  /

  /* End of Script */

Verify the setup using the following SQL:

SELECT WINDOW_NAME,OPTIMIZER_STATS FROM DBA_AUTOTASK_WINDOW_CLIENTS;

MONDAY_WINDOW	DISABLED
TUESDAY_WINDOW	DISABLED
WEDNESDAY_WINDOW	DISABLED
THURSDAY_WINDOW	DISABLED
FRIDAY_WINDOW	DISABLED
SATURDAY_WINDOW	ENABLED
SUNDAY_WINDOW	ENABLED

Customize Statistics Gathering

The default statistics gathering is designed to be generic. It is recommended to customize the default statistics gathering to suit online and batch.

Following are the areas that would need customization for the Oracle Banking Treasury Management:

Statistics Histograms

Configure the default statistics gathered without Histograms.

/* Start of Script – Script to be executed as SYS*/

BEGIN
DBMS_STATS.SET_PARAM (‘METHOD_OPT’,’FOR ALL COLUMNS SIZE 1’);
END;
/

/*End of Script */

Verify the setup using the following SQL:

SELECT DBMS_STATS.GET_PARAM (‘METHOD_OPT’) FROM DUAL;

Value should return as:

FOR ALL COLUMNS SIZE 1