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