Oracle DB automatic collect statistics

1.      Check Oracle DB automatic collect statistics or not:

 

SELECT t.CLIENT_NAME, t.TASK_NAME, t.OPERATION_NAME,t.STATUS  FROM dba_autotask_task t;

2.      check the Scheduler programs

SELECT t.PROGRAM_NAME, t.PROGRAM_ACTION, ENABLED  FROM dba_scheduler_programs t where t.PROGRAM_NAME ='GATHER_STATS_PROG';    

 

3.      check each MAINTENANCE window:

SELECT t1.window_name, t1.repeat_interval, t1.duration,t1.enabled,t1.LAST_START_DATE,t1.NEXT_START_DATE FROM dba_scheduler_windowst1,  dba_scheduler_wingroup_members t2 WHERE  t1.window_name =t2.window_name  AND  t2.window_group_name ='MAINTENANCE_WINDOW_GROUP'

                

4.      check Job historical information, each timeGATHER_STATS_PROG always generates ORA$AT_OS_OPT_XXX jobs, then execute it.

 

SELECT  a.job_name, a.actual_start_date, a.status FROM (SELECT *   FROM dba_scheduler_job_run_detailst         where t.JOB_NAME like'ORA$AT_OS_OPT_%'    order by t.LOG_DATE desc) a  whererownum <= 4;

 

5.       getglobal statistics preferences by using dbms_stats.get_prefs

 

For instance,check the value of 'ESTIMATE_PERCENT':

select dbms_stats.get_prefs('ESTIMATE_PERCENT') from dual;

DBMS_STATS.AUTO_SAMPLE_SIZE:means in 11g Oracle will generate estimate sample size of 100% for the table (ifit is possible for this to fit within the maintenance window), If this 100%sample is not feasible, then try using at least an estimate of 30%, howeversince 11g uses a hashing algorithm to compute the statistic, performance shouldbe acceptable in most cases.

 

Check SAMPLE_SIZE isreally 100% or not

select TABLE_NAME,NUM_ROWS, SAMPLE_SIZE from ALL_TAB_STATISTICS where owner='FSR_USER';

Currently, estimatesample size is of 100% for the table

 

 

Refer https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_STATS.html#GUID-6429FC41-52D7-4541-9242-2AC015266D6Fto get what parameters we can use in  dbms_stats.get_prefs.

 

6.       check objects level of statistics preferences thorough table ALL_TAB_STAT_PREFS or commands

 select dbms_stats.get_prefs('STALE_PERCENT',null,'TCDS_ABI_COMMIT') from dual;

eg. STALE_STATS=’YES’ didn’t meanthe object hasn’t been analyzed.

 

STALE_PERCENT - Determines thepercentage of rows in a table that have to change before the statistics on thattable are deemed stale and should be regathered. The valid domain for stale_percent is non-negative numbers.The default value is 10%. Notethat if you set stale_percent to zerothe AUTO STATS gatheringjob will gather statistics for this table every time a row in the table ismodified.

 

If a monitored table has been modified morethan 10%, then these statistics are considered stale and gathered again.

Check the value of 'STALE_PERCENT’:

selectdbms_stats.get_prefs('STALE_PERCENT') from dual;