DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure

- Info:

Automatic optimizer statistics collection gathers optimizer statistics by calling the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure. 

The GATHER_DATABASE_STATS_JOB_PROC procedure collects statistics on database objects when the object has no previously gathered statistics or the existing statistics are stale because the underlying object has been modified significantly (more than 10% of the rows).

The DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC is an internal procedure, but it operates in a very similar fashion to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER AUTO option. 

The primary difference is that the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure prioritizes the database objects that require statistics, so that those objects which most need updated statistics are processed first. 

This ensures that the most-needed statistics are gathered before the maintenance window closes.

- Check Current Status:

SELECT client_name, status FROM DBA_AUTOTASK_CLIENT;

CLIENT_NAME                         STATUS
----------------------------------- --------------------------------
auto optimizer stats collection     DISABLED
auto space advisor                  DISABLED
sql tuning advisor                  DISABLED

- To Enable/Disable Auto Task for Gathering Statistics:

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

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

Gathering Statistics Job uses CPU
Begin Snap:      1519 26-Jan-21 22:00:44       144       9.7
  End Snap:      1520 26-Jan-21 23:00:12       144      10.5
   Elapsed:               59.46 (mins)
   DB Time:               10.90 (mins)

Top 10 Foreground Events by Total Wait Time

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                                           Total Wait       Avg   % DB Wait
Event                                Waits Time (sec)      Wait   time Class
------------------------------ ----------- ---------- --------- -----
DB CPU                                          478.8            73.2
db file sequential read             15,574       44.9    2.88ms    6.9 User I/O
local write wait                     9,439       36.2    3.83ms    5.5 User I/O
log file sync                       14,062       33.6    2.39ms    5.1 Commit
db file scattered read               3,023       16.1    5.32ms    2.5 User I/O
direct path read                     3,796       14.7    3.88ms    2.3 User I/O
enq: RO - fast object reuse          1,900       12.3    6.46ms    1.9 Applicat
reliable message                     2,035        3.1    1.54ms     .5 Other
PGA memory operation               122,899        3.1   25.03us     .5 Other
enq: TM - contention                    10        2.8  278.76ms     .4 Applicat

Operating System Statistics                 DB/Inst: IGT/igt  Snaps: 1519-1520

-> *TIME statistic values are diffed.

   All others display actual values.  End Value is displayed if different

-> ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name

Statistic                                  Value        End Value
------------------------- ---------------------- ----------------
FREE_MEMORY_BYTES                    376,426,496      192,045,056
INACTIVE_MEMORY_BYTES              3,918,254,080    3,863,429,120
SWAP_FREE_BYTES                   12,415,664,128   12,394,430,464
BUSY_TIME                                464,704
IDLE_TIME                                879,888
IOWAIT_TIME                               25,893
NICE_TIME                                  4,948
SYS_TIME                                  57,235
USER_TIME                                377,969
LOAD                                          13                2
VM_IN_BYTES                           20,910,080
VM_OUT_BYTES                          36,524,032
PHYSICAL_MEMORY_BYTES             10,303,094,784
-> %CPU   - CPU Time      as a percentage of Elapsed Time
-> %IO    - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for   68.1% of Total DB Time (s):             654
-> Captured PL/SQL account for  176.0% of Total DB Time (s):             654
        Elapsed                  Elapsed Time
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
           875.1              1        875.11  133.8   64.9   28.8 b6usrg82hwsa3
Module: DBMS_SCHEDULER
call dbms_stats.gather_database_stats_job_proc ( )

-> Total FG Wait Time:               176.98 (s)  DB CPU time:         478.76 (s)
                                     %Time      Total Wait
Wait Class                     Waits -outs        Time (s)   Avg wait  %DB time
-------------------- --------------- ----- --------------- ---------- ---------
DB CPU                                                 479                 73.2
User I/O                      32,124     0             113     3.52ms      17.3
Commit                        14,062     0              34     2.39ms       5.1
Application                    1,934     0              15     7.79ms       2.3
Other                        125,566     0               9    71.05us       1.4
Concurrency                      339     0               3     9.03ms       0.5
Network                    1,445,391     0               2     1.51us       0.3
System I/O                     1,200     0               1   558.07us       0.1
Configuration                     25   100               0    17.23ms       0.1
                          ------------------------------------------------------
SQL ordered by CPU Time                     DB/Inst: IGT/igt  Snaps: 1519-1520
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> %Total - CPU Time      as a percentage of Total DB CPU
-> %CPU   - CPU Time      as a percentage of Elapsed Time
-> %IO    - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for   51.0% of Total CPU Time (s):             479
-> Captured PL/SQL account for  148.3% of Total CPU Time (s):             479
    CPU                   CPU per           Elapsed
  Time (s)  Executions    Exec (s) %Total   Time (s)   %CPU    %IO    SQL Id
---------- ------------ ---------- ------ ---------- ------ ------ -------------
     568.3            1     568.33  118.7      875.1   64.9   28.8 b6usrg82hwsa3
Module: DBMS_SCHEDULER
call dbms_stats.gather_database_stats_job_proc ( )
      82.3           59       1.40   17.2      138.7   59.4   26.9 av6f85bw3c5v7
Module: JDBC Thin Client

Example:

COL CLIENT_NAME for A40

COL STATUS for A20

SELECT client_name, status FROM DBA_AUTOTASK_CLIENT;

CLIENT_NAME                          STATUS
------------------------------------ ------------------------
sql tuning advisor                   ENABLED
auto optimizer stats collection      ENABLED
auto space advisor                   ENABLED

SQL> 

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

PL/SQL procedure successfully completed.

SQL> SELECT client_name, status FROM DBA_AUTOTASK_CLIENT;

CLIENT_NAME                          STATUS
------------------------------------ ------------------------
sql tuning advisor                   ENABLED
auto optimizer stats collection      DISABLED
auto space advisor                   ENABLED

In case DBMS_STATS.GATHER_DATABASE_STATS_JOB is slow and using a lot of CPU, it might be because of missing dictionary stats.

 

SOLUTION: Gather dictionary stats and fixed object stats by executing following: 

EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');

EXEC DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

To see the jobs:

SELECT * FROM DBA_SCHEDULER_JOBS 

 WHERE job_action LIKE '%collect%';

ORACLE_OCM.MGMT_CONFIG.collect_stats

ORACLE_OCM.MGMT_CONFIG.collect_config

Run auto optimizer stats manually

BEGIN

  DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;

END;

/

See the history runs for the auto optimizer stats task

SELECT * FROM  DBA_AUTOTASK_JOB_HISTORY 

 WHERE client_name LIKE 'auto optimizer stats%' 

   AND job_start_time > SYSDATE -30

 ORDER BY job_start_time DESC;

CLIENT_NAME                     WINDOW_NAME     WINDOW_START_TIME   WINDOW_DURATION     JOB_NAME                JOB_STATUS  JOB_START_TIME      JOB_DURATION JOB_ERROR JOB_INFO        

------------------------------- --------------- ------------------- ----------------    ---------------------   ----------- ------------------- ------------ --------- ---------

auto optimizer stats collection SUNDAY_WINDOW 20260208 06:00:00 0 20:0:0.184801000 ORA$AT_OS_OPT_SY_37361 SUCCEEDED 20260208 22:05:05 0 0:13:8.0 0

auto optimizer stats collection SUNDAY_WINDOW 20260208 06:00:00 0 20:0:0.184801000 ORA$AT_OS_OPT_SY_37358 SUCCEEDED 20260208 18:04:54 0 0:14:16.0 0

auto optimizer stats collection SUNDAY_WINDOW 20260208 06:00:00 0 20:0:0.184801000 ORA$AT_OS_OPT_SY_37355 SUCCEEDED 20260208 14:04:44 0 0:12:38.0 0

auto optimizer stats collection SUNDAY_WINDOW 20260208 06:00:00 0 20:0:0.184801000 ORA$AT_OS_OPT_SY_37352 SUCCEEDED 20260208 10:04:29 0 0:14:44.0 0

auto optimizer stats collection SUNDAY_WINDOW 20260208 06:00:00 0 20:0:0.184801000 ORA$AT_OS_OPT_SY_37332 SUCCEEDED 20260208 06:00:03 0 0:56:38.0 0

auto optimizer stats collection SATURDAY_WINDOW 20260207 06:00:00 0 20:0:0.35269000 ORA$AT_OS_OPT_SY_37321 SUCCEEDED 20260207 22:03:44 0 0:52:16.0 0

auto optimizer stats collection SATURDAY_WINDOW 20260207 06:00:00 0 20:0:0.35269000 ORA$AT_OS_OPT_SY_37318 SUCCEEDED 20260207 18:03:25 0 0:13:13.0 0

auto optimizer stats collection SATURDAY_WINDOW 20260207 06:00:00 0 20:0:0.35269000 ORA$AT_OS_OPT_SY_37315 SUCCEEDED 20260207 14:03:13 0 0:10:56.0 0