- 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
------------------------- ---------------------- ----------------
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
------------------------------------ ------------------------
sql tuning advisor ENABLED
auto optimizer stats collection ENABLED
auto space advisor ENABLED
SQL>
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/
------------------------------------ ------------------------
sql tuning advisor ENABLED
auto optimizer stats collection DISABLED
auto space advisor ENABLED
