GATHER_DATABASE_STATS_JOB_PROC

The GATHER_DATABASE_STATS_JOB_PROC procedure is used to automatically collect statistics for all tables in all databases of the entire tenant. This procedure is typically called by the automatic statistics collection task within the maintenance window.

Syntax

DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC (
  duration             NUMBER DEFAULT 0
);

Parameters

Exceptions

Usage notes

• This procedure requires the SYSDBA privilege to be called.
• In automatic statistics collection scenarios, the duration parameter of this procedure is typically set through DBMS_SCHEDULER.SET_ATTRIBUTE using JOB_ACTION, rather than being called directly.
• When adjusting the duration, you need to modify both the duration of the maintenance window (in seconds) and the duration of this procedure (in microseconds). The two must be converted to the same target duration separately. For detailed operations on adjusting the automatic statistics collection duration, see Automatic statistics collection.

Examples

• Set the maximum runtime of the statistics collection task for the Wednesday maintenance window to 8 hours (28800000000 microseconds).

  CALL DBMS_SCHEDULER.SET_ATTRIBUTE(
    'WEDNESDAY_WINDOW',
    'JOB_ACTION',
    'DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(28800000000)'
  );
  

• Set the maximum runtime of the statistics collection task for the Monday maintenance window to 12 hours (43200000000 microseconds).

  CALL DBMS_SCHEDULER.SET_ATTRIBUTE(
    'MONDAY_WINDOW',
    'JOB_ACTION',
    'DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC(43200000000)'
  );