hi Team,

greetings!!

hope all of your doing good :) . really appreciate your efforts in helping the entire oracle community :).

the scenarios I have is as follows:

In our database the stats gather process is taken care by GATHER_STATS_JOB job which automatically starts and ends based on the maintenance window timings.

below are my questions:

1.) how can I figure out the start time and end time of stats gather for a particular object (so far I can only figure out the start and end time of the stats gather job). do we need to enable debugging or perform similar activity.

2.) in the database the "degree" parameter of parallelism is set as AUTO. so how much parallel will oracle allow. is it decided based on the load on the server at that point and some other factors?

3.) the parameter AUTOSTATS_TARGET is set as AUTO
I got the below information from the oracle documentation:

The GATHER_STATS_JOB job 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).

but in our database we have some tables for which the modification is more than 10% but still stats are not being gathered. can you please help me understand the cause behind this ?

4.) how is the priority decided? what factors decide the priority is it ?is it someting like when underlying object has been modified significantly (more than 10% of the rows).

I got the below information from oracle documentation.

DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC is an internal procedure,
but its operates in a very similar fashion to the DBMS_STATS.GATHER_DATABASE_STATS procedure using the GATHER A
UTO 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.


Thanks
Abhishek

select table_name, last_analyzed ,
       (last_analyzed - lag(last_analyzed) over (order by last_analyzed)) * 86400 time_in_s
from   dba_tables
where  last_analyzed > sysdate - 1
order  by 1 desc, table_name;

select (m.inserts + m.updates + m.deletes) / t.num_rows
from   user_tab_modifications m
join   user_tables t
on     t.table_name = m.table_name;