Autotask 'auto optimizer stats collection' and scheduler jobs logging

Question

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> 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> 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> select count(*) from sysadm.ps_job;  

 COUNT(*)  
----------  
   180794  

In DBA_AUTOTASK_JOB_HISTORY or DBA_SCHEDULER_JOB_RUN_DETAILS views the analyze-table jobs "ORA$AT_OS_OPT_SY_nnnnn" 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:

• 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...)

• 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

Answer 1

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.