3

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

5
  • What is your Oracle version? Are there any errors in the alert log about stats gathering? Apr 13, 2021 at 7:50
  • Version is 18c.
    – Seb
    Apr 13, 2021 at 11:52
  • Simplest explanation: statistics are locked on those tables. Check STATTYPE_LOCKED column in DBA_TAB_STATISTICS. Apr 14, 2021 at 18:13
  • No they're not. I checked that of course before posting here.
    – Seb
    Apr 15, 2021 at 7:26
  • The built in stats job writes a trace file but I do not believe that it shows why it makes the choices it does. You could disable the built in job and write your own stats gathering job and then you would have full control over the logic that it uses. A lot of the PeopleSoft tables are empty and unused unless you are using every feature but PS_JOB is not one of those obviously. Do you have evidence that the older statistics are causing a performance problem? Apr 15, 2021 at 15:13

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.