Its version 19.9.0.0.0 of Oracle. We have auto stats gathering jo runs each day but it fails to complete within the window of set time i.e ~4hours. On some days(mostly sundays) it succeeds by running ~8-9hrs. So we want to find out what exact tables are consuming most of the time during this stats gather and if we can tweak them some way to make them happen faster. We do have partition and non partition tables in our database. The DB resource utilization seems to be staying <30% during this stats job run , so thinking if we can someway make the process faster by using more resources and make it finish withng set ~4hours window?
We are seeing in dba_optstat_operations only start and end time of the auto job is noted but no information is available regarding the breakup of how-much time each partition/non partition table is consuming during this 8-9hrs period. So wanted to know from experts, if there is some way to break it up and then work towards making it faster?