Autotask 'auto optimizer stats collection' and scheduler jobs logging We have a DB hosting Peoplesoft schema, with more than 33.000 tables. Since
december Oracle has left more than 18 thousand of them with no statistics
renewed:
SQL&gt; select to_char(last_analyzed,'YYYYMM') ,count(*) from dba_tables where
   2 owner='SYSADM' group by to_char(last_analyzed,'YYYYMM') order by 1;
TO_CHA  COUNT(*)
------ ----------
202012     18369
202101        23
202103        14
202104       832
           14490
Yet if I check one of them (as an example) within these 18369 tables, I find
it has been modified way above the 10% default threshold for stats
recomputation; select from dba_tab_modifications shows:
TABLE_NAME             INSERTS   UPDATES   DELETES TIMESTAMP
-------------------- ---------- ---------- ---------- ---------
PS_JOB                    8063    181375       395 12-APR-21
SQL&gt; select t.table_name,ts.stale_stats,
   2 t.monitoring,t.last_analyzed,t.num_rows from dba_tables t,dba_tab_statistics
   3 ts where t.owner=ts.owner and t.table_name='PS_JOB' and
   4 ts.table_name='PS_JOB';
TABLE_NAME          STALE_S MON LAST_ANAL  NUM_ROWS
-------------------- ------- --- --------- ----------
PS_JOB              YES    YES 08-DEC-20    162039
SQL&gt; select count(*) from sysadm.ps_job;
 COUNT(*)
----------
   180794
In DBA_AUTOTASK_JOB_HISTORY or DBA_SCHEDULER_JOB_RUN_DETAILS views the
analyze-table jobs &quot;ORA$AT_OS_OPT_SY_nnnnn&quot; never take more than 35 to 40
minutes.
Yet this table is skipped from all stats recomputations that occur every day...
I have 2 questions:
o Someone knows if internal process doing stats recomputing job (i.e.
procedure DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC, which is not documented)
is logged somewhere with relevant details ?? (I'm very surprised that, given
the amount of tables this schema contains, it only takes a little more than
half an hour to complete...)
o What are conditions, or tests performed by DBMS_STATS in the background to
yield list of tables of which statistics should be recalculated ?? (why this
PS_JOB table hasn't been recomputed yet since Dec, 8th ??, in spite of its
growth)
In advance - thanks a lot.
Regards,
Sebino
Try
select sample_size from dba_tables where table_name='PS_JOB';

What does that return?
If that number is not as expected, is it possible another DBA used something like DBMS_STATS.SET_GLOBAL_PREFS
in this way for example:
EXEC DBMS_STATS.set_global_prefs('estimate_percent', '20');

instead of using the default of DBMS_STATS.AUTO_SAMPLE_SIZE? (which may be more than 10 itself)
That figure of 20 would change it from the traditional default of 10.  However AUTO_SAMPLE_SIZE will have Oracle determine the value and that may have been set, and Oracle chose a value larger than 10.
I am also in the dark about 'auto optimizer stats collection' and exactly how it has been working internally since 11g.  We have some windows though (8 total) that run for longer than 3 hours of stats gathering on our production databases.